Cover by Leonard Richardson, Lucas Carlson

Safari, the world’s most comprehensive technology and business learning platform.

Find the exact information you need to solve a problem on the fly, or go deeper to master the technologies and skills you need to succeed

Start Free Trial

No credit card required

O'Reilly logo

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

Find the exact information you need to solve a problem on the fly, or go deeper to master the technologies and skills you need to succeed

Start Free Trial

No credit card required