imploding a list for use in a python MySQLDB IN clause

Question

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

What I need is to accomplish the same thing SAFELY (avoiding SQL injection) using MySQLDB. 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 SO question, but the answers listed there either do not work for MySQLDB or are vulnerable to SQL injection.)

Answer 1

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 is no injection. You can leave any chars you want in the string, no need to remove or quote chars.

Answer 2

Though this question is quite old, thought it would be better to leave a response in case someone else was looking for what I wanted

Accepted answer gets messy when we have a lot of the params or if we want to use named parameters

After some trials

ids = [5, 3, ...]  # list of ids cursor.execute(''' SELECT  ... WHERE   id IN %(ids)s   AND created_at > %(start_dt)s ''', {   'ids': tuple(ids), 'start_dt': '2019-10-31 00:00:00' }) 

Tested with python2.7, pymysql==0.7.11