Search code examples
sqlrubysqliteparameters

Parameter binding and IN operator

I'm pretty convinced it's not possible, but just in case:

How do you bind the right (list) parameter for the IN operator?

SELECT foo FROM bar WHERE baz IN ('cow', 'chicken');

This, unfortunately, doesn't work:

list = ['cow', 'chicken']
db.execute('SELECT foo FROM bar WHERE baz IN ?', list)

Parameter binding is not only prettier than direct interpolation of values, but also safer: one of the main reasons to use it is to stop the infamous Bobby Tables. However, are you stuck with manual escaping for the IN parameter?

list = ['cow', 'chicken']
sqllist = list.map { |el| "'#{SQLite3::Database.quote(el)}'" }.join(',')
db.execute('SELECT foo FROM bar WHERE baz IN (#{sqllist})')

(The examples are Ruby with sqlite3 gem, but the question actually applies to all languages/libraries that do parameter binding for you.)

Solution

  • The SQL you pass to db.execute is just a string and the number of placeholders only needs to match the number of values you hand it. So, you can build the placeholders based on list.length and drop them in with string interpolation:

    placeholders = Array.new(list.length, '?').join(',')
db.execute("SELECT foo FROM bar WHERE baz IN (#{placeholders})", list)

    This is perfectly safe because you know exactly what's going into placeholders.