The #26th DOJO CHALLENGE, SQLovin, aims to exploit an SQL injection in an INSERT statement by creating a payload from a limited set of characters that can be used to update another user’s email address in the database.
💡 You want to create your own DOJO and publish it? Send us a message on Twitter!
WINNERS!
We are happy to announce the list of winners of the #26 DOJO Challenge.
3 BEST WRITE-UP REPORTS
- The best write-ups reports were submitted by: Kair0s3, 0x13A0F and TXB! Congrats 🥳
The swag is on its way! 🎁
Subscribe to our Twitter and/or Linkedin feeds to be notified of the upcoming challenges.
Read on to find the best write-up as well as the challenge author’s recommendations.
The challenge
DOJO Challenge – SQLovin
We asked you to produce a qualified write-up report explaining the logic allowing such exploitation. This write-up serves two purposes:
- Ensure no copy-paste would occur.
- Determine the contestant ability to properly describe a vulnerability and its vectors inside a professionally redacted report. This capacity gives us invaluable hints on your own, unique, talent as a bug hunter.
BEST WRITE-UP REPORT
We would like to thank everyone who participated. The DOJO challenge #26 gave us a large amount of high quality reports for this DOJO challenge, well done to all of you who reported!
We had to make a selection of the best ones. These challenges allow to see that there are almost as many different solutions… as long as there is creativity! 😉
Thanks again for all your submissions and thanks for playing with us!
TXB‘s Write-Up
————– START OF TXB‘s REPORT ——————
Description
SQL injection is a type of security vulnerability that arises when an application fails to properly validate or sanitize user inputs before incorporating them into SQL queries. This allows attackers to manipulate the input data in such a way that the application unintentionally executes malicious SQL statements.
Exploitation
Context
The injection entry point is the variable $pass in the SQL Query :
INSERT INTO superbad(country, email, username, password) VALUES('Hawaii', 'McLovin@gmail.com', 'McLovin', $pass)
In this context, the complete query that we aim to execute in order to modify the McLovin user email address is :
INSERT INTO superbad(country, email, username, password) VALUES('Hawaii', 'McLovin@gmail.com', 'McLovin', 'mdp') ON CONFLICT(username) DO UPDATE set email='McLovin@gmail.com'
Filter evasion
Now to bypass the filter [0-9]|'|"|`|s which escape every numeric value, single/double/back quote and whitespaces, we need to use the char(X1,X2,..,Xn) function of Sqlite. This function returns a string composed of each Unicode code point values corresponding to the integers X1 through XN. To use this function without actual numbers we use the boolean value True which equals to 1 to encode each character by an addition of True (1) that equals it’s decimal value (Exemple “A” decimal value is 65 and it’s encoded version is an addition of 65 true : char(true+true+…+true)).
In our query, we only need to encode the ‘password value’ and the ‘MacLovin@gmail.com’ address because they contain quotes. And finally to avoid the whitespace filter we simply replace them by /**/ which will be interpreted as whitespace by SQL and we add -- at the end of the payload to comment the end of the line. This gives use our final payload :
char(true+true+true...))ONCONFLICT(username)DOUPDATEsetemail=char(true+true+true...,true+true+true+true...)
Result
After executing the payload we get the result of the query proving successful injection :
In a real context, with the email now modified to his own, an attacker could reset the password of the user and gain full access to the account.
PoC
For my Proof of Concept, here is how I proceeded.
First in the challenge we have the chance of having all SQL Queries used to create the database, this allow us to reproduce it and finding out our way in a controlled environment. To simply create SQLite databases online you can use this website sqliteonline.com
Finding the right query
The first step of the process was to find the right sql query knowing that we don’t have control over the full query but only the password variable at the end. When trying to enter a valid password we got the following error “UNIQUE constraint failed: superbad.username”. This error is cause by the UNIQUE constraint set during table creation :
This constraint acts the same way as a primary key by forbidding two entries to have the same value as we have here for the McLovin user. The SQL documentation provide us a solution with the “ON CONFLICT” extension. This clause allow us to transform our INSERT query into an UPSERT which in our case allow an UPDATE to be performed when the INSERT violate the uniqueness constraint.
In the following query when we try to INSERT the already existing McLovin username, the ON CONFLICT will trigger an UPDATE of the email column of the original McLovin user :
INSERT INTO superbad(country, email, username, password) VALUES('Hawaii', 'McLovin@gmail.com', 'McLovin', 'mdp') ON CONFLICT(username) DO UPDATE set email='McLovin@gmail.com'
Filter evasion
Now that we have the correct query to replace the email address we now have to find a with to evade the filter and inject our query.
As explained previously the filter use the regex [0-9]|'|"|`|s to escape every numeric value or single/double/back quote and whitespaces and replace them with _IfYouCanGetADigitYouCanGetACharacter, this combine with the hint SQLite(true+true+true) gave me the idea to use the char() function. This function constructs a string by converting a sequence of Unicode code point values into their corresponding characters.
Combining this function with the hint leads to construct the Unicode value of a character with an addition of true which equals to 1. All we need now is the numeric value of each character and we can now encode our forbidden characters.
To simplify the process we can use the following python script :
def string_to_addition(input_string):
addition = ""
for char in input_string:
decimal_value = ord(char)
addition += "+".join(["true"] * decimal_value) + ","
return addition[:-1]
input_string = input("String to convert: ")
result = string_to_addition(input_string)
print(result)
We use the script to encode the forbidden quotes that we need to define a password (‘mdp’) and the new email address (‘McLovin@gmail.com’) in our sql query.
Now we just have to replace the whitespaces in our query, for that we simply have to create an empty inline comment with/**/ for each whitespace and add -- at the end to comment the remaining ).
And now we have our final payload to inject in $pass: (shortened)
char(true+...+true))ONCONFLICT(username)DOUPDATEsetemail=char(true+...+true)
Risk
The risk here is that any user account could be take over by a attacker by exploiting this injection resulting in catastrophic issues in terms confidentiality of user data, integrity of these data and availability of the service for the users.
Remediation
To address this critical SQL injection vulnerability, the following measures are recommended:
- Parameterized Queries: Adopt the use of parameterized queries or prepared statements in the application’s database interactions. Parameterization ensures that user inputs are segregated from SQL queries.
- Input Validation and Sanitization: Implement robust input validation and sanitization procedures. These procedures validate user inputs for adherence to prescribed formats and data types. Non-compliant inputs should be either rejected or sanitized before being incorporated into SQL queries.
- Enforcing escape mechanisms: If parameterized queries are not possible, enforce escaping mechanisms to implement more exhaustive case.
————– END OF TXB‘s REPORT ——————
