Parameterizing SQL queries in Ruby + TinyTDS

ruby, sql-injection, tiny-tds

Solution

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.

Problem

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.

Original source