Python Psycopg Parameterized Queries and SQL Injection Prevention
python psycopg parameterized queries and sql injection prevention: Use psycopg parameterized queries to prevent SQL injection in Python: %s placeholders, tuple and dic...
python psycopg parameterized queries and sql injection prevention requires a clear understanding of the core syntax, runtime behavior, and practical implementation patterns demonstrated in the examples below.
SQL injection happens when user-controlled input is merged into a SQL statement before it reaches PostgreSQL. The classic psycopg failure looks like this:
email = request.form["email"] cursor.execute( f"SELECT id, name FROM users WHERE email = '{email}'" )
If email contains ' OR '1'='1, the query becomes SELECT id, name FROM users WHERE email = '' OR '1'='1', which returns every row in the table. The fix is to use python psycopg parameterized queries and sql injection prevention techniques: send the query text and the values separately so PostgreSQL never interprets user input as SQL.
Why String Interpolation Fails
When you build a query with f-strings or % formatting, the values are merged into the SQL text before the driver sends it. PostgreSQL has no way to distinguish the structure of the statement from the data inside it. A value containing quotes, semicolons, or comment markers becomes part of the statement grammar.
Consider a login check:
cursor.execute( f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'" )
An attacker who supplies admin' -- as the username produces:
SELECT * FROM users WHERE username = 'admin' --' AND password = '...'
The -- comment marker removes the password check entirely. Escaping quotes manually is fragile because PostgreSQL's escaping rules depend on the server's standard_conforming_strings setting and the encoding in use. Relying on manual escaping is not a maintainable defense.
Using %s Placeholders with execute()
The correct approach is to pass the query and the parameters as separate arguments to cursor.execute(). psycopg uses %s as a placeholder for values:
import psycopg conn = psycopg.connect("dbname=app user=app") cur = conn.cursor() email = "user@example.com" cur.execute( "SELECT id, name FROM users WHERE email = %s", (email,) ) row = cur.fetchone()
The driver sends the query text and the parameter values separately to the server using PostgreSQL's extended query protocol. The server binds the values into the statement after parsing, so the input never becomes part of the SQL grammar. This works for any data type that psycopg can adapt: strings, integers, floats, dates, UUIDs, and JSON.
The same placeholder syntax works for INSERT, UPDATE, and DELETE:
cur.execute( "INSERT INTO users (email, name) VALUES (%s, %s)", (email, name) ) cur.execute( "UPDATE users SET name = %s WHERE id = %s", (new_name, user_id) )
Note that %s is psycopg's placeholder syntax, not Python's % string formatting. The query string is passed as-is to the driver, which parses the placeholders itself.
Passing Parameters as Tuples and Dictionaries
The second argument to execute() can be a tuple, a list, or a dictionary. With a tuple, placeholders are matched positionally:
cur.execute( "SELECT * FROM users WHERE email = %s AND active = %s", (email, True) )
With a dictionary, you name the placeholders:
cur.execute( "SELECT * FROM users WHERE email = %(email)s AND active = %(active)s", {"email": email, "active": True} )
Dictionary parameters are useful when the same value appears more than once in the query:
cur.execute( "SELECT * FROM events WHERE start_time >= %(cutoff)s AND created_at <= %(cutoff)s", {"cutoff": cutoff_time} )
A common mistake is passing a single value without wrapping it in a tuple:
# Wrong: psycopg treats the string as a sequence of characters cur.execute("SELECT * FROM users WHERE email = %s", email) # Correct cur.execute("SELECT * FROM users WHERE email = %s", (email,))
When the parameter is a string, psycopg iterates over it and tries to bind each character as a separate parameter, which raises an error or silently produces the wrong result. A one-element tuple (email,) is required.
Building IN Clauses with Dynamic Lists
The %s placeholder cannot represent a list of values directly. WHERE id IN %s is not valid syntax. You must generate one placeholder per element:
ids = [101, 204, 315] placeholders = ", ".join(["%s"] * len(ids)) query = f"SELECT id, name FROM users WHERE id IN ({placeholders})" cur.execute(query, ids)
For ids = [101, 204, 315], this produces:
SELECT id, name FROM users WHERE id IN (%s, %s, %s)
The f-string builds only the placeholder structure, not the values. The list ids is passed as the parameter sequence, so the values are bound safely. This is not an injection risk because the number of placeholders is derived from the code, not from user input.
The same pattern works with a dictionary when you need named parameters:
ids = [101, 204, 315] placeholders = ", ".join(["%(id_0)s", "%(id_1)s", "%(id_2)s"]) params = {f"id_{i}": value for i, value in enumerate(ids)} cur.execute(f"SELECT * FROM users WHERE id IN ({placeholders})", params)
For very large lists, consider whether an IN clause is the right approach at all. PostgreSQL has a limit on the number of parameters in a single statement (65535 in recent versions), and a query with tens of thousands of placeholders will not plan well. A temporary table or unnest with an array parameter is a better choice for large sets:
cur.execute( "SELECT * FROM users WHERE id = ANY(%s)", (ids,) )
The ANY(%s) form passes the list as a PostgreSQL array, which avoids generating thousands of placeholders. This is both safer and faster for large inputs.
When %s Does Not Apply: Identifiers
Parameterized queries protect values, not identifiers. You cannot use %s for a table name, column name, or other schema object:
# This does not work cur.execute("SELECT * FROM %s WHERE id = %s", (table_name, id))
PostgreSQL does not allow parameters in identifier positions. The driver will raise an error because the placeholder appears where the server expects an identifier, not a value.
If you must build a query with a dynamic table or column name, validate the identifier against an allowlist:
ALLOWED_TABLES = {"users", "orders", "events"} if table_name not in ALLOWED_TABLES: raise ValueError(f"Unsupported table: {table_name}") cur.execute(f"SELECT * FROM {table_name} WHERE id = %s", (id,))
The allowlist is the only reliable defense here. Quoting the identifier with psycopg.sql.Identifier() handles reserved words and special characters, but it does not protect you from allowing an attacker to choose an arbitrary table name. Combine both: validate against an allowlist, then use sql.Identifier() for correct quoting:
from psycopg import sql cur.execute( sql.SQL("SELECT * FROM {} WHERE id = %s").format( sql.Identifier(table_name) ), (id,) )
The sql module builds the query structure safely while values still go through %s parameters.
Common Mistakes That Still Allow Injection
Even with parameterized queries, a few patterns reintroduce the vulnerability.
Mixing Python % formatting with psycopg placeholders is the most common one:
# Wrong: Python formats the values into the string first cur.execute( "SELECT * FROM users WHERE email = '%s'" % email ) # Wrong: same idea with .format() cur.execute( "SELECT * FROM users WHERE email = '{}'".format(email) )
In both cases, the value is merged into the query text before psycopg sees it. The %s inside the string is consumed by Python's formatting, so psycopg never receives a placeholder. The query is just as vulnerable as the f-string version.
Another mistake is using psycopg's mogrify() to build a query and then logging or storing the result. mogrify() returns the fully-bound SQL string, which is useful for debugging, but it should never be executed through a second execute() call:
# Debugging only bound_query = cur.mogrify("SELECT * FROM users WHERE email = %s", (email,)) logger.debug("Query: %s", bound_query)
Executing the mogrified string directly defeats the parameterization because the values are already embedded in the text.
A third issue is passing parameters through string concatenation when building the query in multiple steps:
query = "SELECT * FROM users WHERE email = %s" query += " AND active = %s" # fine cur.execute(query, (email, active))
This is safe because the placeholders remain placeholders. The danger is only when values, not placeholders, are concatenated into the string.
Query Planning and Prepared Statements
Parameterized queries also affect query planning. When you send the same query text repeatedly with different parameters, PostgreSQL can reuse the parsed statement and the query plan. With psycopg3, you can opt into server-side prepared statements to make this explicit:
cur.execute( "SELECT * FROM users WHERE email = %s", (email,), prepare=True )
With prepare=True, the driver sends a PREPARE statement once and then uses EXECUTE for subsequent calls with the same query text. This avoids re-parsing and re-planning the query on every call. The tradeoff is that the plan is fixed for the prepared statement, so if the data distribution changes significantly, the cached plan may become suboptimal.
For most applications, the default behavior is fine. The extended query protocol already separates the query text from the parameters, which is what makes parameterized queries safe. Prepared statements are a performance optimization on top of that, not a security requirement.
The security property does not depend on prepared statements. Even without prepare=True, the values are bound by the server and never interpreted as SQL. The parameterization is what prevents injection; prepared statements only reduce planning overhead for repeated queries.