Parameterizing SQL queries in Ruby + TinyTDS

Question

I am trying to figure out how to parameterize an SQL string before handing it off to be executed, but sadly I find a lot of this on the internet:

sql = "SELECT * FROM table_name WHERE thing LIKE '%#{input}%'"

Which is a bad thing...however, parameterizing sql queries is available in the underlying Sequel library, which is what TinyTDS is built on top of. So I know it's possible. I am just having a hard time figuring it out.

I really wish it could be as simple as this:

@client = TinyTds::Client.new(
      :adapter => 'sqlserver',
      :host => host,
      :database => db,
      :username => username,
      :password => password)

sql = "SELECT * FROM table_name WHERE thing LIKE ?"
safe_sql = @client.prepare(sql, input)
result = @client.execute(safe_sql)

I seem to have found something called a Dataset class in the sourcecode, which has a prepare method. The question is, how do I use it? Do I need to create another object before handing it off to the execute() method in the @client object? I couldn't find an initialize or a new method, so simple instantiation seems like the wrong way to go.

Felten

Is there a DB constant available? – Burnight

Actually, looking into this, Sequel has a TinyTDS adapter, so I'd just use that. I can't see anything that the TinyTDS gem does that you'd gain from not just using Sequel directly. – Burnight

See this doc on filtering to see that you can pass the input as you have above to prepare and it will automatically make the input safe. See the doc for SQL users for how to run SQL directly. – Burnight

+1 @iain. Sequel is great and supports TinyTDS nicely. That's how I'd go. – Brilliantine

Answer 1

I implemented the Sequel gem with TinyTds as the adapter. This allows you to parameterize SQL queries. See example below:

require "tiny_tds"
require 'sequel' 

DB = Sequel.connect(
    adapter: 'tinytds', 
    host: "dataserver", 
    database: "database", 
    user: "username", 
    password: "password"
)

I then was able to make a SQL insert statement with my values parametrized.

posts_table = DB[:posts]
posts_table.insert(:author => 'John Smith', :title => 'How to parametrize sql queries')

I'm connecting to a MS SQL database.

Snodgrass