How does using ? help against SQL injection in Rails?

user_7410602 0
Viewed 80

I have this code in my Rails application that I'm not really sure how it works.

@dogs = Dog.where(breed: search_breed).where(<<-SQL, search_color, search_name)
  color = ?
  AND name = ?
SQL

In the SQL portion, how is putting the search_color and search_name inside the SQL start and using ? inside the SQL different/better than this?

@dogs = Dog.where(breed: search_breed).where(<<-SQL)
 color = search_color
 AND name = search_name
SQL

Thank you.

ruby-on-rails sql
3 Answers

There's really no reason to use raw SQL here. That code seems really confused and should be:

@dogs = Dog.where(
  breed: search_breed,
  color: search_color,
  name: search_name
)

If you're using AND that's how where works by default. That will take care of properly escaping everything for you

Injections occur when you make the mistake of doing:

Dog.where("breed = #{search_breed}") # WRONG!

Which you really do not want to ever do.

The safe alternative is this:

Dog.where("breed=?", search_breed)

In this case the database driver substitutes ?, which is called a placeholder value with the properly escaped search_breed value. Properly escaped here means "avoids SQL injection problems".

user_871890

Because without the placeholder, your query string is not escaped. If malicious user send drop table command to your db, its gone.

Client.where("first_name LIKE '%#{params[:first_name]}%'")

This is opened to sql injection. Any input from user is taken as is by your db.

'John'; DROP TABLE clients;--

Use placeholder to guard against injection.

user_84268910

To build on previous answers, SQL injection can be very dangerous for a number of reasons. Injection attacks enable sneaky users to read or manipulate data in your database via the "OR 1.." statement.

Lets say you have an application in which there are many reports you can search. While certain reports should remain hidden to the public, you allow all users to access it like so in your show action:

reports_controller.rb

Reports.where("id = '#{params[:id]}'")

In this format the ' never gets escaped. This allows a user to set attributes directly in your database with an OR 1 statement, a malicious user can generate a query something like: 

SELECT * FROM reports WHERE name = '' OR 1--'

Which would in fact return all records in the system to the user. Including ones that shouldn't be visible to them!

Another example would be bypassing the authentication system. If you validate login credentials something like so:

User.find_by("login = '#{params[:name]}' AND password = '#{params[:password]}'")

If the user inputs ' OR '1'='1 as the login value, and ' OR '2'>'1 as the password, the query will look something like:

SELECT * FROM users WHERE login = '' OR '1'='1' AND password = '' OR '2'>'1' LIMIT 1

Then not only would return the first user in the db, but it would also grant full access to the malicious user. But if you use this format: 

User.find_by("login = ? AND password = ?", login_info, password_info).first

And what that will do is escape the ' in the SQL statement so a user cannot exploit the query. You can use the short-hand hash form:

User.find_by(login: login_info, password: password_info)

and your query will still be protected.

Reference: http://guides.rubyonrails.org/security.html#sql-injection a very helpful section with more examples/details on this topic.

user_64326580
Related