Python Psycopg Parameterized Queries and SQL Injection Prevention
Use psycopg parameterized queries with %s placeholders to prevent SQL injection in Python. Covers tuple and dictionary parameters, dynamic IN clauses, identifier safety, and common mistakes.

Psycopg's parameterized queries prevent SQL injection by sending the query text and the parameter values separately to PostgreSQL. The examples use psycopg 3 (the psycopg package). This guide covers the %s placeholder syntax, passing parameters as tuples or dictionaries, building dynamic IN clauses, handling identifiers, and common parameterization mistakes.
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 parameterized queries: 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, including strings, integers, floats, dates, and UUIDs.
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. The generated placeholders are fixed by your code; user input remains inside the parameter list.
The same pattern works with a dictionary when you need named parameters:
ids = [101, 204, 315]
placeholders = ", ".join([f"%(id_{i})s" for i in range(len(ids))])
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, and a query with tens of thousands of placeholders will not plan well. A temporary table or = ANY(%s) 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 usually a better fit for very 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 Parameterization Mistakes
Even with parameterized queries, a few patterns can undermine the protection or make the code misleading.
Mixing Python % formatting with psycopg placeholders is the most dangerous 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. There is no placeholder left for psycopg to bind, so the query is just as vulnerable as the f-string version.
Another misuse is treating mogrify() as a way to build a query for later execution. mogrify() returns the fully-bound SQL string, which is useful for debugging, but it should not 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 a mogrified string directly is not the same as sending a parameterized query: the values are already embedded in the SQL text. Use mogrify() only for debugging, and keep passing parameters to execute().
String concatenation is not always unsafe. Building the query from placeholder fragments is fine:
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. If you send the same query text repeatedly with different parameters, a server-side prepared statement lets PostgreSQL reuse the parsed statement and the query plan. With psycopg 3, you can opt into prepared statements to make this explicit:
cur.execute(
"SELECT * FROM users WHERE email = %s",
(email,),
prepare=True
)With prepare=True, the driver prepares the query on the server once and reuses the prepared statement 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 prepared statement may keep reusing a plan that does not fit a particular parameter value, so if the data distribution changes significantly, the cached plan can 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.
Try matching browser tools
All toolsSmall utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related Python guides
All Python guidesPython iter(): How the Built-in Function Works
Explains Python's iter() built-in: how it creates iterators, the two-argument sentinel form, custom iterator classes, and common edge cases.
Python Pika: Queues, Exchanges, Routing Keys, and Acknowledgements
Learn how to use Python's Pika client with RabbitMQ: declare queues and exchanges, publish with routing keys, consume messages, and handle acknowledgements reliably.
Python Kafka: kafka-python vs Confluent Client
python kafka python vs confluent kafka: Compare kafka-python and Confluent Kafka Python clients: API differences, performance, feature coverage, and when to choose eac...
Python Kafka Offsets, Partitions, and Manual Commits
Learn how Kafka offsets and partitions work and how to manually commit offsets in Python consumers with kafka-python and confluent-kafka. Covers sync vs async commits, rebalances, and seeking.
More in Python
All Python guidesThe guides filed next to this one in Python, in library order.