How to Safely Use a Python List in a MySQL IN Clause?

Mapping a List for Use in a Python MySQL IN Clause

When attempting to use a list of IDs in an IN clause in a MySQL query, it is essential to ensure the safety of the operation, avoiding potential SQL injection vulnerabilities. Unlike mapping the list to a string and using it directly in the query, which leaves it vulnerable, a safer approach is to use the list directly as a parameter.

To achieve this, one can utilize a format string to generate the appropriate number of placeholders (%s) based on the length of the list. The list itself is then passed as a tuple to the execute() function, thereby avoiding the need for manual quoting and escaping.

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

By sending the data (list_of_ids) as a parameter, it is handled by MySQL's driver directly, eliminating the risk of injection. This method allows for the preservation of any characters within the string, without the need for additional sanitization or quoting.

The above is the detailed content of How to Safely Use a Python List in a MySQL IN Clause?. For more information, please follow other related articles on the PHP Chinese website!