Classic (in-band)

-- SCOPE: defensive learning on the bundled lab app. Authorised testing only.

-- ===== Why 'classic' =====
-- In-band SQLi: the response carries the result of the injected query.
-- Variants: union-based, error-based, stacked queries, boolean / numeric escapes.

-- ===== Pattern 1: numeric context =====
-- Vulnerable shape (DO NOT do this):
--   sql = 'SELECT * FROM products WHERE id = ' + req.params.id
-- If id = '1 OR 1=1' -> returns all rows.
--
-- Fix:
--   db.query('SELECT * FROM products WHERE id = $1', [Number(req.params.id)])

-- ===== Pattern 2: string context =====
-- Vulnerable:
--   sql = "SELECT id FROM users WHERE email = '" + email + "'"
-- Payload: ' OR '1'='1
--
-- Fix:
--   db.query('SELECT id FROM users WHERE email = $1', [email])

-- ===== Pattern 3: UNION-based =====
-- Used to read columns from other tables when the response shows rows.
-- Vulnerable code allows the response to grow with injected UNION SELECT clauses.
--
-- Fix: parameterise + return ONLY the columns the app needs (DTOs, not SELECT *).
--   Use a row mapper that strips unexpected columns.

-- ===== Pattern 4: error-based =====
-- The DB error leaks state. Vulnerable code echoes DB errors verbatim.
--
-- Fix:
--   - Generic 500 messages to the client
--   - Detailed errors only to internal logs
--   - Disable verbose DB errors in prod (Postgres: log_min_error_statement = error)

-- ===== Pattern 5: stacked queries =====
-- Some drivers / engines allow semicolons:
--   sql = 'SELECT 1; DROP TABLE users;'
--
-- Fix:
--   - Disable multi-statement support at the driver where possible
--   - Parameterised queries do not interpret semicolons in user data

-- ===== Pattern 6: ORDER BY / LIMIT / column names =====
-- Parameters bind VALUES, not identifiers. So ORDER BY is special.
-- Defence: ALLOWLIST.

const ALLOWED_SORT = new Set(['name', 'created_at', 'total_cents']);
const col = ALLOWED_SORT.has(req.query.sort) ? req.query.sort : 'created_at';
const sql = \`SELECT id FROM orders ORDER BY ${col} DESC LIMIT $1\`;
db.query(sql, [limit]);

-- ===== Detection rules (for SOC / EDR) =====
-- 1. Spike in SQLSTATE 42* (syntax) from a single IP / user
-- 2. Same route receiving wildly different query SHAPES
-- 3. Long URLs / body fields with quotes + UNION / AND / OR keywords
-- 4. Error rate increase isolated to one endpoint
-- 5. Unusual outbound DNS from DB host (out-of-band data exfil)

-- ===== Defence layers =====
-- 1. Parameterise EVERY query (the core)
-- 2. Least-privilege DB roles per service responsibility
-- 3. WAF rules on query string + body patterns (defence in depth)
-- 4. Logging shape: route + user_id + template + error_class
-- 5. Statement timeouts (prevents long blind SQLi probes)

-- Postgres:
ALTER ROLE app_auth SET statement_timeout = '2s';

-- MySQL:
SET SESSION MAX_EXECUTION_TIME = 2000;

-- ===== Recovery checklist if you find a real one =====
-- 1. Patch (parameterise) and deploy
-- 2. Audit logs for likely exploitation IPs + timestamps
-- 3. Rotate any credentials or tokens potentially exposed
-- 4. Add a regression test exercising the previously-bad input
-- 5. Communicate per your incident response plan

-- ===== Patterns to internalise =====
-- - Parameterise EVERY query; no exception is small
-- - Generic error messages to clients; detailed errors to logs
-- - Allowlist for any dynamic identifier (ORDER BY, column names)
-- - statement_timeout per service role to bound blast radius

-- ===== Pitfalls =====
-- - 'It is an integer' — still parameterise; never concatenate
-- - Multi-statement support enabled in driver because it was the default
-- - Echoing 'syntax error near X' to clients
-- - Allowing arbitrary sort columns from the URL

// In-band: result is visible in the page.
// Symptom: long error messages, password-less logins, weird ORDER BY.
// Fix: parameters, ORM, allow-list for identifiers.