Avoiding SQL injection in rails app.

SQL injection is the #1 security problem in many web applications. How does it work? If the web application includes strings from unreliable sources (usually form parameters) in SQL statements and doesn't correctly quote any SQL meta characters like backslashes or single quotes, an attacker can change WHERE conditions in SQL statements, create records with invalid data or even execute arbitrary SQL statements.

Imagine a webmail system where a user can select a list of all the emails with a certain subject. A query could look like this:

Email.find(:all, "owner_id = 123 AND subject = '#{params[:subject]}'")

This is dangerous. Imagine a user sending the string ' OR 1 -- in the parameter 'subject'; the resulting statement will look like this:

Email.find(:all, "owner_id = 123 AND subject = '' OR 1 --'")

Because of “OR 1” the condition is always true. The part “--” starts a SQL comment; everything after it will be ignored. The result: the user will get a list of all the emails in the database.

(Of course the owner_id would have to be inserted dynamically in a real application; this was omitted to keep the examples as simple as possible.)

If the argument for find_all is an array instead of a string, ActiveRecord will insert the elements 2..n of the array for the ?placeholders in the element 1, add quotation marks if the elements are strings, and quote all characters that have a special meaning for the database adapter used by the Email model.

If you don't like the syntax of the array, you can take care of the quoting yourself by calling the quote_value method of the model class. You have to do this when you use find_by_sql, as the Array argument doesn't work there:

Email.find_by_sql("SELECT * FROM email WHERE owner_id = 123 AND subject = #{Email.quote_value(subject)}")

The quotation marks are added automatically by Email.quote_value if the argument is a string.

RoR allows the use of prepared statements (or at least something that looks like prepared statements) which you can use to avoid SQL injection problems. The syntax is:
Blog.find_all [ "category = ?", category ]
As you can see, you simply put the a question mark where you want the parameters to be inserted just like when using prepared statements in say Java. You then specify the parameters as a comma separated list after the query. Neat stuff.

If you need to execute a query with the similar options in several places in your code, you should create a model method for that query. Instead of

emails = Email.find(:all, ["subject = ?", subject])

you could define the following class method in the model:

class Email < ActiveRecord::Base
  def self.find_with_subject(subject)
    Email.find(:all, ["subject = ?", subject])
  end
end

and call it like this:

emails = Email.find_with_subject(subject)

This has the advantage that you don't have to care about meta characters when using the function find_with_subject. Generally you should always make sure that this kind of model method can not break anything, even if it is called with untrusted arguments.

More information
If you want more information on these topics, make sure you read the About "Securing your Rails application" on rubyonrails.com