SQL injection, and the one habit that prevents it

SQL injection has been at or near the top of every security-risk list for two decades. It is still there not because it is subtle, but because the unsafe way to write a query is the way that first occurs to you, and it works perfectly until someone attacks it.

The entire defence fits in one sentence: never build a query by putting data into the string. Pass the data separately, as parameters. This lesson is why that works, and the few cases that need more thought.

The mistake

You want to look up a member by name. The obvious code:

name = request.args["name"]
sql = f"SELECT id, name FROM members WHERE name = '{name}'"     # WRONG
cur.execute(sql)

It works in every test, because your tests use names like Asha. It works because you build the query text and the data as one string, and the database cannot tell which parts you meant as instructions and which as values. To the parser it is all one program.

So when the value itself contains SQL syntax — a quote, a keyword — the parser reads it as part of the program. The value has become code. That is the whole vulnerability, and the classic demonstration is a name like ' OR '1'='1, which turns a lookup into "match every row" and is how login forms used to be bypassed. The same hole has been used to read other tables, dump password hashes, and delete data.

It is not only a security problem. A member legitimately named O'Brien breaks the same query with a syntax error, because the apostrophe closes the string early. The unsafe pattern is also just buggy. If you ever wondered why a form rejected an apostrophe, this is often why — someone patched the symptom instead of the cause.

The fix, which you have been using all along

name = request.args["name"]
cur.execute("SELECT id, name FROM members WHERE name = %s", (name,))

The %s is not string formatting. It is a placeholder. The driver sends the query text and the parameter value to the server as two separate things, and the server plans the query before it ever sees the value. The value cannot change the plan, because the plan already exists. It can only fill the hole reserved for it.

Prove it to yourself by looking at what the server actually receives when the malicious payload is passed as a parameter:

SELECT id, name FROM members WHERE name = ''' OR ''1''=''1'

The whole payload — quotes and all — has been quoted into a single string literal. The database dutifully searches for a member literally named ' OR '1'='1, finds none, and returns zero rows. The attack became an ordinary, harmless search. And O'Brien now works too, for the same reason.

This is why every lesson in this course used %s and $1. It was never only about tidiness. It is the security boundary.

Say it in each language

Python (psycopg): %s, values in a tuple.

cur.execute("SELECT * FROM members WHERE id = %s AND membership = %s", (member_id, tier))

Note it is %s for every type — never %d or %f. The driver knows the types.

Node (node-postgres): $1, $2, values in an array.

await pool.query('SELECT * FROM members WHERE id = $1 AND membership = $2', [memberId, tier]);

Java (JDBC): ?, set positionally.

var ps = conn.prepareStatement("SELECT * FROM members WHERE id = ? AND membership = ?");
ps.setLong(1, memberId);
ps.setString(2, tier);

Different syntax, identical idea: the query text is fixed, the values arrive separately.

The trap: the parts a placeholder cannot cover

A placeholder stands in for a value — a number, a string, a date. It cannot stand in for a table name, a column name, a keyword, or the direction of an ORDER BY. This is not a driver limitation; those things are part of the query's structure, which must be fixed before planning.

So this does not work:

cur.execute("SELECT * FROM members ORDER BY %s", (sort_column,))     # not what you meant

It runs, but it sorts by a constant string, not by that column — a silent bug. And the moment you reach for an f-string to fix it, you are back to injection:

sql = f"SELECT * FROM members ORDER BY {sort_column}"                # WRONG again

If sort_column comes from a user — a clickable table header, an API sort parameter — this is a live hole.

The fix is an allow-list. You know every valid column; enumerate them and reject anything else:

SORTABLE = {"name", "joined", "membership"}          # a set of known-good values
if sort_column not in SORTABLE:
    raise ValueError(f"cannot sort by {sort_column!r}")
sql = f"SELECT * FROM members ORDER BY {sort_column}"      # now safe: it is one of three strings

The value is now guaranteed to be one of three literals you wrote, so interpolating it is safe. Validate identifiers against a fixed list; never sanitise them by escaping. Escaping a value is the driver's job and it does it perfectly; escaping an identifier yourself is a game you lose.

psycopg gives you a proper tool for the genuinely dynamic case, which quotes an identifier correctly:

from psycopg import sql
query = sql.SQL("SELECT DISTINCT {} FROM members").format(sql.Identifier(sort_column))

sql.Identifier produces SELECT DISTINCT "membership" FROM members with the name correctly quoted. Use it — but an allow-list is still wise, because a correctly-quoted name of a column that does not exist is just an error, and a correctly-quoted name of a column the user should not see is a data leak.

Other things that are the same mistake wearing a disguise

IN lists. The wrong instinct is to build the list by joining strings. Use one placeholder that takes an array:

cur.execute("SELECT * FROM members WHERE id = ANY(%s)", (member_ids,))   # a Python list

await pool.query('SELECT * FROM members WHERE id = ANY($1)', [memberIds]);

= ANY(array) is one parameter regardless of length, which also avoids a different query text for every list size — the plan-cache point from the planning lesson.

LIKE patterns. The value is still a parameter; you build the pattern in code:

cur.execute("SELECT * FROM books WHERE title LIKE %s", (f"%{term}%",))

The % signs are part of the value you pass, not the query text. Safe. (If the user's term itself contains % or _ and you want those treated literally, escape them with ESCAPE — a correctness matter, not a security one.)

LIMIT and OFFSET. These are values and take placeholders:

cur.execute("SELECT * FROM members LIMIT %s OFFSET %s", (per_page, per_page * (page - 1)))

No f-string needed. If you find yourself interpolating a page number, stop.

Where injection actually hides in a real codebase

It is rarely the query you are looking at. It hides in:

String-building helpers — a function that assembles a WHERE clause from a dict of filters, one f"{k} = '{v}'" at a time. Search your codebase for f-strings and + next to the word SELECT, WHERE, INSERT.
Dynamic search and reporting — the features that legitimately need dynamic columns, which is exactly where the allow-list gets forgotten.
ORM escape hatches. An ORM parameterises for you, which is a real reason to use one. But every ORM has a raw-SQL door — .raw(), .extra(), text(), queryRaw — and interpolating into that is exactly as dangerous as raw psycopg. The next lesson covers this.
Migrations and admin scripts, which people write quickly and review less.

What does not protect you

Worth stating, because these are believed:

Escaping quotes by hand. You will miss a case; different contexts need different escaping; and it is unnecessary when the driver does it correctly. Do not write your own.
A web application firewall. It catches known payload shapes and is trivially bypassed. It is a layer, not the fix.
Client-side validation. The attacker does not use your form; they send HTTP requests directly.
Stored procedures, by themselves — a procedure that builds dynamic SQL from its arguments with string concatenation is just as vulnerable, one layer down.
LIMIT on the query. A UNION-based attack reads other data within your limit.

Only one thing protects you: the data never enters the query string.

Defence in depth, once the query is safe

Parameterisation stops injection. These reduce the blast radius if something else goes wrong:

Least privilege. The account your application connects with should not own the schema. GRANT SELECT, INSERT, UPDATE, DELETE on the tables it uses; not SUPERUSER, not DROP. Then even a hole that slips through cannot drop a table.
statement_timeout, so an injected expensive query cannot run for an hour.
Do not surface raw database errors to users. The message text leaks table and column names that make the next attack easier. Log it; show the user something generic.

Check your work

The one-sentence rule. Never put data into the query string; pass it as parameters.

Why the unsafe version is vulnerable. Query text and data become one string, so the parser cannot tell instructions from values, and a value containing SQL becomes code.

Why it is also just buggy. A legitimate apostrophe (O'Brien) breaks it with a syntax error.

What %s actually is. A placeholder, not string formatting — text and value are sent separately and the query is planned before the value is seen.

What the server receives when a payload is parameterised. The whole payload quoted into a single string literal, matching nothing.

Placeholder syntax in three languages. %s (psycopg), $1 (node-postgres), ? (JDBC).

What a placeholder cannot stand in for. Table names, column names, keywords, ORDER BY direction — the query's structure.

The safe way to allow a dynamic column. An allow-list of known-good names, or sql.Identifier — never hand-escaping.

The safe way to do an IN list. One placeholder with = ANY(array).

Where the % in a LIKE goes. In the value you pass, not the query text.

Whether LIMIT/OFFSET take placeholders. Yes; they are values.

Four things that do not protect you. Hand-escaping, a firewall, client-side validation, stored procedures with dynamic SQL.

Three defence-in-depth measures. Least-privilege account, statement_timeout, and not showing raw errors to users.

Practice

Write the unsafe f-string version against a throwaway table and search for a normal name.
Search for a member named O'Brien and read the syntax error. Explain it.
Rewrite it with %s and confirm both the normal name and the apostrophe now work.
Pass the string ' OR '1'='1 to the parameterised version and confirm it matches zero rows.
Use sql.SQL(...).format(sql.Literal(payload)) to print the exact text the server receives, and find where the payload was quoted.
Try to parameterise an ORDER BY column with %s and observe that it sorts by a constant.
Build an allow-list for sortable columns and reject one that is not in it.
Use sql.Identifier for a dynamic column name and print the generated query.
Do an IN query the wrong way (string-joined) and the right way (= ANY). Compare the query text for a 2-element and a 5-element list.
Write a LIKE search where the term comes from a variable, with the % added in code.
Parameterise LIMIT and OFFSET for pagination.
Create a restricted role with only SELECT, INSERT, UPDATE, DELETE, connect as it, and try to DROP TABLE. Read the permission error.
Search a codebase you have for an f-string or + adjacent to SELECT or WHERE.

Official documentation

PostgreSQL — SQL injection background — Prepared statements, which are parameterisation at the protocol level.
psycopg 3 — Passing parameters to queries — The right way, and an explicit warning against string formatting.
psycopg 3 — The sql module — sql.Identifier and sql.Literal for the dynamic-identifier case.
node-postgres — Parameterized queries — $1 placeholders.
OWASP — SQL Injection Prevention Cheat Sheet — The definitive defensive reference: parameterisation first, then allow-lists and least privilege.

Next: connection pooling, and why it matters sooner than you expect.