EXERCISE 3 — Prioritized SQLi hardening plan + the unifying principle
=====================================================================

APP: new app, relational DB, an ORM, a couple of raw reporting queries, a
search feature.

PRIORITIZED HARDENING PLAN (and why this order):

  1. PARAMETERIZE EVERYTHING — the foundation. (Ch.7)
     - Use the ORM's structured API for normal queries (binds automatically).
     - For the raw reporting queries, use BOUND parameters (?/$1/replacements),
       never concatenation or string-formatting (no f-strings/template
       literals).
     - The search feature: parameterize the value; handle LIKE wildcards (%,_)
       deliberately.
     - This is the load-bearing fix; do it first and everywhere. Without it,
       nothing else truly closes the bug.

  2. ALLOWLIST DYNAMIC IDENTIFIERS. (Ch.7)
     - The search/reporting features likely allow sorting/filtering by column.
       Identifiers can't be parameterized -> map user keys to a fixed allowlist
       of real column names; reject unknown keys. Never concatenate user text as
       a column/table/ORDER BY.

  3. COVER SECOND-ORDER PATHS. (Ch.6)
     - Ensure queries that read STORED data (not just request input) are also
       parameterized. Trust by origin, not location. Audit the reporting queries
       for re-use of stored values.

  4. AUDIT ORM RAW HATCHES + (if any NoSQL) VALIDATE TYPES. (Ch.9)
     - Grep for raw-query methods (sequelize.query, $queryRawUnsafe, whereRaw,
       DB::raw, find_by_sql, createNativeQuery) and confirm bindings. If any
       document store is used, reject objects where scalars belong; forbid
       $where.

  5. INPUT VALIDATION (allowlist). (Ch.8)
     - Enforce expected types/formats (id is a positive int, enums constrained).
       Defence in depth that shrinks the attack surface — not the fix.

  6. LEAST-PRIVILEGE DB ACCOUNTS. (Ch.8)
     - App connects with minimal rights; the reporting path uses a READ-ONLY
       account scoped to needed tables, no FILE/xp_cmdshell. Limits blast radius
       if something slips.

  7. GENERIC ERRORS + WAF BACKSTOP + TESTING. (Ch.3/8/10)
     - Show generic errors, log details server-side. Put a WAF at the perimeter
       as a backstop (not a fix). Then TEST: probe inputs, run sqlmap against
       your own app, check blind and second-order paths, review the raw queries.

WHY THIS ORDERING:
  Start with the measure that REMOVES the vulnerability (parameterization)
  across all query types, then handle the cases parameterization can't
  (identifiers -> allowlist) and the sneaky path it must also cover (second-
  order). Layer validation and least privilege to reduce likelihood and contain
  damage, with generic errors / WAF / testing as supporting layers. Each later
  layer assumes the foundation (parameterized queries) is in place.

THE ONE CORE PRINCIPLE, AND HOW IT UNIFIES THINGS:
  - PRINCIPLE: untrusted input must stay DATA and never become query CODE.
    Parameterization is the direct expression of it: send the query and the data
    on SEPARATE CHANNELS so the data is parsed as data, never as SQL.
  - UNIFIES SQLi WITH XSS: XSS is the SAME bug at a different target — untrusted
    input parsed as code by the BROWSER instead of the DATABASE. Its fix
    (context-aware OUTPUT ENCODING) is the same idea: keep data off the code
    channel. SQLi and XSS are the two big members of the INJECTION family;
    learn one, you understand the other (and command injection, LDAP, template,
    NoSQL operator injection — all the same mistake).
  - CONNECTS TO THE AUTH COURSE: SQLi is one of the ways AUTHENTICATION is
    defeated — the ' OR '1'='1 / admin'-- login bypass grants access with no
    credential, and write/stacked injection can create or elevate accounts or
    reset passwords. So securing queries is also part of securing
    authentication; the courses interlock (HTTPS in transit, Auth establishes
    identity, CSRF/XSS protect the session, SQLi protects the data layer behind
    it).

ONE-LINE TAKEAWAY:
  Parameterize every query first (foundation), allowlist identifiers, cover
  second-order and ORM/NoSQL hatches, then layer validation/least-privilege/WAF/
  testing — all in service of one rule shared with XSS: keep untrusted input as
  data, never code.