EXERCISE 3 — Dynamic sort column (allowlist), and why parameterize > escape
============================================================================

THE PROBLEM: a sortable table, user picks the column via ?sort=  e.g.
  SELECT * FROM products ORDER BY <user choice>

WHY YOU CAN'T FIX THIS WITH A BOUND PARAMETER:
  - Placeholders bind VALUES — the data in WHERE x = ?, VALUES(?), SET col = ?.
    They do NOT bind IDENTIFIERS (table names, column names, ORDER BY columns,
    sort direction), because identifiers are part of the query STRUCTURE, which
    is fixed at PARSE time — before any value is bound.
  - So  ORDER BY ?  with sort bound as a value does NOT work: the driver would
    treat the bound value as a string literal, e.g. ORDER BY 'created_at'
    (ordering by a constant string, a no-op) — not as the column to sort by.
    The sort column simply cannot be a parameter.
  - And you must NOT concatenate the raw user value into the query
    (ORDER BY  + req.query.sort), because that's textbook injection in an
    identifier position (e.g. sort = "(CASE WHEN ... THEN id ELSE name END)" or
    worse).

THE CORRECT ALLOWLIST APPROACH:
  Map the user's input (a KEY they choose) to a fixed set of KNOWN-GOOD
  identifiers that YOU control; never use the user's literal text as the
  identifier.

    const SORT_COLUMNS = { name: 'name', date: 'created_at', price: 'price' };
    const col = SORT_COLUMNS[req.query.sort] ?? 'name';     // default if unknown

    const DIRS = { asc: 'ASC', desc: 'DESC' };
    const dir = DIRS[req.query.dir] ?? 'ASC';

    // col and dir are now from OUR allowlist, not user text:
    const sql = `SELECT * FROM products ORDER BY ${col} ${dir}`;
    db.query(sql);  // safe: col/dir can only be values we defined

  - The user picks a KEY ("date"); we look up the actual column ("created_at").
    Any unknown/malicious key falls through to the default. The user's raw
    string never reaches the SQL — only one of our pre-approved identifiers
    does. This is the one place dynamic SQL is unavoidable, and the rule is
    strict: IDENTIFIERS from your allowlist, VALUES from parameters.

WHY PARAMETERIZATION IS PREFERRED OVER ESCAPING:
  - NUMERIC CONTEXT: escaping is about neutralizing quotes, but numeric inputs
    (WHERE id = 5) aren't quoted — there's no quote to escape, so escaping does
    nothing while  5 OR 1=1  injects freely. Parameterization binds the value
    regardless of context (it would reject "5 OR 1=1" as a non-integer / treat
    it as a literal), so it covers numeric AND string contexts uniformly.
  - MULTI-BYTE / CHARSET BYPASSES: manual escaping is charset-specific and has
    been bypassed via multi-byte encoding tricks (e.g. GBK), where a crafted
    byte sequence combines with the escaping backslash to PRODUCE a valid quote
    the parser sees as a string terminator. Escaping logic that doesn't
    perfectly match the connection charset can be defeated. Parameterization
    has no such fragility — the value never becomes part of the SQL text, so
    there's no escaping to bypass.
  - EASY TO FORGET / INCONSISTENT: escaping must be applied correctly to EVERY
    input on EVERY query; one missed call is an injection. Parameterized APIs
    make the safe path the natural path.
  - CORRUPTS DATA: escaping changes the stored/used value (doubling quotes,
    adding backslashes) and can mangle legitimate input. Binding keeps the data
    intact.
  - CONCLUSION: PREFER parameterized queries everywhere. Treat escaping (via a
    vetted, charset-correct library function) only as a LAST RESORT for the
    rare spot you genuinely cannot parameterize (and identifiers, which neither
    fixes — use an allowlist there).

ONE-LINE TAKEAWAY:
  Sort columns are identifiers, so bind won't help — map user keys to an
  allowlist of real columns; and prefer parameterization over escaping because
  escaping fails on numeric context, breaks on charset tricks, and is easy to
  miss.