The best kittens, technology, and video games blog in the world.

Sunday, January 13, 2008

Protecting custom SQL in Rails from SQL injections

Claw by AnasiZ from Wikimedia Commons (public domain)
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: <<SQL
SELECT COUNT(*) FROM people WHERE last_name LIKE '#{quote_value(prefix)}%'
by far nicer:
connection.safe_select <<SQL, "#{prefix}%"
SELECT COUNT(*) FROM people WHERE last_name LIKE ?'

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

1 comment:

Ray said...

I had the same 'ug', so I made this Oracle patch a year ago. I also made an Oracle patch that used rowid and that made updates extra fast. Keep in mind that this is old code. AR and Rails might have moved since then. Despite the noise around rails, it does not address my problem domain of finding hot chicks on beaches. This is confirmed every time I do a POC. The mohitos keeps winning.

In reflecting on how to marry AR to Oracle, I've concluded that AR suffers from MySql's least common denominator. It really turned me off of rails for anything transaction oriented.

# Add the Power Of Oracle and prepared statements
# Use a class variable so that you only evaluate the sql once. For example:
# unless @@sql
# @@sql = "select " + "sysdate " + "from dual"
# end
# Is better than
# sql = "select " + "sysdate " + "from dual"
# Because the former only executes once.
# Example:
# find_by_prepared_sql(:id=>:e1,
# :sql=>@@e1_sql,
# :args=>[accountId, reservation, start_t.to_i, end_t.to_i],
# :name=>"Event Account Summary")
module ActiveRecord
class Base
class << self # Class methods

# Returns rows using a prepared statement. The SQL should be static - not changing between calls.
def find_by_prepared_sql(opts)
connection.select_all(opts,"#{opts[:name]} Load").collect! { |record| instantiate(record) }
end # Base

module ConnectionAdapters
class OracleAdapter

@@all_cursors ||= # All the cursors we care about.

cattr_accessor :cached_columns
@@cached_columns ||=

# Set this session to readonly
def enable_read_only
sql = "set transaction read only"
log(sql, "Read Only"){@connection.exec sql}

# Sometimes people use ? for bind values. Replaces ? with :1, :2, etc.
def replace_with_numeric_binds(sql)
i=0; sql.gsub('?'){ ":#{i+=1}" }

# Returns the cursor for the sql. The cursor is prepared the first time.
# opts is a Hash with :id, :sql, :args, and :name
def cached_cursor(opts)
sql, args = opts[:sql], opts[:args]
unless cursor = @@all_cursors[opts[:id]]
sql = replace_with_numeric_binds(sql) if sql.include?('?')
log(sql, "#{opts[:name]||opts[:id]} Prepare") do
@@all_cursors[opts[:id]] = cursor = @connection.parse(sql)

# Prepares and save the cursor
# Opts should include the following
# :id - The id for this sql.
# :sql - The sql to with bind values.
# :args - An array of args
# :timeout - false *or* max time in seconds. If missing, a default value is used.
def execute_prepared_cursor(opts)
args = opts[:args]
timeout = opts.has_key?(:timeout) ? opts[:timeout] : 5
cursor = cached_cursor(opts)
if ! timeout
log("#{args.inspect}", "#{opts[:name]||opts[:id]} Exec") { cursor.exec(*opts[:args])}
Timeout::timeout(timeout) do
log("#{args.inspect}", "#{opts[:name]||opts[:id]} Exec") { cursor.exec(*opts[:args])}

rescue Timeout::Error => e
log("Timeout during operation","")
# Send an OCIBreak?

# Execute SQL and return an array of rows.
# Improve performance by using prepared statements. This seperates the sql from the args.
# Use placeholders of type ". . . where id = :1 and type = :2"
# Make :id=>:cur1, :sql=>sql, :args=>args
def select(sql, name = nil)
if sql.is_a?(Hash)
cursor = execute_prepared_cursor(sql)
cols = cached_columns[sql[:id]] ||= { |x| oracle_downcase(x) }
cursor = execute(sql, name)
cols = { |x| oracle_downcase(x) }
rows = []
while row = cursor.fetch
hash =

cols.each_with_index do |col, i|
hash[col] =
case row[i]
when OCI8::LOB
name == 'Writable Large Object' ? row[i]: row[i].read
when OraDate
(row[i].hour == 0 and row[i].minute == 0 and row[i].second == 0) ?
row[i].to_date : row[i].to_time
else row[i]
end unless col == 'raw_rnum_'

rows << hash

cursor.close if cursor && sql.is_a?(String)

end # class
end # ConnectionAdapters #:nodoc:
end # ActiveRecord