14

I just wanted to know how can we escape an SQL query (string) in Ruby to prevent SQL Injection. please note I am not using Rails framework.

Thanks.

Andy Lester
  • 91,102
  • 13
  • 100
  • 152

4 Answers4

17

If possible, use the Ruby DBI module, and instead of trying to quote your strings, use parametrized prepared queries, like this:

dbh = DBI.connect("DBI:Mysql:test:localhost", "testuser", "testpass")
sth = dbh.prepare("INSERT INTO people (id, name, height) VALUES(?, ?, ?)")
File.open("people.txt", "r") do |f|
  f.each_line do |line|
    name, height = line.chomp.split("\t")
    sth.execute(nil, name, height)
  end
end

Quoting will be handled properly for you, and injections will be a thing of the past.

Edit: Note that this example shows nil being passed as the first parameter to execute(). It corresponds to the first ? in the query, and is translated to "NULL" by the DBI module. The other parameters are similarly properly quoted and inserted into the query.

greyfade
  • 24,948
  • 7
  • 64
  • 80
  • Why even insert an id field? With normal design this would be automatically incremented. – Ryan Bigg Feb 09 '09 at 20:29
  • It's only an example. But note that the first parameter to execute() is simply nil, and the corresponding parameter in the query (the first question mark) is for id. "id" is being inserted as NULL in this example. – greyfade Feb 10 '09 at 03:16
  • I wanted to know the same thing like the OP, but I wanted to just write SQL statements to a file, so I guess prepared statements are off the table then? Could I (ab)use .inspect for escaping strings? – Jan Oct 06 '11 at 15:07
4

Write a wee function to quote strings. I think Rails just uses something like this:

def quote_string(v)
  v.to_s.gsub(/\\/, '\&\&').gsub(/'/, "''")
end
ice cream
  • 2,444
  • 2
  • 17
  • 13
  • See greyfade's answer. Or Andy Lester's. This is just a bad idea. – James Moore Sep 26 '11 at 20:40
  • 5
    Sometimes you're working on a one-off migration script and have no extra gems installed (no rails or dbi) and when that happens this is an effective solution. In pretty much any other situation you should use prepared statements of course. – Darren Greaves Jun 08 '12 at 12:14
2

You don't have to use rails, you could just require 'activerecord' and use it as you would in rails (define models and use those). What you're doing there is just re-inventing the wheel.

Ryan Bigg
  • 106,965
  • 23
  • 235
  • 261
0

Don't try to sanitize your data. Use prepared statements. See also http://bobby-tables.com/ruby.html

Andy Lester
  • 91,102
  • 13
  • 100
  • 152