SQL injection through the ORDER BY sort parameter in a FastAPI listing endpoint

Our yearly external penetration test came back with one high-severity finding against the internal-facing orders API that our support and finance teams use. The tester had sent a request that took about five seconds to answer, while the same request with a normal sort value came back in 40 ms:

GET /api/v1/orders?status=paid&sort=created_at,(SELECT 1 FROM pg_sleep(5))&dir=desc
HTTP/1.1 200 OK   (elapsed 5.06 s)

A second probe with an unbalanced quote produced a 500 and this line in our logs:

psycopg.errors.SyntaxError: syntax error at or near "'"
LINE 4: ORDER BY created_at' DESC

A delay that tracks a value in the request and a database syntax error that echoes it are the two classic signals of SQL injection. We had reviewed this endpoint for injection before and believed it was clean, because every filter used bound parameters.

Reproducing it

We reproduced it against a staging copy with a read-only role and no customer data:

1. Call GET /api/v1/orders?sort=total&dir=asc and note the latency (about 35 ms).
2. Call the same URL with sort=total,(SELECT 1 FROM pg_sleep(2)).
3. Observe that the response arrives two seconds later and is otherwise identical.

That is enough to prove that text from the query string is executed as SQL. We stopped there; the point was to confirm the bug, not to see how much an attacker could pull out.

What we ruled out

• The filters. status, customer_id and the date range all went through %s placeholders and psycopg sent them as parameters. Injecting into them only ever produced "no rows".
• The ORM layer. Most of the service uses SQLAlchemy, which quotes identifiers safely. This endpoint was the exception: it had been rewritten to raw SQL months earlier for performance.
• The WAF. Our edge WAF has SQL injection rules, but the payload contains no quotes or UNION keyword, and the WAF only inspected request bodies on this route, not query strings.

Root cause

The listing query was built with an f-string for the ORDER BY clause. The author knew that column names cannot be sent as bind parameters, so they validated dir against asc/desc but passed sort through unchanged:

sql = f"""
    SELECT id, customer_id, status, total, created_at
    FROM orders
    WHERE status = %s
    ORDER BY {sort} {direction}
    LIMIT %s OFFSET %s
"""

Anything after ORDER BY is an expression, so a comma followed by a subquery is valid SQL. The only reason nobody had noticed was that the frontend always sent one of five known column names.

What is happening under the hood

Prepared statements separate SQL text from data. The database parses the text once, with placeholders like $1, and the values arrive later in a separate protocol message, so they can never change the shape of the query. That only works for values. Identifiers (table names, column names) and keywords (ASC, DESC, NULLS LAST) are part of the query's structure and must be known at parse time. PostgreSQL will happily bind ORDER BY $1, but it then sorts by a constant string, which does nothing, so developers fall back to string formatting and the protection is gone.

ORDER BY accepts arbitrary expressions, including CASE and scalar subqueries. That gives an attacker a boolean or timing oracle even though the result set is not directly visible: the order of the rows, or the response time, changes depending on whether a condition about hidden data is true.

Environment

Minimal reproduction

from fastapi import FastAPI, Query
import psycopg

app = FastAPI()

@app.get("/orders")
def list_orders(sort: str = "created_at", dir: str = "desc"):
    direction = "ASC" if dir.lower() == "asc" else "DESC"
    with psycopg.connect("postgresql://orders_api@db.example.com/shop") as conn:
        # BROKEN: `sort` is copied into the statement text.
        rows = conn.execute(
            f"SELECT id, total FROM orders ORDER BY {sort} {direction} LIMIT 50"
        ).fetchall()
    return rows

Who is affected

Any code that builds ORDER BY, GROUP BY, column lists or table names from request input with string formatting, in any language: "ORDER BY " + req.query.sort in Node, orderByRaw($sort) in Laravel, .order(params[:sort]) with raw strings in older Rails code. Parameter binding in the same query does not help, because the injected text never goes through a placeholder.

The fix

We replaced free text with an allowlist that maps public sort keys to SQL fragments we wrote ourselves. The client chooses a key; it never supplies SQL.

from enum import Enum
from psycopg import sql

class SortKey(str, Enum):
    created = "created"
    total = "total"
    status = "status"
    customer = "customer"

# Public key -> trusted SQL. Always end with the primary key so paging is stable.
SORT_SQL = {
    SortKey.created: sql.SQL("o.created_at {dir}, o.id {dir}"),
    SortKey.total: sql.SQL("o.total {dir}, o.id {dir}"),
    SortKey.status: sql.SQL("o.status {dir}, o.id {dir}"),
    SortKey.customer: sql.SQL("c.name {dir} NULLS LAST, o.id {dir}"),
}

DIRECTIONS = {"asc": sql.SQL("ASC"), "desc": sql.SQL("DESC")}

def build_order_by(sort: SortKey, direction: str) -> sql.Composed:
    dir_sql = DIRECTIONS[direction]  # KeyError -> 422 via validation below
    return SORT_SQL[sort].format(dir=dir_sql)

The endpoint now lets FastAPI validate both inputs before our code runs, so an unknown value is a 422 with a clear message instead of a database error:

from typing import Literal

@app.get("/api/v1/orders")
def list_orders(
    status: str,
    sort: SortKey = SortKey.created,
    dir: Literal["asc", "desc"] = "desc",
    limit: int = Query(50, ge=1, le=200),
    offset: int = Query(0, ge=0),
):
    query = sql.SQL("""
        SELECT o.id, o.customer_id, o.status, o.total, o.created_at
        FROM orders o JOIN customers c ON c.id = o.customer_id
        WHERE o.status = %s
        ORDER BY {order_by}
        LIMIT %s OFFSET %s
    """).format(order_by=build_order_by(sort, dir))
    with pool.connection() as conn:
        return conn.execute(query, (status, limit, offset)).fetchall()

- ORDER BY {sort} {direction}
+ ORDER BY {order_by}   -- composed only from SORT_SQL and DIRECTIONS

Alternatives and trade-offs

1. psycopg.sql.Identifier. sql.Identifier(sort) quotes the value as an identifier, so total,(SELECT ...) becomes a single, nonexistent column name and the query fails safely. It blocks injection, but it still lets clients sort on any column, including ones you never meant to expose, and unknown columns surface as 500s. We use it only for internal admin tooling.
2. Sort in the ORM. SQLAlchemy's order_by(getattr(Order, key)) is safe if key comes from an allowlist, but getattr on arbitrary input can reach attributes that are not columns. Still use a mapping.
3. Rely on the WAF. It is a useful extra layer, and we did extend it to query strings, but WAF rules match payload patterns; pg_sleep inside a comma expression is easy to rephrase.

We also tightened the database role: orders_api lost access to every schema except the reporting views it needs, and statement_timeout is now 3 seconds for that role, which limits what any future injection could do with timing tricks.

ALTER ROLE orders_api SET statement_timeout = '3s';
REVOKE ALL ON SCHEMA billing, auth FROM orders_api;

How to test the fix

Unit tests cover the allowlist, and an API test sends hostile values and checks they are rejected before reaching the database:

import pytest
from fastapi.testclient import TestClient

client = TestClient(app)

HOSTILE = [
    "created_at,(SELECT 1 FROM pg_sleep(1))",
    "total; DROP TABLE orders",
    "1",
    "CASE WHEN true THEN id END",
    "status'",
]

@pytest.mark.parametrize("value", HOSTILE)
def test_unknown_sort_rejected(value):
    r = client.get("/api/v1/orders", params={"status": "paid", "sort": value})
    assert r.status_code == 422

def test_every_sort_key_executes(db_conn):
    for key in SortKey:
        for d in ("asc", "desc"):
            q = sql.SQL("SELECT 1 FROM orders o JOIN customers c ON c.id = o.customer_id "
                        "ORDER BY {} LIMIT 1").format(build_order_by(key, d))
            db_conn.execute(q)  # must not raise

pytest tests/api/test_orders_sort.py -q

Confirming in production

• The pentest firm re-ran the original payloads against production; all returned 422 Unprocessable Entity in under 30 ms.
• We searched 90 days of access logs for sort= values outside the five known keys. There were 14 requests, all from the tester's IP range during the engagement window, so we had no evidence of earlier exploitation.

zgrep -hoE 'sort=[^& ]+' /var/log/nginx/orders-api.access.log*.gz \
  | sort | uniq -c | sort -rn | head

• pg_stat_statements showed no query text containing pg_sleep other than from staging.

Regression guard

• A Semgrep rule fails CI when an f-string or % formatting result is passed to execute():

rules:
  - id: no-formatted-sql
    languages: [python]
    severity: ERROR
    message: Build SQL with psycopg.sql or the ORM; never format request data into SQL text.
    pattern-either:
      - pattern: $CONN.execute(f"...", ...)
      - pattern: $CONN.execute("..." % $X, ...)
      - pattern: $CONN.execute("...".format(...), ...)

• The hostile-values test lives in a shared helper that every listing endpoint must call in its test module; code review checks for it on new endpoints.

Security implications

This was a real injection, rated high because the role could read every table in the reporting schema. The fix removes request-controlled SQL text entirely rather than escaping it. The role reduction and statement timeout are defence in depth: they cut what a future mistake could expose, they do not replace the allowlist.

Prevention checklist

• Treat identifiers, keywords and ORDER BY expressions as code, never as data from the client.
• Expose public sort keys mapped to trusted SQL, and validate them at the API boundary.
• Always add a unique tie-breaker column to sort clauses.
• Give API database roles the smallest schema access and a statement timeout.
• Lint for string-formatted SQL in CI.

References

• OWASP SQL Injection Prevention Cheat Sheet — https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html
• psycopg 3 documentation: psycopg.sql composition — https://www.psycopg.org/psycopg3/docs/api/sql.html
• PostgreSQL documentation: Sorting Rows (ORDER BY) — https://www.postgresql.org/docs/16/queries-order.html
• FastAPI documentation: Query parameters and validation — https://fastapi.tiangolo.com/tutorial/query-params-str-validations/