Preventing SQL Injection Attacks


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


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;

	 first VARCHAR(200),
	 last VARCHAR(200)

	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:

	# no_sql_injection.rb

	require cookbook_dbconnect
	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"}

	# 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}

	# 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}
	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

	# 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


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:

	# 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]}"}

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.

See Also

  • "Securing your Rails application" in the Ruby on Rails manual (
  • The RDoc for the ActiveRecord::Base class
  • "SQL Injection Attacks by Example" is a readable introduction to this topic (
  • "Using the Ruby DBI Module" has a section on quoting (



Date and Time



Files and Directories

Code Blocks and Iteration

Objects and Classes8

Modules and Namespaces

Reflection and Metaprogramming


Graphics and Other File Formats

Databases and Persistence

Internet Services

Web Development Ruby on Rails

Web Services and Distributed Programming

Testing, Debugging, Optimizing, and Documenting

Packaging and Distributing Software

Automating Tasks with Rake

Multitasking and Multithreading

User Interface

Extending Ruby with Other Languages

System Administration

Ruby Cookbook
Ruby Cookbook (Cookbooks (OReilly))
ISBN: 0596523696
EAN: 2147483647
Year: N/A
Pages: 399

Similar book on Amazon © 2008-2020.
If you may any questions please contact us: