SQLAlchemy ORM Security: The Raw Query Escape Hatch

SQLAlchemy’s ORM is one of the better defenses against SQL injection available to Python developers. Normal ORM calls — filter(), filter_by(), query() — compile to parameterized queries. The database receives the value separately from the SQL string. String-formatted SQL injection is structurally impossible through that path.

The problem is text().

Why the ORM Usually Protects You

BrassCoders finds very few SQL injection findings in codebases that stay within SQLAlchemy’s ORM layer because the ORM compiles to parameterized queries by design — values travel through bind parameters, not string concatenation.

When an AI assistant writes session.query(User).filter(User.id == user_id).first(), SQLAlchemy compiles this to SELECT * FROM users WHERE id = ? and passes user_id as a separate value. The database driver never sees the value merged into the SQL string. No amount of SQL syntax in user_id can modify the query structure.

The SQLAlchemy documentation calls this parameterization automatic and default. You don’t opt into it. You’d have to go out of your way to break it. And that’s exactly what text() lets you do.

The text() Escape Hatch

BrassCoders flags text() calls with f-strings or % formatting via Bandit B608 — the same rule that fires on raw cursor.execute() calls — because the query string reaching the database is already interpolated before parameterization can happen.

Here’s the pattern AI assistants produce:

from sqlalchemy import text

def get_orders_by_status(session, status, min_amount):
    stmt = text(
        f"SELECT * FROM orders "
        f"WHERE status = '{status}' AND amount >= {min_amount} "
        f"ORDER BY created_at DESC"
    )
    return session.execute(stmt).fetchall()

Enter fullscreen mode Exit fullscreen mode

The f-string builds a complete SQL string before text() receives it. SQLAlchemy wraps it in a TextClause object, but the values are already fused into the string. Passing status = "shipped' OR '1'='1" turns the WHERE clause into WHERE status = 'shipped' OR '1'='1' — every order in the database, regardless of amount.

The database sees a string. There’s nothing to parameterize. The ORM wrapper changes nothing about the injection surface.

SQLAlchemy’s own documentation on text() explicitly warns against string formatting with caller-supplied values and documents the bindparams() approach as the correct alternative.

Why AI Assistants Take It

BrassCoders’ benchmark data shows the same generation-mode pattern across SQL injection findings: the model reaches for the syntactically simpler path, which for complex queries means text() and a formatted string rather than 10-15 lines of chained SQLAlchemy ORM calls.

The ORM path for a complex multi-condition query with dynamic ORDER BY, LIMIT, or subquery logic can span 10-15 lines of chained SQLAlchemy calls. The raw SQL equivalent is 3 lines and immediately readable. An AI assistant satisfying a prompt like “write a function that returns orders filtered by status, amount, and date range, sorted by creation date” produces correct results faster with text() and a formatted string.

The model doesn’t evaluate parameterization. It evaluates whether the output satisfies the prompt. “Returns the right rows” is a prompt-level success. “Safe against injection” is a separate evaluation the model skips unless you ask.

This is the same generation-mode finding from BrassCoders’ benchmark on raw connections (see the sql-injection-ai-generated-python.md post) — the model introduced the vulnerability without warning, and would catch it if asked to review. A scan that runs automatically on every commit is a different tool from a model you have to remember to invoke.

What Bandit B608 Finds

BrassCoders includes Bandit, the de facto Python security linter, as one of its 12 static-analysis scanners. Bandit’s B608 rule — “Possible SQL injection via string-based query construction” — matches any string-formatting operation in a context that looks like a SQL query, regardless of which function or method wraps the result.

The B608 rule documentation describes the detection pattern in detail. B608 doesn’t inspect what you pass the string to. It fires on the formatting pattern itself. A text(f"SELECT ... {value}") call and a cursor.execute("SELECT ... %s" % value) call trigger the same rule because the dangerous operation — merging caller-supplied data into a query string — is identical in both cases.

Severity: medium. Confidence: medium. Those ratings reflect that B608 can’t verify whether the value comes from caller input — but in the escape-hatch pattern, it always does. If you’re calling text() with a formatted string, you’re calling it because the query includes a dynamic value.

How to Reproduce It

If you have BrassCoders installed, create a file with the pattern above and run it offline:

brasscoders --offline scan /path/to/project

Enter fullscreen mode Exit fullscreen mode

B608 fires on the text(f"...") line. The finding in .brass/ai_instructions.yaml names the file, line number, and severity, and includes a remediation note pointing to named bind parameters.

You can also reproduce the raw-connection variant from BrassCoders’ published benchmark corpus:

git clone https://github.com/CopperSunDev/brasscoders
pip install brasscoders
brasscoders --offline scan brasscoders/docs/benchmarks/ai-code-findings-corpus/files

Enter fullscreen mode Exit fullscreen mode

The corpus B608 findings are in user_lookup.py and bulk_insert.py — raw cursor.execute() calls with % formatting. Same rule, same fix. The ORM escape-hatch version produces an identical finding.

The Fix: Parameterization Inside text()

BrassCoders surfaces the fix alongside the B608 finding: use SQLAlchemy’s named bind parameters inside text() rather than string interpolation, and B608 goes silent because the query string contains no formatting operators.

SQLAlchemy’s text() function supports named bind parameters. The colon-prefixed syntax keeps the query string static and the value separate:

from sqlalchemy import text

def get_orders_by_status(session, status, min_amount):
    stmt = text(
        "SELECT * FROM orders "
        "WHERE status = :status AND amount >= :min_amount "
        "ORDER BY created_at DESC"
    )
    return session.execute(stmt, {"status": status, "min_amount": min_amount}).fetchall()

Enter fullscreen mode Exit fullscreen mode

:status and :min_amount are placeholders. SQLAlchemy passes {"status": status, "min_amount": min_amount} as bind parameters — the database driver handles isolation. A value of "shipped' OR '1'='1" goes through as the literal string shipped' OR '1'='1, not as SQL syntax.

B608 does not fire on this pattern. The query string contains no formatting operators. The value never touches the string.

For queries that fit within the ORM’s expression language, the full ORM path avoids text() entirely:

session.query(Order).filter(
    Order.status == status,
    Order.amount >= min_amount
).order_by(Order.created_at.desc()).all()

Enter fullscreen mode Exit fullscreen mode

This compiles to a parameterized query with no text() involved. B608 has nothing to flag. SQLAlchemy handles parameterization automatically.

The ORM path requires knowing SQLAlchemy’s API. The text() path requires knowing SQL. AI assistants know both — but they reach for SQL when the query is complex, because SQL is more direct. The fix is knowing where the escape hatch leads.

BrassCoders version 2.0.10 catches this pattern in the offline scan, no API call required. Install via pip install brasscoders and run brasscoders --offline scan on any Python project using SQLAlchemy.