Imploding a list for use in a Python MySQL 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 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 SO question, but the answers listed there either do not work for MySQL database 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

Answer 3

This appears to still be a problem with Python3 in 2021, as pointed out in the comment by Rubms to the answer by markk.

Adding about 9 lines of code to the method "_process_params_dict" in "cursor.py" in the mysql connector package to handle tuples solved the problem for me:

def _process_params_dict(self, params):
    """Process query parameters given as dictionary"""
    try:
        to_mysql = self._connection.converter.to_mysql
        escape = self._connection.converter.escape
        quote = self._connection.converter.quote
        res = {}
        for key, value in list(params.items()):
            if type(value) is tuple: ### BEGIN MY ADDITIONS
                res[key.encode()] = b''
                for subvalue in value:
                    conv = subvalue
                    conv = to_mysql(conv)
                    conv = escape(conv)
                    conv = quote(conv)
                    res[key.encode()] = res[key.encode()] + b',' + conv if len(res[key.encode()]) else conv
            else: ### END MY ADDITIONS
                conv = value
                conv = to_mysql(conv)
                conv = escape(conv)
                conv = quote(conv)
                res[key.encode()] = conv
    except Exception as err:
        raise errors.ProgrammingError(
            "Failed processing pyformat-parameters; %s" % err)
    else:
        return res

Answer 4

Maybe a little late to the question, but I stumbled upon a similar problem, but I wanted to use a dict of named parameters instead of a tuple (because if I want to modify the parameters to add or remove some, I don't want to re-construct the tuple, messing the order can be very easy and bug-inducing...).

My solution was to format the query string to explode the parameter into several parameters, and then construct the parameter dict with these new params:

from typing import Iterable

query = """
SELECT *
FROM table
WHERE id IN (%(test_param)s)
"""

parameters = {"test_param": [1, 2, 3])

new_params = {}

for k, v in parameters.items():
    if isinstance(v, Iterable):
        iterable_params = {f"{k}_{i}": value for i, value in enumerate(v)}
        iterable_params_formatted = [f"%({k}_{i})s" for i in range(0, len(v))]
        query = query.replace(f"%({k})s", ", ".join(iterable_params_formatted))
        new_params.update(iterable_params)
    else:
        new_params[k] = v

print(query)
print(new_params)

Result:

> SELECT *
FROM table
WHERE id IN (%(test_param_0)s, %(test_param_1)s, %(test_param_2)s)

> {'test_param_0': 1, 'test_param_1': 2, 'test_param_2': 3}

Could be done better, but I couldn't find a solution using a dict of named parameters instead of an ordered tuple.

Answer 5

If you use Django 2.0 or 2.1 and Python 3.6, this is the right way:

from django.db import connection
RESULT_COLS = ['col1', 'col2', 'col3']
RESULT_COLS_STR = ', '.join(['a.'+'`'+i+'`' for i in RESULT_COLS])
QUERY_INDEX = RESULT_COLS[0]

TABLE_NAME = 'test'
search_value = ['ab', 'cd', 'ef']  # <-- a list
query = (
    f'SELECT DISTINCT {RESULT_COLS_STR} FROM {TABLE_NAME} a '
    f'WHERE a.`{RESULT_COLS[0]}` IN %s '
    f'ORDER BY a.`{RESULT_COLS[0]}`;'
)  # <- 'SELECT DISTINCT a.`col1`, a.`col2`, a.`col3` FROM test a WHERE a.`col1` IN %s ORDER BY a.`col1`;'
with connection.cursor() as cursor:
    cursor.execute(query, params=[search_value])  # params is a list with a list as its element

ref: https://stackoverflow.com/a/23891759/2803344 https://docs.djangoproject.com/en/2.1/topics/db/sql/#passing-parameters-into-raw

Answer 6

list_of_ids = [ 1, 2, 3]
query = "select * from table where x in %s" % str(tuple(list_of_ids))
print query

This could work for some use-cases if you don't wish to be concerned with the method in which you have to pass arguments to complete the query string and would like to invoke just cursror.execute(query).

Another way could be:

"select * from table where x in (%s)" % ', '.join(str(id) for id in list_of_ids)

Answer 7

Another simple solution using list comprehension:

# creating a new list of strings and convert to tuple
sql_list = tuple([ key.encode("UTF-8") for key in list_of_ids ])

# replace "{}" with "('id1','id2',...'idlast')"
cursor.execute("DELETE FROM foo.bar WHERE baz IN {}".format(sql_list))

Answer 8

Though this question is quite old. I am sharing my solution if it can help someone.

list_to_check = ['A', 'B'] cursor.execute("DELETE FROM foo.bar WHERE baz IN ({})".format(str(list_to_check)[1:-1])

Tested with Python=3.6

Answer 9

Very simple: Just use the below formation

rules_id = ["9","10"]

sql1 = "SELECT * FROM attendance_rules_staff WHERE id in("+", ".join(map(str, rules_id))+")"

", ".join(map(str, rules_id))