How to escape string in Ruby to protect against SQL Injection? (No Rails)

Question

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.

Answer 1

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.

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

Answer 3

Gosh, those rails fanboys. Not using rails is NOT reinventing the wheel. There is nothing wrong in using DBI. Re-coding DBI would be reinventing the wheel.

Answer 4

And if I don't want to use prepared statements and DBI?

Answer 5

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

Answer 6

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