Safe queries

What you will be able to do

Make sure that text from a request can never change the SQL that the API runs. Then give the API one file where all its SQL lives, and see what an ORM would change.

Before you startWhat to know and to have readyAbout 50 minutes

What you need to know

• Named parameters and the f-string that broke a query, from Reproducible extraction in SQL and Data Preparation for AI. This lesson goes further: a server, a second table, and an API in front of it.
• Routes, models and modules in a FastAPI project, from Separate concerns in Build and Deploy Your First API.
• The foreign keys of migration 005, from Integrity constraints, and commit and rollback, from Transactions.

What you need on your computer

• The project ~/projects/ticket-api at the end of Module 2. python -m pytest prints 53 passed.
• PostgreSQL running (docker compose up -d db) with the small data loaded (python -m scripts.load --reset).
• The labs repository in ~/projects/nextia-learning-labs, as its snapshots README shows. You copy files from the snapshot end-of-m03.

The times in this module come from an Apple M4 with 16 GB, on macOS 26.6, with Python 3.12 and PostgreSQL 18.6 with pgvector 0.8.7 in Docker. Your times will differ. The Windows and Linux commands are written, not run. Every input in this lesson runs against your own local database.

A box on a form

You fill in a form at a bank. One box says “Your name”. You write Olga Olsen. The clerk reads the box and looks for that name. Whatever you write there, the clerk treats it as a name.

Now imagine a careless clerk. He does not read the box as a name. He copies your words into a sentence and reads the sentence to the vault: “Bring the file of the customer called Olga Olsen.” If you write nobody, and also every other file, the sentence becomes “Bring the file of the customer called nobody, and also every other file”. The vault obeys the sentence.

The careful clerk keeps two things apart: the instruction, which the bank wrote, and the value, which you wrote. That separation is this lesson.

The problem: text that becomes SQL

Amira writes the first search of the new API: find a customer by email address. Her first draft builds the SQL text with an f-string, as many tutorials do:

scripts/injection_demo.py (the unsafe version, kept to show the problem)

def find_customer_unsafe(conn: psycopg.Connection, email: str) -> list[tuple]:
    # DO NOT DO THIS: the text becomes part of the SQL.
    sql = f"SELECT customer_id, name, email FROM customers WHERE email = '{email}'"
    print(f"  SQL sent: {sql}")
    return conn.execute(sql).fetchall()

With a normal address, it works. The script scripts.injection_demo runs this version and a safe version side by side, on your own database:

unsafe (string building):
  SQL sent: SELECT customer_id, name, email FROM customers WHERE email = 'olga.olsen@example.com'
  1 row(s), first: ('C-0001', 'Olga Olsen', 'olga.olsen@example.com')

Sam asks what happens with a strange address. The input x' OR '1'='1 is the classic test. Its first quote closes the string that Amira’s code opened. The rest becomes SQL:

unsafe (string building):
  SQL sent: SELECT customer_id, name, email FROM customers WHERE email = 'x' OR '1'='1'
  40 row(s), first: ('C-0001', 'Olga Olsen', 'olga.olsen@example.com')

The search for one customer returned all 40. The WHERE clause now has two conditions, and '1'='1' is true for every row. A worse input reads a different table. UNION adds the rows of a second query to the first, and -- turns the rest of the line into a comment:

unsafe (string building):
  SQL sent: SELECT customer_id, name, email FROM customers WHERE email = 'x' UNION SELECT ticket_id, subject, body FROM tickets --'
  200 row(s), first: ('T-30098', 'Recycling old item', 'Can you take back my old step ladder when you deliver the new one?')

A customer search returned every ticket, with its full text. On a real help desk, that is every customer’s private message. This attack is called SQL injection: text from outside is injected into the SQL and changes what it does.

The author also tried a second statement, in a constructed test on the local database:

unsafe (string building):
  SQL sent: SELECT customer_id, name, email FROM customers WHERE email = 'x'; DELETE FROM customers; --'
  error: update or delete on table "customers" violates foreign key constraint "tickets_customer_fk" on table "tickets"

PostgreSQL ran the DELETE. Only the foreign key from Integrity constraints stopped it, because every customer has tickets. The statement failed as a whole, so nothing was deleted. That was luck, not design: a table without such a rule would now be empty. Do not try this input on a database that you care about.

The idea: the SQL and the values travel apart

The safe version keeps the bank’s two things apart. The SQL text is fixed and has a placeholder, %s. The value goes to the database separately:

scripts/injection_demo.py (the safe version)

def find_customer_safe(conn: psycopg.Connection, email: str) -> list[tuple]:
    sql = "SELECT customer_id, name, email FROM customers WHERE email = %s"
    print(f"  SQL sent: {sql}    value sent separately: {email!r}")
    return conn.execute(sql, (email,)).fetchall()

A value that you pass this way is a parameter, and the query is a parameterized query. The name comes from mathematics: the query is fixed, and the parameter is the part that changes between runs.

Why is it safe? psycopg sends the SQL text and the values in separate parts of its message to the server. PostgreSQL reads the text first and decides what the query means: one table, one condition. Only then does it use the value, and only to fill the slot. A quote in the value is just a character in an email address. Here is the same attack, sent as a parameter:

safe (parameter):
  SQL sent: SELECT customer_id, name, email FROM customers WHERE email = %s    value sent separately: "x' OR '1'='1"
  0 row(s)

No customer has the email address x' OR '1'='1, so the answer is zero rows. That is correct.

The same text from a request, pasted into the SQL or sent as a parameter.

Text description of the diagram

The input is the same on both sides. With string building, the input becomes part of the SQL text. So PostgreSQL reads a query that Amira never wrote: two conditions, the second always true. 40 rows come back. With a parameter, PostgreSQL reads the fixed text first and finds one condition. The value arrives separately and fills the slot as one email address. No customer has it, so 0 rows come back.

The three inputs, side by side:

• Unsafe
• Safe

Rows returned by one customer search on the small data (40 customers, 200 tickets), with the input pasted into the SQL (unsafe) or passed as a parameter (safe). Recorded with scripts.injection_demo.

Text description of the chart

Pairs of bars for three inputs, unsafe then safe. The normal address olga.olsen@example.com: 1 row unsafe, 1 row safe. x' OR '1'='1: 40 rows unsafe (every customer), 0 safe. x' UNION SELECT ticket_id, subject, body FROM tickets --: 200 rows unsafe (every ticket), 0 safe.

What this tells us. For a normal input, the two versions give the same answer. A test with normal data cannot see the difference. Only an input with a quote shows it, so the project’s tests send such inputs on purpose.

Two limits are worth knowing:

• A parameter is a value, never a name. You cannot pass a table name or a column name as %s. The common mistake below shows the error. Choose names from a fixed list in your code.
• Some characters keep a meaning inside a value. In a LIKE pattern, % and _ are wildcards. A parameter cannot change the query, but the value % still matches every row. The Your turn task shows it.

Larkfield: one file for all the SQL

The help desk now needs three ticket endpoints: a list with filters, one ticket with its messages, and a new message. Amira does not want SQL in every route. She puts every SQL statement of the API in one module, ticket_api/repository.py. A repository is the layer between the routes and the database. A route asks it for “one page of open billing tickets”, and the repository knows the SQL.

Its rules are at the top of the file:

ticket_api/repository.py (the rules)

"""Every SQL statement of the API, in one place.

The rules (Module 3 of the databases course):
- Values always go to the database as parameters (%s or %(name)s), never inside
  the SQL text. The SQL text is built only from fixed pieces in this file.
- A function gets a connection; the caller decides where the transaction starts
  and ends.
"""

The list has optional filters, so its WHERE clause changes from request to request. Does that need string building? It needs building, but only from fixed pieces:

ticket_api/repository.py (the list query, shortened)

def list_query(*, status=None, team=None, customer_id=None, limit=20, offset=0) -> tuple[str, dict]:
    where, params = [], {"limit": limit, "offset": offset}
    for column, value in (("status", status), ("team", team), ("customer_id", customer_id)):
        if value is not None:
            where.append(f"t.{column} = %({column})s")
            params[column] = value
    sql = (
        "SELECT * FROM tickets t"
        + (" WHERE " + " AND ".join(where) if where else "")
        + " ORDER BY t.created_at DESC, t.ticket_id DESC LIMIT %(limit)s OFFSET %(offset)s"
    )
    return sql, params

The f-string here only joins column names that are written in the code: status, team, customer_id. The request decides which fixed pieces go in, never what they say. Every value goes into params and travels as a named parameter, such as %(team)s.

The routes add a second layer. FastAPI checks each value before the repository sees it. team must be one of the five known teams, and a ticket ID must be T- and five or six digits. So this attack never reaches the database at all:

$ curl -s "http://127.0.0.1:8000/v1/tickets?team=billing'%20OR%20'1'='1"
{"error":{"code":"invalid_request","message":"The request is not valid.","request_id":"c9ecb95fcea8","fields":["team"]}}

The answer is 422. Which layer protects the API, then? Both, for different reasons. Validation gives the client a clear error. The parameter is the guarantee. Nobody can check a free-text field, such as a message body, against a list, and it must still never become SQL.

The list endpoint has one more property that this course comes back to. For each ticket on a page, it sends two more queries: the customer’s name and the latest AI run. A page of 20 tickets sends 41 queries, and the API reports that number in the response header X-DB-Queries. That is correct, and slow. Module 4 measures it and fixes it.

An ORM: the same rule, written for you

Many Python projects do not write SQL by hand. They use an ORM, an object-relational mapper: a library that maps a table to a Python class, and writes the SQL for you. SQLAlchemy is the most common one in Python. Would it have stopped the injection?

The author mapped three tables with SQLAlchemy 2.1.4 and loaded the same page of open billing tickets, outside the project. A search by a customer’s name shows the SQL that the ORM sends:

SELECT customers.customer_id, customers.name
FROM customers
WHERE customers.name = %(name_1)s::VARCHAR

The value is a bound parameter, %(name_1)s, never pasted into the text. So an ORM follows the rule for you, as long as you use its query methods. Its escape hatch, raw SQL text, has the same risk as Amira’s first draft.

The same run shows the ORM’s best-known trap. The obvious code reads ticket.customer.name in a loop, and it sent 41 queries for one page of 20 tickets. The ORM loads each related row when the code first touches it. The same page with one option, selectinload, sent 3 queries: the page, then all its customers, then all its AI runs. On the large data with the indexes of Module 4, that was 15.2 ms against 2.2 ms. Without those indexes, as your database is now, both are slower, and the 41 queries much more so.

	Plain SQL with psycopg (this project)	An ORM such as SQLAlchemy
Values as parameters	You write %s; review checks it	Automatic with the query methods
The SQL that runs	Exactly what you wrote	Generated; log it to see it
Number of queries	What you wrote	Easy to send many by accident (41 here)
Rows to Python objects	By hand (dict_row, Pydantic models)	Automatic
Cost	More SQL to write and review	A large library to learn, and one more layer to debug

This course keeps plain SQL. Each query plan and each lock in Modules 3 and 4 is easier to see when you wrote the SQL yourself. An ORM is a fine choice for a team that knows it. The rule about values and the habit of counting queries stay the same. The comparison script is in the labs repository, in C20/optional/orm, with its own README and virtual environment. It is optional.

Guided practice: the repository and the ticket endpoints

You add the repository, the three ticket endpoints, the injection demo and their tests. The endpoints get their connections from a new module, ticket_api/db.py. For now you switch its pool off, so that every request opens its own connection, as history.py did until now. The next lesson explains the pool and turns it on.

1. Open a terminal in ~/projects/ticket-api and activate the virtual environment (source .venv/bin/activate; Windows: .venv\Scripts\Activate.ps1).

2. Copy the files of this lesson from the snapshot end-of-m03. The new files are repository.py, tickets.py, db.py, injection_demo.py and two test files. The other files change: models, errors, main, config, history, the contract, the requirements, pyproject.toml (version 1.3.0), the image tag in compose.yaml, and one test file.

   macOS / Linux

   S=../nextia-learning-labs/C20/snapshots/end-of-m03
   for f in ticket_api/repository.py ticket_api/tickets.py ticket_api/db.py ticket_api/models.py \
            ticket_api/errors.py ticket_api/main.py ticket_api/config.py ticket_api/history.py \
            contract/openapi.json requirements.txt requirements-run.txt requirements-lock.txt \
            pyproject.toml compose.yaml scripts/injection_demo.py \
            tests/test_safe_queries.py tests/test_tickets_api.py tests/test_transactions.py; do
     cp "$S/$f" "$f"
   done

   Windows (PowerShell)

   $S = "..\nextia-learning-labs\C20\snapshots\end-of-m03"
   $files = 'ticket_api\repository.py', 'ticket_api\tickets.py', 'ticket_api\db.py', 'ticket_api\models.py',
            'ticket_api\errors.py', 'ticket_api\main.py', 'ticket_api\config.py', 'ticket_api\history.py',
            'contract\openapi.json', 'requirements.txt', 'requirements-run.txt', 'requirements-lock.txt',
            'pyproject.toml', 'compose.yaml', 'scripts\injection_demo.py',
            'tests\test_safe_queries.py', 'tests\test_tickets_api.py', 'tests\test_transactions.py'
   foreach ($f in $files) { Copy-Item "$S\$f" $f }

   If you downloaded the labs repository as a ZIP file, its folder is nextia-learning-labs-main. Change the first line to match.

3. Install the one new package, psycopg-pool: python -m pip install -r requirements.txt.

4. Add one line to .env: DB_POOL=false. It switches the pool off until the next lesson.

5. Run the tests: python -m pytest. Expect 67 passed, 14 more than before.

6. Run the demo three times, with the three inputs:

   python -m scripts.injection_demo
   python -m scripts.injection_demo "x' OR '1'='1"
   python -m scripts.injection_demo "x' UNION SELECT ticket_id, subject, body FROM tickets --"

   Compare your output with the outputs above. Only the unsafe lines change between the inputs.

7. Start the API with fastapi dev. In a second terminal, ask for two open billing tickets: curl -s 'http://127.0.0.1:8000/v1/tickets?status=open&team=billing&limit=2'. If your .env sets API_KEY, add -H "X-API-Key: <your key>". On Windows, type curl.exe instead of curl.

8. Send the attack as a filter: curl -s "http://127.0.0.1:8000/v1/tickets?team=billing'%20OR%20'1'='1". Then stop the API with Ctrl+C.

Show the expected result

Step 5 ends with 67 passed. The new tests include test_string_building_lets_text_change_the_query (40 rows), test_a_parameter_is_only_a_value and test_the_api_treats_a_quote_as_text (422). Step 7 answers with two tickets, newest first:

{"items":[{"ticket_id":"T-30197","customer_id":"C-0029","customer_name":"Noah Ramos","subject":"Payment failed but money taken","team":"billing","priority":2,"status":"open","created_at":"2026-10-08T14:47:53Z","message_count":0,"last_message_at":null,"last_run_model":"chat-small","last_run_status":"ok"},{"ticket_id":"T-30179","customer_id":"C-0033","customer_name":"Yusuf Okafor","subject":"Wrong price charged","team":"billing","priority":3,"status":"open","created_at":"2026-10-06T12:43:44Z","message_count":0,"last_message_at":null,"last_run_model":"keywords-1.1","last_run_status":"ok"}],"next_offset":2}

Step 8 answers with the 422 error above, with "fields":["team"]. Your request_id is different. If a step prints connection refused, PostgreSQL is not running: run docker compose up -d db and try again.

Your turn: a safe subject search

Change one thing: search the ticket subjects for some words, with no difference between upper and lower case. Write scripts/search_subjects.py with a function search_subjects(conn, words) that uses ILIKE and a parameter, and a main() that prints the number of rows. Predict first: what do the inputs charged twice, x' OR '1'='1 and % return on the small data?

Show a solution and the answer

scripts/search_subjects.py

"""Your turn: search ticket subjects for some words, safely."""

import sys

import psycopg

from ticket_api.config import load_env, read_secret


def search_subjects(conn: psycopg.Connection, words: str) -> list[tuple]:
    sql = "SELECT ticket_id, subject FROM tickets WHERE subject ILIKE %s ORDER BY ticket_id"
    return conn.execute(sql, (f"%{words}%",)).fetchall()


def main() -> int:
    load_env()
    with psycopg.connect(read_secret("DATABASE_URL")) as conn:
        rows = search_subjects(conn, sys.argv[1])
    print(f"{len(rows)} row(s)" + (f", first: {rows[0]}" if rows else ""))
    return 0


if __name__ == "__main__":
    sys.exit(main())

The f-string builds the value (%charged twice%), not the SQL text, so it is safe. The author’s run:

$ python -m scripts.search_subjects "charged twice"
6 row(s), first: ('T-30002', 'Charged twice for order LK-182074')
$ python -m scripts.search_subjects "x' OR '1'='1"
0 row(s)
$ python -m scripts.search_subjects "%"
200 row(s), first: ('T-30001', 'Tracking number does not work')

The attack finds nothing. But % finds all 200 tickets, and so does _. That is not injection, because the query did not change. The value itself is a pattern, and % matches any text. If a search must find these characters as characters, put a backslash before each one: words.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_"). With that line, % and _ find 0 tickets, and charged twice still finds 6. On a public search, also limit the number of rows, as the list endpoint does with limit. The script is your own: the snapshot does not have it.

A table name from the request

Common mistake

A parameter used for a table name

Symptom: Sam writes a small admin script that counts the rows of any table. He has learned the rule, so he passes the table name as a parameter:

conn.execute("SELECT count(*) FROM %s", ("tickets",))

psycopg.errors.SyntaxError: syntax error at or near "$1"
LINE 1: SELECT count(*) FROM $1
                             ^

Cause: a parameter is a value, and PostgreSQL must know the table before it can use any value. psycopg sent the text with a numbered slot, $1, in a place where SQL allows no value.

Fix:

1. If the name comes from a request, check it against a fixed list in your code (if table not in {"tickets", "messages"}: …), as list_query does with its three column names.
2. Then build the SQL with psycopg.sql, which quotes a name as a name: sql.SQL("SELECT count(*) FROM {}").format(sql.Identifier(table)). That gives SELECT count(*) FROM "tickets".
3. Never fall back to an f-string. A quoted name fails safely: sql.Identifier("tickets; DROP TABLE tickets") gives relation "tickets; DROP TABLE tickets" does not exist, and nothing is dropped.

For an AI service: text from a model is text from outside, too. A model may write part of a query, such as a filter value or a sort column. Treat that text like a request: values as parameters, names from a fixed list.

Knowledge check

Check your understanding

The API validates every filter against a list of known values. Is the parameter in the repository still needed? Why?

Show the answer

Yes. Validation protects only the fields that have a fixed list of values, such as team and status. Free text, such as a message body or a search word, cannot be checked against a list, and it must still never become SQL. A parameter makes that true for every value, also for the next endpoint that someone adds without validation. Validation gives a clear 422; the parameter is the guarantee.

Check your understanding

Predict: a real customer has the email address o'brien@example.com, with a quote in it. What do the safe and the unsafe search do with it?

Show the answer

The safe search looks for that exact address. The quote is just a character, so it finds the customer if one exists (on the small data, 0 rows). The unsafe search pastes the address into '...'. The quote ends the string early, and PostgreSQL reads brien@example.com' as SQL. The author’s run printed error: syntax error at or near "brien". So string building fails real customers too, not only attackers. Errors like this one are often how injection bugs are found.

Check your understanding

An ORM writes every query of a project with bound parameters. Its list page is slow. What should you check first, and why?

Show the answer

Count the queries that one page sends. The ORM keeps values safe, but it loads related rows when the code first touches them. So a loop over 20 tickets can send 41 queries, as in the comparison. Log the SQL (SQLAlchemy can print every statement) or count it, as the project does with its X-DB-Queries header. Then load the related rows with one query per table, for example with selectinload.

Recap

• String building lets text change the SQL: x' OR '1'='1 returned all 40 customers, a UNION returned all 200 tickets, and a DELETE ran until a foreign key stopped it.
• A parameterized query sends the SQL text and the values apart. PostgreSQL reads the text first, so a value can only fill its slot: the same attacks returned 0 rows.
• A parameter is a value, never a name. Build names only from fixed pieces in your code, with psycopg.sql.Identifier when needed. Watch % and _ in LIKE values.
• The repository (ticket_api/repository.py) holds every SQL statement of the API. The routes validate first; the repository passes every value as a parameter.
• An ORM writes bound parameters for you, but it can hide many queries: 41 for one page, against 3 with selectinload.
• Your project now has the ticket endpoints, with one new connection per request. python -m pytest prints 67 passed.

Next: Connection management