Secure code review in Django: when annotate(**request.GET) becomes SQL injection

Most Django developers treat the ORM as a SQL injection force field: "I use .filter(), so I’m safe." That holds for values, which the ORM parameterizes. It does not hold for identifiers such as column aliases, and in September 2025 that gap became a patched Django CVE, followed by four more advisories for the same root cause. In this post you review a pull request that reintroduces the pattern, prove the bug in your browser against a local Docker lab with a small "Orders report" page, test what a Django upgrade does and does not fix, and leave with a checklist, a Semgrep rule and a grep one-liner that actually matches multi-line code.

This is an authorized, local-only lab: everything runs on 127.0.0.1 with fake seed data.

What is identifier injection in the Django ORM

SQL injection usually means a string concatenated into a value position, such as a WHERE name = '...' built with an f-string. Django’s query builder parameterizes values, so that mistake is hard to make by accident.

Identifier injection is different. Column names, table aliases and ORDER BY targets are part of the SQL grammar, not values, so they cannot be bound as parameters. If attacker-controlled text becomes the name of something in a query, you have left the safe path even though you only used the ORM.

QuerySet.annotate() and .alias() take keyword arguments: the keyword becomes the SQL alias and the value is the expression. That is exactly the shape of a ** expansion:

Order.objects.annotate(**{some_name: some_expression})

If some_name comes from request.GET, you are building a SQL identifier out of user input.

The real CVE behind this pattern

On September 3, 2025 the Django team disclosed CVE-2025-57833: FilteredRelation was subject to SQL injection in column aliases. Its internal add_filtered_relation() took the caller’s alias without validating it, so a crafted key passed via **kwargs reached the SQL as an unquoted identifier.

• Affected: Django main, 5.2 before 5.2.6, 5.1 before 5.1.12, 4.2 before 4.2.24
• Fixed in: 5.2.6, 5.1.12, 4.2.24 (severity "high", reported by Eyal Gabay)
• Source: Django security releases, September 3, 2025

The fix commit adds one line that calls the existing check_alias() validator on this code path:

 def add_filtered_relation(self, filtered_relation, alias):
+    self.check_alias(alias)
     filtered_relation.alias = alias

The same root cause came back on other paths, each with its own fix:

Patching the library closes the specific hole. The design mistake, identifiers built from request data, can resurface anywhere similar.

Lab setup: the vulnerable app under review

The lab is a small Django "orders report" API for a fake multi-tenant SaaS. Two tenants exist, Acme Corp and Globex Inc. An Acme analyst must only see Acme’s orders for active customers. The PR lets the analyst choose the name of the grouping column through a query parameter, the kind of small, plausible change a junior developer or an AI pairing tool might propose.

The lab ships a small web UI so you can watch the bug instead of reading logs. Each app serves an Orders report page at its root: a Group by box, a Run button, a results table, a Result panel with the HTTP status the real endpoint would return (the page calls the view in-process and maps an exception to 500; the page’s own /ui/run/ request always answers 200), and a Lab inspector panel with the exact SQL the database received and any exception. One honest caveat: the inspector is lab instrumentation (reports/lab_ui.py), not part of the PR. It calls the real view unchanged and records the SQL with Django’s connection.execute_wrapper, which is how it can show the statement even when the request crashes. The curl-able API at /api/orders/report/ is untouched, so every mission also has a terminal version. A collapsible Code under review panel on each page shows that app’s reports/views.py, so you can compare the vulnerable and fixed code without leaving the browser.

Here is the PR a reviewer sees (PR_DIFF.md in the lab):

--- a/reports/views.py
+++ b/reports/views.py
@@ -7,12 +7,18 @@
 
 
 def orders_report(request):
+    group_by = request.GET.get("group_by", "active_customer")
+
     qs = Order.objects.annotate(
-        active_customer=FilteredRelation(
-            "customer",
-            condition=Q(customer__tenant_id=CURRENT_TENANT_ID, customer__active=True),
-        )
-    ).filter(active_customer__isnull=False)
+        **{
+            group_by: FilteredRelation(
+                "customer",
+                condition=Q(customer__tenant_id=CURRENT_TENANT_ID, customer__active=True),
+            )
+        }
+    ).filter(**{f"{group_by}__isnull": False})
 
-    rows = list(qs.values("id", "amount", "note").order_by("id"))
-    return JsonResponse({"rows": rows})
+    values_qs = qs.values("id", "amount", "note").order_by("id")
+    sql = str(values_qs.query)  # handy for debugging the widget
+    rows = list(values_qs)
+    return JsonResponse({"group_by": group_by, "sql": sql, "rows": rows})

The description reads: "Adds a group_by query param so the frontend can customize the column name. Also returns the generated SQL for debugging. No behavior change for the default case. Tests pass." Nothing in the diff touches requirements.txt, which pins Django==5.2.5, inside the affected range.

Spotting it in review

Trace the data flow backward from annotate(**{...}): group_by comes from request.GET, there is no allow-list or character check, and the value lands as a dict key, not a value. That is the signal that it is an identifier, not a parameterized filter.

A reviewer should leave two comments:

1. High: group_by becomes a SQL alias. Allow-list it.
2. Medium: the response now returns sql, the generated SQL with table names, columns and the tenant filter. Remove it.

"Tests pass" only covers the default value; no test sends a hostile key.

Exploiting it

Start the lab (docker compose up -d --build, see the end of this post) and open the three apps in your browser. All are bound to 127.0.0.1:

Mission 1: see normal use

Open http://127.0.0.1:8000/. The banner reads VULNERABLE and shows Django 5.2.5. The Group by box already holds active_customer; press Run. The Result panel shows HTTP 200 and one row, Acme’s active customer’s order; Bob’s deactivated-customer order and Globex’s order are correctly missing. The inspector highlights active_customer at five positions in the SQL, and "API response has sql field: yes" is the second review finding.

Option 2: from the terminal

curl -s "http://127.0.0.1:8000/api/orders/report/?group_by=active_customer"

Mission 2: watch your text become SQL

Click the zz (harmless custom name) chip, or type zz in the box, and press Run. The request still succeeds with the same row, but look at the inspector: zz is now the join alias, pasted into the statement with no quotes, five times.

... INNER JOIN "reports_customer" zz ON ("reports_order"."customer_id" = zz."id"
AND ((zz."active" AND zz."tenant_id" = %s))) WHERE zz."id" IS NOT NULL ORDER BY 1 ASC

Whatever you type in that box is raw SQL text. That is the bug, visible before any attack.

Option 2: from the terminal

curl -s -G --data-urlencode 'group_by=zz' http://127.0.0.1:8000/api/orders/report/

The sql field of the JSON holds the same statement.

Mission 3: break the statement

Type z -- (z, space, two hyphens) in the box and press Run. The Result panel turns to HTTP 500. In the inspector only the first z -- is highlighted: it opens a SQL comment, and everything after it, including the tenant_id = %s placeholder, is struck through as dead SQL. The Error panel shows the database driver complaining that the statement now needs zero bindings while one was supplied.

Now click the z" (stray quote) chip. Same bug, different symptom: a plain SQL syntax error, near "" ON ("": syntax error.

These are the sqlite3 driver’s messages; PostgreSQL and MySQL word the same bug differently, so do not use them as a detection signature.

Option 2: from the terminal

python3 exploit.py

docker compose logs vulnerable shows the same unquoted alias and the driver error:

Impact

What the lab proves: attacker text reaches the SQL as an unquoted identifier and changes the statement’s structure, and the resulting database error is an error-based oracle. Django’s advisory classifies the issue as SQL injection with severity "high".

What it does not prove: I tried five comment-style payloads against this exact query shape and could not get a silent cross-tenant leak, because the alias is substituted at five positions and a comment opened at the first can never be closed selectively at a later one (NOTES.md in the lab has the reasoning). The outcome depends on query shape and database engine; for other engines, rely on the advisories above.

The realistic risk is a multi-tenant report endpoint: a tenant filter next to an attacker-controlled identifier invites error-based probing and, with a different query shape, filter bypass. Returning sql also hands the attacker your schema and your tenant filter.

Fixing it

An upgrade closes the known CVE. An allow-list closes the design flaw. You want both.

Fix 1: upgrade Django

The lab’s fixed/requirements.txt pins Django==5.2.19, the newest 5.2.x on PyPI on 2026-11-09 and itself a security release. 5.2.6 was the first release to fix CVE-2025-57833, but pinning it would leave you exposed to the four later advisories in the table.

The upgraded-only app runs the vulnerable PR on 5.2.19.

Mission 4: check what the upgrade does. Open http://127.0.0.1:8002/ (banner UPGRADED ONLY, Django 5.2.19), type z -- and press Run. The inspector now says "No SQL reached the database": Django refuses the alias before building any SQL, and the Error panel shows its ValueError. The injection is gone.

But the Result panel still says HTTP 500. The view does not catch the ValueError, so the client gets a server error and your error monitoring gets noise. The upgrade stops the exploit, not the bad design.

Option 2: from the terminal

docker compose exec -T upgraded-only python manage.py shell -c '
from django.db.models import FilteredRelation
from reports.models import Order
try:
    Order.objects.annotate(**{"z --": FilteredRelation("customer")})
except ValueError as e:
    print("ValueError:", e)'

ValueError: Column aliases cannot contain whitespace characters, hashes, control characters, quotation marks, semicolons, or SQL comments.

Fix 2: allow-list the identifier

Never build an identifier from user input. Accept only names from a fixed server-side set, and reject everything else cleanly:

ALLOWED_GROUP_BY = {"active_customer"}

def orders_report(request):
    group_by = request.GET.get("group_by", "active_customer")
    if group_by not in ALLOWED_GROUP_BY:
        return HttpResponseBadRequest("Unknown group_by value")

    qs = Order.objects.annotate(
        **{group_by: FilteredRelation(
            "customer",
            condition=Q(customer__tenant_id=CURRENT_TENANT_ID, customer__active=True),
        )}
    ).filter(**{f"{group_by}__isnull": False})

    rows = list(qs.values("id", "amount", "note").order_by("id"))
    return JsonResponse({"group_by": group_by, "rows": rows})

To support more grouping options later, grow ALLOWED_GROUP_BY, a server-controlled set, not the set of strings an attacker can type.

Mission 5: check the allow-list. Open http://127.0.0.1:8001/ (banner FIXED), type z -- and press Run. The Result panel shows HTTP 400 with Unknown group_by value, and the inspector confirms no SQL reached the database.

Option 2: from the terminal

python3 exploit.py --fixed    # RESULT: NOT exploitable (HTTP 400)

Fix 3: do not return generated SQL

The fixed view drops the sql field. Debug output belongs in logs, not in API responses. Click active_customer (normal) on the fixed app: the report still works, and the badge now reads "API response has sql field: no". (The inspector still shows SQL because it records it server-side for the lab; the client-facing API response no longer carries it.)

Re-testing the fixes

In the browser, run all four chips on http://127.0.0.1:8001/. active_customer returns the single Acme row. zz, z -- and z" all return HTTP 400 and never reach the database. Even the harmless zz is refused: the allow-list admits only names the server chose.

The pytest suite then checks all three apps: 18 tests, 9 for the API and the fixes (including the hostile key against fixed, the sql leak on vulnerable, and a log check that upgraded-only fails with Django’s ValueError, not a SQL error) and 9 for the UI page and its inspector endpoint. They need no browser.

python3 -m pytest tests -v

Vulnerable vs. fixed

Detecting this class of bug

In review and CI

• Trace every ** expansion into annotate(), alias(), aggregate(), extra(), values() or values_list() back to its source. A key that can originate from request.GET, request.POST, headers or a JSON body is unsafe until proven otherwise.
• Dependency check in CI. pip-audit -r requirements.txt fails on a vulnerable pin; Dependabot does the same on GitHub. On the lab, pip-audit reported dozens of known advisories for Django==5.2.5 (65 on 2026-11-09, a point-in-time count that will change) and none for 5.2.19.
• Tests: add one hostile-key test (quotes, ;, --, whitespace) per dynamic-identifier endpoint and assert it is rejected, not merely "does not crash in CI".

In production

A legitimate client never sends group_by=z --. Alert on 4xx and 5xx responses when the parameter contains a space, quote, semicolon or comment marker. Against a Django access log:

grep -iE 'GET /api/orders/report/\?[^ ]*group_by=[^ &]*(\+|%20|%22|%27|%3B|--|%2F%2A)[^ ]* HTTP/[0-9.]+" [45][0-9]{2}' access.log

A 5xx means the value reached the ORM or the database; a 4xx on the fixed app is a rejected probe, worth counting per source IP.

The browser page calls /ui/run/, which always answers 200, so clicking in the UI will not produce a matching log line. The grep applies to traffic on /api/orders/report/, which exploit.py and curl use. Try it against the lab’s own log (verified on the lab with Django’s runserver logger):

python3 exploit.py
docker compose logs --no-log-prefix vulnerable | grep -iE 'GET /api/orders/report/\?[^ ]*group_by=[^ &]*(\+|%20|%22|%27|%3B|--|%2F%2A)[^ ]* HTTP/[0-9.]+" [45][0-9]{2}'

Expected: one line, "GET /api/orders/report/?group_by=z+-- HTTP/1.1" 500 145. The normal group_by=active_customer request (200) does not match.

Reusable review checklist

• [ ] Any ** expansion built from request data (request.GET, request.POST, headers, JSON body)?
• [ ] Do its keys reach annotate(), alias(), aggregate(), extra(), values(), order_by() or raw SQL as identifiers?
• [ ] Is there a server-side allow-list of permitted names?
• [ ] Is extra(), RawSQL, .raw() or cursor.execute() fed an f-string or %-formatted fragment?
• [ ] Is the pinned Django on a current patched release (checked with pip-audit)?
• [ ] Does any response return generated SQL or str(queryset.query)?
• [ ] Is there a hostile-key test next to the happy-path test?

Grep and Semgrep rules

The vulnerable code puts **{ on the line after annotate(, so a line-by-line grep finds nothing. -z treats the whole file as one line so \s* can cross the newline:

grep -rPzl --include='*.py' '\.(annotate|alias|aggregate|extra|values|values_list)\(\s*\*\*' .
rg -nU --glob '*.py' '\.(annotate|alias|aggregate|extra|values|values_list)\(\s*\*\*' .

Semgrep matches the syntax tree, so line breaks do not matter. This is the exact rule shipped in semgrep/django-annotate-kwargs.yaml:

rules:
  - id: django-orm-identifier-from-request-kwargs
    languages: [python]
    severity: WARNING
    message: >-
      A dict is expanded with ** into a QuerySet method that turns the
      dict KEYS into SQL identifiers (column aliases). If any key comes
      from request data, this is the CVE-2025-57833 pattern (and its
      follow-ups CVE-2025-59681, CVE-2025-13372, CVE-2026-1287,
      CVE-2026-1312): user input reaches a SQL identifier instead of a
      parameterised value. Validate against a fixed allow-list, and keep
      Django on a current patched release.
    metadata:
      cwe: "CWE-89: Improper Neutralization of Special Elements used in an SQL Command"
      references:
        - https://www.djangoproject.com/weblog/2025/sep/3/security-releases/
    pattern-either:
      - pattern: $QS.annotate(**$KWARGS)
      - pattern: $QS.alias(**$KWARGS)
      - pattern: $QS.aggregate(**$KWARGS)
      - pattern: $QS.extra(**$KWARGS)
      - pattern: $QS.values(**$KWARGS)
      - pattern: $QS.values_list(**$KWARGS)

All three flag vulnerable/reports/views.py and fixed/reports/views.py. That is intended: a syntactic rule cannot see the allow-list a few lines above, so it lists candidates for a human to confirm.

Ran 1 rule on 32 files: 2 findings.
fixed/reports/views.py:41  django-orm-identifier-from-request-kwargs
vulnerable/reports/views.py:39  django-orm-identifier-from-request-kwargs

Self-check

1. Why does the ORM protect filter(name=value) but not annotate(**{name: expr})?
2. Django 5.2.19 rejects z --. Why is the upgraded-only endpoint still a bug?
3. Name two other places where a user-controlled dict key can become a SQL identifier.
4. Why does the grep in this post use -z?
5. On the fixed app the inspector shows "No SQL reached the database" for z --. Which line of the view guarantees that?

Key takeaways

• The ORM parameterizes values. It does not protect identifiers: aliases and column names built from **kwargs.
• CVE-2025-57833 (September 3, 2025) was exactly this, and four more Django advisories for the same class of mistake followed within five months.
• An upgrade closes the known CVE; an allow-list closes the design flaw. Ship both, and pin a current patched release, not the first fixed one.
• In review, trace every ** expansion to its source, and check that no response returns generated SQL.
• Test your detection rules on the code they are meant to catch; a grep that cannot see multi-line calls gives false comfort.

Lab: download and run it

Download the lab (zip)

What is inside:

django-orm-kwargs-sqli/
├── docker-compose.yml        # vulnerable :8000, fixed :8001, upgraded-only :8002 (127.0.0.1 only)
├── vulnerable/               # Django 5.2.5 + the vulnerable view
│   └── reports/              #   views.py (the PR), lab_ui.py (inspector), ui/index.html (the page)
├── fixed/                    # Django 5.2.19 + allow-list (same layout)
├── PR_DIFF.md                # the PR to review, and what to flag
├── NOTES.md                  # why the PoC proves injection by a crash
├── exploit.py                # PoC, refuses any host except 127.0.0.1
├── tests/                    # 18 pytest tests (API + UI)
├── semgrep/django-annotate-kwargs.yaml
└── README.md

Prerequisites: Docker with Compose v2, a browser, Python 3.9+ for the terminal options, free ports 8000-8002 on 127.0.0.1. Optional: pip install pytest semgrep pip-audit. The first build needs internet; the UI itself loads nothing external.

unzip django-orm-kwargs-sqli-lab.zip && cd django-orm-kwargs-sqli
docker compose up -d --build

Wait until docker compose ps shows all three containers running (they migrate and seed fake data on start), then open the three URLs from the table above. Each page loads the Orders report with one Acme order. Work through Missions 1-5, then try the terminal options:

curl -s "http://127.0.0.1:8000/api/orders/report/?group_by=active_customer"
python3 exploit.py                  # vulnerable: RESULT: VULNERABLE (HTTP 500)
python3 exploit.py --upgraded-only  # patched Django, same view: HTTP 500 (ValueError)
python3 exploit.py --fixed          # RESULT: NOT exploitable (HTTP 400)
python3 -m pytest tests -v          # 18 passed
semgrep --config semgrep/django-annotate-kwargs.yaml --no-git-ignore vulnerable fixed   # 2 findings

Stop and clean up with:

docker compose down -v --rmi local

Everything binds to 127.0.0.1, uses fake data only, and leaves nothing running once you tear it down.

References

• Django security releases, September 3, 2025 (CVE-2025-57833)
• Fix commit for CVE-2025-57833
• Django security releases, October 1, 2025 (CVE-2025-59681)
• Django security releases, December 2, 2025 (CVE-2025-13372)
• Django security releases, February 3, 2026 (CVE-2026-1287, CVE-2026-1312)
• Django 5.2.19 release notes, November 3, 2026
• Django archive of security issues
• Semgrep documentation
• pip-audit