Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

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.

~5 min readPython
#psycopg#sql-injection#parameterized-queries#postgresql
Illustration of a SQL query with a parameter placeholder separated from user input by a security shield in front of a PostgreSQL database.

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:

python
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:

python
cursor.execute( f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'" )

An attacker who supplies admin' -- as the username produces:

sql
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:

python
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:

python
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:

python
cur.execute( "SELECT * FROM users WHERE email = %s AND active = %s", (email, True) )

With a dictionary, you name the placeholders:

python
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:

python
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:

python
# 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:

python
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:

sql
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:

python
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:

python
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:

python
# 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:

python
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:

python
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:

python
# 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:

python
# 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:

python
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:

python
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 tools

Small utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.

All Python guides

More in Python

All Python guides

The guides filed next to this one in Python, in library order.