« to_json Cross Site Scripting security issue (XSS) | Main | XSS Countermeasures »
Saturday
May192007

SQL Injection

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]})

PrintView Printer Friendly Version

EmailEmail Article to Friend

References (69)

References allow you to track sources for this article, as well as articles that were written in response to this article.
  • Response
    Response: buy cialis online
    buy cialis online buy cialis online http://www.seekyourtrip.com/travel-page/1386 cheap cialis [url=http://www.seekyourtrip.com/travel-page/1386]cheap cialis[/url] cialis [link=http://www.seekyourtrip.com/travel-page/1386]cialis[/link]
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Response: Walliw
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Response: Suggested Webpage
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Response: www.sanisidro.es
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Response: Jayson Woodbridge
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Response: Yeirnie
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Response: apartment cleaning
    ruby on rails security project - journal - sql injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Response: forum.404bajery.pl
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Response: ppc.ciac.jl.cn
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Response: Wong
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Response: link schwartz
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Response: link schwartz
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Response: douglas pitassi
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Response: here
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Response: Acai berry
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Response: dumpsters
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Response: dumpsters
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Response: dumpsters
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Response: D D Photographics
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Response: what do you think
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Response: Dexter Yager
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Response: Slim Trim reviews
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection
  • Response
    Ruby on Rails Security Project - Journal - SQL Injection

Reader Comments (811)

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 19, 2007 | Unregistered CommenterAlex MacCaw

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 21, 2007 | Unregistered CommenterDirk Oschlies

[...] on Rails Security blog has a good article about SQL Injection and Ruby on Rails.  Check out the SQL Injection [...]

This article needs the attention of a copy editor who is fluent in Engligh.

May 22, 2007 | Unregistered CommenterJamie Flournoy

"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 23, 2007 | Unregistered Commenterbob

Thanks Heiko - keep them coming.

May 23, 2007 | Unregistered Commenteranon

Buy cheap phentermine. Buy cheap phentermine fre....

Cheap cymbalta....

May 26, 2007 | Unregistered CommenterCheap cymbalta.

Valium online recipe....

Phentermine online. Buy c heap phentermine online. Buy phentermine online. Phentermine online . Online phentermine. Buy phentermine online 37.5mg no prescription....

May 26, 2007 | Unregistered CommenterOnline phentermine.

Xanax bars. What happens when you crush xanax xr. Xanax prescription. Xanax....

Percocet. Percocet effects. Percocet and nightmares....

May 27, 2007 | Unregistered CommenterPercocet 93-490.

Phentermine online without prescription....

Adipex hurt me. Adipex. Adipex p. Adipex without prescription. Cheap adipex....

Buy phentermine. Buy phentermine without a prescription....

Hydrocodone. Hydrocodone online. 3190 v hydrocodone....

May 28, 2007 | Unregistered CommenterHydrocodone.

Will tramadol hcl test positive in drug testing. Tramadol hcl. Tramadol hcl . The lowest tramadol hcl price guaranteed fast....

May 28, 2007 | Unregistered CommenterTramadol hcl.

Cheapest phentermine pills. Cheapest phentermine d. Cheapest phentermine. Cheapest phentermine online. Cheapest phentermine onli. Cheapest phentermine onlin e. Cheapest phentermine no prescriptions....

Cheap ambien online order ambien now with discount. Online pharmacy order ambien. Ambien order wow what a price restorejustice org....

Comparative potencies of opioids tramadol. Tramadol hydrochloride. Tramadol side affects. Tramadol. Tramadol 50mg....

May 29, 2007 | Unregistered CommenterTramadol drug.

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>