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]})
8 responses so far ↓
1 Alex MacCaw // May 19, 2007 at 8:19
Active resource allows you to pass conditions to a rails app in a query string. I’m wondering if there’s anyway to safely use these conditions?
2 Dirk Oschlies // May 20, 2007 at 22:16
Hello Heiko,
first of all. Thanks for your great blog with all its usefull information!
Here is a small improvement for secure finding, which is easier to read, taken from the release notes to rails 1.2 RC1:
“Finding
Added simple hash conditions to #find that will just convert a hash to an equality/AND-based condition string. Example:
Person.find(:all, :conditions => { :last_name => “Catlin”, :status => 1 })”
http://weblog.rubyonrails.org/2006/11/26/1-2-new-in-activerecord
Greetings Dirk
3 A good article about SQL Injection // May 21, 2007 at 5:30
[…] on Rails Security blog has a good article about SQL Injection and Ruby on Rails. Check out the SQL Injection […]
4 Jamie Flournoy // May 21, 2007 at 19:49
This article needs the attention of a copy editor who is fluent in Engligh.
5 bob // May 22, 2007 at 16:40
“This is not possible in Rails, as a statement may not contain a semicolon beyond quoted strings.”
#on postgres
SomeModel.find(:all, :limit => “1; delete from some_table”)
6 anon // May 23, 2007 at 10:53
Thanks Heiko - keep them coming.
7 A Fresh Cup » Blog Archive » Double Shot #50 // Dec 30, 2007 at 6:19
[…] SQL Injection - A security issue I understand plenty well, but here’s an article about it in the context of Rails. […]
8 DoRails» Blogarchiv » 03. Verwende die Binding-Funktionalität // Apr 14, 2008 at 8:30
[…] More: http://www.rorsecurity.info/2007/05/19/sql-injection/ […]
Leave a Comment