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

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

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 2

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 3

Even simpler, build your query like this:

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

Answer 4

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

Answer 5

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

Answer 6

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.

Answer 7

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 8

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

Answer 9

this works fine aswell (Javascript ES6):

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

Answer 10

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=", ") , ");"))

Answer 11

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.

Answer 12

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

Answer 13

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 )