imedo Development Blog

Number three of the 2009 CWE/SANS Top 25 Most Dangerous Programming Errors is Improper Sanitization of Special Elements used in an SQL Command which could lead to the possibility of SQL injection.

If an application is vulnerable to SQL injection an attacker can add SQL commands to input values in order to manipulate the behavior of this application or to gain unauthorized access. The classic example for SQL injection is the circumvention of authorization by adding an always true condition like ‘or 1=1’ to the passed password in a login dialog as shown in the following example.

User.find(:first, "login = '#{params[:user_name]}' AND password = '#{params[:password]}'")

Entering ’ OR ‘1’=’1 as password will result in the following MySQL query.

SELECT * FROM users WHERE login = 'asdf' AND password = '' OR '1'='1' LIMIT 1

This query will return the first user in the database and therefore the authorization will always succeed as long as there is at least one user in the database.

Besides getting unauthorized access to an application, SQL injection can be used to manipulate the displayed information so that sensitive information may be revealed. In the following example lazy SQL querying of non-sensitive data opens a door to the complete database and an attacker is able to read the user table by extending the input with SQL commands.

Article.find(:all, :conditions => "author = '#{params[:author]}'")

The database for this example consists of two tables: articles and users. While the articles table has four columns (id, author, title, text) the users table has only three (id, login, password). Here’s the attacker’s input for this scenario.

') UNION SELECT id,login AS author,password AS title,1 FROM users --

The input provided by the attacker generates the following MySQL query, which result will not contain any article but all entries from the users table masqueraded as articles.

SELECT * FROM articles WHERE (author = '') UNION SELECT id,login AS author,password AS title,1 FROM users -- ')

In order to prevent SQL injection the build in filters of Ruby on Rails like Model.find(id) or Model.find_by_*() should be used. They have sanitizers included which escape special SQL characters. For the first example this could look like the following.

user = User.find_by_login(params[:login])
raise "Access denied!" if user.password != params[:password]

If the conditions parameter is needed, e.g. in order to filter for several criteria, sanitizers like sanitize_sql_for_conditions in ActiveRecord::Base will create safe condition statements.

Article.find(:all, :conditions => sanitize_sql_for_conditions(:author => params[:author], :title => params[:title]))

Popularity: 5% [?]