Parameter binding gives a database value a designated position in a SQL statement. A name containing a quote or a semicolon remains a value instead of becoming another clause. Reusing a named prepared statement adds a separate optimization: the server can retain statement information across executions in one session. Safe binding does not require the application to create a named statement for every query.

When it breaks

In Psycopg, %s is a driver placeholder, not an invitation to use Python string formatting. Pass the values as the second argument to execute, leave the placeholder unquoted, and use a one-element tuple for one positional value. Interpolating a value first and then calling a parameter-capable API has already crossed the boundary the API was supposed to preserve.

A placeholder represents a value, not a table, column, or keyword. Dynamic identifiers require a deliberate structural choice. Use an allow-list to restrict the supported columns, then the driver’s identifier composition facility to quote that identifier correctly. Keep direction keywords under application control. Numeric LIMIT and OFFSET values can be bound, while still requiring product-specific range validation.

Preparation does not freeze the first execution plan forever. PostgreSQL distinguishes custom plans, built using current parameter values, from generic plans reused across values. Automatic selection considers planning overhead and estimated execution costs. Schema or statistics changes can also trigger replanning. A statement can remain parameterized while its access path changes; plan reuse and injection resistance are different properties.

Check it quickly

Use Python with Psycopg 3 installed and set PG_DSN to a disposable PostgreSQL database. Supply connection configuration through your existing secure mechanism. The recorded run used Psycopg 3.3.5 and PostgreSQL 18.6. Save the following as a Python file and execute it with that environment variable set. The table is temporary and disappears when the connection closes:

import os
import psycopg
from psycopg import sql

with psycopg.connect(os.environ['PG_DSN'], autocommit=True,
                     prepare_threshold=5) as conn:
    conn.execute('CREATE TEMP TABLE people (id integer PRIMARY KEY, label text NOT NULL)')
    conn.execute("INSERT INTO people VALUES (1,'alice'),(2,'bob')")
    query = 'SELECT id FROM people WHERE label=%s ORDER BY id LIMIT %s'
    hostile = "' OR true --"
    assert conn.execute(query, (hostile, 10), prepare=False).fetchall() == []
    count = conn.execute('SELECT count(*) FROM pg_prepared_statements').fetchone()[0]
    assert count == 0
    print('bound without named preparation:', count, 'named statements; no match')
    for value in ("O'Reilly", r'a\b', 'x; DROP TABLE people; --'):
        assert conn.execute('SELECT %s::text', (value,), prepare=False).fetchone() == (value,)
    for label, expected in (('alice', [(1,)]), ('bob', [(2,)])):
        assert conn.execute(query, (label, 10), prepare=True).fetchall() == expected
    assert conn.execute('SELECT count(*) FROM pg_prepared_statements').fetchone() == (1,)
    print('named preparation reused: alice=1, bob=2')

    def ordered(column, limit):
        if column not in {'id', 'label'}:
            raise ValueError('unsupported sort column')
        if type(limit) is not int or not 1 <= limit <= 100:
            raise ValueError('limit must be an integer from 1 through 100')
        statement = sql.SQL('SELECT id FROM people ORDER BY {} LIMIT %s').format(
            sql.Identifier(column))
        return conn.execute(statement, (limit,), prepare=False).fetchall()

    assert ordered('label', 1) == [(1,)]
    for column, limit in [('label; DROP TABLE people', 1), ('id', 0), ('id', 101)]:
        try:
            ordered(column, limit)
        except ValueError:
            pass
        else:
            raise AssertionError('invalid request accepted')
    print('identifier allow-list and bound LIMIT checked')

    conn.execute('PREPARE plan_lab(text) AS SELECT id FROM people WHERE label=$1')
    conn.execute('SET plan_cache_mode=force_custom_plan')
    assert conn.execute("EXECUTE plan_lab('alice')").fetchall() == [(1,)]
    counters = "SELECT generic_plans,custom_plans FROM pg_prepared_statements WHERE name='plan_lab'"
    assert conn.execute(counters).fetchone() == (0, 1)
    conn.execute('SET plan_cache_mode=force_generic_plan')
    assert conn.execute("EXECUTE plan_lab('bob')").fetchall() == [(2,)]
    assert conn.execute(counters).fetchone() == (1, 1)
    print('same prepared statement: custom=1, generic=1')
    conn.execute('RESET plan_cache_mode')
    conn.execute('DEALLOCATE plan_lab')

The first result reports zero named statements and no match for the hostile value. That call explicitly disables named preparation while retaining binding, so it demonstrates that the safety boundary exists before statement reuse. Quotes, backslashes, and semicolons then round-trip unchanged; the table remains available for subsequent queries.

Two calls with prepare=True reuse one named statement and return Alice’s identifier one and Bob’s identifier two. The configured threshold permits explicit preparation. Setting prepare_threshold=None instead disables named preparation altogether in the tested driver, including explicit requests. Do not confuse that switch with disabling value binding.

The ordering function returns one row for a supported column and bound limit. It rejects an unsupported column and limits outside one through one hundred before issuing SQL. Identifier quoting protects syntax; the allow-list defines which valid syntax the endpoint actually supports. Neither decision should be delegated to arbitrary request text.

Finally, the same SQL prepared statement executes once under forced custom planning and once under forced generic planning. Its counters change from zero generic and one custom plan to one of each. Both return the expected row. These forced settings make the distinction observable; they do not measure what automatic planning would choose for a realistic skewed workload or recommend forcing either mode globally.

Inspect reuse in the real deployment

PostgreSQL’s default automatic policy starts parameterized prepared statements with custom plans and later compares a generic alternative. Look at representative parameter distributions and actual execution plans before deciding whether reuse helps. This two-row exercise makes no latency or network-round-trip claim. Driver protocol batching, connection setup, and query cost affect those measurements independently.

Named statements belong to server sessions. Connection pools and transaction-pooling proxies must manage that lifetime correctly, using explicitly supported prepared-statement behavior or disabling named reuse where necessary. Binding remains the default even for a query executed only once. Continue to enforce tenant predicates, object permissions, and result-size limits: a safely parameterized query can still return data the caller was never meant to see.

Sources