Next week BackdoorCTF 2015 kicks off, and i found out they have a “practice arena” with several challenges from past CTF’s made available.
One of these challenges was one titled “2013-web-150“, which i really enjoyed doing, and allowed me to further increase my SQLi skills, so i figured i’d do a little writeup for my own reference, and maybe for anyone else learning SQLi.
Please remember this and a lot of other challenges are always available at backdoorCTF’s webpage https://backdoor.sdslabs.co so i’m not going to show the flag in this writeup, but explain how i got it.
So…heres the challenge.
The flag has to be the sha256 hash of the admin’s password (which is in md5 format) taken from a “news site”. When you click the link to the website, you are taken to the following page.
So, we have to enter a “Notice ID”, first off i just hit “Submit” with nothing in the field.
OK, so the output looks like some news about the site….and it seems like there are 2 there, both with their own title and “data” or whatever they want to say.
Going back to the first page, if i enter “1” as the notice ID, i get.
OK…so “Notice ID 1” gives us the item with the title “New Version Launched!”, which must mean if we enter the Notice ID of “2” we get…
The item with the title “Happy New Year”. So what about Notice ID “3”.
Ahh…nothing…not even an error. At this point i try a few more (4,5,6,7,8,9,10,100,1000) and get the same result, so it’s safe to assume there are probably only 2 news notices on this site.
At this point, i’m suspecting this website uses a SQL database on the backend to hold all the notices in, but we can’t be sure yet, so lets try and confirm this.
First up, the good ole “‘” trick, lets throw that into the Notice ID field, see what happens.
Hmmmn, OK, no SQL related error, but an error about an “Invalid ID”. So how about putting in “1′“….the same result.
We know there is a notice with id of “1”, but it’s not liking it when we add a “‘” after it…..how after adding a SQL comment character after it…..incase theres anything been added onto the end of the query (such as another “‘” to close the SQL statement)
Lets first try a “#” which is the comment character for a MySQL database.
Nope…same error, ok, now lets try a “—” comment, which is for a PostGreSQL or a MSSQL database.
Aha! Success, so it’s safe to assume there is a database in the backend serving the content for this site. But what sort of database? Well we’ve ruled out MySQL as the “#” comment character doesn’t work, but it could be MSSQL, PostGreSQL or even an Oracle database…..we’ll come back to this later.
Next, lets try and find out how many columns are likely in the SQL database table this query is being run against. For this, i’m going to use the following:
1' ORDER BY 1--
The ORDER BY statement is used to sort the results by one or more of the columns.
Entering this into the “Notice ID” field returns a the 1st notice OK, so we know theres at least 1 column in this table….now, change the ORDER BY number to 2…same result.
3….same result. 4…”Invalid ID”! Aha!
We can now assume that the table this statement queries has 3 columns in it.
Another good way to identify the number of columns is using a UNION SELECT statement. This statement is used to add a new SQL query onto the end of the existing one, and for this example i’m going to use the following:
1′ UNION SELECT “column1″,”column2″,”column3”–
What this is going todo, is to return the Notice with ID 1 and also return a second result with the values of “column1”, “column2” and “column3”.
Nice…it worked, the result here tells us the following:
- It’s DEFINITELY using a SQL database on the backend, as the UNION SELECT statement worked.
- There is definitely 3 columns in the table it is querying, as if we add/remove a “columnX” from the above statement we get the “Invalid ID” error.
- The PHP page only returns the values in the 2nd and 3rd column of the table (“column1” isn’t shown)
So gathered with all this info, we can now get an idea of what the SQL table that is being queried looks like:
At this stage i wanted to confirm what sort of database i was dealing with, so i had a bit of a Google search on ways to “fingerprint a database” and came across this OWASP page on the subject, and particularly the section on “String Concatenation”, which is the process of joining 2 strings together.
Say we have 2 strings of “abd” and “def” and we want to join them together to form “abcdef”.
With MSSQL you would use:
"abc" + "def"
And with PostGreSQL you would use:
"abc" || "def"
So…we can test this using a variation on the previous UNION SELECT statement.
1' UNION SELECT "column1","column2","abc" + "def"--
The result of this statement gives:
Which isn’t right….it looks like it’s tried to ADD the 2 small strings together, so lets now try it using the PostGreSQL way on concatenation.
1' UNION SELECT "column1","column2","abc" || "def"--
AHA! It worked, it joined the 2 strings together….ladies an gentleman…we have a PostGreSQL database for our backend.
WOW….this is a long post already, hope you’re still with me 🙂 Now onto the fun stuff….getting the admins password.
Now usually at this stage we would try and get various other info out of the database using commands like:
version() – Gives us further info on the SQL database in use.
SELECT datname FROM pg_database – Lists the databases running on the SQL server.
But none of these seemed to work, same for similar commands to output the table names, or column names. So i thought that the system had been configured to disallow or conceal this info, so i went with the next best solution….guessing!
As i mentioned previously, we have an “idea” as to how the table that displays all the news notices looks like, maybe we can “guess” how the table that contains users looks like, maybe something like this:
Again, i have NO idea, as i’m unable to retrieve info on the name of the tables or columns from the database, so we have to go with guesswork.
First lets try and get the table name, using the following statement:
1' UNION SELECT "column1","column2","column3" FROM members--
This is going to try and query a table named members, if successful, it will simply just display the “column2” and “column3” info like before…but if it works, it means there is a database table called “members” in the database.
Invalid ID again….so that table name was wrong, OK, how about “usernames”
1' UNION SELECT "column1","column2","column3" FROM usernames--
Same result “Invalid ID”…..how about “users”.
1' UNION SELECT "column1","column2","column3" FROM users--
Aha! It returned as we expected….so there IS a table named “users“. Now we need to try and return the information from columns, again by guessing the column names, first starting with “username“.
1' UNION SELECT "column1","column2",username FROM users--
Nope…”Invalid ID” again…how about “user”
1' UNION SELECT "column1","column2",user FROM users--
Nope same again……err….”name“?
1' UNION SELECT "column1","column2",name FROM users--
WooHoo! We are getting somewhere. We’ve just queried the users table asking for the column name and it’s outputted the usernames, which are “john” and….”admin”.
At this stage, i just want to stop (just before the good bit) and perform some “housekeeping” on the SQL query we are using. First off, lets ditch that first news notice, as we don’t care anymore….so the “1” at the beginning goes.
' UNION SELECT "column1","column2",name FROM users--
Next we know that the 1st column doesn’t get returned, so instead of “column1” we can just use the “null” character to return nothing.
' UNION SELECT null,"column2",name FROM users--
And finally, as we guessed that maybe the username was in the 2nd column, lets move “name” into there, and write what we want for the 3rd column.
' UNION SELECT null,name,"Almost there!!!" FROM users--
Much better…..so the only thing todo now….is get it to guess the password column name, and get it to output that too. Hmmnn….a column name for a password….what could that be? 😉
' UNION SELECT null,name,password FROM users--
SUCCESS!!!! (With the flags removed)
All we do now, is generate the SHA256 hash of the admins password, and enter it into the backdoorctf page.
I really enjoyed this challenge, and really learnt a lot more about SQL Injection. I encourage everyone to take part in backdoorCTF 2015 on April 2nd 2015.
And if i made a mistake….or you know how to get the version, table names or column names out of this challenge….please let me know 🙂