Examples

-- ============================================================
-- Example 1 — PHP (PDO)
-- A search-by-email handler concatenates input into the query.
-- ============================================================

-- VULNERABLE
<?php
$email = $_GET['email'] ?? '';
$sql = "SELECT id, name FROM users WHERE email = '$email'";
$rows = $pdo->query($sql)->fetchAll();
?>

-- FIXED — parameterised with named placeholders
<?php
$email = $_GET['email'] ?? '';
$stmt = $pdo->prepare('SELECT id, name FROM users WHERE email = :email');
$stmt->execute(['email' => $email]);
$rows = $stmt->fetchAll();
?>

-- ============================================================
-- Example 2 — Node.js (pg)
-- An ORDER BY built from the client breaks parameterisation.
-- ============================================================

// VULNERABLE — both the WHERE and the ORDER BY are concatenated
const { Pool } = require('pg');
const pool = new Pool();

async function listProducts(category, sort) {
  const sql = \`SELECT * FROM products WHERE category = '${category}'
               ORDER BY ${sort} DESC\`;
  return (await pool.query(sql)).rows;
}

// FIXED — parameterise the value; whitelist the column name
const SORT_COLS = { newest: 'created_at', price: 'price_cents', name: 'name' };
async function listProductsSafe(category, sort) {
  const col = SORT_COLS[sort] ?? SORT_COLS.newest;
  const { rows } = await pool.query(
    \`SELECT * FROM products WHERE category = $1 ORDER BY ${col} DESC\`,
    [category],
  );
  return rows;
}

-- ============================================================
-- Example 3 — Python (psycopg)
-- A LIKE-search builds the pattern by string concatenation.
-- ============================================================

# VULNERABLE
import psycopg
def search(needle: str, conn):
    with conn.cursor() as cur:
        cur.execute("SELECT id, title FROM articles WHERE title LIKE '%" + needle + "%'")
        return cur.fetchall()

# FIXED — bind the pattern as a parameter; escape SQL wildcards in input
def search_safe(needle: str, conn):
    safe = needle.replace('\\\\', '\\\\\\\\').replace('%', '\\\\%').replace('_', '\\\\_')
    with conn.cursor() as cur:
        cur.execute(
            "SELECT id, title FROM articles WHERE title LIKE %s ESCAPE '\\\\'",
            (f'%{safe}%',),
        )
        return cur.fetchall()

-- ============================================================
-- Bonus — ORM pitfall: raw escape hatches
-- The framework is safe; the raw escape hatch is where bugs live.
-- ============================================================

# Eloquent (Laravel) — UNSAFE
User::whereRaw("email = '$email'")->get();

# Eloquent — SAFE
User::where('email', $email)->get();
User::whereRaw('email = ?', [$email])->get();

# Sequelize — UNSAFE
sequelize.query("SELECT * FROM users WHERE id = " + req.params.id);

# Sequelize — SAFE
sequelize.query('SELECT * FROM users WHERE id = :id', {
  replacements: { id: req.params.id }, type: QueryTypes.SELECT
});

-- Migrate one query at a time from concatenation to parameter binding.
-- Add tests that POST a quote ' and assert a 4xx, not a 500.