0

I'm trying to understand SQL Injection. It seems like people can get pretty creative. Which gets me wondering about my search-based rails webapp I'm making.

Suppose I just fed user-entered information directly into the "where" statement of my SQL query. How much damage could be done to my database by allowing this?

def self.search(search)
  if search
    includes(:hobbies, :addresses).where(search)
  else
    self.all
  end

So basically, whatever the user types into the search bar on the home page gets fed straight into that 'where' statement.

An example of a valid 'search' would be:

"hobby LIKE ? OR (gender LIKE ? AND hobby LIKE ?)", "golf", "male", "polo"

Does the fact that it's limited to the context of a 'where' statement provide any sort of defense? Could they still somehow perform delete or create operations?

EDIT:

When I look at this tutorial, I don't see a straightforward way to perform a deletion or creation action out of the where clause. If my database contains no information that I'm not willing to display from a valid search result, and there's no such thing as user accounts or admin privileges, what's really the danger here?

ineedahero
  • 488
  • 2
  • 7
  • 22
  • if you're using placeholders and prepared statements PROPERLY, then there is NO injection risk. and no, just because the injected data is in a `where` doesn't mean anything. Start reading: http://bobby-tables.com) – Marc B Oct 28 '16 at 16:37
  • There where clause is exactly the vector to attack. But when you parameterize it the vulnerability goes away. – Sean Lange Oct 28 '16 at 16:38
  • How do I parameterize it? – ineedahero Oct 28 '16 at 16:42
  • I'm confused, you expect the user to enter the string `"hobby LIKE ? OR (gender LIKE ? AND hobby LIKE ?)"`? – user229044 Oct 28 '16 at 17:02
  • A good reference: http://rails-sqli.org/ – messanjah Oct 28 '16 at 17:03
  • Good catch meagar, see the update – ineedahero Oct 28 '16 at 17:06
  • Thanks messanjah -- I've looked at that tutorial before. In fact, it was that which led me to believe the 'where' provides safety since I didn't (and still don't) see how they can delete from it. There is no sensitive data that I'm not willing to reveal from a search, so what's the real danger here? – ineedahero Oct 28 '16 at 17:10

1 Answers1

0

I took this from another post here: Best way to go about sanitizing user input in rails

TL;DR Regarding user input and queries: Make sure to always use the active record query methods (such as .where), and avoid passing parameters using string interpolation; pass them as hash parameter values, or as parameterized statements.

Regarding rendering potentially unsafe user-generated html / javascript content: As of Rails 3, html/javascript text is automatically properly escaped so that it appears as plain text on the page, rather than interpreted as html/javascript, so you don't need to explicitly sanitize (or use <%= h(potentially_unsafe_user_generated_content)%>

If I understand you correctly, you don't need to worry about sanitizing data in this manner, as long as you use the active record query methods correctly. For example:

Lets say our parameter map looks like this, as a result of a malicious user inputting the following string into the user_name field:

:user_name => "(select user_name from users limit 1)" The bad way (don't do this):

Users.where("user_name = #{params[:id}") # string interpolation is bad here The resulting query would look like:

SELECT users.* FROM users WHERE (user_name = (select user_name from users limit 1)) Direct string interpolation in this manner will place the literal contents of the parameter value with key :user_name into the query without sanitization. As you probably know, the malicious user's input is treated as plain 'ol SQL, and the danger is pretty clear.

The good way (Do this):

Users.where(id: params[:id]) # hash parameters OR

Users.where("id = ?", params[:id]) # parameterized statement The resulting query would look like:

SELECT users.* FROM users WHERE user_name = '(select user_name from users limit 1)' So as you can see, Rails in fact sanitizes it for you, so long as you pass the parameter in as a hash, or method parameter (depending on which query method you're using).

The case for sanitization of data on creating new model records doesn't really apply, as the new or create methods are expecting a hash of values. Even if you attempt to inject unsafe SQL code into the hash, the values of the hash are treated as plain strings, for example:

User.create(:user_name=>"bobby tables); drop table users;") Results in the query:

INSERT INTO users (user_name) VALUES ('bobby tables); drop table users;') So, same situation as above.

I hope that helps. Let me know if I've missed or misunderstood anything.

Edit Regarding escaping html and javascript, the short version is that ERB "escapes" your string content for you so that it is treated as plain text. You can have it treated like html if you really want, by doing your_string_content.html_safe.

However, simply doing something like <%= your_string_content %> is perfectly safe. The content is treated as a string on the page. In fact, if you examine the DOM using Chrome Developer Tools or Firebug, you should in fact see quotes around that string.

Community
  • 1
  • 1
ruby_newbie
  • 3,190
  • 3
  • 18
  • 29