Bootcamp

# ===== Objectives =====
# 1. Audit a single feature end-to-end for SQL injection
# 2. Fix every bug found by parameterising correctly
# 3. Ship a least-privileged DB role so future bugs are contained

# ===== 0-5 min: pick the target =====
# Choose ONE auth + UGC feature. Search, login, user profile edit, admin tools.
# Auth + UGC + dynamic ORDER BY / search is the highest-yield combo.

# ===== 5-25 min: find the bugs =====

# 1) Raw string concatenation building SQL
git grep -nE 'query\("SELECT.*$\{?|query\("SELECT.*\+\s*req'
git grep -nE 'whereRaw\(.*\$|whereRaw\(.*\+'      # Laravel
git grep -nE 'sequelize\.query\(.*\$\{|sequelize\.query\(.*\+'   # Node
git grep -nE 'cur\.execute\(.*\+'                  # Python psycopg
git grep -nE 'EXECUTE\s*\(.*\|\|.*'                # PL/pgSQL dynamic SQL

# 2) Dynamic identifiers — ORDER BY, LIMIT, table/column names from user input
git grep -nE 'order_by.*query.*sort'
git grep -nE 'ORDER BY .*\$\{'

# 3) LIKE patterns built from input without escaping wildcards
git grep -nE 'LIKE.*%.*\+|LIKE.*%.*\$\{'

# 4) Raw escape hatches in ORMs
git grep -nE 'whereRaw|selectRaw|DB::statement|sequelize.query|raw_query'

# 5) Stored procs that EXECUTE concatenated SQL
git grep -nE 'EXECUTE.*\|\|'              # PostgreSQL plpgsql

# For each hit, ask:
# - Where does this value come from?
# - Is the chain fully parameterised (placeholders all the way)?
# - If it's an identifier (column / direction), is it whitelisted?

# ===== 25-45 min: fix the bugs =====

# PHP (PDO)
# BEFORE: "SELECT id FROM users WHERE email = '$email'"
# AFTER:
$stmt = $pdo->prepare('SELECT id FROM users WHERE email = ?');
$stmt->execute([$email]);

# Node (pg)
# BEFORE: pool.query(\`SELECT id FROM users WHERE email = '${email}'\`)
# AFTER:  pool.query('SELECT id FROM users WHERE email = $1', [email])

# Python (psycopg)
# BEFORE: cur.execute("SELECT id FROM users WHERE email = '" + email + "'")
# AFTER:  cur.execute('SELECT id FROM users WHERE email = %s', (email,))

# Whitelist for ORDER BY
const COLS = { newest: 'created_at', price: 'price_cents', name: 'name' };
const col  = COLS[req.query.sort] ?? COLS.newest;
const sql  = \`SELECT * FROM products ORDER BY ${col} DESC\`;

# Escape LIKE wildcards
def search(needle: str, conn):
    safe = needle.replace('\\\\', '\\\\\\\\').replace('%', '\\\\%').replace('_', '\\\\_')
    cur.execute("SELECT id FROM articles WHERE title LIKE %s ESCAPE '\\\\'",
                (f'%{safe}%',))

# ===== 45-55 min: ship a least-privileged DB role =====
# Even with a successful SQLi, blast radius drops dramatically.

# PostgreSQL
CREATE ROLE app_user LOGIN PASSWORD 'strong-secret';
GRANT CONNECT ON DATABASE shop TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
-- DO NOT grant: CREATE, ALTER, DROP, TRUNCATE, COPY, BYPASSRLS, SUPERUSER

# A separate read-only role for reporting / analytics
CREATE ROLE app_readonly LOGIN PASSWORD 'strong-secret';
GRANT CONNECT ON DATABASE shop TO app_readonly;
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

# Point read-only endpoints (search, analytics) at app_readonly; SQLi against
# the search endpoint can never write.

# ===== 55-60 min: lock the bug class out =====
# - Add a semgrep rule for raw-SQL escape hatches (whereRaw, sequelize.query, etc.)
# - Fail CI on any new file that imports a raw-query API without a comment justifying it
# - Add 1-2 SQLi tests to every list / search / login endpoint

# ===== Post-bootcamp =====
# - Document the new DB roles in the runbook
# - Rotate the production app role password and re-deploy
# - Open issues for any sinks left for a follow-up branch

-- 30-day SQLi defence bootcamp in the lesson body.