O'Reilly logo

Ruby Cookbook by Leonard Richardson, Lucas Carlson

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

13.15. 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.

Let's work against a simple database table tracking people's 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');

Here's a simple script that searches against that table. It's 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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required