Blind SQLi

# Boolean-based blind SQLi — attacker observes app behaviour for true/false

# Vulnerable code (Node, pg) — never write this
const sql = `SELECT id FROM users WHERE username = '${req.query.user}'`;
const { rows } = await db.query(sql);
if (rows.length) return res.send('user exists');
else return res.status(404).end();

# Attacker probes:
#   ?user=ada' AND SUBSTRING(password, 1, 1) = 'a' --      → 200 if first char is 'a'
#   ?user=ada' AND SUBSTRING(password, 1, 1) = 'b' --      → 404
# Binary-search through every character. Hundreds of requests; full hash extracted.

# Defence — parameterised + scoped query (impossible to inject)
const { rows } = await db.query(
    'SELECT id FROM users WHERE username = $1',
    [req.query.user],
);

# === Time-based blind SQLi ===
# When the app doesn't even leak success/failure in HTTP status — attacker uses
# database SLEEP to infer truth via response latency.

# Vulnerable:
const sql = `SELECT count(*) FROM products WHERE category = '${req.query.cat}'`;

# Attacker probes:
#   ?cat=x' OR (SELECT pg_sleep(5) FROM users WHERE id=1 AND substr(password,1,1)='a') IS NULL --
# If the page takes 5s, the condition was true.

# Server-side detections you should have:
#   - WAF rule: queries with sleep/pg_sleep/benchmark in URLs
#   - DB statement timeout (Postgres: SET statement_timeout = '5s')
#   - Per-IP rate limit on endpoints that hit the DB
#   - Anomaly monitor on query latency

# === Out-of-band exfiltration ===
# When neither boolean nor time channel is available, attackers may use DNS / HTTP from inside the DB:
#   xp_dirtree (MSSQL), UTL_HTTP (Oracle), copy ... program (PG with superuser)
# Defences:
#   - DB user has ZERO network egress permissions (no superuser, no copy program)
#   - Egress firewall — DB host can't reach the internet

# === Real defence stack ===

# 1) Parameterise — every query, every driver
await db.query('SELECT * FROM users WHERE username = $1 AND active = true', [user]);

# 2) Allowlist identifiers (table / column / sort)
const SORT = { id: 'id', date: 'created_at', total: 'total' };
const sort = SORT[req.query.sort] ?? 'created_at';
await db.query(`SELECT * FROM orders ORDER BY ${sort} DESC LIMIT 50`);

# 3) Least privilege
# - App user can SELECT/INSERT/UPDATE/DELETE only the schemas it needs
# - No CREATE TABLE, DROP, GRANT, COPY FROM PROGRAM
# - Use connection pooling (PgBouncer) — limits blast radius if creds leak

# 4) Statement timeout
# Postgres: SET statement_timeout = '5s';
# MySQL:    SET SESSION MAX_EXECUTION_TIME=5000;  (ms)

# 5) Generic 404s — don't leak existence
# Consistent 404 whether the row exists or not (after auth fails) makes blind boolean SQLi require more probes.

# 6) Logging + alerting
# - Log every query with parameters; alert on outliers (unusual table access, abnormal duration)
# - SIEM rule: many 4xx from same IP, or many requests with sleep/benchmark patterns
# - DB audit: pg_audit / Datadog DB monitoring

# 7) Web Application Firewall (WAF)
# - Cloudflare / AWS WAF / Imperva — generic SQLi patterns blocked at the edge
# - NOT a substitute for parameterisation; defence-in-depth
# - Pair with bot detection (Cloudflare Turnstile, hCaptcha) for high-value endpoints

# 8) ORMs reduce surface area
# Type-safe query builders make string-built SQL the rare exception.
# Lint rules ban raw query methods ($queryRawUnsafe, FromSqlRaw, find_by_sql).

# 9) Pre-prod testing
# - sqlmap, dotdotpwn — automated SQLi scanners (use ONLY against authorised targets)
# - Burp Suite extensions: Backslash Powered Scanner, SQLiPy
# - DAST in CI: OWASP ZAP, Nuclei templates for SQLi

# 10) Code review checklist
# Look for: string concatenation in queries, template literals with user data, raw query methods
# In review: 'Is every user-controlled value a placeholder?' If no → reject.

# 11) Defence-in-depth wins
# Even with parameterised queries everywhere:
# - WAF stops dumb scanners (saves logs + CPU)
# - Least-privilege limits damage if a NEW bug ships
# - Statement timeout limits time-based exfiltration window
# - Egress firewall stops out-of-band exfiltration
# - Audit logs let you reconstruct an incident

# 12) Audit your existing code
rg -t js -e 'query\(\s*`' -e 'query\(\s*"' src/
rg -t py -e 'execute\([^,)]+%' -e 'f"SELECT' app/
# Find candidates; verify each is either safe (parameterised) or fix it.

// No data in the response — attacker infers by status / content / timing.
// Same defence: parameterise. Hide raw DB errors from users.