How Can I Safely Use Variables in SQL Queries with Python's `cursor.execute()` Function?

Using variables in Python for parameterized SQL queries

In Python, parameterized SQL queries are crucial to prevent SQL injection. Although the recommended approach is to pass variables directly to the execute function, in some cases it is also desirable to use variables to represent the query itself.

Query as variable: syntax error

Trying to execute a SQL query directly from a variable (as shown below) results in a syntax error:

<code>sql = "INSERT INTO table VALUES (%s, %s, %s)", var1, var2, var3
cursor.execute(sql)</code>

Execute function

cursor.executeThe signature of the function specifies that it accepts up to three parameters:

<code>cursor.execute(self, query, args=None)</code>

Therefore, it is incorrect to pass a variable containing query and parameters as a single parameter.

Solution

To solve this problem, there are two possible solutions:

Tuple expanded with parameters:

<code>sql_and_params = "INSERT INTO table VALUES (%s, %s, %s)", var1, var2, var3
cursor.execute(*sql_and_params)</code>

However, this approach may cause errors if the tuple has more than three elements.

Separate query and parameters:

Alternatively, you can separate the query and parameters:

<code>sql = "INSERT INTO table VALUES (%s, %s, %s)"
args = var1, var2, var3
cursor.execute(sql, args)</code>

This method is more verbose, but ensures that the correct number of parameters are passed to the execute function.

The above is the detailed content of How Can I Safely Use Variables in SQL Queries with Python's `cursor.execute()` Function?. For more information, please follow other related articles on the PHP Chinese website!