Safely insert variables into SQL queries in Python

When processing database queries in Python, you usually need to include variables in the query statement. However, be sure to operate in a way that prevents syntax errors or security vulnerabilities.

Consider the following Python code:

cursor.execute("INSERT INTO table VALUES var1, var2, var3")

In this code, var1 is an integer, var2 and var3 are strings. However, when Python tries to include the names var1, var2, and var3 as part of the query text itself, problems arise, resulting in the query invalidation.

To solve this problem, you can use the placeholder replacement mechanism provided by the database API. Here is how to rewrite the code using placeholders:

cursor.execute("INSERT INTO table VALUES (%s, %s, %s)", (var1, var2, var3))

In this improved code:

• %s represents a placeholder to fill in the value.
• (var1, var2, var3) is a tuple containing the value to be inserted.

By passing values ​​as tuples, the database API handles necessary variable escapes and references, ensuring database compatibility and preventing potential security risks.

Note that for a single parameter, a tuple with trailing commas is required:

cursor.execute("INSERT INTO table VALUES (%s)", (var1,))

In addition, avoid using the string formatting operator (%) to insert variables because it can cause security vulnerabilities and the database API does not support it.