Preventing SQL Injection Attacks

Table of contents:

Problem

You want to harden your code against SQL injection attacks, whether in DBI or ActiveRecord code.

Solution

With both ActiveRecord and DBI applications, you should create your SQL with question marks where variable interpolations should go. Pass in the variables along with the SQL to DatabaseHandle#execute, and the database will make sure the values are properly quoted.

Lets work against a simple database table tracking peoples names:

	use cookbook;

	DROP TABLE IF EXISTS names;
	CREATE TABLE names (
	 first VARCHAR(200),
	 last VARCHAR(200)
	) ENGINE=InnoDB;

	INSERT INTO names values (Leonard, Richardson),
	 (Lucas, Carlson),
	 (Michael, Loukides);

Heres a simple script that searches against that table. Its been hardened against SQL injection attacks with three techniques:

	#!/usr/bin/ruby
	# no_sql_injection.rb

	require cookbook_dbconnect
	activerecord_connect
	class Name < ActiveRecord::Base; end

	print Enter a last name to search for: 
	search_for = readline.chomp

	# Technique 1: use ActiveRecord question marks
	conditions = ["last = ?", search_for]

	Name.find(:all, :conditions => conditions).each do |r|
	 puts %{Matched "#{r.first} #{r.last} with ActiveRecord question marks"}
	end

	# Technique 2: use ActiveRecord named variables
	conditions = ["last = :last", {:last => search_for}]
	
	Name.find(:all, :conditions => conditions).each do |r|
	 puts %{Matched "#{r.first} #{r.last}" with ActiveRecord named variables}
	end

	# Technique 3: use DBI question marks
	with_db do |db|
	 sql = SELECT first, last FROM names WHERE last = ?

	 db.execute(sql, [search_for]).fetch_hash do |r|
	 puts %{Matched "#{r[first]} #{r[last]}" with DBI question marks}
	 end
	end
	
	puts "Done"

Heres how this script looks in use:

	$ ruby no_sql_injection.rb
	Enter a last name to search for: Richardson
	Matched "Leonard Richardson" with ActiveRecord question marks
	Matched "Leonard Richardson" with ActiveRecord named variables
	Matched "Leonard Richardson" with DBI question marks
	Done

	# See the Discussion if you
e not sure how this attack is supposed to work.
	$ ruby no_sql_injection.rb
	Enter a last name to search for: " or 1=1
	Done

Discussion

SQL is a programming lanuage, and running SQL is like calling eval on a string of Ruby code. Unless you have complete control over the entire SQL string and all the variables interpolated into it, you need to be very careful. Just one mistake can leave you open to information leakage or database corruption.

Heres a naive version of sql_injection.rb thats vulnerable to an injection attack. If you habitually write code like this, you may be in trouble:

	#!/usr/bin/ruby
	# sql_injection.rb
	require cookbook_dbconnect

	print "Enter a last name to search for: "
	search_for = readline.chomp
	query = %{select first, last from names where last="#{search_for}"}
	puts query if $DEBUG
	with_db do |db|
	 db.execute(query).fetch_hash do |r|
	 puts %{Matched "#{r[first]} #{r[last]}"}
	 end
	end

Looks fine, right?

	$ ruby -d sql_injection.rb
	Enter a last name to search for: Richardson
	select first_name, last_name from people where last_name="Richardson"
	Matched "Leonard Richardson"

Not necessarily. Whatever I type is simply being stuck into a SQL statement. What if I typed as my "query" part of a SQL WHERE clause? One that, when combined with the original WHERE clause, matched anything and everything?

	$ ruby -d sql_injection.rb
	Enter a last name to search for: " or 1=1
	select first_name, last_name from people where last_name="" or 1=1
	Matched "Leonard Richardson"
	Matched "Lucas Carlson"
	Matched "Michael Loukides"

I can see every name in the table.

This is just one example. SQL injection attacks can also alter or delete data from a database.

The correct version of this program, the one described in the Solution, quotes my attempt at a SQL injection attack. My attack is executed as a normal query: the program looks for people (or robots, I guess) whose last name is the string " or 1=1. Quoting the data makes the application do what you want it to do every time, no matter what kind of weird data a user can come up with.

DBI will not run two SQL commands in a single do or execute call, so certain types of SQL injection attacks are impossible with DBI. You can hijack a SELECT statement to make it select something else, but unlike with some other systems, you can make a SELECT also do an UPDATE or DELETE. An attacker can use SQL injection to drop database tables unless your application already runs a DROP TABLE command somewhere.

You don usually write full-blown SQL statements with ActiveRecord, but you do write conditions: snippets of SQL that get turned into to the WHERE clauses of SELECT or UPDATE statements. Whenever you write SQL, you must take these precautions.

Problem

Solution

Discussion

See Also