SQLi Testing

// SCENARIO — authorised SQLi testing of YOUR own staging app.
// All payloads are DEFENSIVE knowledge: how to detect, how to verify, how to report.
// Stay inside the RoE; document everything; never use real customer data.

// ─── 1) Methodology overview ─────────────────────────────────
//
//   1. Reconnaissance — list inputs that reach SQL (URL params, body, headers, cookies)
//   2. Detection      — confirm SQLi exists (error, boolean, time, union)
//   3. Confirmation   — DB type + version (Postgres, MySQL, MSSQL, Oracle, SQLite)
//   4. Impact assessment — data accessible? RCE / file write possible?
//   5. Documentation   — minimum repro, screenshots, log evidence
//   6. Reporting       — risk rating, CWE-89, fix mapping
//   7. Verification     — re-test after fix
//
//   STOP if you find evidence of customer data leakage you weren't expecting — report and pause.

// ─── 2) Inputs that reach SQL ────────────────────────────────
//
//   • URL path segments        /products/{id}
//   • Query parameters         ?status=paid
//   • Form body fields
//   • JSON keys + values (incl. nested)
//   • Cookies (session, prefs)
//   • Custom headers (X-User-Id, etc.)
//   • XML / GraphQL inputs
//   • Filenames, search terms, sort keys
//
// Map them all FIRST. Each one needs testing.

// ─── 3) Detection — boolean-based ─────────────────────────────
//
// Compare responses for two payloads:
//   ?id=1 AND 1=1     → normal response
//   ?id=1 AND 1=2     → different response (empty, error, or different content length)
//
// If responses differ, the input flows into a SQL query without parameterisation.
//
// curl example (authorised staging):
//   curl -s 'https://staging/api/products?id=1%20AND%201=1' | wc -c
//   curl -s 'https://staging/api/products?id=1%20AND%201=2' | wc -c
//
// Different byte counts → boolean SQLi likely.

// ─── 4) Detection — error-based ─────────────────────────────
//
// Some apps leak DB errors directly:
//   ?id=1'
//   → 'syntax error at or near' (Postgres)
//   → 'You have an error in your SQL syntax' (MySQL)
//
// Errors confirm SQLi + reveal DB type.
//
// In production: errors SHOULD be suppressed. If you see them, it's both a SQLi AND error-disclosure bug.

// ─── 5) Detection — time-based ──────────────────────────────
//
// When response content doesn't reveal anything, force the DB to SLEEP:
//   Postgres:  ?id=1; SELECT pg_sleep(5)--
//   MySQL:     ?id=1 AND SLEEP(5)--
//   MSSQL:     ?id=1; WAITFOR DELAY '0:0:5'--
//
// Compare response time: 5+ seconds = injection present.
// Time-based is the LAST resort — slow + noisy.
//
// CAUTION on staging shared with others: sleep injections SLOW down everyone's tests. Coordinate.

// ─── 6) Detection — union-based ─────────────────────────────
//
// If the query returns columns, UNION lets you read other data:
//   ?id=1 UNION SELECT NULL, NULL, NULL--
//
// Adjust NULL count until no error. Then probe for the DB version:
//   Postgres: ?id=1 UNION SELECT version(), NULL, NULL--
//   MySQL:    ?id=1 UNION SELECT @@version, NULL, NULL--
//
// Only do this in an environment where you're CLEARED to read system info.

// ─── 7) Stored / second-order SQLi ──────────────────────────
//
// Insert a payload via one endpoint (signup, profile edit), then trigger SQL elsewhere.
//
// Test:
//   1. Update display name to 'a'); DROP TABLE…
//   2. Visit a page that includes the name in SQL
//   3. Observe error / data leak
//
// More common when codebase has mixed parameterised + concat patterns.

// ─── 8) Authenticated vs unauthenticated testing ──────────
//
// Test BOTH:
//   • Anonymous user — public endpoints first
//   • Authenticated user — many SQLi bugs are behind auth
//   • Admin user — high-impact bugs often only reachable as admin
//
// Use the test accounts in your RoE. NEVER attempt with real user credentials.

// ─── 9) Tooling — sqlmap (with consent) ─────────────────────
//
// sqlmap -u 'https://staging/api/products?id=1' --batch --risk=2 --level=3
// sqlmap -r request.txt --batch                          # from a Burp request file
// sqlmap --cookie='session=xyz' --identify-waf            # detect WAF before scanning
// sqlmap --crawl=2 -u 'https://staging' --batch          # crawl + scan
//
// IMPORTANT: never run sqlmap against production unless explicitly authorised in writing,
// with a maintenance window, and isolated to a test tenant.
//
// --dump and --os-shell are DESTRUCTIVE; only use on isolated test data.

// ─── 10) Burp Suite — interactive testing ────────────────────
//
// • Intruder for parameter fuzzing with payload lists
// • Repeater for iterating payloads with response diff
// • Logger / Logger++ to capture every probe
// • Active Scan to enumerate likely issues
//
// Burp's manual workflow gives much more context than sqlmap for tricky bugs.

// ─── 11) Impact assessment ───────────────────────────────────
//
//   • Can I read DATA? (information schema, tables, columns)
//   • Can I read PASSWORD HASHES? (very high severity)
//   • Can I MODIFY data? (UPDATE / INSERT / DELETE via stacked queries)
//   • Can I DROP tables? (DROP via stacked queries)
//   • Can I read SYSTEM files? (LOAD_FILE in MySQL, COPY in Postgres if perms)
//   • Can I write to OS? (LOCK TABLES, DBMS specifics)
//   • Can I RCE? (UDF in MySQL, COPY ... PROGRAM in Postgres)
//
// Higher impact = higher severity. Critical if data exfil or RCE possible.

// ─── 12) Evidence to capture ─────────────────────────────────
//
//   • Exact endpoint (method + path + parameter)
//   • Payload + response (truncated, redacted)
//   • Time-based timing measurements
//   • DB type + version (if detectable)
//   • Scope of data accessible (1 record, all, system tables)
//   • Repro steps engineers can execute
//   • Severity + CWE-89 mapping + OWASP A03:2021

// ─── 13) Reporting template ──────────────────────────────────
//
//   Title:       SQL Injection in /api/products?id
//   Severity:    Critical
//   Endpoint:    GET /api/products?id
//   Vulnerability: SQL injection via 'id' parameter
//   Repro:
//     1. curl 'https://staging.example.com/api/products?id=1%20AND%201=1' (HTTP 200, 4321 bytes)
//     2. curl 'https://staging.example.com/api/products?id=1%20AND%201=2' (HTTP 200, 0 bytes)
//     Boolean SQLi confirmed.
//   Impact:     Tested with --batch sqlmap: extracted public.products schema. Did NOT proceed to user data.
//   Recommendation: Switch raw query 'SELECT * FROM products WHERE id = ' + req.params.id
//                   to parameterised query: 'SELECT * FROM products WHERE id = $1'.
//                   See file src/controllers/products.ts:42.
//   CWE:        CWE-89
//   Status:     Reported, awaiting fix

// ─── 14) Verification after fix ──────────────────────────────
//
// Re-run the same payloads. Expect:
//   • Identical responses for AND 1=1 and AND 1=2
//   • No DB errors from quotes
//   • No time deltas on pg_sleep / SLEEP
//   • sqlmap reports 'no parameters appear to be injectable'
//
// If still vulnerable: re-open ticket with updated repro.

// ─── 15) RoE essentials ──────────────────────────────────────
//
//   • Written authorisation BEFORE testing
//   • In-scope endpoints + accounts ONLY
//   • Stop conditions: noticeable load, data leakage, customer impact
//   • Use SYNTHETIC accounts and data only
//   • Time windows in non-business hours where possible
//   • Communication channel for escalation
//   • Post-engagement: securely delete evidence

// ─── 16) Common mistakes to avoid ────────────────────────────
//
//   • Running sqlmap against production without authorisation
//   • Using --dump on real user data
//   • Time-based scans causing collateral slowdown
//   • Sharing raw payload output that contains PII
//   • Skipping authenticated endpoints (many bugs hide there)
//   • Reporting 'sqlmap says vulnerable' without manual verification
//   • Missing CORS / WAF context — WAF + origin direct testing both needed
//   • Bypassing the WAF and reporting no bugs — origin still vulnerable
//   • Reporting a SQLi without a clear fix recommendation

# Detect SQLi safely on AUTHORISED apps:
#   - Add a single quote to a parameter; watch for 500s / weird errors
#   - Try time-based: ' OR pg_sleep(2)--  (response 2s later = signal)
# Tool: sqlmap (only with permission), point at single endpoint, narrow scope.
# Defence: parameterised queries (see the SQLi track).