Nesting a list within IN clause in Python MySQL

Question

I know how to map a list to a string:

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

I know I can put that string into an IN clause using:

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

I need to implement the same functionality in a MySQL database in a safe way (avoiding SQL injection). In the above example, because foostring is not passed as a parameter to execute, it is vulnerable. I also have to do quoting and escaping outside of the MySQL library.

(There is a related SO question, but the answers listed there either don't work with MySQL databases or are vulnerable to SQL injection attacks.)

Answer 1

Although this question is old, I wanted to leave a reply in case anyone else is also looking for what I'm looking for

The accepted answer gets confusing when we have a lot of parameters or want to use named parameters

After some attempts

ids = [5, 3, ...]  # id列表
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 under python2.7 and pymysql==0.7.11 passed

Answer 2

Use directlylist_of_ids：

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

This way you can avoid quoting yourself and avoid various SQL injection problems.

Please note that the data (list_of_ids) is passed directly to mysql's driver as a parameter (not in the query text), so there are no injection issues. You can keep any characters in the string without removing or quoting characters.