flawopen.com/SQL Injection in Python/SQLAlchemy

Is SQLAlchemy text() safe from SQL injection?

Reference guide

Short answer

A bound parameter is like a sealed value card in a query form: SQLAlchemy passes the SQL statement and its parameter mapping to the underlying DBAPI, keeping values out of the SQL expression your code builds. The DBAPI driver handles database-specific binding, so wire behavior can vary. text() accepts SQL text but does not sanitize or parameterize values you already interpolated into that text.

UNSAFE — interpolate input into SQL

stmt = text(
  f"SELECT id FROM users WHERE id = {user_id}"
)
rows = conn.execute(stmt)

SAFE — bind values; constrain SQL structure

stmt = text(
  "SELECT id FROM users WHERE id = :id"
)
rows = conn.execute(stmt, {"id": user_id})

# Bind values, not identifiers. Choose any
# selectable column from a fixed application map.
columns = {"name": users.c.name,
           "created": users.c.created_at}
column = columns.get(requested_sort, users.c.created_at)
stmt = select(users.c.id).order_by(column)
rows = conn.execute(stmt)

What text() does — and what it does not do

text() creates a textual SQL construct; it does not sanitize a string that already contains interpolated input. Declare value placeholders in the SQL, then pass values separately to Connection.execute() or Session.execute(). SQLAlchemy's Core expression API binds Python literal values where appropriate, but textual fragments remain SQL syntax. Bind parameters cannot stand for table names, column names, or sort directions. Map any user-selected structure to fixed SQLAlchemy expressions. Parameterization prevents SQL injection; it does not replace authorization or input validation for business rules.

How to audit your codebase

rg -n 'text\(|\.execute\(' --glob '*.py' .

FAQ

Are SQLAlchemy Core and ORM query builders always safe?

They bind Python values in normal expression construction, but safety depends on how the statement is built. Review textual SQL, literal SQL constructs, and dynamic identifiers as well. A query API cannot make an unsafe SQL string safe after application code has concatenated input into it.

Is SQLAlchemy text() safe from SQL injection?

What text() does — and what it does not do

How to audit your codebase

FAQ

References