SQL injection attack, listing the database contents on Oracle
SQL Injection Attack, Listing the Database Contents on Oracle
This lab demonstrates a UNION-based SQL injection vulnerability in an application backed by an Oracle database. The objective is to identify how user input is incorporated into a dynamically constructed SQL query and progressively exploit it to enumerate database structure and extract sensitive data.
Oracle behaves slightly differently from other database systems such as MySQL or PostgreSQL. For example, it uses the dual table for selecting literal values and follows specific constraints in UNION-based queries. Understanding these characteristics is important when performing SQL injection analysis in Oracle environments.
The vulnerability in this lab arises from improper input handling, where user-supplied data is directly embedded into SQL queries without sanitization or parameterization. This allows an attacker to manipulate query structure and retrieve unauthorized data.
Observed Payloads and Testing Methodology
The following payloads were used throughout the lab to identify and exploit the injection point:
Finding 1:
' UNION SELECT NULL, NULL --
Finding 2:
' UNION SELECT 1, NULL --
Finding 3:
' UNION SELECT 1, 'text' --
Finding 4:
' UNION SELECT null, column_name FROM information_schema.columns --
Finding 5:
' UNION SELECT null, table_name FROM information_schema.tables --
Finding 6:
' UNION SELECT null, column_name FROM information_schema.columns WHERE table_name='users' --
Finding 7:
' UNION SELECT null, username FROM users --
During testing, it was also observed that Oracle-specific behavior may require valid table references such as dual for certain operations, depending on the query context. However, in this lab scenario, the injection point directly supports UNION-based payloads without requiring explicit use of dual in every case.
Understanding Oracle SQL Injection Context
Oracle databases differ from other SQL engines in several important ways. One key difference is the use of the dual table, a special one-row, one-column table used for selecting expressions without referencing real data tables.
In many Oracle injection scenarios, queries must comply with Oracle’s syntax rules, including correct column counts and valid subquery structures. However, in this lab, the injection point allows UNION SELECT operations directly, indicating that the backend query is already structured in a way that supports direct result merging.
This simplifies exploitation because the attacker does not need to rely heavily on Oracle-specific workarounds such as FROM dual in every payload.
Determining the Query Structure
The first step in the lab is identifying the number of columns returned by the original query. This is essential for constructing a valid UNION SELECT statement, as Oracle requires exact column matching between queries.
By injecting:
' UNION SELECT NULL, NULL --
the application responds normally without errors, confirming that the original query returns two columns.
This step is critical because mismatched column counts would result in an Oracle SQL error, preventing successful injection.
The use of NULL values ensures type neutrality and avoids conflicts during early testing.
Identifying Data Type Compatibility and Reflection
After confirming the column count, the next step is identifying which column supports string output. This is done by replacing NULL values with different data types.
The transition from:
' UNION SELECT 1, NULL --
to
' UNION SELECT 1, 'text' --
confirms that the second column accepts string values.
More importantly, this column is reflected in the application response, meaning injected data is directly displayed to the user. This reflection point is essential for visible exploitation, as it allows extracted database content to be observed immediately.
At this stage, the injection point is confirmed to be both structurally valid and output-relevant.
Database Enumeration Using Metadata Tables
The next phase of the lab involves enumerating database structure using metadata views. In Oracle environments, system metadata is typically accessed through internal schema views such as information_schema equivalents or Oracle-specific catalog views.
The following payloads are used:
' UNION SELECT null, column_name FROM information_schema.columns --
' UNION SELECT null, table_name FROM information_schema.tables --
These queries demonstrate that the database exposes structural metadata, allowing enumeration of tables and columns.
This indicates that the database user has excessive privileges, enabling access to schema-level information. In secure Oracle deployments, access to such metadata should be restricted to administrative roles.
Targeted Enumeration of Sensitive Tables
Once general schema information is obtained, the lab narrows focus to a specific table: users.
The payload:
' UNION SELECT null, column_name FROM information_schema.columns WHERE table_name='users' --
reveals the structure of the users table, including its column names.
This step demonstrates targeted enumeration, where attackers move from general reconnaissance to identifying specific high-value tables that may contain authentication or personal data.
In Oracle-based systems, such tables are often critical components of application authentication logic.
Data Extraction from the Users Table
The final stage of the lab validates data extraction from the identified table.
The payload:
' UNION SELECT null, username FROM users --
demonstrates that user-related data can be retrieved and displayed in the application response.
This confirms that the injection point allows direct access to database contents and that query results are not properly sanitized or filtered before being rendered.
The presence of this vulnerability indicates that sensitive user information may be exposed in a real-world scenario.
Query Behavior Reconstruction
Based on observed behavior, the backend query can be partially reconstructed as a two-column SELECT statement where at least one column is displayed in the application response.
The injected UNION SELECT statements successfully append attacker-controlled results to the original query output.
This reconstruction is derived from:
- Confirmed two-column structure
- Successful string reflection in the second column
- Ability to extract data from application tables
Understanding this structure is important because it shows how attackers infer backend logic without access to source code.
Detection and Monitoring Perspective
From a defensive perspective, this type of activity generates clear indicators in logs. These include repeated UNION SELECT attempts, access to metadata schemas, and abnormal query structures.
Security tools such as ModSecurity can help detect and block these payloads by analyzing request patterns. However, detection alone is not sufficient without secure coding practices at the application level.
In Oracle environments, database auditing and monitoring should also be enabled to detect unusual schema access patterns and repeated metadata queries.
Impact Assessment
The impact of this vulnerability is critical. It enables:
- Enumeration of database structure
- Extraction of sensitive user data
- Direct reflection of database output in responses
- Simplified attacker reconnaissance and exploitation
In Oracle-backed applications, this can lead to exposure of authentication systems, internal application logic, and potentially privileged account information.
Root Cause and Remediation
The root cause is insecure SQL query construction using unsanitized user input. The application fails to properly separate SQL logic from external input, enabling query manipulation.
Recommended remediation includes:
- Use of parameterized queries (prepared statements)
- Strict input validation and sanitization
- Restricting database permissions to minimum required access
- Limiting exposure of metadata views
- Ensuring output is properly encoded before rendering
These controls are essential to prevent UNION-based SQL injection attacks in Oracle environments.
Comments