How to Safely Use SQL Wildcards with Python's `MySQLdb` to Avoid Injection?

Using SQL Wildcards and LIKE Operators in Python String Formats

Inserting SQL wildcards and LIKE operators into Python string formats can be challenging. This article offers a solution to common errors encountered when attempting to execute MySQL queries using MySQLdb.

Error from MySQLdb

Attempt 3 and 4 returned errors from MySQLdb, indicating "not enough arguments for format string." This occurs because the % sign is interpreted as a format specifier by the string formatting operator in Python.

Vulnerability to SQL Injection

The first four attempts shown are vulnerable to SQL injection attacks. User input, such as the query variable, should be sanitized or escaped to prevent malicious SQL from being executed.

Correct Approach

The recommended approach is to use a placeholder instead of string formatting. Here's an example:

curs.execute("""SELECT tag.userId, count(user.id) as totalRows 
                  FROM user 
            INNER JOIN tag ON user.id = tag.userId 
                 WHERE user.username LIKE %s""", ('%' + query + '%',))

Two arguments are passed to execute():

• The SQL statement with a placeholder to insert the wildcard.
• A tuple containing the sanitized query value with the wildcard characters.

This approach ensures that the SQL statement is valid and protected from SQL injection.

The above is the detailed content of How to Safely Use SQL Wildcards with Python's `MySQLdb` to Avoid Injection?. For more information, please follow other related articles on the PHP Chinese website!