Detection Architecture: Where to Look and What to See

You've spent the last four pages learning how attackers think — string concatenation, UNION stacking, time-based blind probes, sqlmap's relentless automation. Now flip the table. You're the one watching ShopBox (192.0.2.10) from the outside, trying to spot that same work happening against you. The question isn't whether you can detect SQL injection. It's whether you can detect your SQL injection — the one shaped for your schema, your query patterns, your noise floor.

This page is a decision tree. Start at the root with whatever alert woke you up, follow the branches, and end at a concrete action with a concrete ShopBox example. I've ordered these by detection layer, but the real skill is knowing when one layer fails and another catches what you missed.

Root Question: I have an alert. What kind?

Alert Source	First Question	If Yes	If No
Network IDS/IPS (Suricata, Snort)	Is the payload in the HTTP request body or URL?	→ Branch A: Signature match on known pattern	→ Branch B: Encoded, fragmented, or in a header? Check HTTP keywords
Web Application Firewall	Did the WAF block it, or just log it?	→ Branch C: Blocked — verify no bypass	→ Branch D: Logged only — immediate active response
Application logs (ShopBox Apache/nginx)	Does the log show the full query string?	→ Branch E: Direct pattern match	→ Branch F: Parameterized or POST body — need deeper inspection
Database audit logs (MySQL general log, pgAudit on 192.0.2.30)	Is `log_statement = all` or pgAudit active?	→ Branch G: Full query reconstruction	→ Branch H: Enable now — you're flying blind

Branch A: Network IDS — Signature Hit on Known Pattern

When this succeeds: UNION-based attacks with obvious keywords, error-based probes, classic boolean patterns. The stuff from Page 3 — manual exploitation with clear strings like ' UNION SELECT.

When this fails: Time-based blind (Page 4), where the payload is just AND SLEEP(5). No error. No extra data in response. The HTTP response looks identical. I watched Suricata 9.0.0-dev sail past a sqlmap time-based run for six hours because the tool's default detection thresholds expect visible anomalies. The packets were clean. The timing was the signal, and Suricata doesn't measure that across sessions.

Concrete ShopBox example — UNION detection:

Here's a Suricata rule that caught our manual UNION probe against the product search:

alert http $EXTERNAL_NET any -> $HOME_NET any ( msg:"SHOPBOX UNION SQLi in search parameter"; flow:established,to_server; http.uri; content:"search="; http.uri; content:"UNION"; fast_pattern; content:"SELECT"; http.uri; content:"FROM"; classtype:web-application-attack; sid:1000001; rev:1;
)

Why this matters: fast_pattern tells Suricata's multi-pattern matcher to prioritize "UNION" as the anchor. Without it, the engine scans for the longer "search=" first, wasting cycles on every legitimate request. On a busy ShopBox instance, that latency matters — dropped packets are missed attacks.

But that rule is useless against this payload from our blind run:

search=test' AND (SELECT * FROM (SELECT(SLEEP(5)))a) AND 'a'='a

No UNION. No SELECT in the URI that the regex catches cleanly. The SLEEP is buried in a subquery. I had to build a second rule using http.user_agent because sqlmap's default UA announces itself — but a real attacker changes that in five seconds.

Signal-to-noise truth: On ShopBox's training network, with students hammering it, that UNION rule fires 200+ times daily from legitimate search strings containing "UNION" (product descriptions, user-generated content). I tuned it to require both UNION and SELECT ... FROM within 30 bytes. False positives dropped to two per week. False negatives? Unknown by definition — that's the trade.

Branch C/D: Web Application Firewall — Blocked or Logged?

A WAF (Web Application Firewall, a reverse proxy that inspects HTTP traffic before it reaches your application) sitting in front of ShopBox sees what the IDS sees, plus it can enforce — block, rate-limit, challenge. But WAFs have two failure modes, and one is worse than the other.

WAF Action	What You Do	Rationale
Blocked (Branch C)	Verify the block was correct, then hunt for bypass variants	Attackers probe WAFs like they probe SQL — expect follow-up
Logged only (Branch D)	Immediate escalation — why didn't it block?	Policy gap, threshold miss, or evasion in progress

⚠️ Authorized, defensive use only.

The logged-only case is where I've found the most evil. A WAF configured in learning mode, or with a rule set to "alert" because some business unit screamed about false positives. I once traced a ShopBox breach to a WAF that logged 1' AND 1=1-- but not 1' AND 1=1# — the comment variant slipped through because the rule used a literal -- match. The attacker found the gap in twenty minutes.

Triage action: If your WAF logged it, pull the full request chain. SQL injection is rarely single-shot. Look for:

Preceding requests: 1', 1'', 1'-- — probe sequence
Following requests: longer strings, schema references (information_schema, pg_catalog)
Timing clusters: requests at machine speed (sqlmap default: 1 req/sec with --delay=0)

Branch E/F: Application Logs — What the Web Server Saw

ShopBox's Apache logs on 192.0.2.10 record the HTTP layer. If the application logs the full query string, you can grep for artifacts. But "full" is doing heavy lifting here.

Branch E — Full query string visible:

# On ShopBox Apache, typical log format includes query string
grep -E "(UNION|SELECT|INSERT|DELETE|DROP|sleep\(|benchmark\()" \ /var/log/apache2/shopbox-access.log | head -20

Why this matters: The sleep( and benchmark( matches target time-based blind indicators that network IDS often misses. Application logs see the raw parameter before the database executes it.

Branch F — Parameterized or POST body obscured:

Modern frameworks log the route, not the body. A POST to /api/search with JSON body {"query": "test' AND SLEEP(5)--"} leaves no trace in default Apache logs. You need application-level logging — ShopBox's own code writing to /var/log/shopbox/app.log — or you're blind.

In plain terms: The web server sees the envelope; the application sees the letter inside. If you only log envelopes, you can't read the threat.

Branch G: Database Audit Logs — The Ground Truth

This is where Samuel gets religious. Network IDS and WAFs guess at intent. Database logs know what executed.

MySQL on 192.0.2.20: Enable general_log or use the audit plugin. The general log shows every statement as received.

PostgreSQL on 192.0.2.30: Two layers. Standard log_statement = all gives you basics — every statement text. pgAudit extends this with structured fields: statement class, command type, object touched, bound parameters. Check your release for current capabilities; configurations vary.

# postgresql.conf on 192.0.2.30 — illustrative, verify for your pgAudit version
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'write, ddl' # less volume than 'all', catches the dangerous stuff

Why this matters: pgaudit.log = 'all' on a production ShopBox would generate hundreds of GB daily — I learned this the hard way during a compliance scan. The volume crushes your log shipper and your SIEM budget. Start targeted: write (INSERT, UPDATE, DELETE) and ddl (schema changes). SELECT-based exfiltration won't appear, but you'll catch the data destruction and backdoor table creation.

pgAudit output format — tagged with AUDIT: and structured fields. Ship this off-host immediately; local storage is a deletion target.

# illustrative output — verify on your target
AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.backdoor,"CREATE TABLE backdoor (data text)",<not logged>

Signal-to-noise reality: Database audit logs are low-noise, high-signal for successful attacks. But they miss the probe that the application sanitizes away. You need both: network layer for the attempt, database layer for the success.

Building Correlation: From Single Query to Attack Pattern

No single log tells the story. The UNION attack from Page 3 that Suricata caught? I found it because the alert correlated with a MySQL general log entry showing SELECT ... UNION SELECT password FROM users--. The time-based blind from Page 4? Suricata missed it entirely. I only found it because sqlmap's six-hour run produced a pattern in application logs: identical search= parameters with microsecond-varied timing, always returning HTTP 200, no error body.

Splunk/ELK correlation query — illustrative structure, adapt to your schema:

# Splunk — illustrative, verify field names in your environment
index=shopbox (source="suricata" OR source="shopbox-app" OR source="mysql-audit")
| eval attack_phase=case( match(_raw, "UNION|SELECT.*FROM"), "exploitation", match(_raw, "SLEEP\(|BENCHMARK\("), "blind-probe", match(_raw, "information_schema|pg_catalog"), "enumeration", 1=1, "other" )
| stats dc(attack_phase) as phase_count, values(src_ip) as attacker_ips, range(_time) as duration_seconds by session_id
| where phase_count >= 2 AND duration_seconds > 300

Why this matters: This looks for sessions that hit multiple attack phases over more than five minutes — the hallmark of manual or automated exploitation, not a one-off probe. A scanner might hit information_schema then vanish. sqlmap running blind will cycle through enumeration, confirmation, and extraction over hours.

In plain terms: One weird query is noise. The same source IP hitting your search box with SLEEP(5), then SLEEP(10), then a schema query, then a UNION — that's an attack unfolding in slow motion.

The Responder's Triage Flow

When correlation fires, you don't fix. You verify, scope, contain, eradicate — and I treat this as literal checklist, not suggestion.

Step	Decision Point	ShopBox Action
Alert	Any source fires	Open ticket, timestamp, preserve logs
Verify	Is this real SQLi or a false positive?	Reconstruct the full query from database logs; replay in lab if needed
Scope	Which hosts? Which data?	Check 192.0.2.20 (MySQL) and 192.0.2.30 (PostgreSQL) for unauthorized SELECT into outfile or COPY TO
Contain	Can we block without breaking ShopBox?	WAF rule update, network segment isolation, disable affected search feature
Eradicate	What did they leave behind?	Hunt for UDFs, new tables, altered users, web shells; compare schema to known-good

Earned insight: I've seen analysts skip "verify" and firewall an IP that turned out to be a search engine crawler with aggressive parameter fuzzing. I've seen "scope" missed because the attacker pivoted from MySQL to PostgreSQL after initial failure — same ShopBox, different backend, single oversight. The flow is sequential for a reason: contain before you verify, and you might block legitimate revenue; eradicate before you scope, and you miss the backdoor they planted in the other database.

When to Read On

Page 6 covers what happens when this architecture fails — a real ShopBox breach where we missed the correlation window. Page 7 builds the defenses that make detection less critical. But you can't build defense without knowing where your eyes are weak. Audit your detection layers now: network IDS for the obvious, WAF for the enforced, application logs for the context, database audit for the truth. Then tie them together, or accept that you'll only see the attacks that want to be seen.