Fix SQL injection in Python and Django

1. Find every raw query: cursor.execute(), Model.objects.raw(), QuerySet.extra(), RawSQL() and Func or Expression subclasses with their own SQL, especially where request.GET, request.POST or URL kwargs reach them through helper functions.
2. Prefer the ORM: filter(), exclude(), annotate() and lookups such as customer__name send every value as a parameter.
3. When you need raw SQL, keep the SQL text constant with %s placeholders, unquoted, and pass the values as a list: cursor.execute(sql, [name, status]) or Model.objects.raw(sql, [name]). The SQLite backend accepts only a list, not a dict.
4. For the sort column and direction, map each public option to a model field with a dict ('customer' to 'customer__name', 'desc' to '-') and answer 400 for anything else; never pass request data straight to order_by().
5. Add regression tests with this card's payloads (tautology, UNION, comment, sort expression) next to functional tests for names such as O'Brien and non-ASCII text, and run them with pytest-django.
6. Keep DEBUG off in production so a database error becomes a generic 500 page instead of the technical 500 page with the SQL.

The three implementations share one API and one test suite; only the code below changes. The tests load the one that IMPL names.

def orders_for_customer(customer_name: str, status: str) -> list[dict]:
    sql = f"""
        SELECT o.id, o.status, o.total_cents, o.created_at
        FROM orders AS o
        JOIN customers AS c ON c.id = o.customer_id
        WHERE c.name = '{customer_name}' AND o.status = '{status}'
        ORDER BY o.id"""
    with connection.cursor() as cursor:
        cursor.execute(sql)
        return _rows(cursor)

def orders_for_customer(customer_name: str, status: str) -> list[dict]:
    # filter() never puts values into the SQL text: SQLite receives them as parameters.
    orders = Order.objects.filter(customer__name=customer_name, status=status).order_by("id")
    return list(orders.values("id", "status", "total_cents", "created_at"))

# order_by() refuses names that are not fields, but it accepts every field, including
# customer__email, which the API never returns. Map the caller's choice onto public fields.
SORT_FIELDS = {
    "created_at": "created_at",
    "total_cents": "total_cents",
    "customer": "customer__name",
}
SORT_DIRECTIONS = {"asc": "", "desc": "-"}


def search_orders(status: str, sort: str, direction: str) -> list[dict]:
    field = SORT_FIELDS.get(sort)
    prefix = SORT_DIRECTIONS.get(direction)
    if field is None or prefix is None:
        raise ValueError("unsupported sort column or direction")
    orders = Order.objects.filter(status=status).order_by(prefix + field, "id")
    rows = orders.values("id", "customer__name", "status", "total_cents", "created_at")
    return [{**row, "customer": row.pop("customer__name")} for row in rows]

def _escape_sql_string(value: str) -> str:
    return value.replace("\\", "\\\\").replace("'", "\\'")


def orders_for_customer(customer_name: str, status: str) -> list[dict]:
    sql = f"""
        SELECT o.id, o.status, o.total_cents, o.created_at
        FROM orders AS o
        JOIN customers AS c ON c.id = o.customer_id
        WHERE c.name = '{_escape_sql_string(customer_name)}' AND o.status = '{_escape_sql_string(status)}'
        ORDER BY o.id"""
    with connection.cursor() as cursor:
        cursor.execute(sql)
        return _rows(cursor)

The exploit tests send the attack and check that it fails; functional tests next to them keep ordinary requests working, so a fix that rejects everything fails too.

# Exploit: each payload must stay data and leave the query's meaning unchanged.

def test_exploit_tautology_does_not_return_other_customers_orders(client):
    assert order_ids(client, "/orders", customer="' OR '1'='1", status="shipped") == []


def test_exploit_union_cannot_read_another_customers_orders(client):
    customer = "' UNION SELECT id, status, total_cents, created_at FROM orders WHERE customer_id = 2 --"
    assert order_ids(client, "/orders", customer=customer, status="shipped") == []


def test_exploit_comment_cannot_cut_off_the_status_filter(client):
    assert order_ids(client, "/orders", customer="Alice Martin'--", status="shipped") == []


def test_exploit_sort_expression_is_rejected_not_used_as_an_oracle(client):
    # Row order would reveal whether each customer's hidden email starts with "b".
    sort = "CASE WHEN substr(c.email, 1, 1) = char(98) THEN 0 ELSE 1 END"
    res = client.get("/admin/orders", {"status": "shipped", "sort": sort, "dir": "asc"})
    assert res.status_code == 400


def test_exploit_direction_cannot_add_a_second_sort_expression(client):
    direction = "ASC, CASE WHEN substr(c.email, 1, 1) = char(98) THEN 0 ELSE 1 END"
    res = client.get("/admin/orders", {"status": "shipped", "sort": "created_at", "dir": direction})
    assert res.status_code == 400

• Django's own warning: do not format the SQL string and do not put quotes around %s. '%s' % name is concatenation, and "WHERE name = '%s'" with params breaks the parameter binding the backend does.
• order_by() is not SQL injection any more (CVE-2021-35042, fixed in Django 3.1.13 and 3.2.5, made it reject expressions), but it accepts every field and relation, so order_by(request.GET['sort']) lets a caller sort by customer__email and read its order. This card checked that order_by('customer__email') works on Django 6.1.2 and that a CASE expression raises FieldError, which becomes a 500 unless you catch it.
• extra() is a last resort and the docs plan to deprecate it; its where, select and order_by arguments are raw SQL. Use RawSQL or the ORM instead, and pass params to them.
• RawSQL(sql, params) binds params, but the sql argument is still raw text: never build it from request data.
• Model.objects.raw() must return the primary key and only maps columns to fields; it does not make the SQL safe. Pass params.
• LIKE: filter(name__contains=value) escapes % and _ for you; in raw SQL a bound % still matches every row, so escape \, % and _ and add ESCAPE '\'.
• The ORM's IN lookup (id__in=values) binds each value; in raw SQL generate one %s per value.

1. Run the tests in all three modes: IMPL=fixed must pass; IMPL=vulnerable and IMPL=wrong must fail (node ../../run-all.mjs sql-injection/python-django does this for you).
2. Against your own endpoint, send customer=' OR '1'='1 and expect an empty result, and send a CASE expression or customer__email as the sort and expect 400.
3. grep for execute(, .raw(, .extra( and RawSQL(; Semgrep's formatted-sql-query rule and Bandit B608 flag the f-strings here, but the Django taint rule misses SQL built outside the view.

What each rule reported when it was run on this card's code, or what its source shows it covers where it was not run. A rule that exists for the weakness does not always catch it.

Download the project, install its dependencies once (this step needs the network), then run the tests against each implementation from the sql-injection/python-djangofolder.

python3 -m venv .venv
.venv/bin/python -m pip install -r requirements.txt

# Expected: every test passes
IMPL=fixed .venv/bin/python -m pytest

# Expected: an exploit test fails
IMPL=vulnerable .venv/bin/python -m pytest

# Expected: a test fails
IMPL=wrong .venv/bin/python -m pytest

Each run writes a JUnit report to reports/junit.xml. From the top folder of the download, node run-all.mjs sql-injection/python-django runs all three modes and checks the expected results.

Download the project ZIP, 22 KB

Verified

Machine

macOS 27.0 (Darwin 27.0.0, arm64)

Toolchain

• python CPython 3.14.8
• django 6.1.2
• pytest 9.1.1
• pytest-django 4.14.0
• semgrep 1.180.0
• bandit 1.9.4

Matrix

fixed: 11 of 11 tests pass; vulnerable: 7 of 11 tests fail (5 exploit tests); wrong: 6 of 11 tests fail (4 exploit tests)

Test results

fixed 11/11 pass; vulnerable 7/11 fail (5 exploit tests); wrong 6/11 fail (4 exploit tests), offline after install

Dependency freshness

Django 6.1.2 was published on 2026-10-06 and is taken at four days old because it is a security release (CVE-2026-77050, CVE-2026-84429, CVE-2026-87890); pytest-django 4.14.0 dates from 2026-08-10, asgiref 3.12.1 from 2026-07-14 and sqlparse 0.6.0 from 2026-08-13. iniconfig is pinned at 2.3.0 rather than 2.3.1 (2026-10-06), which was four days old and is not a security release. OSV reported no known vulnerabilities for any pin. Rerun on CPython 3.14.8, the 30 September 2026 expedited security release.

Versions and results hold for the toolchain above. The card's tests are the check: run them with your own versions before relying on the fix.

References

1. OWASP SQL Injection Prevention Cheat Sheet
2. Django 6.1: SQL injection protection
3. Django 6.1: Performing raw SQL queries (params, unquoted placeholders)
4. Django 6.1: QuerySet.order_by()
5. Django 6.1: QuerySet.extra()
6. Django 6.1: RawSQL expressions
7. Django security releases, July 2021 (CVE-2021-35042, QuerySet.order_by())
8. Django security releases issued: 6.1.2, 6.0.9 and 5.2.18
9. pytest-django documentation
10. CWE-89: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')

Attack patterns:

• CAPEC-66 at MITRE