How Can I Safely Execute Dynamic Parameterized SQL Queries in Python?

Using parameterization to execute dynamic SQL queries in Python

In Python, parameterized SQL queries are used to prevent SQL injection vulnerabilities. The recommended approach is to use the cursor.execute() method, passing the query and parameters separately. However, sometimes you want to store a query in a variable for later execution.

Queries using parameterized variables

To execute a parameterized SQL query using variables, we need to unwrap the variables into the cursor.execute() call. cursor.execute() The method accepts up to three parameters: query, optional parameters, and an optional dictionary of named parameters.

Use * (asterisk) to unpack variables

One way is to unpack the variable using the asterisk (*) operator, which expands a tuple into a single argument. However, this requires us to split the query and parameters into two separate lists or tuples.

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

Manually unpack variables

Alternatively, we can manually unpack the variables into the correct parameters. This allows us to use a single variable for the query and parameters.

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

Advantages of explicit variables

Using separate variables for queries and parameters makes the code more readable and easier to maintain. It also allows us to easily modify the query or parameters at runtime without having to recreate the variables.

So while it is possible to store parameterized SQL queries in variables, it is generally preferred to separate the query and parameters into explicit variables to improve readability and flexibility.

The above is the detailed content of How Can I Safely Execute Dynamic Parameterized SQL Queries in Python?. For more information, please follow other related articles on the PHP Chinese website!