How to Safely Use Lists in MySQL IN Clauses to Prevent SQL Injection?

Securely Imploding Lists for MySQL IN Clauses

Database security is paramount, and avoiding SQL injection is crucial. When using a list of values in a MySQL IN clause, it's essential to do so safely.

Problem:

Expanding on a list into a string for use in an IN clause is susceptible to SQL injection.

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

Solution:

To safeguard against SQL injection, use the list of values directly as parameters:

# Safe
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 approach passes the list of values as parameters instead of embedding them in the query string, effectively preventing injection attacks.

By directly passing data to the MySQL driver as parameters, you eliminate the need for manual quoting and escaping, ensuring the integrity of your database operations.

The above is the detailed content of How to Safely Use Lists in MySQL IN Clauses to Prevent SQL Injection?. For more information, please follow other related articles on the PHP Chinese website!