SQL Prepared Statements: A Practical Tutorial

What and Why

A prepared statement is a parameterized query that the database parses, plans, and stores once, then executes many times with different values. Two huge benefits follow: SQL injection becomes structurally impossible, and repeated execution gets cheaper.

If you write any application that touches a database, prepared statements should be your default. String concatenation into SQL is the single most exploited vulnerability category in web history.

Mental Model

A normal query goes through parse, plan, execute every time. A prepared statement splits that into two phases: PREPARE (parse + plan) and EXECUTE (bind values + run). The placeholders ($1, ?) are not text substitution - they are typed values bound through a separate protocol channel, so user input can never become SQL syntax.

Hands-on Example

In raw Postgres SQL:

PREPARE find_user (text) AS
  SELECT id, name FROM users WHERE email = $1;

EXECUTE find_user('alice@example.com');
EXECUTE find_user('bob@example.com');

DEALLOCATE find_user;

In Python with psycopg:

cur.execute(
    "SELECT id, name FROM users WHERE email = %s",
    ("alice@example.com",),
)

The driver handles PREPARE/EXECUTE for you when you reuse the same SQL text. Notice you pass values as a tuple, not interpolated into the string.

Client                       Server
|   PREPARE "SELECT..."       |
|---------------------------->|
|                             | parse + plan
|   <plan cached>             |
|<----------------------------|
|                             |
|   EXECUTE bind=[alice]      |
|---------------------------->|
|                             | execute(plan)
|<--------- rows -------------|
|                             |
|   EXECUTE bind=[bob]        |
|---------------------------->|
|                             | execute(plan)
|<--------- rows -------------|

The plan is computed once and reused. Bound values go through the binary protocol and can never escape into the SQL text.

Common Pitfalls

The most dangerous mistake is mixing parameterization with interpolation. This is still injectable:

cur.execute(f"SELECT * FROM {table} WHERE id = %s", (user_id,))

The id is safe, but table is concatenated. Identifiers (table and column names) cannot be parameterized in SQL - they require a strict allowlist or a quoting helper like psycopg.sql.Identifier.

Another trap is plan caching with skewed data. If the first execution binds a rare value, Postgres may pick an index plan that becomes terrible when later executions bind a common value. This is the “bind peek” or “generic plan” problem. Postgres switches to a generic plan after 5 executions; if you see weird regressions, try plan_cache_mode = 'force_custom_plan'.

People also assume prepared statements survive across connections. They do not. Each connection has its own prepared statement cache, which is why connection pooling (especially transaction-mode PgBouncer) can break prepared statements unless you use protocol-level prepares.

Practical Tips

Always use the placeholder syntax of your driver: %s for psycopg, ? for SQLite and JDBC, $1 for native pg protocol, named like :email for SQLAlchemy. Never concatenate user input - not even “safe” types like integers from request paths.

For ORMs, trust them: SQLAlchemy, Django ORM, and ActiveRecord all parameterize. The danger zone is raw SQL escape hatches like .raw(), .extra(), or text() with f-strings.

If you are using PgBouncer in transaction or statement mode, enable protocol-level prepared statements (Postgres 14+ and PgBouncer 1.21+) so the cache lives on the server side correctly. Otherwise disable named prepares in your driver to avoid “prepared statement does not exist” errors.

For high-throughput hot queries, measure the plan cache hit. Tools like pg_stat_statements show calls, mean_exec_time, and now plans vs calls - if plans keeps climbing, your prepares are not being reused.

Wrap-up

Prepared statements are the boring, correct way to talk to a database. They make injection impossible by design and make repeated queries cheaper by reusing plans. Use them everywhere, never interpolate user input, allowlist identifiers, and understand how your connection pooler interacts with the prepared statement cache. That covers 99% of the safety and performance value with almost no extra code.