Protecting custom SQL in Rails from SQL injections

The technique I'm presenting here is not an "awesome revolutionary breakthrough in Rails security" kind of thing (like XSS Shield), it's more like a small cool trick that can improve your code and make it more secure.

Most web applications are backed by some SQL-based database. If they're written in a language like PHP in which SQL is freely mixed with code, it's very easy to open an SQL injection vulnerability. There are two common solutions for making such vulnerabilities less likely. One is "prepared statements", where instead of creating complete SQL like "SELECT * FROM foo WHERE name='#{name}'" one crerates SQL with placeholders like "SELECT * FROM foo WHERE name=?", and then fills the placeholders with the actual data in a safe way within the database or database driver. The other solution is using Object-Relational Mappers instead of raw SQL, and that's what Rails is doing with Active Record. As long as you're using Active Record only with no custom SQL you don't have to worry about SQL injections at all.

Unfortunately in real applications Active Record is often not good enough. It is OK for locally manipulating small graphs of objects (basically creating, reading, updating and deleting single objects/rows at time), but you need to go back to raw SQL every time you want to compute a query on a lot of data (database views would also work, but that's not a subject today). Having to use SQL is not a bad thing, however due to Rails' lack of support for prepared statements it leaves us in a rather bad situation from security point of view.

Did I just say Rails doesn't support prepared statements ? So what are all those :conditions => ['foo > ? AND bar IN ?', frob, bang] if not prepared statements ? They are "fake" prepared statements, expanded on ActiveRecord level unlike "real" prepared statements expanded on database level. The method which fakes them is ActiveRecord::Base#sanitize_sql and we can reuse it to fake prepared statements of other kinds.

This means replacing ugly code like that:

connection.select <<SQL
  SELECT COUNT(*) FROM people WHERE last_name LIKE '#{quote_value(prefix)}%'
SQL

by far nicer:

connection.safe_select <<SQL, "#{prefix}%"
  SELECT COUNT(*) FROM people WHERE last_name LIKE ?'
SQL

The code to make that happen is pretty simple:

class ActiveRecord::ConnectionAdapters::AbstractAdapter
  [:select, :delete, :update, :execute].each do |m|
    define_method(:"safe_#{m}") do |*args|
      send(m, ActiveRecord::Base.send(:sanitize_sql, args))
    end
  end
end