Possible SQL injection

[JW]

The 'archive' action in BlogController executes the following Ruby code:

    @total_entries = Entry.find(:all, :conditions => "state = 'P' and " +
      "YEAR(date_published) = #{params[:year]} and MONTH(date_published) = #{params[:month]}",
      :order => "date_published DESC")

In this function, the 'year' and 'month' paramaters are not escaped, so SQL code can be written inside them.

[JW (jw-00000)]

At this moment I'm not yet able to exploit this bug. The SQL created by this bug looks like this:

    SELECT * FROM entries WHERE (state = 'P' and YEAR(date_published) = 2007 and MONTH(date_published) = 1) ORDER BY date_published DESC

where 2007 and 1 are replaced with the asked year and month.

A URL such as http://0.0.0.0:3000/archive/2007/1); returns a MySQL error:
    Mysql::Error: You have an error in your SQL syntax

The URLs
http://0.0.0.0:3000/archive/2007/1); -- (
http://0.0.0.0:3000/archive/2007/1); DROP TABLE entries; -- (
and
http://0.0.0.0:3000/archive/2007/1); DELETE FROM users WHERE id = 1; -- (
can, fortunately, not get executed; a MySQL error gets returned.

[JW (jw-00000)]

Tests on the URL
    http://0.0.0.0:3000/archive/2007/1);DELETE FROM `entries` WHERE `id` = 2 -- (
(all of it, you may need to replace the ` with %60)

When requesting that URL, a MySQL error is returned, saying that there's an error in the SQL syntax:
    Mysql::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';DELETE FROM `entries` WHERE `id` = 2 -- () ORDER BY date_published DESC' at line 1: SELECT * FROM entries WHERE (state = 'P' and YEAR(date_published) = 2007 and MONTH(date_published) = 1);DELETE FROM `entries` WHERE `id` = 2 -- () ORDER BY date_published DESC

However, when executing the previous SQL code in a MySQL browser such as phpMyAdmin, it gets executed correctly, and the entry with ID 2 is deleted. I think this may have something to do with the fact that multiple statements are joind together using a ';', more info will follow.

[JW (jw-00000)]

My previous remark was right: the MySQL adapter used by Rails disables multiple statements by default. Therefore, the URL in the previous comment is harmless, at least when using MySQL. I'm still investigating other ways a DELETE or DROP statement might be executed.

[JW (jw-00000)]

On MySQL, this particular bug most likely won't do any great harm since support for multiple statements is disabled by default. SQLite and Oracle users also won't experience any immediate, severe threat. Users of Microsoft's SQLServer should be more careful, as multiple statements are supported by default by SQLServer, and this greatly increases the possible harm an SQL injection can cause.

It seems to me that this bug can be exploited to get extra information out of the database using SELECTs, but that it won't be possible to edit or delete data in the database. So, the greatest harm this bug possibly can do, is allow an attacker to read an article currently still in draft, or get a list of all users and their e-mail addresses.

Extra attention for anything out of the ordinary is however still advised, keep an eye out for anything that may cause harm to your database. As always, it's also recommended to make back-ups of all your data every once in a while, in and out of the database.

Before fixing this bug, I will search the code for other similar bugs.

[JW (jw-00000)]

The following attachment is a patch that fixes this bug.

[JW (jw-00000)]

This bug was fixed in the trunk, 0.5 and 0.6 branches.

[JW (jw-00000)]

Chameleon 0.6.1.1 has been released. Download it at http://chameleon.wikidot.com/download.