SQLite: bind list of values to "WHERE col IN ( :PRM )"

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);

Even simpler, build your query like this:

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

One solution (which I haven't tried yet in code, but only on the SQLite shell) is to use json_each function from SQLite.

So you could do something like:

SELECT * FROM table
WHERE col IN (SELECT value FROM json_each(?));

The caveat is that you'd have to manually assemble a valid JSON array with the values you're trying to bind.

Depending on your build of sqlite (it's not part of the default build), you may be able to use:

SELECT * FROM table WHERE col IN carray(?42);

and then bind ?42 using (assuming the C API):

int32_t data[] = {110, 130, 90};
sqlite3_carray_bind(
    stmtPtr, 42,
    data, sizeof(data)/sizeof(data[0]),
    CARRAY_INT32, SQLITE_TRANSIENT);

I haven't actually tested that, I just read the docs: https://sqlite.org/carray.html

You cannot pass an array as one parameter, but you can pass each array value as a separate parameter (IN (?, ?, ?)).

The safe way to do this for dynamic number parameters (you should not use string concatenation, .format(), etc. to insert the values themselves into the query, it can lead to SQL injections) is to generate the query string with the needed number of ? placeholders and then bind the array elements. Use array concatenation or spread syntax (* or ... in most languages) if you need to pass other parameters too.

Here is an example for Python 3:

c.execute('SELECT * FROM TABLE WHERE col IN ({}) LIMIT ?'
       .format(', '.join(['?'] * len(values))), [*values, limit])

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);

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();
            });
        });
    };

this works fine aswell (Javascript ES6):

let myList = [1, 2, 3];
`SELECT * FROM table WHERE col IN (${myList.join()});`

You can try this

RSQLite in R:
lst <- c("a", "b", "c")

dbGetQuery(db_con, paste0("SELECT * FROM table WHERE col IN (", paste0(shQuote(lst), collapse=", ") , ");"))

My solution for node (ES6, Promises):

let records = await db.all(`
    SELECT * FROM table
    WHERE (column1 = ?) and column2 IN ( ${[...val2s].fill('?').join(',')} )
`, [val1, ...val2s])

Works with a variable number of possible values.

This uses sqlite-async but you can modify it for the callback style version trivially.

if you are using Python the easiest way to handle this, in practice, is to create a local function that tests against a string value of the list (which can be passed as a bind variable).

I used this when providing "Query By Example" functionality in a Python GUI app.

pros: can use common approach in parsing and building the SQL across entries as I would when parsing LIKE xxx and > xxx etc

just one extra call to set the function up - either at connection time
or if the function call is detected in the created sql

cons: function needs to parse string list for each row. This is bad if the query is running against a large table

embedded commas, blanks and other similar stuff may be difficult to handle

For example

1. user enters IN 18C, 356, 013 into Account field in application
2. application creates sql with ... WHERE inz( Account , ? ) ...
3. application creates string bind value 18C, 356, 013
4. application issues <sqlite3.connection>.create_function("inz", 2, inz ) to bind local python function inz (see below) to sqlite function inz.
5. application issues query

the coding for the inz function is as follows

def inz(  val , possibles ) :
    """implements the  IN list function allowing one bind variable
    
       use <sqlite3.connection>.create_function("inz", 2, inz )
       and  ensure that the bind variable is a comma delimited list 
       in string form (without quotes)
       
       matches can be string or integer but do not allow for leading
       or trailing spaces or  contained commas, quotes etc or floating points

    """
    
    poss  = [ x.strip() for x in possibles.split(',') ] 
    
    if val in poss :
        return True  
    if isinstance(  val, int ) :
        ipos = [ int(x) for x in poss if x.isdecimal() ] 
        if val in ipos :
            return True 
            
    return False

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 )