Parameter binding and the IN operator
I'm pretty convinced this isn't possible, but just in case:
How do you bind the correct (list) parameter for the operator IN
?
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 value interpolation, but also safer: one of the main reasons for using 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})')
(Examples are Ruby with sqlite3 gem, but the question really applies to all languages / libraries that bind parameter bindings to you.)
source to share
The SQL you pass to db.execute
is just a string, and the number of placeholders should only match the number of values you pass to them. So you can create placeholders based on list.length
and omit them using string interpolation:
placeholders = Array.new(list.length, '?').join(',')
db.execute("SELECT foo FROM bar WHERE baz IN (#{placeholders})", list)
It is perfectly safe because you know exactly what is going on in placeholders
.
source to share