Sat 19 May 2007
SQL Injection
Posted by Heiko under Rails
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, thisquery 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
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
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 UNIONinstruction. 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. Itreturns 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, itpresents 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
[string containing question marks,
substitution list for the question marks]
As in:
User.find(:first, ["login = ? AND password = ?", params[:name],
params[:password]])
6 Responses to “ SQL Injection ”
Comments:
Leave a Reply
Trackbacks & Pingbacks:
-
Pingback from A good article about SQL Injection
May 21st, 2007 at 5:30[…] on Rails Security blog has a good article about SQL Injection and Ruby on Rails. Check out the SQL Injection […]
May 19th, 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?
May 20th, 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
May 21st, 2007 at 19:49
This article needs the attention of a copy editor who is fluent in Engligh.
May 22nd, 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”)
May 23rd, 2007 at 10:53
Thanks Heiko - keep them coming.