SQL Injection and ActiveRecord

Question

Is this safe from SQL injection:

Guest.where(:event_id => params[:id])

I am sending in params[:id] without doing any type of sanitization.

and in general, are all of those activerecord method safe? (like where, joins, etc..)

And if not, what is the best practise to be safe? Also, please is there any caveats/edge cases I should be aware of?

Thanks

Answer 1

If you really need to use raw sql, you could use quote to prevent SQL injection

Here is an example that has been copied from here

conn = ActiveRecord::Base.connection
name = conn.quote("John O'Neil")
title = conn.quote(nil)
query = "INSERT INTO users (name,title) VALUES (#{name}, #{title})"
conn.execute(query)

Answer 2

All of ActiveRecord's query-building methods, like where, group, order, and so on, are safe against SQL injection AS LONG AS you do not pass them raw SQL strings. This is vulnerable to SQL injection:

 Model.where("event_id = #{params[:id]}")

When you pass a string to a query-building method like that, the string will be inserted directly into the generated SQL query. This is useful sometimes, but it does raise the danger of an injection vulnerability. On the other hand, when you pass a hash of values, like this:

 Model.where(event_id: params[:id])

...then AR automatically quotes the values for you, protecting you against SQL injection.

Answer 3

Yes, your code is safely being cleansed before it is run on the database. Rails protects you from sql injection by automatically sanitizing input.

THE EXCEPTION is string interpolation:

Guest.where("event_id = #{params[:id]}") # NEVER do this

Use one of these 2 options instead:

Guest.where(:event_id => params[:id]) # if you want pure ruby, use this
# OR
Guest.where("event_id = ?", params[:id]) # if you prefer raw SQL, use this

Check out the Rails Guide on security for more information related to sql injection as well as other common attacks.