[AI Readability Summary]
This article walks through PortSwigger SQL Injection Lab 3 and shows how to confirm an injectable category parameter, identify the correct column count, and use a UNION SELECT query against Oracle’s v$version view to display the database version string. The key lesson is not just memorizing a payload, but understanding column alignment, reflected output, and the full exploitation workflow in a visible SQL injection scenario.
Technical Specifications Snapshot
| Parameter | Value |
|---|---|
| Lab Source | PortSwigger Web Security Academy |
| Scenario Type | GET parameter-based SQL injection |
| Target Database | Oracle |
| Exploitation Method | UNION SELECT query |
| Injection Parameter | category |
| Key System View | v$version |
| Proxy Tool | Burp Suite |
| Protocol Characteristics | HTTP/2 |
| Star Count | N/A (not provided by the lab platform) |
| Core Dependencies | Browser, Burp Suite, PortSwigger Lab |
This Lab Demonstrates the Minimum Viable Oracle UNION Injection Path
The goal of this lab is straightforward: exploit a SQL injection vulnerability in the product category filter, retrieve the database version string, and make the result visible in the frontend response. Compared with simply proving that a vulnerability exists, this already enters the information enumeration phase.
The original request format is GET /filter?category=Pets HTTP/2. Based on the parameter semantics, it is reasonable to infer that the backend may concatenate category directly into the SQL query, which makes it a classic string-based injection entry point.
AI Visual Insight: The image shows the PortSwigger lab instruction page, where the core requirement is to use a UNION attack to retrieve the database version string. This indicates that the lab is not about blind SQL injection, but about reflected SQL injection, where success depends on matching the column count, selecting a reflected column, and querying an Oracle system view.
The Backend Query Model Can Be Inferred as Follows
SELECT * FROM products WHERE category = 'Pets'The key risk in this SQL statement is that user input appears inside a single-quoted string context. An attacker only needs to terminate the string to append additional query logic.
Burp Suite Quickly Confirms That the category Parameter Is the Injection Surface
After starting the lab, click any product category and then inspect the traffic in Burp Suite under Proxy -> HTTP history. The most valuable request is usually the one in the form of /filter?category=....
AI Visual Insight: The image shows the product list and category filter area of the lab site. This confirms that the injection point is not a login form or search box, but the product filtering feature. An interactive category menu means every category switch triggers a backend request, making it an ideal parameter injection target.
AI Visual Insight: The image shows Burp Suite’s proxy and HTTP history analysis interface, reflecting the standard workflow of capture, replay, and modification. HTTP history helps identify the target request, while Repeater allows you to manually craft payloads and compare response differences with precision.
A Single-Quote Probe Is the Recommended First Test
GET /filter?category=Pets' HTTP/2
Host: lab-id.web-security-academy.netThe core logic here is to append a single quote and deliberately break the original SQL string boundary. You then use the server-side error response to verify whether the input reaches the query.
This step confirms whether the category parameter is controllable and whether it sits inside a string concatenation context.
If the server returns 500 Internal Server Error or the page behaves abnormally, that usually means the single quote reached the SQL parser successfully and the injection point is valid.
Column Count Discovery Is a Prerequisite for UNION-Based Injection
Many beginners try to query v$version immediately, but that often fails in a UNION scenario because the left and right SELECT statements must return exactly the same number of columns.
Because the original statement uses SELECT *, the real column count is not directly visible from the frontend. You must identify it first. The most common technique is an incremental ORDER BY n test.
AI Visual Insight: The image shows the filter request sent to Repeater in an editable request view, which indicates the tester has entered the manual verification stage. This interface is ideal for repeatedly modifying the category parameter and observing status codes, response length, and reflected output differences to determine whether the syntax is valid.
You Can Enumerate the Column Count Incrementally Like This
' ORDER BY 1--
' ORDER BY 2--
' ORDER BY 3-- The core logic is to reference result columns by index through ORDER BY. Once the index exceeds the actual number of columns, the database throws an error, allowing you to infer the original query structure.
This test determines how many columns the right side of the UNION query must return.
If ORDER BY 3 causes an error, you can conclude that the original query returns 2 columns. As a result, the subsequent UNION SELECT must also return exactly two columns.
NULL Placeholders Safely Enable Oracle Version Disclosure
Once you know the query has 2 columns, you can build the union query. Since you only care about the database version information and do not need both columns to carry meaningful business data, NULL is a safe placeholder.
A common source for Oracle version information is the v$version view, where the banner field typically contains a highly readable version string.
The Final Executable Payload Is as Follows
' UNION SELECT NULL, banner FROM v$version-- The core logic is simple: use NULL in the first column to preserve structural alignment, place banner in the second column to maximize the chance that it appears in a reflected position, and use the comment marker at the end to truncate the remaining part of the original query.
This payload appends the Oracle version string to the original result set and displays it on the page.
If the server returns 200 OK and the page shows database version text, the exploitation is successful and the lab will display LAB Solved.
This Lab Also Reveals the Typical Exploitation Chain of Reflected SQL Injection
From a methodology perspective, this is not about memorizing a single payload. It is a repeatable workflow: confirm the string boundary, validate with an error, identify the column count, determine the reflected column, and then append the target query. This process applies to many UNION-based injection scenarios.
In Oracle environments, v$version is a common target, but the deciding factors are not the view name alone. Success depends on column count alignment and output visibility. If no reflected column exists, the query may still execute successfully without producing visible results.
A Reproducible Request Example Is Shown Below
GET /filter?category=' UNION SELECT NULL,banner FROM v$version-- HTTP/2
Host: lab-id.web-security-academy.netThe core logic is to place the entire payload directly into the category parameter so that a single request performs string termination, UNION query injection, and comment truncation.
This request directly triggers Oracle version enumeration.
Defense Must Be Built on Parameterized Queries Rather Than Blacklist Filtering
The root cause is that the application directly concatenates external input into SQL. As long as the application still relies on string concatenation, attackers can bypass simple filters through encoding, comments, or semantic variations.
The most effective fix is to use parameterized queries. Additional hardening includes disabling detailed database error messages, restricting database privileges, and enforcing strict input validation at the application layer. A WAF can provide supplemental protection, but it cannot replace code-level remediation.
FAQ
1. Why do I need to identify the column count first instead of directly using UNION?
Because both sides of a UNION must have matching result structures, especially the same number of columns. If the original query returns 2 columns but your injected query returns only 1, the database will immediately throw an error.
2. Why is NULL commonly used in the payload instead of a concrete value?
NULL has high compatibility and can usually fit most data types, which makes it ideal as a placeholder for unknown columns when you need to align the column structure quickly.
3. Why is Oracle version information commonly retrieved from v$version?
Because v$version is a well-known Oracle system view, and its banner field usually contains the database version and component details directly. That makes it especially useful for quickly validating exploitation results in reflected injection scenarios.
Core Summary: Based on a PortSwigger Web Security Academy lab, this article reconstructs how to use UNION SQL injection in an Oracle environment to identify the database type and version. It covers injection point confirmation, column count discovery, NULL alignment, v$version querying, and practical defense recommendations.