SQL Injection in Practice: Exploiting, Detecting and Mitigating in a Controlled Lab

SQL Injection turned 27 in 2026 and still sits in the OWASP top three, not because attackers got smarter but because misused ORMs, dynamic queries in internal dashboards, and GraphQL resolvers that concatenate strings keep shipping to production. This walkthrough builds a controlled lab where you exploit, detect, and mitigate the vulnerability end to end. The point is not to flex a users table dump: it is to understand the full loop, from the first probe to a WAF rule that kills sqlmap in under two minutes. Everything here runs on hardware you own, against targets you are authorized to break, in an isolated network with no route to anything real.

What SQL injection actually is

SQL injection happens when untrusted input reaches the SQL interpreter as code instead of data. The database cannot tell the difference between the developer's intent and an attacker's payload once both are glued into the same string. The canonical example is "SELECT * FROM users WHERE id = " + req.id, where id=1 OR 1=1 turns a single-row lookup into a full table read. The root cause is never the database engine; it is the concatenation. Modern frameworks make parameterization the default, yet raw string building survives in reporting endpoints, dynamic ORDER BY clauses, search filters, and hand-rolled query builders.

The taxonomy of injection classes

You cannot test what you cannot name. In-band UNION-based injection returns data directly in the HTTP response through an appended UNION SELECT. Boolean-based blind injection infers one bit at a time from how the page changes when a condition is true or false. Time-based blind injection uses SLEEP() or pg_sleep() to leak data through response latency when there is no visible output at all. Error-based injection coaxes the database into echoing data inside an error message. Out-of-band injection exfiltrates through DNS or HTTP when the channel is fully blind. Finally, second-order injection stores a payload that fires later, in a different query, on a different route. Real applications usually expose more than one class.

Building the isolated lab

Start with Docker on a host-only network. Run a DVWA instance, MySQL 8.0 with the binary log enabled, and a Burp Suite proxy that captures every request. A minimal compose file gives you a repeatable target: docker compose up -d dvwa mysql. Point your browser at the DVWA proxy through Burp, set the security level to low for the first pass, then raise it to medium and high to feel how input filtering changes the game. Never expose this stack on a routable interface; SQLi labs are attractive to opportunistic scanners, and a vulnerable MySQL on the public internet becomes someone else's cryptominer within hours.

Step-by-step exploitation with sqlmap

The first experiment hits the DVWA GET endpoint. Run sqlmap -u 'http://lab.local/vulnerabilities/sqli/?id=1&Submit=Submit' --cookie='PHPSESSID=...; security=low' --batch --technique=BEUST --level=3 --risk=2. In roughly four seconds sqlmap confirms boolean-based blind injection in the id parameter; in eleven it enumerates the dvwa database and its tables. Add --dump -T users to extract credentials, or --os-shell where FILE privileges and secure_file_priv allow writing a web shell. Watch the payloads on the wire: 1 AND 4523=4523, 1 AND 1=2 UNION SELECT NULL,NULL. Those repetitive numeric literals, the default user agent sqlmap/1.8.x, and the missing Accept-Encoding header are exactly the signal you will weaponize for detection later.

Manual exploitation, because tools miss context

Automate discovery, but exploit by hand when it matters. For UNION-based extraction, first find the column count with ORDER BY 5-- - until the query errors, then match types with UNION SELECT 1,2,3,4,5-- - and place @@version, database(), and group_concat(table_name) into visible columns via information_schema.tables. For blind boolean extraction, bisect each character with AND ASCII(SUBSTRING((SELECT ...),1,1))>77. For time-based, replace the comparison with AND IF(condition, SLEEP(3), 0). Doing this manually once teaches you the mental model that no scanner gives you, and it is the only way to handle WAF-mangled contexts where sqlmap's tamper scripts fall short.

Second-order injection: the bug scanners skip

Register a user named admin'-- through a signup form whose prepared statement safely escapes the quote on write. The payload sits dormant in the database. The problem lives in an internal search or profile endpoint that reuses that stored value in a dynamic query without reparameterizing. The result is an authentication bypass on a route that never appeared in the initial scan, because the payload only detonates in a different context. This is the same failure mode that punctures GraphQL APIs whose resolvers share a query builder. Automated tools rarely catch it, which is why manual code review keeps paying rent in every serious engagement.

Detection and instrumentation

Turn the attacker's noise into a signal. Enable SET GLOBAL general_log = 'ON' so every statement lands in the query log with microsecond timestamps. Compare thirty seconds of legitimate traffic against thirty seconds of sqlmap: the coefficient of variation of query length jumps from roughly 0.12 to 1.8, and syntax errors spike. Ship the log through Filebeat into Elastic and build three detections: SQL syntax errors above five per minute per source IP, sequences containing UNION SELECT NULL, and query execution time beyond three standard deviations from the hourly baseline. Against sqlmap running --random-agent and --delay=2, all three fired in under ninety seconds in our tests. Plant honeytokens too: a fake credit_card_test column with a traceable string tells you exactly which endpoint leaked the day it appears on a paste site.

Mitigation and hardening that actually holds

Real mitigation starts with parameterized prepared statements and does not end there. Swap interpolated strings for bound parameters in every language: NamedParameterJdbcTemplate in Java, $1 placeholders in Go's pgx, and PDO with PDO::ATTR_EMULATE_PREPARES=false on PHP to avoid the classic multibyte quote bypass. Because prepared statements cannot parameterize identifiers, guard dynamic ORDER BY and column names with a strict allowlist. Layer least privilege on the application user (GRANT SELECT, INSERT, UPDATE only, never FILE or SUPER), add input validation at the edge, and put a WAF such as ModSecurity with CRS 4.x in blocking mode in front. Shrink the attack surface before you trust detection; defense in depth means an attacker must beat every layer, not just one.

Common pitfalls

Teams break their own defenses in predictable ways. They parameterize the value but interpolate the table name. They trust an ORM and then drop to a raw query for one report. They escape on output instead of parameterizing on input. They run the app user as root on the database so a minor injection becomes a full server compromise. They test only the login form and ignore search, export, and admin panels where the ugly dynamic SQL hides. And they treat a green sqlmap scan as proof of safety, when sqlmap by design never reports the second-order and business-logic paths that a human reviewer finds.

A field checklist

Before signing off: every query that touches external input is parameterized; identifiers pass through an allowlist; the database user holds least privilege; error messages are generic and never echo SQL; a WAF blocks in production, not just logs; the query log feeds a SIEM with the three detections above; honeytokens are planted in high-value tables; and a quarterly review covers every endpoint that accepts input, including internal and admin routes. Prove the loop works by attacking your own build and watching the alerts fire.

Beyond MySQL: other engines change the payloads

The class of the bug is portable, but the syntax is not, and a tester who only knows MySQL freezes the moment the backend is PostgreSQL or SQL Server. On PostgreSQL, string concatenation uses ||, comments use --, delays come from pg_sleep(3), and stacked queries are often available through the driver, which opens the door to COPY ... TO PROGRAM command execution on misconfigured installs. On SQL Server, WAITFOR DELAY '0:0:3' drives time-based inference, xp_cmdshell is the classic escalation when it is enabled, and error-based extraction leans on CONVERT() type mismatches. Oracle forces every SELECT through FROM dual and concatenates with || as well. NoSQL stores are not safe either: MongoDB accepts operator injection such as {"$gt": ""} when a JSON body is passed unvalidated into a query, turning a login into an always-true match. The defensive lesson is identical across all of them, which is why it generalizes: never build the query from the input, always bind, and always run the database account with the narrowest possible grant so that even a successful injection cannot reach the operating system.

FAQ

Does an ORM make me immune to SQL injection? No. ORMs parameterize the common path, but raw query escapes, native SQL fragments, and dynamically built WHERE or ORDER BY clauses reintroduce the vulnerability. Audit every place your ORM lets you drop to raw SQL.

Is a WAF enough on its own? No. A WAF buys time and blocks commodity tooling, but encoding tricks, tamper scripts, and second-order payloads bypass signature rules. Treat the WAF as one layer over parameterized queries and least privilege, never as a substitute for them.

Practical takeaway: spin up the lab, run sqlmap once to feel the rhythm of its payloads, then spend eighty percent of the remaining time on detection and fixes. SQL injection is not an attacker creativity problem; it is a dynamic query nobody reviewed. Parameterize everything that is a value, allowlist everything that is an identifier, and the day you can prove your application kills sqlmap in under two minutes with automated blocking, you have won.