#25 SQL Injection

Apr 30, 2007 | 5 minutes | Active Record, Security, Forms

One of the most common security problems for dynamic sites is SQL Injection. Thankfully Rails does everything it can in solving this issue, but you still need to be aware of it.

Click to Play Video ▶

Download:
mp4Full Size H.264 Video (10.2 MB)
m4vSmaller H.264 Video (6.24 MB)
webmFull Size VP8 Video (14.7 MB)
ogvFull Size Theora Video (13 MB)

Did you really name your son Robert'); DROP TABLE Students; -- ? - XKCD No. 327

For the next few episodes we’ll talk about security and protecting your site from hackers. One of the first rules of security is to never trust input from users. In Rails this means taking care of the items in the params hash. The user can control both the keys and the values in the params hash, so all input must be considered suspicious. The same applies to the cookies hash; the user can completely control what is in it. On the other hand the session hash is only set on the server, so can be trusted.

One of the most common security issues is known as SQL injection. This happens when user input is placed directly into a SQL query. If a user knows that their input is being directly inserted into a query they can deliberately write input that can retrieve data that they should not see or even alter or destroy data in the database. We’ll explain how this works and how to protect against it with a simple example.

Task Search

Below we have a simple search form on a page that performs a SQL LIKE search to find tasks with a name that matches the input supplied in the form.

The problem is that the input supplied by the user is directly passed to the database query in our controller.

          ruby
        
class TasksController < ApplicationController
 def index
  @tasks = Task.find(:all,:conditions=>"name LIKE &rsquo;%#{params[:query]}%&rsquo;")
 end
end

The TasksController showing the potentially unsafe SQL query.

The value in the :conditions key in the find hash is passed straight to a SQL query. The problem with this is that if the user passes a parameter that contains a single quote character the rest of the statement is considered to be SQL. So, if we enter a search term containing a single quote, e.g. Task 1’TEST, the input after the quote will be executed as SQL and Rails will throw an error.

          terminal
        
Processing TasksController#index (for 127.0.0.1 at 2009-02-01 21:29:26) [GET]
  Parameters: {"query"=>"Task 1&rsquo;TEST"}
  Task Load (0.0ms)   SQLite3::SQLException: near "TEST": syntax error: SELECT * FROM "tasks" WHERE (name like &rsquo;%Task 1&rsquo;TEST%&rsquo;) 

ActiveRecord::StatementInvalid (SQLite3::SQLException: near "TEST": syntax error: SELECT * FROM "tasks" WHERE (name like &rsquo;%Task 1&rsquo;TEST%&rsquo;) ):

Invalid SQL is generated if the user input contains a single quote.

In the SQL command above we have our search term, Task 1’TEST; and we can see that the quote in the search term completes the SQL statement. The remaining part, TEST%’), is invalid SQL which is why an error is thrown. This is dangerous as it means that any SQL could be executed against the database. How do we stop this?

The answer is to escape the quotes. Rails provides an easy way to do this. We can pass an array of conditions, the first element of which is our search term with the value replaced by a question mark. Each question mark in the first element will be replaced by the value in each subsequent element, but escaped so that it is safe. If we had, say, three parameters we’d have three question marks and our array would have to contain four elements.

          ruby
        
@tasks = Task.find(:all, :conditions=> [ "name LIKE ?", "%#{params[:query]}%" ]

A safer way to pass a search term that contains user input.

Now that we’ve updated our query, if we search again for a term with a single quote in it Rails escapes the quote safely. To make sure that our LIKE search is maintained the percent signs need to be placed around the query in the second element of the array. Placing them around the question mark will not work.

          terminal
        
The SQL in our log file now looks like this.
Processing TasksController#index (for 127.0.0.1 at 2009-02-01 21:59:31) [GET]
  Parameters: {"query"=>"Task 1&rsquo;TEST"}
  Task Load (0.5ms)   SELECT * FROM "tasks" WHERE (name like &rsquo;%Task 1&rsquo;&rsquo;TEST%&rsquo;)

The search term is now safely escaped.

We can see now that the quote is escaped. Note that as we’re using Sqlite the quote is escaped by using two single quotes. Other databases may use a backslash before the quote.

In Rails you only need to worry about escaping input in find methods if you’re using the :conditions parameter. If you’re using the dynamic find_by methods as discussed back in episode 2 then Rails will automatically escape any input which will ensure that you’re safe from SQL injection.