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

⚠️ Disclaimer

This write-up is for educational and defensive purposes only. Use the techniques shown here only in legal, controlled environments such as PortSwigger Web Security Academy or your own testbeds. Do not attempt these techniques on systems you do not own or have explicit permission to test.

TL;DR

This lab demonstrates how to use a UNION SELECT to return multiple values inside a single column by concatenating database fields. The trick is useful when the target query returns fewer text-capable columns than the data you want to extract. I confirmed the query structure (two columns where only one is text-capable), then used a concatenation expression to combine username and password into the text-capable column. The successful lab payload (sanitized) was:

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

After sending the payload and observing the concatenated username~password strings in the response, I used the credentials to log in as administrator and solved the lab. Below is a full, beginner-friendly walkthrough: reconnaissance, exact payloads, validation, troubleshooting, defensive advice, detection recipes, and real-world context.

1 - Lab goal & initial notes

Lab goal (PortSwigger): Exploit a SQL injection in the product category filter to retrieve concatenated username and password values from the users table (displayed as username~password) using a UNION query, then log in as administrator.

Why this lab is useful: In many apps you may only have one column you can print as text. When usernames and passwords are in separate columns, concatenation lets you return both inside that single printable column - an essential technique for data extraction when column availability is limited.

Important rules I followed:

• Keep requests sanitized in the write-up: use <LAB_HOST> placeholders.
• Only test in authorized environments.
• Preserve request structure (cookies, CSRF if present) while testing in labs.

2 - Recon: capture the filter request

I proxied my browser through Burp and navigated to the product listing page filtered by category. The target URL pattern I worked with looked like this (sanitized):

GET /filter?category=Pets HTTP/1.1
Host: <LAB_HOST>
Accept: text/html
...

Observing the product listing and the category parameter confirmed an ideal injection surface: the parameter is likely interpolated into a SQL WHERE clause that returns rows rendered into the HTML response - perfect for UNION-style injections.

Note: In this lab the server returned HTML containing query results, so any injected UNION row that produces text in a printable column will appear in the page.

3 - Step 1 - confirm original query shape & text-capable columns

Before concatenating values, confirm:

1. How many columns the original query returns.
2. Which columns can accept/display text.

I already solved the previous labs, but the quick checks I used were:

• Determine columns with NULL probes:

/filter?category=Pets+'+UNION+SELECT+NULL,NULL--

(If that errors, increment the NULL count until the union is accepted.)

• Test which column shows text by replacing a NULL with a test string 'abc':

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

In this lab the result was: two columns returned by the original query, and only the second column is text-capable (the first column is numeric or otherwise not rendered as plain text).

Because we have only one printable column but two values to extract (username and password), concatenation is the right path.

4 - Step 2 - why concatenation helps

If the app renders only one field per row into HTML, we can combine multiple DB columns into a single string using a concatenation operator. The operator differs per RDBMS:

• SQLite/PostgreSQL: ||
• MySQL: CONCAT(col1, col2) or col1 || col2 (MySQL requires CONCAT)
• SQL Server: + may work for strings

PortSwigger labs often use a database that accepts || (or is tolerant of ||), and the lab hint used username||'~'||password with a tilde ~ as a delimiter because it is unlikely to appear in username/password values. Using a tombstone delimiter makes parsing easier when viewing results.

So the extraction row we want is something like:

• NULL, username || '~' || password
  which matches the two-column shape and produces a single text column with both values.

5 - Exact payload that solved the lab

With the column count and text column identified, I crafted the concatenation payload (URL-encoded in the browser or sent via Burp Repeater):

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

Notes:

• NULL fills the first (non-text) column.
• username||'~'||password produces a single string in the second (text) column.
• The -- comments out any trailing content from the original query.
• Use the lab-provided host and exact parameter formatting in Burp Repeater to avoid encoding issues.

After sending the payload, the page contained lines like:

• carlos~w3akP@ss
• administrator~admin123

(Example format; the exact values come from the lab and should be treated as lab-only data.)

Once I had an administrator username/password, I used them to POST to the /login endpoint and confirmed administrator access - the lab marked as solved.

6 - Verifying and validating extraction

Validation steps I used after seeing candidate values:

1. Inspect the HTML source to ensure the concatenated tokens are visible and not hidden inside attributes or encoded.
2. If the concatenated string is present but contains HTML entity encoding, decode or search for the raw form in the response.
3. Copy the candidate administrator password string and attempt a login in a fresh browser session (or Burp Repeater).
4. Confirm admin-only pages are reachable (dashboard, user management) - this confirms the credential works.

If the returned string looked malformed or missing, I tried:

• Different delimiters (e.g., ':' or '|') if the ~ character was being filtered.
• Using CAST() or CONCAT() depending on DB compatibility for more robust concatenation.
• Selecting a smaller result set via LIMIT to make the output manageable.

7 - Troubleshooting common pitfalls

Concatenation extraction is reliable, but a few issues can block you:

• Wrong concatenation operator for the DB: If || fails, try DB-specific functions. For MySQL: CONCAT(username, '~', password).
• Type casting errors: If one column is not a string, use CAST(username AS TEXT) or DB-specific casting. Example (Postgres): username::text.
• WAF or filters blocking || or CONCAT: In labs this is rare. On hardened targets, obfuscation is possible but should be avoided unless authorized.
• HTML escaping hiding results: Inspect full HTML source (CTRL+U) and search for the token; sometimes templating escapes special characters.
• Large result sets: Use LIMIT to restrict extraction, e.g., ... FROM users LIMIT 5--.
• Permission issues: If the DB user lacks access to users, you cannot extract. Note this in findings and recommend least-privilege corrections.

Practical tip: Use a unique delimiter (~) unlikely to appear in usernames or password hashes to make it clear where one field ends and the next begins.

8 - Defensive perspective (how to prevent concatenation-exploitation paths)

This is the most important section for engineers and ops teams. The prevention checklist below addresses design, coding, and runtime detection.

8.1 Use prepared statements / parameterized queries

Never construct SQL by concatenating untrusted input. Parameterized queries ensure inputs are data, not executable SQL.

8.2 Map inputs to internal IDs

For filters (like category), map user-controlled values to internal safe IDs (e.g., category_id = 3). This prevents arbitrary SQL fragments reaching the query.

8.3 Limit DB privileges

Web application DB accounts should not be able to read admin-only tables (users, secrets). Use separate DB roles where necessary.

8.4 Output escaping & templating hygiene

Sanitize and escape any user-provided content before rendering in HTML. Avoid directly rendering raw DB values without encoding.

8.5 Hide DB errors & limit info leakage

Do not expose DB error messages to end users - generic errors reduce an attacker's ability to fine-tune payloads.

8.6 WAF & runtime detection rules

• Flag parameters containing UNION SELECT, CONCAT(, || or repeated NULL patterns.
• Detect repeated probes from a single IP that increment NULL counts - an indicator of column enumeration.

8.7 CI / automated tests

Add security tests that run UNION SELECT NULL probes against staging endpoints that accept filters. Fail builds if probes succeed.

9 - Detection & logging recipes (tailored alerts)

Recommended signals you can add to your SIEM/WAF:

• WAF rule: Alert/block when query parameters contain UNION SELECT, username||, CONCAT(, or NULL,NULL sequences.
• SIEM correlation: Raise an alert when the same IP sends multiple filter requests containing SQL keywords and gets errors or unusual status codes.
• Application logging: Log parameter values that contain SQL tokens and trigger an automated review.
• Behavioral alert: If static filter pages begin returning dynamic, unexpected content in responses during scans, mark as suspicious and investigate source IPs.

These detection layers buy time while you patch vulnerable code.

10 - Real-world examples & impact

Concatenation extraction is not just a lab trick - it shows up in real incidents.

Example 1 - Credential harvesting from public filters
A retailer exposed a product filter that concatenated fields internally. Attackers used concatenation to return email|hashed_password strings and then used that to perform offline cracking, resulting in account takeover.

Example 2 - Secret discovery through concatenated config values
An internal tool concatenated multiple config fields into a single rendered column for a dashboard. Attackers used a UNION concatenation to pull API keys and connection strings, enabling further access to internal APIs.

Example 3 - Automated scraping & chaining
Bots that enumerate column counts and then concatenate fields at scale can rapidly assemble large datasets of credentials. Detecting the early NULL probes prevented a full-scale scrape in one incident.

These real cases reinforce why data access must be minimized and why concatenation-based extraction deserves special attention.

11 - Ethical handling & reporting

If you reproduce this lab in a sanctioned test and find real credentials:

• Do not copy, exfiltrate, or publish sensitive data outside of the authorized report.
• Provide a minimal proof-of-concept (e.g., screenshot showing username~(redacted)) and detailed remediation steps.
• For bug bounties, follow the program’s disclosure policy and avoid sharing raw secrets publicly.

12 - Practice steps & suggested exercises

To build mastery:

1. Recreate this lab flow until you can reliably produce the username~password output.
2. Try variants: use CONCAT() vs ||, use different delimiters, and test casting if a text column is not present.
3. Create a small vulnerable app locally that concatenates columns and then fix it with prepared statements - the before/after exercise is invaluable.
4. Add a CI test that asserts filter endpoints reject UNION SELECT NULL probes in staging.

13 - Wrap-up & final thoughts

This lab highlights a practical data-extraction technique: when you’re limited to a single printable column, concatenation gives you the power to retrieve multiple fields in one go. The defenses are straightforward: parameterize, map inputs to IDs, restrict DB privileges, and monitor probes. Use the lab to practice carefully - the same method is an important tool in both offensive learning and defensive hardening.

References & further reading

• PortSwigger Web Security Academy - SQL Injection (UNION) labs.
• OWASP - SQL Injection Prevention Cheat Sheet.
• Database documentation for CONCAT, CAST, and concatenation operators (MySQL, PostgreSQL, SQLite).