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.

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.