SQLite: bind list of values to “WHERE col IN ( :PR

Question

all I want to do is send a query like

SELECT * FROM table WHERE col IN (110, 130, 90);

So I prepared the following statement

SELECT * FROM table WHERE col IN (:LST);

Then I use

sqlite_bind_text(stmt, 1, "110, 130, 90", -1, SQLITE_STATIC);

Unfortunately this becomes

SELECT * FROM table WHERE col IN ('110, 130, 90');

and is useless (note the two additional single quotes). I already tried putting extra ' in the string but they get escaped. I didn't find an option to turn off the escaping or prevent the text from being enclosed by single quotes. The last thing I can think of is not using a prepared statement, but I'd only take it as last option. Do you have any ideas or suggestions?

Thanks

Edit:

The number of parameters is dynamic, so it might be three numbers, as in the example above, one or twelve.

Answer 1

Even simpler, build your query like this:

"SELECT * FROM TABLE WHERE col IN ("+",".join(["?"]*len(lst))+")"

Answer 2

A much simpler and safer answer simply involves generating the mask (as opposed to the data part of the query) and allowing the SQL-injection formatter engine to do its job.

Suppose we have some ids in an array, and some cb callback:

/* we need to generate a '?' for each item in our mask */
const mask = Array(ids.length).fill('?').join();

db.get(`
  SELECT *
    FROM films f
   WHERE f.id
      IN (${mask})
`, ids, cb);

Answer 3

You can dynamically build a parameterized SQL statement of the form

 SELECT * FROM TABLE WHERE col IN (?, ?, ?)

and then call sqlite_bind_int once for each "?" you added to the statement.

There is no way to directly bind a text parameter to multiple integer (or, for that matter, multiple text) parameters.

Here's pseudo code for what I have in mind:

-- Args is an array of parameter values
for i = Lo(Args) to Hi(Args)
   paramlist = paramlist + ', ?'

sql = 'SELECT * FROM TABLE WHERE col IN (' + Right(paramlist, 3)  + ')'

for i = Lo(Args) to Hi(Args)
  sql_bind_int(sql, i, Args[i]

-- execute query here.

Answer 4

For example, if you want the sql query:

select * from table where col in (110, 130, 90)

What about:

my_list = [110, 130, 90]
my_list_str = repr(my_list).replace('[','(').replace(']',')') 
cur.execute("select * from table where col in %s" % my_list_str )

Answer 5

I just faced this question myself, but answered it by creating a temporary table and inserting all the values into that, so that I could then do:

SELECT * FROM TABLE WHERE col IN (SELECT col FROM temporarytable);

Answer 6

Working on a same functionality lead me to this approach: (nodejs, es6, Promise)

    var deleteRecords = function (tblName, data) {
        return new Promise((resolve, reject) => {
            var jdata = JSON.stringify(data);
            this.run(`DELETE FROM ${tblName} WHERE id IN (?)`, jdata.substr(1, jdata.length - 2), function (err) {
                err ? reject('deleteRecords failed with : ' + err) : resolve();
            });
        });
    };