Advent of CTF - Challenge 6
“Union”
Challenge
Yesterday was the first SQL Injection challenge, it is only logical to follow up with a more elaborate SQL Injection challenge, in this case a UNION SELECT challenge.
What you will learn:
- How to identify a UNION SELECT
- How to learn about other tables in a schema
- How to use substring
Solution
The challenge opens up to a search box.

Entering any values will result in various lists to be returned. In the below screenshot de result is shown for the search of a
.

The thing you will notice is that all fields are truncated. In the footer of the card it reads that due to privacy concerns the data is truncated to the first 5 characters.
Obviously the entry that has a FLAG written at the start of the line looks interesting, but it is a misdirect. It is meant to get you to figure out how to read the rest of the string.
Determine next steps
In order to determine the steps it is important to figure out what is possible with the search. One of the first things to attempt is to use a UNION SELECT. This will make it possible to retrieve arbitrary data from the database. Lets try the most basic UNION SELECT.
' UNION SELECT 1,2,3 #
The result will be that a new row is added to the results with 1, 2 and 3 added in the columns.

Solve method 1 - The long way
Getting the database
Great, so now we can start reading data from the database. As it probably is a MySQL/MariaDB server (as the last one was it), lets try reading the information schema. To speed that up, we need to know the database we are in. Lets try the DATABASE() function first.
noextra' UNION SELECT 1,DATABASE(),3 #
I added noextra
to the search to only get 1 row back.

The problem in this challenge is that only 5 characters are shown. How do we get the rest? One way to do it is to use the substr
function. With substr
it is possible to get a subset of a string, for instance character 6 through 10. Using it the rest of the string can be retrieved. In the following query the first part of the DATABASE() function is in column 2, the 2nd part in column 3.
noextra' UNION SELECT 1,DATABASE(),substr(DATABASE(),6,5) #
From the output it is clear that the name of the database is testdb
.

The information schema
Using this strategy it is possible to read out the table names in the testdb
schema. A reference of tables in the information schema can be read online.
noextra' UNION SELECT 1,table_name,substr(table_name,6,5) FROM information_schema.tables WHERE table_schema='testdb' #
The result shows 2 tables in the output. A table called flags
and one called secrets
.

Obviously the table called flags
is the most interesting, as it will contain, well, flags hopefully. Using the previous strategy it is possible to get the column names as well.
noextra' UNION SELECT 1,column_name,substr(column_name,6,5) FROM information_schema.columns WHERE table_name='flags' #
The only result from this query is description
. So in the testdb
schema there is a table called flags
that has a column called description
. Using this information a flag extractor can be created.
The flag
noextra' UNION SELECT 1,description,substr(description,6,5) FROM flags #
The output reveals that the flag is more then 10 characters long, so multiple iterations will be needed to get to the full flag.

A follow up of this query would be to apply substring in both columns, each 5 characters further.
noextra' UNION SELECT 1,substr(description,11,5), substr(description,16,5) FROM flags #
With that the challenge is solved. Be sure to also check out the badge.

Solve method 2 - The short way
If you were creative enough to not get trapped in an id column can not contain text mindset you would’ve tried the following statement:
' UNION SELECT database(),2, 3
The thing you will notice is that the 1st column can be of unlimited size. Using this approach will lead to a SQL payload that is shown in the below listing.
' union select description,2,3 from flags #
Go back to the homepage.