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.)
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. Show activity on this post.
When I try it, an error says that is because of an error in MySQL syntax In general, if you have an error message, you should include it in the question. The answer to your original question is: No, you can't insert a list like that.
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.)
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With