Beginners Quest (Google CTF) - 6° Media-DB

Good afternoon! (this time is afternoon)

QUICKLY! NoEXPLANATION

https://capturetheflag.withgoogle.com

Used techniques:

• SQL Injection with UNION statement

As always, download the attachment, check the file type, rename it and extract the content:

• file 2b6cfe9b17556d78cd7142e39400f9bf711f98eefb6332811088bd11a9665523

It is a zip file.

• mv 2b6cfe9b17556d78cd7142e39400f9bf711f98eefb6332811088bd11a9665523 mediadb.zip
• unzip mediadb.zip

The content is composed of a python file media-db.py that contains the code that describes how the application at media-db.ctfcompetition.com 1337 works.

For first, type:

• nc media-db.ctfcompetition.com 1337

The application starts. Try to analyze how it works.

Then, open the media-db.py file and analyze it. You understand that this quest could be solved with SQL Injection. First of all, this application creates 2 tables: oauth_tokens (with 1 field: oauth_token) and media (2 fields: artist and song). This application shows 4 options:

1. Add song: here, if we insert a string for artist and song with " character, this character will be deleted. Then this option executes:
   
   INSERT INTO media VALUES ("{}", "{}")""".format(artist, song)
2. Play artist: here, if we insert the name of an artist, if this name is contained in the DB, the application will show the artist and the song. To extract this information from the DB, this option executes:
   
   SELECT artist, song FROM media WHERE artist = '{}'".format(artist)
3. Play song: here, if we insert the name of a song, if this name is contained in the DB, the application will show the artist and the song. To extract this information from the DB, this option executes:
   
   SELECT artist, song FROM media WHERE song = '{}'".format(song)
4. Shuffle artist: chooses songs from random artist and prints them:
   
   SELECT artist, song FROM media WHERE artist = '{}'".format(artist)

Looking these choices, the strategy to manipulate the DB could be: since the 2, 3 and 4 options read directly from DB, I can try to insert SQL command by option 1. If we see these 3 options, a good method could be to insert an artist name following by ' character and then we can use other SQL statements to get what we want from the DB.

BUT there is a problem: the options 2 and 3 have a control on the ' character because if I insert artistname' to close the value of WHERE statement, the ' character is deleted (look the .py file), but the option 4 does not have this kind of control so I can use the option 4 to execute the SQL command with our injection.

As said before, we have 2 tables, media and oauth_tokens. So, the strategy is: I insert an artist name, then close the WHERE statement with ' character and I can continue the SQL command by using the UNION statement that I can use to select other fields from the same table or another table of the DB, so I will use UNION to select the interested field of the oauth_tokens table that I guess is the table containing the oauth token as described in the quest text. Then I will close the SQL command with -- to comment all the codes/characters/words/etc after -- because I don't need of them.

Just one note: if we use the UNION statement with SELECT, the number of columns of the two SELECT statement before and after the UNION should be the same. In our case, reading the code, the first SELECT has two columns, artist and song, so also the SELECT after the UNION should be two columns.

Try to train in this website https://kripken.github.io/sql.js/GUI/ with the SQL rules.

Ok, so... 

• nc media-db.ctfcompetition.com 1337
• Select 1
• As artist insert: anycakeyouwant' UNION SELECT oauth_token, 2 FROM oauth_tokens -- (In the second SELECT I can use also any number or any "string" (with double quotes), but in this case the "string" does not work because we said that the option 1 deletes all the " character from the artist and song name, so here use just a number)
• As song insert: whatyouwant
• Select 4

In this way, the SQL command that will be executed by SQL interpreter will be:

SELECT artist, song FROM media WHERE artist = 'anycakeyouwant' UNION SELECT oauth_token, 2 FROM oauth_tokens --

So, this SQL command will read also the content of the oauth_tokens table that contains the solution key.

And also this time MY WORK IS DONE!

If you want to be SPOILED for the solution key, just click below

CTF{fridge_cast_oauth_token_cahn4Quo}