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

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

Answer 1

I know many provided a version of this answer but this is what worked for me. Essentially adding two empty values to the end of a list.

v=[1,2,3]
query= f""" SELECT * FROM table IN {tuple(list(v)+['',''])};"""

Answer 2

Use:

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 3

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.

Answer 4

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 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])  # parameters is a list with a list as its element

References

• How to pass a list of values for an `in` SQL clause in Django
• Passing parameters into raw()

Answer 6

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

Note:

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

Answer 7

The accepted answer gets messy when we have a lot of the parameters 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'
})

It was kested with Python 2.7 and pymysql 0.7.11.

Answer 8

yet another way.

myList = [
  "this","andthis","butalsothis","anddontforgetaboutthis"
]

myListStr = '"' + '","'.join(myList) + '"'


query = (
            ' SELECT'
            '  col1,'
            '  col2,'
            '  col3
            ' FROM'
            '  myTable'
            ' WHERE'
            '   col3 IN ('+ myListStr +')'
          )

Answer 9

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 10

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 11

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