SQL injection has appeared in the OWASP Top 10 since the list was created. Despite two decades of “use prepared statements” being conventional wisdom, SQLi vulnerabilities still account for a significant share of critical findings in real-world penetration tests. The defenses exist. The failures are in how they’re applied.
Why Prepared Statements Work
The fundamental problem with string-interpolated queries is that user input is parsed as SQL syntax:
# Vulnerable
query = f"SELECT * FROM users WHERE email = '{email}'"If email is ' OR '1'='1, the parser sees a syntactically valid SQL expression with injected logic.
Prepared statements separate the query structure from the data. The database compiles the query plan before any user data is substituted:
# Safe
cursor.execute("SELECT * FROM users WHERE email = %s", (email,))The %s placeholder is never interpreted as SQL. The database treats it as a typed value, not executable syntax. This defense is correct and complete — for the cases it covers.
Where Prepared Statements Don’t Apply
Prepared statements can’t parameterize identifiers — table names, column names, ORDER BY columns, schema names. These must be assembled dynamically and can’t be bound as typed values.
This creates a class of injection points that developers often miss:
# Common in sortable tables — sort_col comes from a query param
query = f"SELECT * FROM products ORDER BY {sort_col} DESC"An attacker controlling sort_col can inject:
sort_col = "(SELECT SUBSTRING(password,1,1) FROM users LIMIT 1)"and use timing or error-based techniques to exfiltrate data one character at a time.
Fix for dynamic identifiers: use a strict allowlist, never raw input:
ALLOWED_SORT_COLUMNS = {"price", "name", "created_at"}
if sort_col not in ALLOWED_SORT_COLUMNS:
sort_col = "created_at"
query = f"SELECT * FROM products ORDER BY {sort_col} DESC"ORM Misuse
ORMs like SQLAlchemy, Django ORM, and Hibernate prevent injection in their normal usage, but they all expose escape hatches that reintroduce raw SQL:
# Django — safe
User.objects.filter(email=email)
# Django — vulnerable: raw() doesn't parameterize f-string interpolation
User.objects.raw(f"SELECT * FROM users WHERE email = '{email}'")// Hibernate HQL — safe
session.createQuery("FROM User WHERE email = :email").setParameter("email", email)
// Hibernate — vulnerable: string concatenation in native query
session.createNativeQuery("SELECT * FROM users WHERE email = '" + email + "'")The pattern is consistent: the escape hatch bypasses the ORM’s parameterization. Any use of .raw(), text(), createNativeQuery(), or equivalent should be treated as a high-risk surface requiring explicit review.
Second-Order Injection
First-order injection hits the database on the same request that delivers the payload. Second-order injection is more subtle: malicious input is stored safely (parameterized write), then retrieved and unsafely interpolated into a later query.
-- Step 1: User registers with username: admin'--
INSERT INTO users (username) VALUES ('admin''--') -- stored correctly
-- Step 2: A stored procedure uses the retrieved username without re-parameterizing
SET @sql = 'UPDATE users SET role = ''user'' WHERE username = ''' + @username + ''''
EXEC(@sql)
-- Now the injected apostrophe corrupts the dynamic SQLSecond-order injection is harder to find in code review because the vulnerable query is spatially and temporally separated from the injection point. ORM-based systems are not immune if they use dynamic SQL in stored procedures, triggers, or reporting queries.
Blind Injection and Detection Evasion
When the application returns no error output and no query results, attackers use boolean-based or time-based blind techniques:
-- Boolean-based: page behavior differs based on condition truth
' AND SUBSTRING(username,1,1)='a'--
-- Time-based: infer truth from response latency
'; IF (1=1) WAITFOR DELAY '0:0:5'-- -- MSSQL
' AND SLEEP(5)-- -- MySQLModern WAFs attempt to detect these patterns. Evasion techniques include:
- Encoding variations:
%27for',%2527(double-encoded), Unicode equivalents - Case and whitespace manipulation:
SeLeCt, comments as whitespace (SE/**/LECT) - HTTP parameter pollution: splitting the payload across duplicate parameters
- Out-of-band channels: DNS exfiltration via
LOAD_FILE()orxp_dirtree, bypassing response-based detection entirely
WAFs are not a substitute for parameterized queries. They’re an additional detection layer with a non-zero bypass rate, especially against custom or obfuscated payloads.
Defense in Depth
| Layer | Mechanism | Covers |
|---|---|---|
| Parameterized queries | Structural separation of code and data | All standard query values |
| Identifier allowlisting | Validate dynamic SQL identifiers against known-good sets | Column/table names |
| ORM default usage | Avoids raw SQL entirely | Most CRUD operations |
| Least-privilege DB accounts | Read-only accounts for read-only operations | Limits blast radius |
| WAF + anomaly detection | Pattern matching on requests | Defense depth, not primary |
| Regular code audit | Grep for raw SQL construction patterns | Second-order and ORM escapes |
# Quick grep for raw SQL construction in a Python codebase
grep -rn "\.raw\|execute(f\"\|execute(\".*%s.*%" . --include="*.py"Testing Your Own Code
Use sqlmap in a controlled environment against staging:
sqlmap -u "https://staging.example.com/api/products?sort=name" \
--level=3 --risk=2 --dbms=postgresql --batchHigh --level tests more injection points including headers and cookies. The output tells you which parameters are injectable and what techniques work against your specific database and application behavior.
Conclusion
The fix for SQL injection is known. The failure modes are:
- Dynamic identifiers handled with string interpolation instead of allowlists
- ORM escape hatches used without re-parameterization
- Second-order injection in stored procedures or reporting layers
- Trusting WAFs as a primary defense
Audit every place raw SQL is constructed. Parameterize what can be parameterized. Allowlist what can’t. Restrict database permissions. The vulnerability class is entirely preventable.