EXERCISE 2 — Auditing an app against the broken-defence catalogue
=================================================================

THE APP:
  (i)   concatenates a numeric id into the query
  (ii)  "strips single quotes" from inputs
  (iii) hides DB errors
  (iv)  validates only request inputs, not stored data
  (v)   uses a stored procedure that builds dynamic SQL
  (vi)  relies on a WAF

FLAW (i) — Concatenated numeric id. (Ch.1-2)
  WHY: numeric context needs NO quote. id = "5 OR 1=1" (or "5; DROP..." where
  stacking is allowed) injects directly. Concatenation is the root cause.
  FIX: parameterize — "... WHERE id = ?" with [id] bound — and validate id is a
  positive integer.

FLAW (ii) — "Strips single quotes." (Ch.2)
  WHY: useless for NUMERIC context (no quote to strip), fragile for string
  context (multi-byte/charset bypasses), and it corrupts legitimate data
  (O'Brien). It's the wrong defence and gives false confidence.
  FIX: stop relying on quote manipulation; parameterize. For identifiers, use an
  allowlist.

FLAW (iii) — Hides DB errors as "the fix". (Ch.3)
  WHY: hiding errors is fine HYGIENE (reduces info leak) but doesn't stop the
  injection — it merely DOWNGRADES error-based to BLIND. Data still leaks via
  boolean/time-based inference.
  FIX: keep generic errors + server-side logging, but treat it as defence in
  depth; the real fix is parameterization.

FLAW (iv) — Validates only request inputs, not stored data. (Ch.6)
  WHY: SECOND-ORDER SQLi. A value stored earlier (passing input validation as a
  "valid" string, e.g. admin'--) fires when a later query concatenates it from
  the DB. Input-time validation can't see this; the dangerous data arrives "from
  our own database."
  FIX: parameterize EVERY query, including those reading stored data; trust by
  ORIGIN (a user) not LOCATION (the DB).

FLAW (v) — Stored procedure building dynamic SQL. (Ch.8)
  WHY: a proc that CONCATENATEs its parameters into an EXEC'd dynamic SQL string
  is just as injectable as app-side concatenation — the vuln moved into the DB.
  "We use stored procs" is not protection.
  FIX: inside the proc, use BOUND parameters (e.g. sp_executesql with params /
  parameterized prepare), or static parameterized statements — never concatenate
  inputs into dynamic SQL.

FLAW (vi) — Relies on a WAF. (Ch.8)
  WHY: a WAF blocks known PATTERNS and is bypassable (encoding, comments, case,
  novel payloads) and BLIND to second-order (the payload enters as innocuous
  data). The injectable code remains; any bypass = full exploitation.
  FIX: keep the WAF as a perimeter BACKSTOP, but fix the code with
  parameterized queries underneath.

CORRECTED POSTURE (summary):
  Parameterize all queries (id and everything else, including reads of stored
  data and inside the proc) + allowlist any dynamic identifiers + validate types
  for data quality + least-privilege DB account + generic errors + WAF as
  backstop. Every one of the six items is a textbook catalogue entry; the unifying
  fix is to stop concatenating and bind data as data.