MySQL and PYTHON. How to query an WHERE 'column' IN tuple

Question

I have a tuple of integer, I want to query all rows with column value found within the tuple. It's easy to construct the query, but I want it to be sql injection proof. I normally use prepared statement, but I don't see how to deal with both needs.

My query construction looks like that :

filterList = (1, 2, 4) #Taken as input. Should be integers

sqlRequest = 'SELECT * FROM table'
    if filterList != None and len(filterList) > 0:
        sqlRequest += ' WHERE column IN ('
        addComa = False
        for filter in filterList:
            if addComa:
                sqlRequest += ','
            else:
                addComa = True
            sqlRequest += '%s'%(int(filter)) #casted to int to avoid SQL injection. Still not as good as I would like
        sqlRequest += ')'

    #At this point sqlRequest == 'SELECT * FROM table WHERE column IN (1,2,4)'
    sqlResult = cursor.execute(sqlRequest)

I would love to have a query more like :

sqlRequest = 'SELECT * FROM table WHERE column IN (%s, %s, %s)'

And to execute it with prepared statement :

sqlResult = cursor.execute(sqlRequest, filterList[0], filterList[1], filterList[2])

But filterList as a variable length. Is there any way to do something like?

sqlResult = cursor.execute(sqlRequest, filterList) #where filterList is the whole tuple

Same way as with other databases, see [Using a WHERE \_\_\_ IN \_\_\_ statement](https://stackoverflow.com/q/14245396) and [Database API: How to deal with multi where condition in Python](https://stackoverflow.com/q/19043987) for examples. — Martijn Pieters, Mar 21 '16 at 19:57
Sorry, didn't mean to nix your suggested edit, just pare it down to the placeholder multiplication fix. — Martijn Pieters, Mar 23 '16 at 22:34
No problem. I had to make at least 6 changes (including some useless) for the edit to be taken. — m_pOatrix, Mar 23 '16 at 23:55

Martijn Pieters · Accepted Answer · 2016-03-23T22:28:16.340

You can use string formatting to generate your placeholders:

statement = "SELECT * FROM table WHERE column IN ({0})".format(
    ', '.join(['%s'] * len(filterList)))
cursor.execute(statement, filterList)

You may still want to test that filterList is not empty, and omit the WHERE clause altogether in that case:

statement = "SELECT * FROM table"
if filterList:
    statement += " WHERE column IN ({0})".format(
        ', '.join(['%s'] * len(filterList)))
cursor.execute(statement, filterList)

Nick Ellis · Answer 2 · 2016-03-22T18:17:48.913

-1

you should be able to insert it as a complete string into a mysql in clause like so.

inList = "','".join(str(i) for i in filterList)
inList = r"'%s'" % inList
sqlRequest = 'SELECT * FROM table WHERE column IN (%s)'
sqlResult = cursor.execute(sqlRequest, inList)

edited Mar 22 '16 at 18:17

answered Mar 21 '16 at 20:03

Nick Ellis

1,048
11
24

No, because this *SQL quotes* the comma-separated string. If `filterList` is `['foo', 'bar', 'baz']`, you now limit your rows to those where `column` is equal to one string, with value `'foo,bar,baz'`. – Martijn Pieters Mar 21 '16 at 20:18
My bad, I edited it so it works, by making it a raw string. – Nick Ellis Mar 22 '16 at 18:19
No, you misunderstand what a raw string does. And it is the *database adapter* uses the `%s` placeholder to quote the string value in `inList`, to prevent sql injection attacks (and as a performance improvement opportunity where the `sqlRequest` query plan can be cached independent of the parameter values). I now notice a second mistake, the second argument to `execute()` must be a sequence; you passed in a string so *each individual character* is now a parameter value. – Martijn Pieters Mar 22 '16 at 18:22
A raw string is just *syntax*, to produce a string object the same way `'...'` does, but minus interpreting the ``\...`` escape sequences in the regular syntax. – Martijn Pieters Mar 22 '16 at 18:22

MySQL and PYTHON. How to query an WHERE 'column' IN tuple

2 Answers2