Easy Rails Quoting to Protect Against SQL Injection

If you have simple queries that you are performing in Ruby on Rails, it's very easy to use Active Record's bind variable facility. For instance, you can easy protect against SQL injection using a syntax like this:

Person.find(:all, :conditions => ["name = ?", params[:name] ]

However, when you start building up more complex SQL queries, using the simple Active Record finders don't always work. Depending on the type of search you are performing, you can very quickly end up with a large number of search criteria and a very complex SQL statement, requiring use of the "find_by_sql" method instead. So how do you protect against SQL injection in these complex cases?

One way is to take advantage of the same quoting methods that the Active Record finders use. There are several methods available and those can be reviewed in the Rails API documentation.

To use these methods, use the following syntax:

Person.connection.quote(params[:name])

This will add quotes around your input string while escaping any existing single quotes or backslashes. Then, you can pass your newly escaped string into your WHERE clause in "find_by_sql" or into a conditions variable that you can pass around.

[Thanks to Steve Midgley for pointing this out to me]