SQL Injection

Filed under Uncategorized

SQL injection attacks aim at injecting database queries by manipulating web application parameters. Almost all SQL injection attacks are immediately reflected, that means a malicious parameter moves from the client to the server, will be put together to a SQL query, sent to the database server and the result will be returned to the client. A popular goal of SQL injection attacks is to bypass authorization. But also reading of arbitrary data or manipulating it can be done with this form of attacks. The following shows two typical Rails function calls to find data in the database, in this case all projects with a specific name.
 
Project.find(:all, :conditions => "name = '" + params[:name] + "'")

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

These examples are vulnerable to SQL injection attacks as an attacker could enter ' OR 1 — and thus, after Rails substituted it into SQL, the query string will be: SELECT * FROM projects WHERE name = '' OR 1 –'

The boolean value 1, and thus name = '' OR 1 is always evaluated to true. The double dash signs start an SQL comment, everything after it will be ignored. Consequently, this query will return all projects in the database and present it to the user. A number sign (#) also starts a comment and /* starts a multi-line comment.

Bypassing Authorization

Many web applications include access control, and users have to log in to the application to use it. I.e. they have to enter their login credentials. The following shows a typical database query in Rails to find the first record in the userstable which matches the login credentials parameters supplied by the user. If the attacker entersthe following user names and passwords, he will get access to the application.
 

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

params[:name] = ' OR '1'='1
params[:password] = ' OR '2'>'1
params[:name] = ' OR login LIKE '%a%
params[:password] = ' OR ISNULL(1/0) #

Unauthorized Reading

This section shows a different technique which is based on manipulating queries that present information to the user. This can be web page titles, articles, comments et cetera.

In the example from above, where all projects with a specific name are queried, an attacker can join in the result from a second SELECT statement using the UNION instruction. UNION connects two queries and returns the data in one set, and if the column's data types do not match, they are being converted. The following example introduces SQL column renaming with the AS instruction. It returns a few columns only (contrary to the overall asterisk (*) selector), and renames them according to the column names in the projects table. The actual number of columns can be determined by adding more ones (1) to the SELECT statement. Consequently, the web application believes to return all project names and its descriptions, however, it presents all login names and passwords for the application.

# injecting "') UNION SELECT id,login AS name,password AS
description,1,1,1,1 FROM users /*" will result in:

SELECT * FROM projects WHERE (name = '') UNION SELECT id,login AS
name,password AS description,1,1,1,1 FROM users /*')

Countermeasures

In many web applications that are vulnerable to SQL injection, you can inject another query using the batch processing operator (;). Thus other instructions, apart from SELECT, can be appended, namely INSERT, UPDATE or DELETE to add, modify or remove records from any table in the database. This is not possible in Rails, as a statement may not contain a semicolon beyond quoted strings.

 

When building a filter against malicious input, you should not search and replace strings. For example, a filter that removes the string INSERT will be useless if the attacker enters INSINSERTERT, because the filter will make the attack work.

 

Ruby on Rails has a built in filter for special SQL characters, which will escape ' , " , NULL character and line breaks. Especially the single quote characters is absolutely necessary for SQL injection attacks on Rails applications. Normally, this filter will be applied automatically, but sometimes has to be applied manually. In any SQL fragment, especially in any condition string (:conditions => "…"), the connection.execute() or the find_by_sql() function, it is not advisable to use string appending (string1 + string2 ), or the conventional Ruby #{…} mechanism to substitute strings. The correct way is to use the bind variable facility, which has the following syntax:

 

[string containing question marks,
substitution list for the question marks]
As in:

User.find(:first, :conditions => ["login = ? AND password = ?", params[:name],
params[:password]])

Or the same in Rails 1.2:

User.find(:first, :conditions => {:login => params[:name],
:password => params[:password]})