Skip to content

Instantly share code, notes, and snippets.

Last active May 28, 2021 20:31
Show Gist options
  • Save Mykk-a/2a07baa16334dca831b165bcbb9d269e to your computer and use it in GitHub Desktop.
Save Mykk-a/2a07baa16334dca831b165bcbb9d269e to your computer and use it in GitHub Desktop.
NSEC2021 Wizard Hackademy SQL Injection Write up

Write ups

Write ups for the NorthSEC 2021 Wizard Hackademy SQL Injection challenges.


Click to expand

The admin's password is worthless.

You can bypass the login with ' union SELECT 1,"",""-- and get flag #2.

By exploiting the difference between Invalid username and Invalid password and the fact that any query results means a "valid username", with the help of a script, you can find anything in the database with a LIKE lookup.

' union SELECT tbl_name,1,1 from sqlite_master where tbl_name like "%%"--

Finds the table fl4g_1s_h3re

' union SELECT 1,1,1 FROM PRAGMA_TABLE_INFO('fl4g_1s_h3re') WHERE name like "%%"--

Find the column apprentice_flag

' union select apprentice_flag,1,1 from fl4g_1s_h3re where apprentice_flag like "%%"--

Finds the message : flag-******************************** (1/2). if you don_t already have, try to bypass the login (still with the injection).


I am by no mean a pro, simply an entousiasth working in Governance since may 2019. I've had one class on exploits and code security, as well as the opportunity to participate in a few CTF events. I also did a few challenges on websites such as Ringzer0, but am still in the very low scores. Still, I love CTFs with teams and enjoy doing easier challenges and trivias while my teamates focus on the harder challenges. It took me about 24h from when I first looked into this challenge to finaly get the two flags.

As such, I will describe my write ups with the level of detail I feel I would need to understand how to reach the solution as a lower skilled participant myself and explain my reasonning.

Challenge description

The challenge presented itself as a simple login page for the Wizard Hackademy Library, but the page to access it specified it was an SQL Injection challenge. The fields were named "Wizard username" and "password".

There were no other clues on the page that I could see.


Obviously, for any type of login page in a CTF, I explore a few of the obvious points and analyse what happens and how the page reacts.

Here are a few of my basic recon entries :

  • try to login with a random uername
  • try to login with common usernames (admin;admin, guest;guest; etc.)
  • try SQLi basics entry in both fields (' or 1 = 1 --)
  • try to crash the SQL query (')

I also analysed how the POST was sent and what were the datas sent, to see if I could get valuable informations from them. The data sent was in the form of username=[user entry]&password=[user entry]&login=login. I tried playing with the login=login by sending stuff such as bypass or whatever.

Here are my interesting findings from these explorations.

Invalid username VS Invalid password

When I tried various random usernames, the page reloaded as the login page with a red alert Invalid username. There was one exception though, when I tried admin;admin. Then the page replied Invalid password.

This was what became the most usefull issue with this loging screen, because I now knew that admin was a valid username.

This is exactly why logins and mostly any other user input related fields should give the least amount of detail possible regarding any type of error.

I of course tried a few common passwords for admin, but none of them worked as I expected, since I still hadn't exploited an SQL query or injected anything as the challenge was about.

Page crash

The very next best finding is that sending simply ' in username made the page reload as completly blank. This meant the username field was vulnerable to SQL Injection attacks. This was expected from the name of the challenge of course. It did not work in the password field though, so only my username field was going to be "usefull" for now.

The next attempt, ' or 1 = 1 -- gave me an Invalid Password response, which meant the SQL query worked enough to make a sucessfull query, but not to let me login. Since the password field was not vulnerable to this attack, this was a dead end.

But what was interesting from this little experiment is that I now had 3 possible results from the login page, either my username or my password was invalid, or my SQL query crashed the page. This will be very important in my following attacks.

Attempt 1 - This was a blind challenge to find a password

I figured that maybe, the point of this challenge was to blindly get the admin account's password.

SPOILER : it was not, but this attempt was meaningfull.

Again, I had tried to guess the password, but since it's an SQLi challenge, I had no real hope to figure it out. Also, since there were no results being shown on the page, there was no way I could try to show the password.

After much thinking, I figured I could exploit the two possible responses of the login : if the query returned a valid username, it would say Invalid Password, but what was a valid username except admin? Well, since my first SQLi replied Invalid Password but contained no username, it mean a valid username was when the query "found" something, whatever that was.

So I used the username field to validate a few assumptions that ' or username="admin"-- would work just the same as simply putting admin and return the invalid password response. I also validated that using ' or password=""-- would return an invalid username response.

Now what to do when you are not sure about the word you want to search for? ' or password LIKE "%"-- And the page replied Invalid password, which meant the query found an account for which the username was something (probably admin) and the password was something. This sounds maybe obvious for experimented hackers, and useless for those that never did SQLi, but it was critical for my understanding of how to exploit it as a low-skilled hacker.

For those who don't know, in a SQL query, the LIKE command will search for something given a pattern. The % means any number of any characters. There is also a usefull ___ to use in LIKE that means any character but only one.

So I had some fun manually trying to get the password doing stuff like "%a%" and when I found a letter, I'd try to find another one before or after it. This was way too long for anything usefull, so I had to automate this somehow.

I used python to write a simple script that would try every letter of a given alphabet in a specific spot. I could have done something more powerfull that would cycle through the letters and automatically try again when it found something, but I felt like I would spend more time making my code efficient that what time I had at this time.

Quick explanation of my very basic script It was a simple curl request with a sed (and/or grep, depending on how I felt) to try my various letters in the password and see if the page replied with Invalid Password or not. If I had Invalid Password, this meant the letter was correct. So I would add that letter in its place and continue searching for the next letter.

This way, being patient and sending a few hundred request, I found the password flabbergasted!. I immediately tried it with the username 'admin' and it replied Invalid Password.

A little research on google led me to understand that the LIKE command was not case sensitive. I tried to find a way to make it case sensitive, but did not find one. I tried guess-forcing it by trying things like "Flabbergasted!" and "FlAbBeRgAsTeD!"... I even tried it as a flag... And then I thought that it was a kind of joke with the usual FLAG that preceded most flags and tried "FLABbergasted!" and it worked and I was in! (Thinking back, I probably failed to make the search case sensitive because I did not use the proprer command for the proper engine.)

Or not. The page replied :

You have successfully logged in. Congrats, you have found the admin password... But it's worthless. Use your magic to bypass the login instead (still with the SQL injection).

And I was both very happy and angry and sad. Still, we now had an indication as to the nature of the flag.

Attempt 2 - Isn't 1 = 1 ?

Bypass had to mean somehow not using a valid or existing username and/or password, bypassing some validations.

Since the ' or 1 = 1 -- did not work, I first explored the possibility of creating a new user by using '; INSERT [...] and stuff, but nothing worked, and I quickly realised it wouldn't work, since it wouln't affect the query preceding the one I inserted. I was also realising at this time that the password field was not vulnerable to SQLi because only the first field was used in a query, which means all the necessary info was being picked up by the first query, including the password.

This led me to think about joining some results to the SELECT that would then create for that only one validation a new inexistant user. That meant inserting any result into the query directly and not into the table.

Side note about SQL which had been a pain to me in the begining, when doing a union, you must join the same number of columns as there are in the initial query and in the "right" order.

At this point, I had already been working on and off on this challenge for about 8 hours, so I came to know the users' table had a column named ID, and I guesstimated there were either 2 or 3 columns selected in the query (ID, Username, Password) and got it right in my first try, but I'll explain how I would have done if it hadn't been the case.

My test (and successfull) query was ' UNION SELECT 1,"",""-- and I was in with a flag

You have successfully logged in. Congrats, FLAG-******************************** (2/2). If you don't already have, look in the database for the other flag...

Here in this query, I simply add to the result of the first SELECT a result that was ID 1, Username blank and password blank. Since I had not put anything before my single quote and returned nothing on the "first" select, only my imaginary blank username with a blank password was "found", and the password field being also blank meant the password check would compare blank with blank. Indeed, ""="".

If I had guessed wrong in the number of colums, given the behaviour of the page, the query would have failed and the page would have returned blank. To find the right numnber of columns, I would simply try a SELECT with a various number of 1,1,1[...] until I got either an invalid username or password. the way this was setup, I could ignore the username, since it was only used to find the results and I was injecting those results in, so it would always say "invalid password" no mather the username. The password field was easier to use as simply blank and it wasn't causing any validation issues, so it was easier to inject a blank password. If there had been additionnal validations, I could have used the known creds from the previous attempt to figure out the right positions of the username and password in my SELECT (trial and errors until I find the right positions), but it was not necessary in this challenge.

Now the funny thing is that this was the second flag of the challenge and I somehow missed the first one. The flag message said to look in the database...

Attempt 3 - This is indeed a blind challenge, but to find something you don't know where you don't know.

Guessing from the message, there was a secret hidden somewhere and using my previous experience in the challenge, I knew it wasn't a user because there was no other users in the table. The message also said to look into the database and not into the table.

Previous challenges experience taught me about the information_schema.tables which lets you lookup what other tables there are in this database and information_schema.columns which lets you lookup the column names in a given table. Thing is, I am still blind and cannot simply display everything to find something interesting.

I had to go back to using my little script and a query with a WHERE something LIKE "%%". My trouble was that my queries always crashed the page and I got a blank page. This meant I was either spelling information schema wrong (which I usually do) or messed up something else (which I often do).

Things you know but forget

Though the base is generally always the same, there is many diffrent SQL services/engines/DB/whateverTheyAreCalled which all have their own little quirks. Calling for information_schema would not work unless that was the actual table's name from this specific service (Oracle, MySQL, SQLite, etc.)

This is where you must never forget that Google is your friend. I looked online for a list of different popular engines and search for all of them what was the corresponding equivalent of information_schema. Then it's just trial and error. In a non-blind challenge, there is some way to get the version or something and make it easier, but otherwise, it's trial and error.

At least I wasn't completly "blind" since finding the right command would result in the page not returning blank, so rather than blind, I should say mostly binary : yes or no, it exists or doesn't exists, it works or doesn't work.

Some trial and error later

I had a wrong password response on the SQLite command : ' union SELECT tbl_name,1,1 from sqlite_master where tbl_name like "%%"-- And from there, I could simply use my previous script to find the interesting table name. Since of course I was looking for a flag, I ignored the potential result of the users table and went right for the f as the first letter since it was a valid first letter. You could explore endlessy (but patiently) this way.

After running many times my script, I found the nice table fl4g_1s_h3re which is self explaning of course. Now I found the SQLite equivalent for the columns and went to my script again with : ' union SELECT 1,1,1 FROM PRAGMA_TABLE_INFO('fl4g_1s_h3re') WHERE name like "%%"-- This had involved some reasearch since I'd never used this before, but again, Google is your friend and learning is a major part of CTFs.

I found a nice column name in my table : apprentice_flag

So then it was a simple matter of looking for something in the interesting column of the interesting table : ' union select apprentice_flag,1,1 from fl4g_1s_h3re where apprentice_flag like "%%"-- And after running my very innefficient and amature script way too many times, I was able to construct the following message :

flag-******************************** (1/2). if you don_t already have, try to bypass the login (still with the injection).

Key points to remember

  • Google is our friend
  • Invalid username vs Invalid password means yes or no.
  • Asking enouh simple yes or no questions can give you elaborate results.
  • Every point matters, even if it takes you long to do what is a "beginner challenge", those 1, 2 or 3 points matter just as much as any other 1, 2 or 3 points of a harder track when it comes to the final scoreboard. At least, that's what keeps me motivated.

Very basic script for the curious

Coding is not my "forte"
import sys
import os

url_basic = 'curl -s "http://[9000:470:b2b5:cafe:216:3eff:feba:df67]:80/" -H "Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8" -H "Accept-Language: en-US,en;q=0.5" --compressed -H "Content-Type: application/x-www-form-urlencoded" -H "Origin: http://chal4.wizard-hackademy.ctf" -H "Connection: keep-alive" -H "Referer: http://chal4.wizard-hackademy.ctf/" -H "Upgrade-Insecure-Requests: 1" '
data_raw_begining = '--data-raw "username=[**Whatever query I was using**]'
data_raw_end = '--&password=&login=login"'
sed = ' | sed -n "29,29p" | grep "password" '

alphabets = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', '1', '2', '3', '4', '5', '6', '7', '8', '9', '0', '.', ',', '_', ' ', '-', '!','?','#','@','$','%','^','&','*','(',')','*']

keywords = []
for alpha1 in alphabets:
    letter = alpha1
    word = '"%"25'+letter+'"%"25'
    like = '"%"22' + word + '"%"22'
    command = url_basic + data_raw_begining + like + data_raw_end + sed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment