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 and Database API: How to deal with multi where condition in Python for examples. — Martijn Pieters
– Martijn Pieters, Commented Mar 21, 2016 at 19:57
Sorry, didn't mean to nix your suggested edit, just pare it down to the placeholder multiplication fix. — Martijn Pieters
– Martijn Pieters, Commented Mar 23, 2016 at 22:34
No problem. I had to make at least 6 changes (including some useless) for the edit to be taken. — m_pOatrix
– m_pOatrix, Commented Mar 23, 2016 at 23:55

Martijn Pieters · Accepted Answer · 2016-03-23 22:28:16Z

4

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)

edited Mar 23, 2016 at 22:28

answered Mar 21, 2016 at 20:01

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nick Ellis · Accepted Answer · 2016-03-22 18:17:48Z

-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, 2016 at 18:17

answered Mar 21, 2016 at 20:03

Nick Ellis

1,07711 silver badges25 bronze badges

4 Comments

Martijn Pieters Over a year ago

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'.

Nick Ellis Over a year ago

My bad, I edited it so it works, by making it a raw string.

Martijn Pieters Over a year ago

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 Over a year ago

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.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related