Intro

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

-- ===== The mechanism =====
-- App builds a SQL string by concatenating user input.
-- The DB sees ONE string; it cannot tell which characters came from the user.

-- ===== The vulnerable shape (DO NOT do this) =====
-- pseudo:
--   sql = "SELECT * FROM users WHERE email = '" + email + "' AND pw = '" + pw + "'"
-- The user can break out of the string and add SQL syntax.

-- ===== The fix: parameterise =====
-- pseudo:
--   db.execute('SELECT id, name FROM users WHERE email = ? AND pw = ?', [email, pw])
-- The driver tells the DB which tokens are SQL and which are values. End of class.

-- Every driver has the same pattern:
--   pg (node):       pool.query(sql, params)
--   mysql2:          conn.execute(sql, params)
--   psycopg (py):    cur.execute(sql, (email, pw))
--   PHP PDO:         stmt->execute([':email' => email])
--   Java JDBC:       PreparedStatement.setString(1, email)
--   .NET ADO.NET:    cmd.Parameters.AddWithValue('@email', email)

-- ===== Classes (defensive taxonomy) =====
-- In-band:       result returned in the response (union-based, error-based)
-- Inferential:   no direct output; learn via timing or booleans
-- Out-of-band:   response comes via DNS / HTTP from the DB host

-- ===== Defence in depth =====
-- 1. Parameterise everything (the main control)
-- 2. Least-privilege DB roles (the auth role does not need DELETE on orders)
-- 3. WAF / API gateway as a backstop
-- 4. Logging shape (templates + error class) — not values

-- ===== ORM caveats =====
-- ORMs default to parameters but have escape hatches:
--   Prisma queryRaw, Sequelize raw, TypeORM .raw(), Hibernate native queries
-- Any string concatenation -> you opted out of the ORM's protection.

-- ===== Dynamic identifiers (ORDER BY) =====
-- Parameters bind VALUES, not identifiers. Allowlist column names:
const allowed = new Set(['name', 'created_at', 'total']);
const col = allowed.has(input) ? input : 'created_at';

-- ===== Detection checklist =====
-- - String concatenation into SQL (any language)
-- - Template literals with ${...} inside SQL
-- - .raw() / queryRaw / db.exec(stringWithInput)
-- - Dynamic ORDER BY / column from user input without allowlist

-- ===== Recovery if you find one =====
-- 1. Parameterise immediately
-- 2. Rotate any credentials the DB held in scope
-- 3. Audit logs for the timeframe (template + error_class spikes)
-- 4. Add a test that exercises the previously-bad input

-- ===== Patterns to internalise =====
-- - Parameterise every query, no exceptions
-- - Least-privilege roles per service responsibility
-- - Allowlist for dynamic identifiers
-- - Log SHAPES, not VALUES

-- ===== Pitfalls =====
-- - 'It is an internal tool' — internal tools still get phished
-- - 'We use an ORM' — yes, and people still call .raw()
-- - 'We escape input' — encoding bypasses happen yearly
-- - Trusting JSON path expressions stored in TEXT columns

// SQL Injection: untrusted input changes the SHAPE of a SQL query,
// not just its data. Almost always preventable with parameterised queries.