SQL injection: UNION and extraction

You have confirmed SQL injection in a query whose results are shown on the page. Now you turn that into extraction: reading arbitrary tables, usernames, password hashes, anything the database holds, by appending your own SELECT to the application's. This is UNION-based SQL injection.

The idea#

SQL has a UNION operator that glues the results of two SELECT statements into one result set. If the application runs SELECT name, price FROM products WHERE id=1 and shows you those rows, and the id is injectable, you can add:

?id=1 UNION SELECT username, password FROM users--

Now the database runs both selects and returns both sets of rows together. The product query returns products; your query returns usernames and password hashes; the page, which just prints whatever rows came back, prints yours too. You have read a table you were never meant to see, through a feature that only ever showed products.

Two rules make UNION work, and getting them right is the whole technique.

Rule 1: the column count must match#

UNION requires both selects to return the same number of columns. The original query returns some number you do not yet know, so you find it. Two ways:

ORDER BY climbing. Add ORDER BY n for increasing n. It works while n is a valid column number and errors when n exceeds the count:

?id=1 ORDER BY 1--    ok
?id=1 ORDER BY 2--    ok
?id=1 ORDER BY 3--    error  →  the query has 2 columns

UNION SELECT NULL climbing. Try UNION SELECT NULL, then NULL,NULL, then NULL,NULL,NULL, until the error disappears. The count that succeeds is the column count. NULL is used because it is compatible with any column type.

Rule 2: find which columns print, and use compatible types#

Not every column the original query selects is necessarily displayed, and columns have types. Once you know the count, put a recognisable marker in each position to see which ones appear on the page:

?id=-1 UNION SELECT 'AAA','BBB'--

(Using id=-1, a value that returns no real rows, so only your injected row shows.) Whichever of AAA/BBB you see rendered tells you which column position you can read data from. Put your real target in that position:

?id=-1 UNION SELECT username, password FROM users--

If a column is numeric-only, a string there may error, that is why you probe with markers first and place your data in a column that accepts it.

Extracting the good stuff#

Once you have a working UNION and a visible column, you read the database systematically. Most engines expose their own structure through metadata tables, so you can discover table and column names before dumping them:

-- list tables (MySQL / most engines)
?id=-1 UNION SELECT table_name, NULL FROM information_schema.tables--
-- list columns of a table
?id=-1 UNION SELECT column_name, NULL FROM information_schema.columns WHERE table_name='users'--
-- dump credentials
?id=-1 UNION SELECT username, password FROM users--

You can also concatenate multiple values into one visible column when you have only one printing slot (CONCAT(username,':',password) in MySQL). And you can read the current database, version, and user (version(), database(), current_user) to fingerprint the engine, which matters because syntax differs between MySQL, PostgreSQL, SQLite, and SQL Server.

Different engines, different dialect#

UNION is universal; the details are not. Comment syntax (-- , #, /* */), string concatenation (CONCAT vs ||), and metadata tables differ by engine. So identify the engine early (from an error message, the version() string, or which syntax works) and use that dialect. A cheat sheet per database is worth keeping open; the PortSwigger SQL injection reference and OWASP's testing guide both list the dialect quirks.

The honesty gate#

The finding is reading data you should not be able to read. On a real target, prove it with the minimum: pull one non-sensitive value (the database version, or a single row you are authorised to see) to demonstrate extraction works, and then describe the reachable tables rather than actually dumping thousands of real users' credentials. Extracting the whole user table of a live system is both unnecessary for the report and harmful. On this lab, dump away, the data is synthetic.

Your lab#

PawMatch breed search (UNION injection) is a search whose query you can extend with UNION. Find the column count with ORDER BY, find which columns print, then UNION in a query against the users table. When credentials or other secret data appear in the search results, you have gone from finding injection to exploiting it for data disclosure, exactly what an exam means by "extract information and credentials from backend databases."

Where this goes next#

UNION works when results are shown on the page. Often they are not, the app uses your query internally but never prints its output. The next lesson handles that harder, common case: blind SQL injection, where you extract data one yes/no answer at a time.