Preventing SQL Injection Attacks

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.

See Also

  • "Securing your Rails application" in the Ruby on Rails manual (http://manuals.rubyonrails.com/read/chapter/43)
  • The RDoc for the ActiveRecord::Base class
  • "SQL Injection Attacks by Example" is a readable introduction to this topic (http://www.unixwiz.net/techtips/sql-injection.html)
  • "Using the Ruby DBI Module" has a section on quoting (http://www.kitebird.com/articles/ruby-dbi.html#TOC_8)


Strings

Numbers

Date and Time

Arrays

Hashes

Files and Directories

Code Blocks and Iteration

Objects and Classes8

Modules and Namespaces

Reflection and Metaprogramming

XML and HTML

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

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net