UNION-Based SQL Injection: Appending Your Own Result Set

The blind techniques exist because the database will not show you query results. When it will — on a search page, a product listing, any endpoint that renders rows back to the user — there is a far more direct path. The UNION operator combines the result of two SELECT statements into one. If an attacker controls part of the first query, they can append a second one of their own and have its rows rendered right where the legitimate data was meant to go.

This post covers the conditions a UNION needs, how a harmless marker proves that a second result set reached the page, and why parameterization closes the path.

When this technique applies

Four conditions must line up: the original statement is a SELECT, input can alter its SQL structure, the result is rendered or returned, and the appended query can match the original column count and compatible types. If the endpoint performs an UPDATE, discards rows or serializes only fixed fields from an unrelated object, UNION may not provide a visible channel even though another SQL injection technique still works.

How it happens

The vulnerability is ordinary concatenation, with one extra requirement: the query's columns are displayed to the user. A keyword search that renders its results is the canonical target:

$q = $_GET['q'];
$rows = $db->query(
    "SELECT name, price FROM products WHERE name LIKE '%$q%'");

foreach ($rows as $row) {
    $name = htmlspecialchars($row['name'], ENT_QUOTES, 'UTF-8');
    $price = htmlspecialchars((string) $row['price'], ENT_QUOTES, 'UTF-8');
    echo "<tr><td>$name</td><td>$price</td></tr>";
}

Whatever this query returns becomes a table row on the page. If an attacker can append a UNION SELECT, the data they choose lands in those same name and price cells.

A concrete attack: building a valid UNION

A UNION only works if both SELECTs return the same number of columns. In a disposable lab, an assessor can establish the expected shape from the test schema and use NULL values, which are compatible with many column types:

%' UNION SELECT NULL, NULL -- -

Next, place a unique harmless marker in the text position:

%' UNION SELECT NULL, 'ssec-lab' -- -

If ssec-lab appears as a product field and a paired invalid-column or incompatible-type control fails, the application has rendered a row authored inside injected SQL. That is sufficient proof. An attacker could replace the fixed marker with expressions or rows accessible to the database account, which is why the account's privileges determine the reachable impact.

A UNION injection does not trick the database into leaking data sideways — it asks, in valid SQL, for a completely different table, and the application dutifully renders the answer.

Type matching and per-engine nuances

The appended columns must be type-compatible with the originals, or the engine rejects the whole statement. NULL helps during a controlled proof; text belongs only in a position the database and response can represent. Dialect details also matter:

CONCAT('ssec', '-', 'lab')
'ssec' || '-' || 'lab'
'ssec' + '-' + 'lab'

Oracle requires a FROM clause, so even a constant marker typically selects FROM dual. MySQL uses CONCAT, PostgreSQL and Oracle use ||, and SQL Server commonly uses + for text. These differences make a copied payload unreliable and make engine fingerprinting part of accurate detection.

Signals that are not proof

• The marker is merely reflected from the request outside the query result.
• A search legitimately finds a product whose name contains the marker.
• A WAF returns the marker in its block page.
• The response changes only because malformed SQL caused an error.
• The marker appears once but not across repeated paired controls.

The fix: parameterize and restrict

UNION injection cannot start if the input is bound as a value rather than spliced into the query text:

using var cmd = new SqlCommand(
    "SELECT name, price FROM products WHERE name LIKE @q", connection);
cmd.Parameters.Add("@q", SqlDbType.NVarChar, 100).Value = "%" + q + "%";

With a parameter, %' UNION SELECT ... is searched for as a literal product name and matches nothing — there is no query boundary to break out of. Reinforce it with least privilege: the web account should reach only the tables it needs, so even a successful injection cannot SELECT from users. Avoid exposing schema-discovery surfaces by denying access to information_schema where the platform allows it.

How SelfSec finds it

SelfSec's SQL injection engine covers 19 database families and treats UNION-based injection as one of its core techniques. It determines the column count via ORDER BY and NULL probes, identifies reflected and type-compatible columns, and adapts concatenation and FROM requirements to the detected engine. It requires a uniquely marked database result before reporting a finding and includes a reproduction request. Core scan processing runs locally on 127.0.0.1.

References

• PostgreSQL documentation: Combining Queries
• MySQL reference: UNION Clause
• Microsoft Learn: Set Operators — UNION
• OWASP SQL Injection Prevention Cheat Sheet
• MITRE CWE-89