SQL Injection Probing Methodology for Analysts

This is a sink lab, not a payload cookbook. Target is a 25-line Flask app with two endpoints that look identical on the wire: /users_bad concatenates, /users_ok binds a parameter. Goal: show the SQL log difference, then show how a WAF 403 is not the same signal. I do not dump a production database. The “exfil” in this notebook is a single lab row alice that I inserted myself.

1Figure 1. Same HTTP parameter, two query shapes. Only one is a sink.
2GET /users_bad?q=  --concat-->  SQL parser  --> 500 syntax error
3GET /users_ok?q=   --bind  ?-->  SQL parser  --> 200 empty/row

Lab layout

1labs/sqli_lab/
2  app.py          # Flask, sqlite3, two routes
3  seed.sql        # three users, no PII
4  waf-noise.log   # CRS alerts from the same probes

 1# app.py — toy, binds 127.0.0.1, SQLite file in /tmp
 2import sqlite3
 3from flask import Flask, request, abort
 4
 5app = Flask(__name__)
 6DB = "/tmp/sqli_lab.db"
 7
 8def db():
 9    c = sqlite3.connect(DB)
10    c.row_factory = sqlite3.Row
11    return c
12
13@app.get("/users_bad")
14def users_bad():
15    q = request.args.get("q", "")
16    sql = "SELECT id, name FROM users WHERE name = '%s'" % q   # sink
17    print("SQL", sql)
18    try:
19        rows = db().execute(sql).fetchall()
20    except sqlite3.Error as e:
21        print("SQL_ERR", e)
22        abort(500)
23    return {"rows": [dict(r) for r in rows]}
24
25@app.get("/users_ok")
26def users_ok():
27    q = request.args.get("q", "")
28    sql = "SELECT id, name FROM users WHERE name = ?"
29    print("SQL", sql, "param", q[:32])
30    rows = db().execute(sql, (q,)).fetchall()
31    return {"rows": [dict(r) for r in rows]}

Seed, not a dump of anything real:

1CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, note TEXT);
2INSERT INTO users(name, note) VALUES
3  ('alice', 'lab'),
4  ('bob',   'lab'),
5  ('carol', 'lab');

Analysis step 0: name the sink

Before any quote character hits the wire I write down:

Parameter q on GET /users_*.
Feature: lookup by exact name.
Likely SQL: WHERE name = ... (equality, not LIKE, not ORDER BY).
Who answers: Flask, or nginx in front, or a WAF.

If I cannot name those four, I do not probe. Random ' against an unknown layer produces tickets, not evidence.

Artifact: concat vs bind, same HTTP

Benign lookup:

1curl -s 'http://127.0.0.1:5000/users_bad?q=alice'
2# {"rows":[{"id":1,"name":"alice"}]}
3curl -s 'http://127.0.0.1:5000/users_ok?q=alice'
4# {"rows":[{"id":1,"name":"alice"}]}

SQL log (Flask stdout):

1SQL SELECT id, name FROM users WHERE name = 'alice'
2SQL SELECT id, name FROM users WHERE name = ?  param alice

Now a single quote, which is a syntax probe, not a dump:

1curl -sD - 'http://127.0.0.1:5000/users_bad?q=alice%27'
2# HTTP/1.0 500 INTERNAL SERVER ERROR
3curl -sD - 'http://127.0.0.1:5000/users_ok?q=alice%27'
4# HTTP/1.0 200 OK
5# {"rows":[]}

1SQL SELECT id, name FROM users WHERE name = 'alice''
2SQL_ERR near "alice": syntax error
3
4SQL SELECT id, name FROM users WHERE name = ?  param alice'

That pair is the methodology:

500 + syntax error in the app SQL log — the parameter reached a SQL parser unescaped. Confirmed sink.
200 + empty rows + bound ? — the quote is data. Not a sink.

I do not need a UNION SELECT to file this. I need the SQL log.

WAF noise is a different layer

Same quote through a lab nginx + ModSecurity CRS in front of /users_ok (the safe endpoint):

 1# /var/log/modsec_audit.log  (lab)
 2--a1b2--
 3GET /users_ok?q=alice%27
 4Host: 127.0.0.1
 5--a1b2--
 6Action: Intercepted (phase 2)
 7--a1b2--
 8id "942100"  msg "SQL Injection Attack Detected via libinjection"
 9id "942110"  msg "SQL Injection Attack: Common Injection Testing Detected"
10client: 127.0.0.1  unique_id: [REDACTED]
11HTTP/1.1 403 Forbidden

The origin SQL log is silent — nginx never proxied. A 403 with CRS 942100 on a parameterized endpoint is WAF noise relative to the sink question. It is still a signal that someone probed. It is not evidence the database parsed attacker SQL.

Triage table I keep on the ticket:

Observation	Layer	Sink?
500, `SQL_ERR syntax error`	origin DB	yes
200, empty, SQL shows `?`	origin DB	no
403, CRS 942xxx, no origin log	WAF	unknown
400, JSON schema, no SQL log	gateway	no

Sanitized reproduction (error / crash only)

The 500 above is the repro. I also planted a second bug so there is a crash artifact: the concat path will pass whatever string I give it, including a long name. SQLite does not ASAN; I rebuilt a tiny C helper that runs the same concat for the crash dump.

 1/* qcrash.c — mirrors the Python concat, lab only */
 2#include <stdio.h>
 3#include <string.h>
 4#include <sqlite3.h>
 5
 6int main(int argc, char **argv) {
 7    char sql[64];
 8    sqlite3 *db;
 9    sqlite3_open("/tmp/sqli_lab.db", &db);
10    snprintf(sql, sizeof(sql),
11             "SELECT id, name FROM users WHERE name = '%s'", argv[1]);
12    puts(sql);
13    sqlite3_exec(db, sql, NULL, NULL, NULL);
14    return 0;
15}

 1$ clang -fsanitize=address -g -o qcrash qcrash.c -lsqlite3
 2$ ./qcrash alice
 3SELECT id, name FROM users WHERE name = 'alice'
 4
 5$ ./qcrash $(python3 -c 'print("A"*80)')
 6=================================================================
 7==9012==ERROR: AddressSanitizer: stack-buffer-overflow on address 0x[REDACTED]
 8WRITE of size 81 at 0x[REDACTED] thread T0
 9    #0 snprintf
10    #1 main /labs/sqli_lab/qcrash.c:11
11HINT: this is a lab overflow in the SQL *builder*, not a database dump

Two findings, two files:

SQLi sink: Python % concat, evidenced by SQL_ERR syntax error.
Unbounded snprintf in the C helper (I will not ship that helper). The ASAN stack-buffer-overflow is the crash I want; it is not “dump users”.

I never print note or any column that is not id, name. The SELECT list in both routes is the sanitization.

If the 500 is hidden (custom error page) I am allowed one differential:

1# equality that matches vs one that does not — same length, same charset
2curl -s -o /dev/null -w '%{http_code} %{size_download}\n' \
3  'http://127.0.0.1:5000/users_bad?q=alice'
4# 200 36
5curl -s -o /dev/null -w '%{http_code} %{size_download}\n' \
6  'http://127.0.0.1:5000/users_bad?q=alizz'
7# 200 11

Body-length differential on the concat route, none on /users_ok for the same two values (both empty or both one-row depending on data). That is enough to say “the parameter influences the result set”. It is not a license to extract other rows. I stop.

I do not use time-based probes against shared databases. Sleep payloads are hostile to production.

Mitigation

1# the fix is the /users_ok shape
2rows = db().execute(
3    "SELECT id, name FROM users WHERE name = ?",
4    (q,),
5).fetchall()

Review ticks:

No %, +, or f-string into SQL. ? / %s as a bound placeholder, not as Python format.
ORM: watch .extra(), order_by(raw), text().
Errors: do not return sqlite messages to the client. Lab 500 leaked syntax error on purpose; production maps it to a generic 400 and logs server-side.
WAF: keep CRS, but do not close a sink ticket because 403 fired. Close it when the SQL log shows a bind.

What I file after this lab

App: Flask + SQLite, two routes, three seed rows (alice/bob/carol)
/users_bad?q=alice' → 500, SQL_ERR near "alice": syntax error → confirmed concat sink
/users_ok?q=alice' → 200, param alice', zero rows → bound, safe
CRS 942100 on the safe route → WAF noise, origin silent
C helper ASAN stack-buffer-overflow on 80-byte name → separate builder bug
Fix: parameterized query; stop returning DB errors; do not SELECT extra columns

Commands appendix

1sqlite3 /tmp/sqli_lab.db < seed.sql
2flask --app app.py run -h 127.0.0.1 -p 5000
3curl -sD - 'http://127.0.0.1:5000/users_bad?q=alice%27'
4curl -sD - 'http://127.0.0.1:5000/users_ok?q=alice%27'
5clang -fsanitize=address -g -o qcrash qcrash.c -lsqlite3

Lab layout#

Analysis step 0: name the sink#

Artifact: concat vs bind, same HTTP#

WAF noise is a different layer#

Sanitized reproduction (error / crash only)#

Blind / timing: what I allow myself#

Mitigation#

What I file after this lab#

Commands appendix#