Find the blind SQL injection via text() in SQLAlchemy
This SQLAlchemy code builds a query by interpolating a user-supplied table_name into a raw text() fragment. Find the injection and explain why it is "blind".
Constraints:
table_namecomes from user input- the query returns no rows to the user (fire-and-forget)
db.execute(
text(f"select create_new_table('{table_name}', 'ts', if_not_exists => true)")
)Diagnose the cause.
SQL injection — text() runs the concatenated f-string verbatim instead of parameterizing it, so table_name injects arbitrary SQL. Blind because nothing returns; only response timing confirms execution. Fix: bound parameters and an allowlist.
- ✗Thinking
text()parameterizes the interpolated f-string - ✗Assuming a blind injection is impossible without visible output
- ✗Mistaking the injection for a race condition or information disclosure
- →How does an attacker extract data bit by bit through a time-based blind injection?
- →Why do bound parameters eliminate the injection while a blacklist does not?
The vulnerability
SQLAlchemy's text() runs the passed string verbatim — it does NOT parameterize an f-string:
text(f"select create_new_table('{table_name}', 'ts', if_not_exists => true)")table_name is concatenated into SQL → injection. No output → blind.
How it is detected. Because the response carries no data, a vulnerable endpoint is distinguishable only through a side channel — primarily response timing: if input that closes the literal and adds a database-side delay measurably slows the response, the input is reaching the SQL parser. In practice this is caught by static analysis (a bandit/CodeQL rule for raw SQL built from an f-string) and by logging the statement text, rather than by probing — the flaw is visible in the code before anyone exploits it.
The fix
Never interpolate input into text(); use bound parameters and validate the identifier against an allowlist:
ALLOWED = {"events", "logs"}
if table_name not in ALLOWED:
abort(400)
db.execute(text("select create_new_table(:t, 'ts', if_not_exists => true)"), {"t": table_name})✅ Bound parameters escape data; the allowlist constrains table names.
