SQL Injection Still Ranks #1: Parameterized Queries Are the Fix

Root cause

Concatenating user input into a SQL string lets an attacker write ' OR '1'='1 to change the meaning and read, alter or delete data.

The fix: parameterized queries

Keep code and data separate: placeholders ? or named params make the database always treat input as data, never as command.

// Wrong: concatenation
db.query("SELECT * FROM u WHERE name = '" + name + "'")

// Right: parameterized
db.query("SELECT * FROM u WHERE name = ?", [name])

Two illusions

• ORMs are safe: an ORM that builds raw SQL by concatenation is just as dangerous; parameterize raw queries too;
• Escaping is enough: manual escaping is easy to miss and error-prone; parameterization is steadier.

Real-world cases: three injection surfaces still seen today

1. ORM raw queries: an ORM is not a talisman — raw("... WHERE id=" + id) is injectable exactly as before. Wherever strings are concatenated, switch to bound parameters.
2. LIKE and sort fields: LIKE '%"+kw+"%' is injectable; ORDER BY "+col cannot be parameterized in many drivers, so map the column name through an allowlist — never concatenate it.
3. Second-order injection: input is escaped on write, then concatenated again on read — by then the quotes look "clean". The root cause is still concatenation; parameterization is the fix.

FAQ

Does parameterization stop all injection? It stops data being treated as command; if table or column names come from user input, validate them against an allowlist. Why is escaping not enough? It is tightly coupled to charset and driver behaviour, and any mismatch leaks — parameterization hands the boundary to the driver. Is a read-only account safe? Reads still leak data and the account may later be granted writes; follow least privilege and always parameterize. Can I log SQL? Yes, log the parameterized template plus the parameter values, never the concatenated statement — that avoids the log becoming a second injection path.

Defence in depth: shrink the blast radius

1. Least privilege: grant the application account only the tables and DML it needs, and forbid DROP, FILE and cross-database queries, so even a successful injection cannot be catastrophic;
2. A single data-access layer: funnel database access through one wrapper and ban hand-built SQL in business code, removing the entry point by process rather than by vigilance;
3. Review and static analysis: make "string-concatenated SQL" a mandatory review item and scan for raw(, string addition and template interpolation;
4. Allowlists, not blocklists: map enum-like parameters (sort fields, status values) through an allowlist; accept no free text;
5. Monitoring and alerting: alert on anomalous query shapes (many UNIONs, SLEEP, error spikes) — injection attempts usually leave traces before they succeed.

Boundary cases

• Dynamic table or column names: parameterisation cannot cover them — use an allowlist mapping, never escaping;
• Bulk inserts: use the driver's batch API (a parameter array with VALUES (?,?),(?,?)) rather than concatenating in a loop;
• Fuzzy search: add wildcards to the parameter value ("%" + kw + "%"), not to the SQL text;
• Stored procedures: dynamic SQL built by concatenation inside them is just as injectable — review them too.

After an incident

If injection is suspected: preserve logs and traffic evidence first, then rotate database credentials and reduce privileges immediately, then audit recent data changes and outbound connections, and only afterwards fix code and add tests. Reversing that order destroys the evidence chain.

Testing and regression

• A fixed corpus: keep a test set with quotes, comment markers, UNION fragments and encoding variants, and rerun it with the API;
• Boundary parameters first: sort fields, table-name mappings and fuzzy-search parameters are the most commonly missed spots — test them explicitly;
• Automated scanning: wire SQL injection scanning into CI so new concatenation is caught before merge;
• Verify in production: after release, confirm with a read-only query that the app account cannot run DDL, proving least privilege holds.

Fix priority

Once an injection point is found, close externally reachable entry points first (especially unauthenticated ones), then work inward; rotate potentially leaked credentials and audit recent anomalous queries in parallel. The order is stop the bleeding, preserve evidence, fix the root cause, then add tests — editing code first destroys the scene.