Search code examples
pythonmysql

Imploding a list for use in a Python MySQL IN clause

I know how to map a list to a string:

foostring = ",".join( map(str, list_of_ids) )

And I know that I can use the following to get that string into an IN clause:

cursor.execute("DELETE FROM foo.bar WHERE baz IN ('%s')" % (foostring))

How can I accomplish the same thing safely (avoiding SQL injection) using a MySQL database?

In the above example, because foostring is not passed as an argument to execute, it is vulnerable. I also have to quote and escape outside of the MySQL library.

(There is a related Stack Overflow question, but the answers listed there either do not work for MySQL database or are vulnerable to SQL injection.)

Solution

  • Use the list_of_ids directly:

    format_strings = ','.join(['%s'] * len(list_of_ids))
cursor.execute("DELETE FROM foo.bar WHERE baz IN (%s)" % format_strings,
                tuple(list_of_ids))

    That way you avoid having to quote yourself, and avoid all kinds of SQL injection.

    Note that the data (list_of_ids) is going directly to MySQL's driver, as a parameter (not in the query text) so there isn't any injection. You can leave any characters you want in the string; there isn't any need to remove or quote characters.