Exercises

-- ============================================================
-- Drill 1 — Login bypass via UNION
-- A login query joins the password check with username concatenation.
-- ============================================================
-- VULNERABLE (PHP, do NOT ship)
<?php
$u = $_POST['username'];
$p = $_POST['password'];
$sql = "SELECT id FROM users WHERE username = '$u' AND password_hash = '$p'";
$row = $pdo->query($sql)->fetch();
?>
-- Attacker input:  username = admin' --     password = anything
-- Effective SQL:   SELECT id FROM users WHERE username = 'admin' --' AND password_hash = '...'

-- YOUR TASK: parameterise + use a real password hash
-- SOLUTION
<?php
$u = $_POST['username'];
$p = $_POST['password'];
$stmt = $pdo->prepare('SELECT id, password_hash FROM users WHERE username = :u');
$stmt->execute(['u' => $u]);
$user = $stmt->fetch();
if (!$user || !password_verify($p, $user['password_hash'])) {
    abort(401);
}
?>

-- ============================================================
-- Drill 2 — ORDER BY injection
-- A list endpoint sorts by a column name from the query string.
-- ============================================================
-- VULNERABLE (Node, pg)
const sortBy = req.query.sort ?? 'created_at';
const sql = \`SELECT * FROM products ORDER BY ${sortBy} DESC\`;
const rows = (await pool.query(sql)).rows;

-- Attack: sort = (SELECT password_hash FROM users LIMIT 1)
-- YOUR TASK: column names cannot be parameterised — whitelist them
-- SOLUTION
const COLS = { newest: 'created_at', price: 'price_cents', name: 'name' };
const col  = COLS[req.query.sort] ?? COLS.newest;
const rows = (await pool.query(\`SELECT * FROM products ORDER BY ${col} DESC\`)).rows;

-- ============================================================
-- Drill 3 — LIKE search with wildcards
-- A search box concatenates input into a LIKE pattern.
-- ============================================================
-- VULNERABLE (Python, psycopg)
def search(needle: str, conn):
    with conn.cursor() as cur:
        cur.execute("SELECT id FROM articles WHERE title LIKE '%" + needle + "%'")
        return cur.fetchall()

-- Attack: needle = ' OR '1'='1
-- YOUR TASK: parameterise the value AND escape LIKE wildcards in the input
-- SOLUTION
def search(needle: str, conn):
    safe = needle.replace('\\\\', '\\\\\\\\').replace('%', '\\\\%').replace('_', '\\\\_')
    with conn.cursor() as cur:
        cur.execute(
            "SELECT id FROM articles WHERE title LIKE %s ESCAPE '\\\\'",
            (f'%{safe}%',),
        )
        return cur.fetchall()

-- ============================================================
-- Drill 4 — Stored procedure with dynamic SQL
-- A 'helper' proc concatenates input into EXECUTE.
-- ============================================================
-- VULNERABLE (PL/pgSQL)
CREATE OR REPLACE FUNCTION get_user(uname text) RETURNS SETOF users
LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY EXECUTE
    'SELECT * FROM users WHERE username = ''' || uname || '''';
END;
$$;

-- YOUR TASK: parameterise inside the dynamic SQL with USING
-- SOLUTION
CREATE OR REPLACE FUNCTION get_user(uname text) RETURNS SETOF users
LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY EXECUTE
    'SELECT * FROM users WHERE username = $1'
    USING uname;
END;
$$;

-- ============================================================
-- Drill 5 — ORM raw escape hatch
-- A ticket counts query uses Eloquent's whereRaw with concatenation.
-- ============================================================
-- VULNERABLE (Laravel)
$status = request('status');
$count = Order::whereRaw("status = '$status'")->count();

-- YOUR TASK: keep the ORM, just bind the value safely
-- SOLUTION (any of these)
$count = Order::where('status', request('status'))->count();
$count = Order::whereRaw('status = ?', [request('status')])->count();

-- ============================================================
-- Bonus — what stops a leaked credential becoming a full-DB read?
-- ============================================================
-- ANSWER: a least-privileged DB user.
-- - The 'app' role gets SELECT/INSERT/UPDATE/DELETE on the tables it owns.
-- - It does NOT get GRANT, CREATE, or access to pg_user / information_schema.
-- - Read replicas use a different, even more restricted role.
-- A successful SQLi against a least-privileged role is contained to its scope.

-- Fill in: SELECT * FROM users WHERE id = ____;   -- placeholder, not concatenation