Parameter binding and IN operator
parameters, ruby, sql, sqlite
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`.
Problem
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.)