Recipe 11.2. Protecting Queries from SQL Injection

Problem

You want to eliminate the possibility of malicious users tampering with your database queries.

Solution

Use Active Record's bind variable support to sanitize strings that become part of your application's SQL statements. Consider the following method, which queries your database for user records based on an id parameter:

def get_user   @user = User.find(:first, :conditions => "id = #{params[:id]}") end

If params[:id] contains an integer, as you hope it will, the statement works as expected. But what if a user passes in a string like "1 OR 1=1"? Interpolating this string into the SQL generates:

SELECT * FROM users WHERE (id = 1 OR 1=1)

This SQL statement selects all users because of the Boolean OR and the condition "1=1", which is always true. The call to find returns only one user (because of the :first parameter), but there's no guarantee it will be the user with an id of 1. Instead, the result depends on how the database has ordered records in the table internally.

The following version of get_user avoids this kind of SQL tampering using a bind variable:

def get_user   @user = User.find(:all, :conditions => [ "id = ?", params[:id] ]) end

Now, passing "1 OR 1=1" into the call to find produces the following SQL:

SELECT * FROM users WHERE (id = '1 OR 1=1')

In this version, id is being compared to the entire string, which the database attempts to cast into a number. In this case, the string "1 OR 1=1" is cast into just 1, resulting in the user with that id being retrieved from the users table.

Discussion

SQL injection is one of the most common methods of attacking web applications. The results of such an attack can be extreme and result in the total destruction or exposure of your data. Your best defense against SQL injection is to filter all potentially tainted input and escape output (e.g., what is sent to your database).

You should use bind variables whenever possible to guard against this kind of attack. Even if you don't expect a method to receive input from an untrusted source (e.g., users), treating every database query with the same amount of caution will avoid security holes becoming exposed later, as your code is used in new and unanticipated ways.

See Also

• Section 11.3"