« The updated Rails Security Guide | Main | DoS vulnerability in REXML »
Monday
Sep082008

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

PrintView Printer Friendly Version

EmailEmail Article to Friend

References (54)

References allow you to track sources for this article, as well as articles that were written in response to this article.
  • Response
    Response: photographs
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    NFL is seriously 1 of the most significant sports in America. It has a key following.
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Response: Arts
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Response: try this out
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Response: Agathe
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Response: Amie
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Response: Annarbor
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Response: 42nd Street Photo
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Response: taftiha.ir
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Response: ifwet.org.uk
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Response: Violetta
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Response: www.corpfox.com
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Response: teachipr.com
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Response: exchange trading
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Response: www.petshop.com.tw
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Response: Zita
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Response: dumpsters
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Response: good blog sites
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Response: cash for junk cars
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Response: Omega 3 Fish Oil
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Response: fish oil pills
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection issue in :limit and :offset parameter

Reader Comments (5)

<p>I pointed this out in the comments of your sql injection post over a year ago. I guess no one read it.</p><p>http://www.rorsecurity.info/2007/05/19/sql-injection/</p>

September 8, 2008 | Unregistered Commenterbob

<p>Another best practice which helps with sql injection attacks is to give your database user account as few permissions as possible.
If the account is not allowed to drop tables, then that's one less avenue for sql injection to exploit.</p>

September 8, 2008 | Unregistered CommenterPeter Weitzman

[...] SQL Injetion issue in :limit and :offset parameter - A Rails security issue worth paying a bit of attention to. [...]

September 9, 2008 | Unregistered CommenterA Fresh Cup » Blog Archi

<p>Steve Bristol and I wrote the original patch in the speaker's lounge @ railsconf 2008 right before the release. For some reason the MySQL part of the patch didn't get included in the diff, but Jonathan Weiss picked it up and included it shortly afterwards. Unfortunately 2.1 had already been released, so it's important to upgrade if you are using Rails < 2.1.1 + MySQL</p>

September 11, 2008 | Unregistered CommenterAaron Bedra

[...] läßt. Mit Ruby on Rails 2.1.1 haben die Entwickler zudem, die seit Version 2.1 bekannte SQL-Injection-Lücke beseitigt, die zwar bereits geschlossen, aber dennoch im MySQL-Adapter zu finden war. Hier gibt es [...]

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>