Basically, this challenge was a SQL Injection but the flag were only shown if you were logged as the user "Administrator". What we're going to show is how to solve this challenge... unauthenticated :).
Standard Solution (Recommended):
Create an account, login as that user, spot an easy SQLi but... no tables or data with our flag :(.
One tip suggested to "login as Administrator" so let's do it. On the write-ups that I have seen, the Teams were reusing "Administrator" added by other teams or creating the account using the cool trick of adding spaces to the end of the login_name.
Another way to create an "Administrator" account was using a SQLi that was present on the register.php page. First, the page check that the user is not registered and, if this condition is met, then the user was created.
So, we have an INSERT statement, how we can use that? If you test the parameter, you'll find that the parameter 'email' is vulnerable. So, let's add a new Administrator account using the following request (observe how I use the MD5() function, as the database was storing the passwords in this format)
POST /register.php?q=944a5ae3483ed5c1e10bbccb7942a279 HTTP/1.1
Now we can login into the application and get the flag using a UNION query, as it's already explained on another writeups.
Unauthenticated Solution (or converting vuln200 into vuln400+):
So, we have a SQL injection in the register page... how can we use it? It's interesting because this can be seen in real life.
The first thought can be to use SLEEP() and you can get Binary Search with that, but I chose to use a different approach, just for fun.
- Step 1) Assume that we want to read the value of USER() on the database. How we can do it? Easy, read on byte of the response... convert to INT and then store that information in the parameter "password" using SQLi. With that, we'll have created a new user with a password that it's the first byte of the text that we want to extract.
firstname.lastname@example.org') ('name','surname','random_user_byte1',(SELECT MD5(SUBSTRING(USER(),0,1)) FROM dual),'mypassword'),'email@example.com'%23
- Step 2) Then, to obtain that byte back, we just need to bruteforce the login page with the 'random_user_byte1' account. With this account, the valid password will be the first byte of the data that we want to retrieve.
- Step 3) Repeat, wait++ and enjoy (taking care that a new we need to register a unique username on each request).
This approach is *really* slow, but it can be parallelize easily and also you can convert it to a Binary Search Algorithm (you'll make 2 request, but you won't need to wait until the SLEEP() function has returned). Seems that the CODEGATE Team knew that and (I'm guessing) that's why they added a lot of random rows on the table that was storing the flag.
So, if we want to cut off the hours required to find the flag we need to find another way... Once you have starting to download the data on the table raw_data you can see that it's a base64 string with plenty of invalid hashes. So, why we are not using the power of the SQL language to Query the database?
Something like that will be awesome:
SELECT raw_id from table where BASE64_DECODE(raw_data) like '%flag%
Sadly, as far as I know, there is no a function on MySQL like BASE64_DECODE, so no cigar this time as we cannot search on the table for bas64 strings that contains the text "flag" :(. However, if you think a bit about that, you'll find a solution for our data-mining. But I'll explain later in another post. See u!
Get the PoC code here.