SQL Injection issue in :limit and :offset parameter

An SQL Injection vulnerability has been found in Rails. The issue affects Rails < 2.1.1, namely the :limit and :offset parameters that are not correctly sanitized:

Person.find(:all, :limit => “10; DROP TABLE users;”)

A possible attack will work only if you allow the user control these two values as in User.find(:all, :limit => 10, :offset => params[:offset]). Note that will_paginate is not affected, it escapes the values before.

This seemed to affect only PostgreSQL and SQLite as MySQL by default disallows multiple SQL statements. So you cannot drop a table. However, it could be used for information disclosure. Consider the UNION SQL statement:

User.find(:all, :limit => params[:limit])

params[:limit] #= “1 UNION (select 1,2,password,4,5,6,7,8,9,1,2,3,4,5,6,7,8,9,1,2,3,4,5,6,7,8,9,0 from users)”

What does this mean? The result is the full users table, with one small modification: One field contains the user’s password and the other fields are always a number between 0 and 9. Let’s assume the third column is the user’s first name and the application returns everything it found. This means an attacker may read the user’s password in the first name field. All he has to do is find out about the table names (take a look at the controller names), the column names (review the HTML source, guessing) and the number of columns in the table (try it). The UNION statement will work only if the second table has the same number of columns as the first one – hence the list of numbers.

Of course there might not be a password column in clear text, but this could be used to read any data from the database, or even other databases.

Countermeasures

  • Review your application whether you allow the user to control :limit or :offset
  • If you are on Rails 2.1.0, please apply this patch or get Rails 2.1.1
  • If you are on the Rails 2.0 or 1.2 branch, apply this backport patch