13

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.

yurisich
  • 6,991
  • 7
  • 42
  • 63
  • Is there a `DB` constant available? – ian Jan 24 '13 at 23:24
  • 2
    Actually, looking into this, [Sequel has a TinyTDS adapter](http://sequel.rubyforge.org/rdoc-adapters/classes/Sequel/TinyTDS/Database.html), 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. – ian Jan 24 '13 at 23:42
  • 2
    See [this doc on filtering](http://sequel.rubyforge.org/rdoc/files/doc/dataset_filtering_rdoc.html) 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](http://sequel.rubyforge.org/rdoc/files/doc/sql_rdoc.html) for how to run SQL directly. – ian Jan 24 '13 at 23:44
  • 1
    +1 @iain. Sequel is great and supports TinyTDS nicely. That's how I'd go. – the Tin Man Jan 25 '13 at 00:29

1 Answers1

3

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.

Patrick G.
  • 697
  • 7
  • 13