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

Answer 1

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)