SQL from code, and SQL injection

Programs speak SQL too

Every lesson so far has typed SQL into psql. Almost every real query is sent by a program instead: a website looking up a town, an API answering a request, a script cleaning data. The program opens a connection, sends SQL as text, and gets rows back. CivicGrid's API does exactly that, from Python, every time someone looks up a city.

That changes one thing, and it's the subject of this lesson: the program builds the SQL, often from things a user typed. A search box, a URL, a form. If the program pastes that text straight into the SQL, the user isn't just giving it a value to search for. They're writing part of the query.

Think of a bank teller who reads every note you hand over out loud, as an instruction. Your note says "deposit 50 dollars into account 1234". Fine. Someone else's note says "deposit 50 dollars into account 1234, and also give me the cash drawer". If the teller reads out the whole note and does what it says, the second line goes straight through. What the teller needs is a form, with one box for the account number, that will only ever be read as an account number, whatever's written in it. That form is a parameter.

Running the demo

The demo is from_code.py in the practice repo. Part 7 connects as a read-only role, Lesson 11 style, so make that first. Then run the script in a Python container. --network container:larkspur puts it on the same network as the database, so the script reaches Postgres at localhost:

docker exec larkspur psql -U postgres -d larkspur -c "CREATE ROLE web_reader LOGIN PASSWORD 'practice-only'; GRANT SELECT ON towns TO web_reader;"
docker run --rm --network container:larkspur -v "$PWD":/practice -w /practice python:3.13-slim \
  sh -c "pip install -q --root-user-action=ignore 'psycopg[binary]' && python from_code.py"

The first run downloads the Python image. pip install adds psycopg, the usual Postgres library for Python. Every change the script makes is rolled back, so the county is untouched afterwards. The rest of this lesson walks through the script and what it printed, part by part.

Connecting, and reading

import psycopg
from psycopg.rows import dict_row

ADMIN = "host=localhost dbname=larkspur user=postgres password=practice"

with psycopg.connect(ADMIN, row_factory=dict_row) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT current_user, version()")
        row = cur.fetchone()
        print(row["current_user"], "|", row["version"].split(",")[0])
        cur.execute("SELECT id, name, population FROM towns ORDER BY id LIMIT 3")
        for town in cur.fetchall():
            print("  ", town)

postgres | PostgreSQL 16.15 (Debian 16.15-1.pgdg13+2) on aarch64-unknown-linux-gnu
   {'id': 1, 'name': 'Ashford', 'population': 48210}
   {'id': 2, 'name': 'Ashford', 'population': 1904}
   {'id': 3, 'name': 'Brennan Falls', 'population': 21544}

• psycopg.connect(...) opens a connection, the same thing psql does, with the server, database, user and password in a connection string. with closes it when the block ends.
• A cursor sends a query and holds its result. execute sends the SQL; fetchone and fetchall bring back one row, or all of them.
• row_factory=dict_row makes each row a dictionary, so you read columns by name, row["current_user"], instead of by position.

psycopg also starts a transaction for you, the first time you run anything. Nothing is kept until you commit(), and rollback() throws it away: Lesson 7's BEGIN, done automatically. The demo rolls back after every query.

A search box, built from strings

Here's a town search, written the way that feels natural, with an f-string pasting the user's text into the SQL:

def find_town_unsafe(conn, name):
    # DON'T: the user's text becomes part of the SQL
    run(conn, f"SELECT id, name, kind FROM towns WHERE name = '{name}'")

(run is the script's helper: it prints the SQL, runs it, prints the rows or the error, and rolls back.) Search for Ashford:

--- input: 'Ashford'
SQL: SELECT id, name, kind FROM towns WHERE name = 'Ashford'
   {'id': 1, 'name': 'Ashford', 'kind': 'city'}
   {'id': 2, 'name': 'Ashford', 'kind': 'village'}
   (2 rows)

Works. Now four other things someone might type.

1. A name with an apostrophe:

--- input: "O'Brien"
SQL: SELECT id, name, kind FROM towns WHERE name = 'O'Brien'
   ERROR: SyntaxError - syntax error at or near "Brien"

Look at the SQL line. The apostrophe in O'Brien closed the quote early, and Brien' is left over as nonsense. Nobody was attacking anything: a real name broke the search. That's the first sign that the user's text is being read as SQL.

2. Every row:

--- input: "x' OR '1'='1"
SQL: SELECT id, name, kind FROM towns WHERE name = 'x' OR '1'='1'
   {'id': 1, 'name': 'Ashford', 'kind': 'city'}
   {'id': 2, 'name': 'Ashford', 'kind': 'village'}
   {'id': 3, 'name': 'Brennan Falls', 'kind': 'city'}
   {'id': 6, 'name': 'Elk Ridge', 'kind': 'village'}
   {'id': 8, 'name': 'Glen Oak', 'kind': 'town'}
   {'id': 9, 'name': 'Harlow', 'kind': 'town'}
   {'id': 10, 'name': 'Juniper', 'kind': 'village'}
   {'id': 11, 'name': 'Kestrel Bay', 'kind': 'city'}
   {'id': 12, 'name': 'Linden', 'kind': 'town'}
   {'id': 7, 'name': 'Fairview', 'kind': 'city'}
   {'id': 5, 'name': 'Dunmore', 'kind': 'town'}
   {'id': 4, 'name': 'Cedar Hollow', 'kind': 'town'}
   (12 rows)

The input closes the quote itself, then adds OR '1'='1', which is true for every row. So WHERE matches everything: all twelve towns, from a search meant to return one. (In no particular order: there's no ORDER BY, so Lesson 3's warning applies.) On a login form, the same trick turns "find the user with this name and password" into "find any user", and that's how logins get bypassed.

3. Another table's data:

--- input: "x' UNION SELECT id, name, role FROM leaders --"
SQL: SELECT id, name, kind FROM towns WHERE name = 'x' UNION SELECT id, name, role FROM leaders --'
   {'id': 9, 'name': 'Grace Lin', 'kind': 'mayor'}
   {'id': 13, 'name': 'Nora Quinn', 'kind': 'administrator'}
   {'id': 19, 'name': 'Joy Hart', 'kind': 'mayor'}
   {'id': 14, 'name': 'Hal Brooks', 'kind': 'mayor'}
   {'id': 17, 'name': 'Ana Torres', 'kind': 'mayor'}
   {'id': 10, 'name': 'Owen Price', 'kind': 'mayor'}
   {'id': 20, 'name': 'Carl Webb', 'kind': 'mayor'}
   {'id': 3, 'name': 'Priya Shah', 'kind': 'administrator'}
   {'id': 15, 'name': 'Rosa Diaz', 'kind': 'mayor'}
   {'id': 11, 'name': 'Ivy Chen', 'kind': 'mayor'}
   {'id': 1, 'name': 'Maria Okafor', 'kind': 'mayor'}
   {'id': 5, 'name': 'Dana Ruiz', 'kind': 'mayor'}
   {'id': 16, 'name': 'Ben Adler', 'kind': 'mayor'}
   {'id': 7, 'name': 'Ellen Marsh', 'kind': 'mayor'}
   {'id': 12, 'name': 'Marcus Bell', 'kind': 'mayor'}
   {'id': 2, 'name': 'Thomas Reyes', 'kind': 'mayor'}
   {'id': 6, 'name': 'Leo Park', 'kind': 'administrator'}
   {'id': 18, 'name': 'Ken Moss', 'kind': 'administrator'}
   {'id': 8, 'name': 'Sam Whitfield', 'kind': 'mayor'}
   {'id': 4, 'name': 'Thomas Reyes', 'kind': 'mayor'}
   (20 rows)

A town search returned every leader in the county. Lesson 10's UNION stacks a second query's rows under the first: here, one the attacker wrote. It only needs the same number of columns, three, and -- turns the leftover ' into a comment. Swap leaders for a table of users and password hashes, and this is how databases are stolen. The search page displays whatever comes back, so it shows the attacker everything.

4. A dropped table:

--- input: "x'; DROP TABLE meetings; --"
SQL: SELECT id, name, kind FROM towns WHERE name = 'x'; DROP TABLE meetings; --'
   (0 rows)
   DROP TABLE
   before rollback, meetings table is: None

The ; ends the search, and a second statement follows it. The search found nothing, then DROP TABLE ran, and the table was gone: to_regclass('meetings'), which looks a table up by name, found nothing. Here it was inside a transaction, so the rollback brought it back:

===== did meetings survive?
SQL: SELECT count(*) FROM meetings
   {'count': 30}
   (1 rows)

All 30 meetings, back. In a program that commits after each request, which is most of them, there'd be no rollback, and the meetings would be gone.

This is SQL injection: the user's input changes what the query does, not just what it looks for. It's been near the top of every list of common web vulnerabilities for over twenty years, and it's entirely preventable.

The fix: parameters

The same search, with the user's text sent as a parameter:

def find_town_safe(conn, name):
    # DO: the SQL is fixed; the user's text is sent separately, as data
    run(conn, "SELECT id, name, kind FROM towns WHERE name = %s", (name,))

%s is a placeholder, and the value goes in a separate tuple (the trailing comma in (name,) makes it a tuple of one). The same five inputs:

--- input: 'Ashford'
SQL: SELECT id, name, kind FROM towns WHERE name = %s
params: ('Ashford',)
   {'id': 1, 'name': 'Ashford', 'kind': 'city'}
   {'id': 2, 'name': 'Ashford', 'kind': 'village'}
   (2 rows)

--- input: "O'Brien"
SQL: SELECT id, name, kind FROM towns WHERE name = %s
params: ("O'Brien",)
   (0 rows)

--- input: "x' OR '1'='1"
SQL: SELECT id, name, kind FROM towns WHERE name = %s
params: ("x' OR '1'='1",)
   (0 rows)

--- input: "x' UNION SELECT id, name, role FROM leaders --"
SQL: SELECT id, name, kind FROM towns WHERE name = %s
params: ("x' UNION SELECT id, name, role FROM leaders --",)
   (0 rows)

--- input: "x'; DROP TABLE meetings; --"
SQL: SELECT id, name, kind FROM towns WHERE name = %s
params: ("x'; DROP TABLE meetings; --",)
   (0 rows)

Ashford still works. O'Brien doesn't break anything; there's just no town by that name. And the three attacks find no rows, because the database searched for a town literally called x' OR '1'='1, and there isn't one. Notice the SQL line is identical every time. The query is fixed in the program, and the user's text never becomes part of it.

Why it works: psycopg sends the SQL and the values to Postgres separately. Postgres reads and plans the query first, with a hole where %s was, and only then fills the hole with the value, as data. By the time the user's text arrives, there's no SQL left for it to change. It's the teller's form: whatever is written in the account box is only ever an account number.

Never build SQL with f-strings, +, or % formatting from anything a user could have touched. Not "carefully", not "after escaping the quotes": every hand-made escaping scheme has been beaten. Pass values as parameters, every time.

What a parameter can't be

Parameters are for values: text, numbers, dates. They can't be the names of things in the query: columns, tables, ASC or DESC. Here's a sort order passed as a parameter:

SQL: SELECT name, population FROM towns ORDER BY %s DESC LIMIT 3
params: ('population',)
   {'name': 'Ashford', 'population': 48210}
   {'name': 'Ashford', 'population': 1904}
   {'name': 'Brennan Falls', 'population': 21544}
   (3 rows)

No error, and not sorted by population. The village of Ashford, 1,904 people, comes second. The database sorted by the text 'population', the same value for every row, which sorts nothing, and the rows came out in whatever order they were stored. A quietly wrong answer, which is worse than an error. Try an attack the same way, and it's harmless, but just as unsorted:

SQL: SELECT name, population FROM towns ORDER BY %s DESC LIMIT 3
params: ('population; DROP TABLE towns',)
   {'name': 'Ashford', 'population': 48210}
   {'name': 'Ashford', 'population': 1904}
   {'name': 'Brennan Falls', 'population': 21544}
   (3 rows)

When a name has to come from the user, like a "sort by" menu, do two things: check it against a list of allowed names, then put it in with psycopg's sql.Identifier, which quotes it as a name:

allowed = {"name", "population", "founded"}
if choice not in allowed:
    print("   refused: not an allowed column")
else:
    query = sql.SQL("SELECT name, {col} FROM towns ORDER BY {col} DESC NULLS LAST LIMIT 3").format(
        col=sql.Identifier(choice))

--- sort by: 'population'
SQL: SELECT name, "population" FROM towns ORDER BY "population" DESC NULLS LAST LIMIT 3
   {'name': 'Ashford', 'population': 48210}
   {'name': 'Fairview', 'population': 33102}
   {'name': 'Kestrel Bay', 'population': 27450}
   (3 rows)

--- sort by: 'population; DROP TABLE towns'
   refused: not an allowed column

Sorted properly this time, with NULLS LAST from Lesson 3. "population" in double quotes is the column's name, Lesson 3's double quotes. The allowlist alone would be enough here; sql.Identifier is the belt to its braces.

LIKE, and lists

Two everyday cases that trip people up. A "contains" search needs % around the value, and the % has to be outside the parameter, joined on in SQL with ||:

SQL: SELECT name FROM towns WHERE name ILIKE '%%' || %s || '%%' ORDER BY name
params: ('ash',)
   {'name': 'Ashford'}
   {'name': 'Ashford'}
   (2 rows)

%% is how you write a literal % in a query that also has %s placeholders, so psycopg doesn't mistake it for one. The user's ash is still a parameter.

A list of ids, for "show me these towns": pass the Python list as one parameter, and compare with = ANY(...):

SQL: SELECT id, name FROM towns WHERE id = ANY(%s) ORDER BY id
params: ([1, 5, 10],)
   {'id': 1, 'name': 'Ashford'}
   {'id': 5, 'name': 'Dunmore'}
   {'id': 10, 'name': 'Juniper'}
   (3 rows)

psycopg sends the list as a Postgres array, and ANY matches any element. One placeholder, whatever the length of the list. No building IN (1, 5, 10) by hand.

A transaction in code

Lesson 7's all-or-nothing, from Python. with conn.transaction(): commits if the block finishes, and rolls back if anything in it raises an error:

try:
    with conn.transaction():
        with conn.cursor() as cur:
            cur.execute("UPDATE towns SET population = %s WHERE id = %s", (8500, 4))
            print("   updated:", cur.rowcount, "row")
            cur.execute("UPDATE towns SET population = %s WHERE id = %s", ("unknown", 4))
except psycopg.Error as e:
    print("   ERROR:", type(e).__name__, "-", str(e).splitlines()[0])

   updated: 1 row
   ERROR: InvalidTextRepresentation - invalid input syntax for type integer: "unknown"
   Cedar Hollow's population afterwards: 8390

The first update worked, the second failed, and the population is still 8,390: the first was undone with it. Every Postgres error arrives in Python as an exception you can catch, named after the problem, like InvalidTextRepresentation here. And the values are parameters even here, where nothing came from a user. Make it a habit, and you never have to decide whether this time is safe.

The second line of defense

Parameters stop injection. But code has bugs, and one forgotten f-string is enough. Lesson 11's least privilege limits what a mistake can cost. Part 7 connects as web_reader, which can read towns and nothing else, and runs the unsafe search again:

===== 7. the same attack, as a read-only role
SQL: SELECT id, name, kind FROM towns WHERE name = 'x'; DROP TABLE meetings; --'
   ERROR: InsufficientPrivilege - must be owner of table meetings
SQL: SELECT id, name, kind FROM towns WHERE name = 'x' UNION SELECT id, name, role FROM leaders --'
   ERROR: InsufficientPrivilege - permission denied for table leaders

Still injectable, and still bad: the ' OR '1'='1' trick would still list every town. But the drop and the leak both fail, because this role was never allowed to do either. A search box only needs to read towns, so that's all its role can do. That's why every program gets its own role, and why "the API connects as the superuser" is a finding on any security review.

Clean up the role afterwards:

docker exec larkspur psql -U postgres -d larkspur -c "DROP OWNED BY web_reader; DROP ROLE web_reader;"

Spot the problem

Work out what's wrong before you read the explanation.

The safe-looking report. A developer writes a filter for a report: cur.execute("SELECT * FROM meetings WHERE body = %s AND held_on >= '" + start_date + "'", (body,)). It uses a parameter, it passes code review because "it's parameterized", and it works. What's wrong? Only body is a parameter. start_date is pasted in with +, so it's as injectable as the f-string at the start of this lesson, and if it comes from a URL like ?start=2026-01-01, anyone can change it. One placeholder doesn't make a query safe: every value has to be one. held_on >= %s, with start_date in the tuple too.

Where you are now

You can send SQL from a program, and you've seen exactly how pasting user input into it goes wrong, and why parameters stop it. Lesson 13 is the last line of defense of all: backups, and proving you can restore them.

Check yourself

Key terms

• Driver: the library a program uses to talk to a database. For Python and Postgres, psycopg.
• Connection string: where and how to connect: server, database, user and password.
• Cursor: what sends a query and holds its result.
• SQL injection: user input that changes what a query does, because it was pasted into the SQL.
• Parameter: a value sent separately from the SQL, filling a placeholder like %s. Always data, never SQL.
• Allowlist: the fixed set of values allowed, like the columns a user may sort by.
• Defense in depth: more than one protection, so one mistake isn't enough: parameters, and a limited role.

Go deeper

• The psycopg documentation: Passing parameters to SQL queries (opens in a new tab), which starts with a warning worth reading twice.
• The OWASP SQL Injection Prevention Cheat Sheet (opens in a new tab).
• Try: add a "find leaders by name" function to from_code.py, with a parameter, and run the same five attacks against it.