Replace f-string SQL construction with parameterized queries #6

New Issue

2026-04-20T09:34:29Z

sharang commented

2026-04-20 09:34:29 +00:00

Problem

Multiple routes build SQL WHERE clauses via f-string concatenation and pass them directly to sqlalchemy.text():

compliance/api/canonical_control_routes.py:295-377 — text(f"SELECT ... WHERE {w_all}")
compliance/api/quality_routes.py — same pattern
compliance/api/obligation_routes.py — same pattern

Even though some individual values are bound via params, the structural parts of the WHERE clause are built from unvalidated user input. This is exploitable SQL injection.

Required Actions

Audit all files under compliance/api/ for text(f" and text(" with concatenation
Replace dynamic WHERE clause construction with SQLAlchemy ORM filter chaining or .bindparams()
If raw SQL is unavoidable, build a safe WhereClauseBuilder that only allows known column names as keys (allowlist-based)
Add bandit -r compliance/ to CI python-lint job to catch future regressions

Acceptance Criteria

grep -r 'text(f' compliance/api/ returns zero results
bandit scan exits 0 on all API route files

## Problem Multiple routes build SQL WHERE clauses via f-string concatenation and pass them directly to `sqlalchemy.text()`: - `compliance/api/canonical_control_routes.py:295-377` — `text(f"SELECT ... WHERE {w_all}")` - `compliance/api/quality_routes.py` — same pattern - `compliance/api/obligation_routes.py` — same pattern Even though some individual values are bound via `params`, the structural parts of the WHERE clause are built from unvalidated user input. This is exploitable SQL injection. ## Required Actions 1. Audit all files under `compliance/api/` for `text(f"` and `text("` with concatenation 2. Replace dynamic WHERE clause construction with SQLAlchemy ORM filter chaining or `.bindparams()` 3. If raw SQL is unavoidable, build a safe `WhereClauseBuilder` that only allows known column names as keys (allowlist-based) 4. Add `bandit -r compliance/` to CI python-lint job to catch future regressions ## Acceptance Criteria - `grep -r 'text(f' compliance/api/` returns zero results - `bandit` scan exits 0 on all API route files

sharang added this to the M1: Security Foundation milestone 2026-04-20 09:34:29 +00:00

sharang added the data-integrity severity: high security labels 2026-04-20 09:34:29 +00:00

Benjamin_Boenisch referenced this issue from a commit

2026-05-11 09:51:36 +00:00

refactor: Paket 2 Analyse umstrukturiert + AI Act/Evidence verschoben

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: Benjamin_Boenisch/breakpilot-compliance#6