I’ve been playing around with SQL injection with Domain Pigeon in an effort to ensure its robustness and to more effectively understand the built in protection offered by Rails. To do this, I set up a simple form with one input (the domain name) which was then used as a parameter for various mehods of searching the database:

Method #1: Direct Insertion (bad)

@domains = Domain.find(:all, :conditions => "name = '#{params[:domain]}'")

This is the classic “don’t do this you idiot”-vulnerable-to-SQL-injection method.

Example #1:

• Input: holla.com
• SQL: SELECT * FROM “domains” WHERE (name = ‘holla.com‘)
• Output: 1 domain

Example #2:

• Input: holla.com’ or ‘a’=’a
• SQL: SELECT * FROM “domains” WHERE (name = ‘holla.com’ or ‘a’=’a‘)
• Output: The entire table of domains

The first example works because the input is crafted to break the query. In the second example, inserting the input directly into the query results in the entire table of domains being displayed.

Verdict: Fail.

Method #2: Bind Variable (good)

@domains = Domain.find(:all, :conditions => ["name = ?", params[:domain]])

This method, know as the Rails bind variable facility, is the recommended method for defending against SQL injection. From Agile Web Development with Rails: “It will add quotation marks if the elements are strings and quote all characters that have a special meaning for the database adapter…”

In this case (sqlite), that means adding an extra single quote before every single quote within the string.

Example:

• Input: holla.com’ or ‘a’=’a
• SQL: SELECT * FROM “domains” WHERE (name = ‘holla.com” or ”a”=”a‘)
• Output: Nothing

Note: for the second example and all the WHERE clauses in the examples that follow, those are two single quotes, not one double quote.

Verdiect: Secure.

Method #3: CGI::escapeHTML and escape_html (bad)

@escaped = CGI::escapeHTML(params[:domain])
@domains = Domain.find(:all, :conditions => "name = '#{@escaped}'")

Until playing around with this, I thought that escape_html (alisted to simply h) was only available to the views and in order to escape HTML within a controller, you had to use CGI::escapeHTML. However, you actually can escape_html using ERB::Util:

>> ERB::Util.html_escape('Then he & she yelled "Hey!"')
=> "Then he & she yelled "Hey!""

Therefore in this example I could have also written:

@escaped = ERB::Util.escape_html(params[:domain])
@domains = Domain.find(:all, :conditions => "name = '#{@escaped}'")

and achieved the same thing.

However, you don’t want to do this. html_escape and escapeHTML only escape ampersands, double quotes, and greater than and less than symbols. Since they do not escape single quotes your application would still be vulnerable to SQL injection:

These two methods should be used for displaying data in a view; they should not be used to prevent SQL injection.

Example:

• Input: holla.com’ or ‘a’=’a
• SQL: SELECT * FROM “domains” WHERE (name = ‘holla.com’ or ‘a’=’a‘)
• Output: The entire table of domains

Verdict: Fail.

Method #4: find_by methods (good)

@domain = Domain.find_by_name(params[:domain])

Example:

• Input: holla.com’ or ‘a’=’a
• SQL: SELECT * FROM “domains” WHERE (“domains”.”name” = ‘holla.com” or ”a”=”a‘)
• Output: Nothing

The find_by methods properly escape their input.

Verdict: Secure.

Method #5: Conditional Hashes (good)

@domains = Domain.find(:all, :conditions => {:name => params[:domain]})

Example:

• Input: holla.com’ or ‘a’=’a
• SQL: SELECT * FROM “domains” WHERE (“domains”.”name” = ‘holla.com” or ”a”=”a‘)
• Output: Nothing

Using hashes is an alternative method for querying your database and it also properly escapes the user’s input.

Verdict: Secure.

Bottom line (literally):

Don’t insert user input directly into SQL queries, either alone or using HTML sanitization methods.