Summary

# ===== The root cause =====
# Concatenation of untrusted data into SQL.
# 'WHERE id = ' + input        -> bug
# 'WHERE id = ?'  bound          -> fix

# ===== The shapes =====
# - Classic:    WHERE clauses, login forms
# - Blind:      response differs by row count but content is hidden
# - Time-based: WAITFOR/sleep makes server response delay reveal data
# - Union:      attacker injects UNION SELECT to pull extra columns
# - Out-of-band: DNS/HTTP exfil from the SQL engine

# ===== The primary defence =====
# Parameterised queries EVERYWHERE.
# - PHP PDO:        prepare / execute with named or '?' placeholders
# - Node pg:        $1 / $2 placeholders + array of values
# - Python psycopg: %s placeholders + tuple of values
# - Java:           PreparedStatement + setX
# - C# Dapper:      parameterised string + anonymous object
# - Go:             db.Query with $N placeholders

# ===== Inputs that CANNOT be parameters =====
# - Table / column names
# - ORDER BY columns + direction
# - LIMIT in some drivers (most accept it now)
# Solution: WHITELIST. Map untrusted input through a fixed dictionary.
# const SORTS = { newest: 'created_at DESC', price: 'price_cents ASC' };
# const order = SORTS[req.query.sort] ?? SORTS.newest;

# ===== LIKE wildcards =====
# Escape the wildcards in user input BEFORE substituting:
# const safe = input.replace(/[%_]/g, c => '\\\\' + c);
# Then bind:  WHERE name LIKE $1
#             with '%' + safe + '%'

# ===== Stored procedures =====
# 'We use sprocs' is NOT a defence by itself.
# An sproc that concatenates input is just as vulnerable.
# Parameterise inside the sproc (EXECUTE ... USING / sp_executesql).

# ===== Defence in depth (secondary, do them too) =====
# 1) Least-privileged DB user
#    GRANT only what the app needs; deny CREATE/ALTER/DROP/COPY/TRUNCATE
#    Separate read-only role for reporting endpoints
# 2) WAF (ModSecurity OWASP CRS) in front of the app
#    Catches obvious payloads; never the primary defence
# 3) Query log monitoring
#    Alert on UNION/sleep/benchmark/information_schema patterns
# 4) Output sanitisation on PII
#    A SQLi that succeeds returns rows; minimise the surface
# 5) Logging of failed queries
#    Volume + 4xx auth-failures + DB errors all rise together when probed

# ===== Code review smell tests =====
# 1) Any raw-string SQL constructor: rawQuery, sequelize.query, DB::statement,
#    cur.execute with string concat, whereRaw
# 2) ORDER BY / LIMIT / table-name from user input WITHOUT a whitelist
# 3) Stored procs that do EXECUTE in plpgsql / sp_executesql
# 4) JSON injection via JSON_EXTRACT path concatenation
# 5) NoSQL-style $where clauses driven by user input

# ===== Detection signal =====
# - Spike in 500s from DB driver errors
# - 'syntax error near' in error tracker
# - Spike in egress bandwidth (data exfil)
# - WAF rule hits with the SQLi signatures
# - Anomalous queries to information_schema / pg_catalog / mysql.user

# ===== Realistic posture =====
# - All queries parameterised, enforced by lint (Semgrep)
# - Whitelists for non-parameterisable parts
# - Least-privileged DB role
# - WAF in front + query log alerts behind
# - Pentest annually + bug bounty
# - Tests in the suite that hit each list/search/login with canonical payloads

# ===== When you find a bug =====
# - Rotate the DB role password
# - Audit logs for the route since the bug landed
# - Notify legal if PII was exposed
# - Patch + ship a regression test that fails without the fix

-- Next: stored-proc injection, NoSQL ops injection, ORM .raw audit, supply-chain DBs.