Parameterised Queries

// 1) Node — pg (PostgreSQL)
import { Pool } from 'pg';
const pool = new Pool();

const { rows } = await pool.query(
    'SELECT id, name FROM users WHERE email = $1 AND status = $2',
    [email, 'active'],
);

// NEVER do this:
// await pool.query(`SELECT * FROM users WHERE email = '${email}'`);   // INJECTION

// 2) Node — mysql2/promise
import mysql from 'mysql2/promise';
const conn = await mysql.createConnection({ host, user, password, database });
const [rows2] = await conn.execute(
    'SELECT id, name FROM users WHERE email = ? AND status = ?',
    [email, 'active'],
);

// 3) Python — psycopg
import psycopg
with psycopg.connect(dsn) as conn, conn.cursor() as cur:
    cur.execute(
        'SELECT id, name FROM users WHERE email = %s AND status = %s',
        (email, 'active'),
    )
    for row in cur:
        print(row)

# 4) Python — SQLAlchemy text + bound params
from sqlalchemy import create_engine, text
engine = create_engine(dsn)
with engine.connect() as conn:
    result = conn.execute(
        text('SELECT id, name FROM users WHERE email = :email AND status = :status'),
        {'email': email, 'status': 'active'},
    )

# 5) PHP — PDO
$pdo = new PDO('mysql:host=localhost;dbname=app', $user, $pass, [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES   => false,                   // <-- IMPORTANT
]);
$stmt = $pdo->prepare('SELECT id, name FROM users WHERE email = ? AND status = ?');
$stmt->execute([$email, 'active']);
foreach ($stmt as $row) { ... }

# 6) Java — JDBC PreparedStatement
try (PreparedStatement ps = conn.prepareStatement(
        "SELECT id, name FROM users WHERE email = ? AND status = ?")) {
    ps.setString(1, email);
    ps.setString(2, "active");
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) { ... }
    }
}

# 7) Go — database/sql
rows, err := db.QueryContext(ctx,
    `SELECT id, name FROM users WHERE email = $1 AND status = $2`,
    email, "active")

# 8) ORMs — already parameterised by default. The trap: raw SQL escape hatches.
// Prisma
await prisma.$queryRaw`SELECT * FROM users WHERE email = ${email}`;  // SAFE — tagged template
await prisma.$queryRawUnsafe(`SELECT * FROM users WHERE email = '${email}'`);  // UNSAFE

// Drizzle
await db.execute(sql`SELECT * FROM users WHERE email = ${email}`);   // SAFE

# 9) Stored procedures DO NOT save you
# A stored proc that builds dynamic SQL with EXEC + concatenation is just as exploitable.
# Parameterise INSIDE the proc, too.

# 10) When the placeholder can't help — identifiers (table/column names)
# Use an allowlist (see the allowlist lesson)
const SORTS = { id: 'id', created: 'created_at' };
const sort = SORTS[req.query.sort] ?? 'id';
await pool.query(`SELECT * FROM users ORDER BY ${sort} DESC LIMIT 100`);

# 11) Test for injection via static analysis
# - semgrep — has rules for raw SQL + user input
# - sqlmap — adversarial test in a staging env (NEVER in prod without permission)

# 12) Logging — never log the raw query with user input present
# log.info(`Running: ${sql}`)  → SIEM index, becomes a problem

// Node — node-postgres
await pg.query('SELECT * FROM users WHERE id = $1', [id]);
// Python — psycopg
cur.execute('SELECT * FROM users WHERE id = %s', (id,))
// PHP — PDO
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
$stmt->execute(['id' => $id]);
// Java — JDBC
PreparedStatement ps = c.prepareStatement("SELECT * FROM users WHERE id = ?");
ps.setLong(1, id);