Defence in Depth

Chapter 8

Defence in Depth

Validation, least privilege, stored procedures, escaping, WAFs — layered on top

Parameterized queries (Chapter 7) are the fix. This chapter is everything you add around them so that a single mistake isn't catastrophic — and, just as importantly, a clear-eyed account of what each extra layer does and does not achieve. The recurring caution: none of these replaces parameterization; they reduce likelihood and limit blast radius.

Layer 1: Input Validation (allowlist)

Validate that input matches its expected shape — an ID is a positive integer, an email looks like an email, a status is one of a fixed set. Reject anything off-format (an allowlist, not a blocklist of "bad characters"):

// reject anything that isn't a positive integer before it reaches a query
if (!Number.isInteger(id) || id < 1) return badRequest();

Validation is defence in depth, not a SQLi fix — same lesson as the XSS course

Input validation genuinely helps for strict-format fields (an integer id can't carry a SQL payload if you reject non-integers). But it is not a substitute for parameterization, for the same reasons it failed XSS: many legitimate fields (names like O'Brien, free-text comments, search) must accept the very characters payloads use, so you can't reject them without breaking the feature; blocklisting "bad" characters is endlessly bypassable; and it does nothing for second-order SQLi (Chapter 6), where the payload arrives from your own database. Validate for data quality and to shrink the attack surface — then still parameterize every query.

Layer 2: Least-Privilege Database Accounts

The app should connect to the database as an account with the minimum permissions it actually needs — not as root/sa/a DB owner. This doesn't prevent injection, but it dramatically limits the damage if one occurs:

Read-only where possible — a reporting endpoint's account with only SELECT can't be used to UPDATE/DROP via a stacked query.
Scope to needed tables — no access to tables the feature doesn't use.
No dangerous privileges — no FILE, no ability to create users, no xp_cmdshell; these are the rungs from SQLi to OS compromise (Chapter 1).
Separate accounts per service — a breach of one is contained.

Least privilege is the difference between "an attacker read one table" and "an attacker dropped the database and ran commands on the server."

Layer 3: Stored Procedures (only if parameterized inside)

Stored procedures are often cited as a SQLi defence — but the protection comes from how they use parameters, not from being a stored procedure:

// SAFE: the procedure uses its parameter as a bound value
CREATE PROCEDURE getUser(IN uname VARCHAR(50))
  SELECT * FROM users WHERE username = uname;   // bound, fine

// VULNERABLE: the procedure builds dynamic SQL by concatenation internally
CREATE PROCEDURE getUser(IN uname VARCHAR(50))
  SET @sql = 'SELECT * FROM users WHERE username = ''' + uname + '''';
  EXECUTE @sql;   // concatenation inside the proc → still injectable!

A stored procedure is not automatically safe

The myth "stored procedures prevent SQLi" is only true when the procedure treats its inputs as bound parameters. A procedure that builds and EXECs a dynamic SQL string by concatenating its parameters is exactly as injectable as application-side concatenation — the vulnerability just moved into the database. Stored procedures can be a fine layer (and help with privilege separation), but the actual protection is the same one as always: parameters used as data, never concatenated. Don't treat "we use stored procs" as a substitute for that discipline.

Layer 4: ORMs & Query Builders

ORMs (Sequelize, Hibernate, Django ORM, ActiveRecord, etc.) parameterize by default — calling User.find({ where: { id } }) generates a bound query, which is why ORM-heavy code has far less SQLi. But they're not a force field: raw query escape hatches reintroduce it (covered fully next chapter). For now: ORMs are a strong layer because they make the safe path the default, but their raw-SQL methods are where injection creeps back.

Layer 5: Escaping — Last Resort Only

Manually escaping input (via a vetted, charset-correct library function) to make it safe to concatenate is the weakest option and should be reserved for the rare case you genuinely can't parameterize. Recap from Chapter 7: it's fragile (charset/multi-byte bypasses), useless for numeric context, easy to forget, and corrupts data. If you find yourself escaping, ask first whether you can parameterize or use an allowlist instead.

Layer 6: WAFs — A Backstop, Not a Fix

A Web Application Firewall inspects requests and blocks ones matching known SQLi patterns. It can stop opportunistic/automated attacks and buy time to patch — useful as an outer layer. But it's pattern-matching, so it's bypassable (encoding, comments, case tricks, novel payloads — the same blocklist-loses problem from the XSS course), and it does nothing about second-order injection or the underlying bug.

Never let a WAF substitute for fixing the code

A WAF is a valuable backstop — defence in depth at the perimeter, helpful against mass-scanning and zero-day windows — but treating it as your SQLi defence is the classic mistake. Attackers routinely bypass WAF signatures, and a WAF can't see second-order payloads (the malicious value enters as innocuous data). The vulnerability remains in the code. Use a WAF to reduce noise and add a safety margin; fix the actual injection with parameterized queries underneath.

The Layered Picture

Layer	What it does	Status
Parameterized queries	removes the vulnerability	THE fix (Chapter 7)
Input validation (allowlist)	shrinks attack surface; rejects malformed input	defence in depth
Least privilege	limits blast radius if injected	defence in depth (critical)
Stored procedures	safe only if internally parameterized	conditional
ORM	makes safe the default; raw queries leak	strong default
Escaping	fragile concatenation safety	last resort
WAF	blocks known patterns at the perimeter	backstop

Stack them — but the load-bearing wall is parameterization; everything else assumes it's there and exists to catch what slips through (a forgotten query, a new bug) and to contain the damage.

Hands-On Exercises

Exercise 1

Explain why input validation helps but cannot replace parameterized queries. Give a field where allowlist validation genuinely blocks injection and one where it can't (without breaking the feature), and explain why second-order SQLi defeats input validation entirely.

📄 View solution

Exercise 2

Explain why least-privilege database accounts don't prevent SQLi but are still essential. For a read-only reporting endpoint, list the privileges its DB account should and shouldn't have, and describe how each restriction limits a specific attack from earlier chapters (stacked DROP, FILE read, xp_cmdshell).

📄 View solution

Exercise 3

Debunk two myths: "stored procedures prevent SQLi" and "our WAF protects us." For each, show the case where it fails (a concatenating proc; a WAF bypass / second-order), and state its correct role as defence in depth alongside parameterization.

📄 View solution

Chapter 8 Quick Reference

Defence in depth layers around parameterization — none replaces it; they cut likelihood & blast radius
Input validation (allowlist) — reject malformed input; helps strict-format fields, but not a fix (free-text fields, second-order) — same lesson as XSS
Least privilege — app connects with minimum rights (read-only where possible, no FILE/xp_cmdshell, scoped tables) → contains damage, doesn't prevent injection
Stored procedures — safe only if they use bound parameters internally; a proc that concatenates dynamic SQL is just as injectable
ORMs — parameterize by default (strong), but raw-query escape hatches reintroduce SQLi (Chapter 9)
Escaping — last resort; fragile (charset/multi-byte), useless for numeric context, easy to forget
WAF — perimeter backstop against known patterns; bypassable & blind to second-order — never a substitute for fixing the code
Stack them, but the load-bearing wall is parameterized queries; the rest catches slips and limits damage
Next chapter: SQLi in modern stacks — ORM pitfalls and NoSQL injection