Gotcha: SQL injection in dynamic queries even with ORMs

Developers assume ORMs prevent all SQL injection, but raw queries, dynamic ordering, and string interpolation in ORMs are still vulnerable.

SQL injection vectors that survive ORMs:

# Python / SQLAlchemy

# BAD: String interpolation in raw SQL
user_input = "'; DROP TABLE users; --"
db.execute(f"SELECT * FROM users WHERE name = '{user_input}'")
# SQL injection!

# GOOD: Parameterized query
db.execute("SELECT * FROM users WHERE name = :name", {"name": user_input})

# BAD: Dynamic column name from user input
sort_col = request.args.get('sort')  # Could be: "name; DROP TABLE users"
db.execute(f"SELECT * FROM users ORDER BY {sort_col}")

# GOOD: Whitelist allowed columns
ALLOWED_SORT = {'name', 'email', 'created_at'}
sort_col = request.args.get('sort', 'name')
if sort_col not in ALLOWED_SORT:
    sort_col = 'name'
db.execute(f"SELECT * FROM users ORDER BY {sort_col}")

# BAD: LIKE with unescaped wildcards
search = request.args.get('q')  # Could be: "%" (matches everything)
db.execute("SELECT * FROM users WHERE name LIKE :q", {"q": f"%{search}%"})
# Technically parameterized but % and _ are wildcards

# GOOD: Escape LIKE wildcards
def escape_like(s):
    return s.replace('%', '\\%').replace('_', '\\_')
db.execute(
    "SELECT * FROM users WHERE name LIKE :q",
    {"q": f"%{escape_like(search)}%"}
)

// Node.js / Sequelize / Knex

// BAD: Raw query with string concat
const name = req.query.name;
db.query(`SELECT * FROM users WHERE name = '${name}'`);

// GOOD: Parameterized
db.query('SELECT * FROM users WHERE name = $1', [name]);

// BAD: Sequelize literal without sanitization
const order = req.query.order;
User.findAll({ order: db.literal(order) }); // Injection!

// GOOD: Whitelist
const ALLOWED = ['name ASC', 'name DESC', 'created_at DESC'];
const order = ALLOWED.includes(req.query.order) ? req.query.order : 'name ASC';

Parameterized queries protect values but not identifiers (table names, column names, ORDER BY). Any user input used as SQL structure (not just values) needs whitelisting.

Web application security