Gotcha: SQL injection in dynamic query builders

SQL injection vulnerabilities from string concatenation in dynamically built queries, even when using an ORM.

Always parameterize, even in dynamic queries:

# BAD: String concatenation (SQL injection!)
def search_users(name, role):
    query = f"SELECT * FROM users WHERE name LIKE '%{name}%'"
    if role:
        query += f" AND role = '{role}'"
    return db.execute(query)  # DANGEROUS!

# Input: role = "admin' OR '1'='1" -> returns all users!

# GOOD: Parameterized dynamic queries
def search_users(name=None, role=None, min_age=None):
    conditions = []
    params = []
    
    if name:
        conditions.append("name LIKE %s")
        params.append(f'%{name}%')
    if role:
        conditions.append("role = %s")
        params.append(role)
    if min_age:
        conditions.append("age >= %s")
        params.append(min_age)
    
    query = "SELECT * FROM users"
    if conditions:
        query += " WHERE " + " AND ".join(conditions)
    
    return db.execute(query, params)  # Safe!

# GOOD: Use query builder (SQLAlchemy)
from sqlalchemy import select

query = select(User)
if name:
    query = query.where(User.name.ilike(f'%{name}%'))
if role:
    query = query.where(User.role == role)
results = session.execute(query)

# For column names (can't parameterize):
ALLOWED_SORT = {'name', 'created_at', 'email'}
def sort_users(sort_by):
    if sort_by not in ALLOWED_SORT:
        raise ValueError(f'Invalid sort column: {sort_by}')
    return db.execute(f'SELECT * FROM users ORDER BY {sort_by}')

Key insight: parameterized queries protect values. Column/table names must be validated against an allowlist.

SQL injection is consistently in OWASP Top 10. Dynamic query builders are especially risky because the concatenation feels 'internal' and safe, but user input reaches it.

Any application building SQL queries with user input