Post cover image

July 21, 2026

Lab: SQL injection UNION attack, retrieving multiple values in a single column

Mastering SQL Injection: Beating the Single-Column Problem with UNION Attacks

By @i.ogore

3 min read

Most SQL injection labs make UNION attacks look almost too easy.

Figure out how many columns the query returns. Find the columns that accept text. Pull the data.

UNION SELECT username, password FROM users

Simple enough.

Then you hit a lab where only one column is actually reflected back to the page. Suddenly, the usual approach falls apart. You know the database is returning multiple columns, but only one gives you anything useful to work with.

That's exactly what we run into in the PortSwigger Practitioner Lab "SQL injection UNION attack, retrieving multiple values in a single column." Instead of treating it like a roadblock, we'll use SQL string concatenation to squeeze multiple pieces of data into a single visible column.

It's a small trick but a very useful one.

Understanding the Vulnerability

The vulnerable application exposes a classic SQL injection flaw in the product category filter. Every time we choose a category, the application builds a SQL query behind the scenes that looks something like this:

SELECT product_name, description
FROM products
WHERE category = 'Gifts'

The problem is obvious once you stop trusting user input.

The value supplied for category is inserted directly into the SQL statement without proper parameterization or sanitization. That means we aren't limited to supplying a category name—we can inject our own SQL and change how the database processes the query.

At that point, the database isn't just returning products anymore.

It's executing our instructions.

From here, the goal is to abuse the UNION operator, merge our own results with the application's original query, and eventually retrieve sensitive information. The only catch? The application only renders one text column back to the browser.

That changes the game, but it doesn't stop the attack. It just means we have to get a little more creative with how we package the data before sending it back.

Step-by-Step Walkthrough

Step 1: Mapping the Database Structure

Before throwing a UNION SELECT payload at the application, we need to understand the shape of the original query. UNION isn't forgiving. If our injected query doesn't line up with the original one, the database rejects it immediately.

Two things have to match:

  • The injected query must return the same number of columns as the original query.
  • The data types in each corresponding column must be compatible.

The quickest way to determine the column count is with an ORDER BY test. Drop the intercepted request into Burp Suite Repeater and increment the column number until the application throws an error.

GET /filter?category=Gifts'+ORDER+BY+1--
GET /filter?category=Gifts'+ORDER+BY+2--
GET /filter?category=Gifts'+ORDER+BY+3--

The first two requests complete successfully. The third one doesn't.

That tells us everything we need to know.

The backend query returns two columns, so every UNION SELECT payload we send from this point forward also needs to return exactly two values.

Step 2: Finding the Text Column (The Bottleneck)

Knowing the column count is only half the puzzle.

Now we need to figure out which column actually reflects text back to the browser. A column might exist in the query but never be rendered in the page, making it useless for displaying extracted data.

The easiest way to test this is by replacing one column at a time with a recognizable string while filling the remaining position with NULL.

First, we test the first column:

GET /filter?category=Gifts'+UNION+SELECT+'abc',NULL--

If abc appears in the application's response, we've found a usable output column. If nothing shows up, move the string to the next position and repeat the process.

In this lab, only the second column reflects our input. The first column either isn't displayed or expects a different data type.

That's the bottleneck.

At first glance, it feels limiting because we eventually want to retrieve both the username and password columns from the database. With only one visible text column available, the straightforward UNION SELECT username, password approach simply won't work.

Step 3: Beating the Bottleneck with String Concatenation

This is where the lab gets interesting.

We have two values we want to steal.

We only have one place to display them.

Instead of returning each value in its own column, we combine both into a single string before the database sends the response back to us.

Oracle and PostgreSQL make this easy using the double-pipe (||) concatenation operator. To keep the output readable, we also insert a tilde (~) between the username and password so we can split them apart later.

The finished payload looks like this:

GET /filter?category=Gifts'+UNION+SELECT+NULL,+username||'~'||password+FROM+users--

The first column is set to NULL because we already know it isn't useful. The second column performs the heavy lifting by joining both fields into one continuous string that the application is happy to display.

Simple trick but Huge payoff.

Step 4: Harvesting the Credentials

After sending the payload, the application responds with an HTTP 200 OK.

Now comes the satisfying part.

Open the response body — or search the HTML source — and look for the delimiter we inserted earlier. Searching for the tilde (~) makes the injected values stand out immediately.

The result looks something like this:

administrator~bj20dq53dneuyb0m7qm8

The delimiter makes it obvious where one value ends and the next begins. The text before the tilde is the username, while everything after it is the corresponding password.

At this point, the lab is essentially complete. With valid administrator credentials in hand, we can authenticate as the target user and finish the challenge.

It seems like a small workaround, but string concatenation is one of those techniques you'll end up using repeatedly during real SQL injection assessments. Applications don't always expose every column you want, and when they don't, packing multiple values into a single reflected field is often the cleanest way to get the data back.