' UNION — fingerprinting Oracle

A shop's category filter drops user input straight into a SQL WHERE clause. One quote breaks out of it, a UNION SELECT rides along with the real query, and the database prints its own version banner onto the product page. The target is Oracle, and Oracle has rules the other engines don't. The whole attack fits in a single curl.

Executive summary

The lab is SQL injection attack, querying the database type and version on Oracle. The goal is to make the application return the Oracle version strings. That's the first thing a real tester does after confirming an injection, because the engine decides every payload that comes after it: comment syntax, string concatenation, metadata tables, and which functions exist.

Nothing here needed sqlmap or Burp. Working out the payload took two facts: how many columns the original query returns, and that Oracle won't run a SELECT without a FROM.

Finding where input meets SQL

The app is a stock Academy storefront. The only user-controlled input is the category filter, a plain GET parameter:

GET /filter?category=Gifts

The page lists products with two text fields each, a name and a description. That's a good hint at the shape of the backing query:

-- what the server is almost certainly running
SELECT name, description FROM products WHERE category = 'Gifts'

The value sits inside single quotes, so a ' in the parameter closes the string early. Whatever follows it is parsed as SQL.

Column count and Oracle's rules

A UNION only works if both sides return the same number of columns with compatible types. The two visible fields point to two columns. The standard ways to confirm that are an ORDER BY walk (sort by column N until it errors) or a NULL probe:

# confirm two columns

-- ORDER BY walk: 2 renders, 3 errors → two columns
/filter?category=Gifts'+ORDER+BY+2--
/filter?category=Gifts'+ORDER+BY+3--

-- or a NULL probe: note the FROM dual
/filter?category=Gifts'+UNION+SELECT+NULL,NULL+FROM+dual--

Oracle's quirk. On MySQL, MSSQL and PostgreSQL, UNION SELECT NULL,NULL-- is a valid probe. On Oracle it's a syntax error, because every SELECT must have a FROM. Oracle provides the one-row dummy table dual for this. If your probes keep erroring on what should be a clean injection, that's often the reason, and it's also a fingerprint in itself.

# where the version lives

On Oracle the version strings sit in the dynamic performance view v$version, one row per component, in a single column called BANNER. It's one column, and the query needs two, so NULL fills the second slot. NULL is type-compatible with anything, which is why it's the standard filler.

One request

Putting it together: close the string, union in the banner column plus a filler, pull from v$version, and comment out the trailing quote the app appends.

$ curl -s "https://<lab-id>.web-security-academy.net/filter?category=Gifts'+UNION+SELECT+BANNER,NULL+FROM+v%24version--" \
    | grep -iE 'oracle|solved'

Two encoding details matter here:

• $ → %24. A raw $version is a shell variable expansion waiting to happen inside double quotes, and some URL handling eats it too. Encoding it gets the literal view name to the server.
• --. Oracle's line comment. It swallows the closing ' the application adds, so the statement stays valid.

The query the server ends up running:

SELECT name, description FROM products WHERE category = 'Gifts'
UNION SELECT BANNER, NULL FROM v$version--'

The banner rows come back as extra "products" alongside the real Gifts listings:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

The banner rendered, and the lab header flipped to Solved on refresh. A single request from the terminal did it, with no proxy involved.

Why it matters beyond the flag. An exact engine and version (Oracle 11.2.0.2 XE, on Linux) tells you which metadata views to enumerate next (all_tables, all_tab_columns), that string concatenation is ||, and which known CVEs and privilege-escalation paths apply to that build. Fingerprinting is the step that makes every later step targeted instead of guessed.

Detecting and fixing it

# detection

• Access logs. UNION, SELECT, v$version, dual, ORDER BY with climbing integers, or a stray ' / -- in a parameter that should only ever hold a category name. An ORDER BY 1, 2, 3… sequence from one client is column-count recon in progress.
• Database errors. A burst of ORA-00923 (FROM keyword not found) or ORA-01789 (wrong number of result columns) from the app account is someone shaping a union.
• DB auditing. A web application account selecting from v$ views is something it never needs to do. Audit it.

# remediation

• Parameterized queries. Bind category as a parameter (WHERE category = :cat) so input is always data and never SQL. This is the actual fix. Everything else is defense in depth.
• Allow-list the input. The category is one of a known, small set of values. Reject anything that isn't on the list before it gets near a query.
• Least privilege. The app's DB account shouldn't be able to read v$version or the all_* dictionary views. Revoke SELECT_CATALOG_ROLE and similar grants from it.
• Suppress verbose errors. Raw ORA- errors in responses turn blind guessing into guided guessing.