This CTF brought to us an SQLite and a MySQL dump to examine. the SQLite challenge yielded a password needed to solve another challenge. The MySQL challenge involved a dump from a university called Shallow Grave University!
Past Demons
We've had a hard time finding anything on spookyboi. But finally, with some search engine finessing, an analyst found an old, vulnerable server spookyboi used to run. We extracted a database, now we need your help finding the password.
We are given a database dump, out.db but don’t know anything about it.
Let’s examine the file.
silence@mayday:~/pastdemons$ file out.db
out.db: SQLite 3.x database, last written using SQLite version 3032003
silence@mayday:~/pastdemons$ trid out.db
TrID/32 - File Identifier v2.24 - (C) 2003-16 By M.Pontello
Definitions found: 13206
Analyzing...
Collecting data from file: out.db
100.0% (.SQLITE/SQLITE3) SQLite 3.x database (15000/1)
As you can see, both file and trid agree that the file is definitely a SQLite database dump. Let’s run the SQLite 3 console to check out the dump and the .tables command to display the database’s tables:
silence@mayday:~/pastdemons$ sqlite3 out.db
SQLite version 3.32.3 2020-06-18 14:00:33
Enter ".help" for usage hints.
sqlite> .tables
passwd users
Now we know that there are only 2 tables, users and passwd. We’re looking for spookyboi’s password, remember? Let’s check the format of these tables to see how they are linked:
sqlite> PRAGMA table_info(users);
0|uid|integer|0||1
1|username|text|1||0
2|email|text|0||0
sqlite> PRAGMA table_info(passwd);
0|pid|integer|0||1
1|passwd|text|1||0
2|uid|integer|0||0
We can see that what links both tables is uid, 1st field in users and last field in passwd. Ok let’s see what we have in these tables:
sqlite> select * from users;
1|manage.po1nt|
2|carriage_5enior241|
3|s7r3am5ilver708|
4|MAL1A.PURS3LL|
5|monarch.kne3|
6|d1sp1ay.5hrink1484|
7|f1awed4unt1274|
8|spookyboi|
9|ankle_r3vive|
10|5ay_crosswalk1719|
There’s spookyboi! User 8! Let’s check the passwd table and hope we can find the same ID:
sqlite> select * from passwd;
1|4E6C0DBCCA0E45C805CE753C5974B3F9|1
2|8D302A5C9E06C8A6A52778A09583FD2C|2
3|3AC0D175A50406327CBE0BA0C6675892|3
4|6D5A4277C1F826D5EAAF08F63AEC84C5|4
5|CDF78099FBBBB52BE1AAA086D60289BC|5
6|66E85956792A2BEE9AA95B6F2662297F|6
7|EF468A19E03DDFD6D91ACF6602F71AF9|7
8|59DEA36D05AACAA547DE42E9956678E7|8
9|EB62D05A31866DFF8EC4EF28BAEF9377|9
10|2E8FF4B113C64A3C4B3F9D53AB1F0C53|10
Remember in the passwd table, uid is the last field, so looking at the “8” in the last column, we find the highlighted hash above. Let’s solve this!
I enter the hash into CrackStation and get spookyboi’s password: zxcvbnm
Address Book
Shallow Grave University has provided us with a dump of their database. Find luciafer's email address.
First things first, how are we going to access this database dump? Sure, it’s plain text and a lot of information can be found just by using grep and other tools. But if some challenges require complicated queries involving joins, this won’t be enough. So the smart thing to do is to import the database dump into mysql to be able to query data using the MySQL console or phpMyAdmin in a browser.
How to import a MySQL database:
- Start the MySQL service if it isn’t already running
silence@mayday:~/addressbook$ sudo service mysql start
- Launch the MySQL console and create the database that you will be importing the dump into. This can be any name you choose, it doesn’t need to be the same as the source database’s. After creating the database, exit the MySQL console.
silence@mayday:~/addressbook$ sudo mysql
MariaDB [(none)]> create database shallowgrave;
Query OK, 1 row affected (0.017 sec)
MariaDB [(none)]> exit
Bye
- Now all we have left to do is to import the database dump into our freshly created “shallowgrave” database:
silence@mayday:~/addressbook$ sudo mysql shallowgrave < shallowgraveu.sql
That’s it! The database is loaded into our MySQL and will be much easier to query than by parsing the dump file.
Ok back to the challenge, we need to find luciafer’s email address. Let’s see what tables we have first:
MariaDB [(none)]> use shallowgrave;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [shallowgrave]> show tables;
+------------------------+
| Tables_in_shallowgrave |
+------------------------+
| countries |
| courses |
| degree_types |
| enrollments |
| passwords |
| payment_statuses |
| programs |
| roles |
| roles_assigned |
| states |
| term_courses |
| terms |
| users |
+------------------------+
13 rows in set (0.011 sec)
From the looks of it, we’re probably going to find email addresses in the users table. Let’s check it out:
MariaDB [shallowgrave]> describe users;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| user_id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(52) | NO | UNI | NULL | |
| first | varchar(52) | NO | | NULL | |
| last | varchar(52) | NO | | NULL | |
| middle | varchar(24) | YES | | NULL | |
| email | varchar(52) | NO | UNI | NULL | |
| street | varchar(52) | NO | | NULL | |
| city | varchar(52) | NO | | NULL | |
| state_id | int(11) | NO | MUL | NULL | |
| zip | varchar(10) | NO | | NULL | |
| gender | varchar(8) | NO | | NULL | |
| dob | date | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
12 rows in set (0.010 sec)
Ok there’s an email field in this table. Let’s try to see if luciafer’s name can be found in her email, or first name, or username:
MariaDB [shallowgrave]> select * from users where email like "%luciafer%";
Empty set (0.006 sec)
MariaDB [shallowgrave]> select * from users where first like "%luciafer%";
Empty set (0.002 sec)
MariaDB [shallowgrave]> select * from users where username like "%luciafer%";
Empty set (0.003 sec)
Hmmmm…. no joy there. Let’s shorten the name a bit to “lucia”. We might get some false positives but we’ll reassess the situation if we do.
MariaDB [shallowgrave]> select * from users where email like "%lucia%";
Empty set (0.000 sec)
MariaDB [shallowgrave]> select * from users where first like "%lucia%";
+---------+------------+-------+--------+--------+-----------------------------------+--------------+------------+----------+-------+--------+------------+
| user_id | username | first | last | middle | email | street | city | state_id | zip | gender | dob |
+---------+------------+-------+--------+--------+-----------------------------------+--------------+------------+----------+-------+--------+------------+
| 49 | luchav1987 | LUCIA | HAVRON | R | luc1afer.h4vr0n@shallowgraveu.com | 2991 Y Alley | Broken Bow | 38 | 27856 | f | 1987-12-13 |
+---------+------------+-------+--------+--------+-----------------------------------+--------------+------------+----------+-------+--------+------------+
1 row in set (0.000 sec)
That must be her! We couldn’t find her name in her email address because it’s spelled with a “1” instead of an “i”!
Sure enough, this was luciafer’s email address and the solution to this 1st challenge.
Null and Void
Using the Shallow Grave SQL dump, which field(s) in the users table accepts NULL values? Submit the field name followed by the single command used to show the information (separated by a comma). Submit the flag as flag{column-name, command}.
This will be quick, let’s check how the users table is set up:
MariaDB [shallowgrave]> describe users;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| user_id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(52) | NO | UNI | NULL | |
| first | varchar(52) | NO | | NULL | |
| last | varchar(52) | NO | | NULL | |
| middle | varchar(24) | YES | | NULL | |
| email | varchar(52) | NO | UNI | NULL | |
| street | varchar(52) | NO | | NULL | |
| city | varchar(52) | NO | | NULL | |
| state_id | int(11) | NO | MUL | NULL | |
| zip | varchar(10) | NO | | NULL | |
| gender | varchar(8) | NO | | NULL | |
| dob | date | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
12 rows in set (0.001 sec)
There’s the answer to this challenge, the field is middle.
Calisota
One of our other analysts isn't familiar with SQL and needs help finding out how many users live in which states. Submit the SQL command used to get the total number of users in the users table who live in California and Minnesota. Use the file from Address Book.
This should be pretty easy. We see above that there is a state_id field in users, and that there is a table called states which we can see from when we executed the show tables query. Let’s check what the states table looks like.
MariaDB [shallowgrave]> describe states;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| state_id | int(11) | NO | PRI | NULL | auto_increment |
| state_full | varchar(128) | NO | UNI | NULL | |
| state_abbrev | varchar(32) | NO | UNI | NULL | |
| country_id | int(11) | NO | MUL | NULL | |
+--------------+--------------+------+-----+---------+----------------+
4 rows in set (0.002 sec)
Ok looks like state_abbrev might be the 2 letter state abbreviation, let’s check it out with a small sample:
MariaDB [shallowgrave]> select * from states limit 5;
+----------+----------------+--------------+------------+
| state_id | state_full | state_abbrev | country_id |
+----------+----------------+--------------+------------+
| 1 | Alabama | AL | 199 |
| 2 | Alaska | AK | 199 |
| 3 | American Samoa | AS | 199 |
| 4 | Arizona | AZ | 199 |
| 5 | Arkansas | AR | 199 |
+----------+----------------+--------------+------------+
5 rows in set (0.000 sec)
After a quick search on Google, I find that the 2 abbreviations we’re looking for are CA and MN. We have everything we need now to find the solution:
MariaDB [shallowgrave]>
select
count(*)
from
users
where
state_id in (select state_id from states where state_abbrev in ('CA', 'MN'));
+----------+
| count(*) |
+----------+
| 40 |
+----------+
1 row in set (0.018 sec)
There are 40 users in this database from California and Minnesota.
90s Kids
According to conversations found in Ghost Town, r34p3r despises 90s kids and tends to target them in his attacks. How many users in the Shallow Grave SQL dump were born in October in the 1990s?
Submit the flag as flag{#}.
Use the file from Address Book.
Max attempts: 10
This can be solved by a simple SQL query. The users table described above shows us that there is a field called dob. Let’s try this:
MariaDB [shallowgrave]>
select
count(*)
from
users
where
month(dob)=10 and year(dob) between 1990 and 1999;
+----------+
| count(*) |
+----------+
| 32 |
+----------+
1 row in set (0.015 sec)
There are many ways to find this result including using regular expressions, which I won’t do here. But here are 2 other ways:
MariaDB [shallowgrave]> select count(*) from users where substring(dob,1,3)='199' and month(dob)=10;
+----------+
| count(*) |
+----------+
| 32 |
+----------+
1 row in set (0.001 sec)
MariaDB [shallowgrave]> select count(*) from users where dob like '199_-10-%';
+----------+
| count(*) |
+----------+
| 32 |
+----------+
1 row in set (0.001 sec)
In this last one, note that I used the underscore “_” single character wildcard for the year, and a percent “%” wildcard for 0 or multiple characters fo the day of the month.
There are 32 users born in October in the 90s.
Fall Classes
Without counting duplicates, how many courses are being offered in the FALL2020 term at Shallow Grave University?
Submit the flag in the following format: flag{#}
Use the file from Address Book.
Looking at the available tables, I see terms and term_courses tables that are probably what we need to solve this challenge. But first let’s understand how they are linked:
MariaDB [shallowgrave]> describe terms;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| term_id | int(11) | NO | PRI | NULL | auto_increment |
| term_name | varchar(56) | NO | UNI | NULL | |
| start_date | date | NO | | NULL | |
| end_date | date | NO | | NULL | |
| description | varchar(128) | NO | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.002 sec)
MariaDB [shallowgrave]> describe term_courses;
+-------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+----------------+
| term_crs_id | int(11) | NO | PRI | NULL | auto_increment |
| course_id | int(11) | NO | MUL | NULL | |
| term_id | int(11) | NO | MUL | NULL | |
| instructor | int(11) | NO | MUL | NULL | |
+-------------+---------+------+-----+---------+----------------+
4 rows in set (0.002 sec)
Now we know that we need to use the term_id field to join both tables, and use distinct on term_crs_id to make sure we don’t count duplicates, as required by the challenge. By the way, don’t we need a value for term_id? Let’s check out the terms table:
MariaDB [shallowgrave]> select * from terms;
+---------+------------+------------+------------+----------------------+
| term_id | term_name | start_date | end_date | description |
+---------+------------+------------+------------+----------------------+
| 1 | SPRING2020 | 2020-04-06 | 2020-07-20 | Spring semester 2020 |
| 2 | FALL2020 | 2020-08-03 | 2020-11-20 | Fall semester 2020 |
+---------+------------+------------+------------+----------------------+
2 rows in set (0.001 sec)
Ok so we’ll be working with term_id=2 which is for the FALL2020 term.
MariaDB [shallowgrave]>
select
count(distinct term_crs_id)
from
term_courses tc
inner join
terms t
using
(term_id)
where
t.term_id=2;
+-----------------------------+
| count(distinct term_crs_id) |
+-----------------------------+
| 504 |
+-----------------------------+
1 row in set (0.000 sec)
There will be 504 courses offered during the Fall 2020 term at Shallow Grave University. Enroll now! 🙂
Jigsaw
We've slowly been piecing together the name of one of DEADFACE's future victims. Here's the information we have:
The first two characters of the user's last name begin with K, R, or I.
Followed by any character except newline, then three letters
Immediately followed by the last letter E-N
Submit the username as the flag: flag{username}
Use the file from Address Book.
Max attempts: 10
I don’t know about you, but this sounds like regex time! Fortunately we can use regular expressions directly in MySQL. Let’s do this.
The first 2 characters begin with K, R, or I. In regex syntax, this would be:
[K,R,I]{2}
The next character can be anything except a newline. This is solved with:
[^\n]
Then we have 3 letters, this would be:
[a-zA-Z]{3}
And finally we have the last letter which should be between E and N:
[E-N]
Putting this all together, we get:
^[K,R,I]{2}[^\n][a-zA-Z]{3}[E-N]$
Let’s try it out in MySQL:
MariaDB [shallowgrave]>
select
username, last
from
users
where
last regexp '^[K,R,I]{2}[^\n][a-zA-Z]{3}[E-N]$';
+----------------+---------+
| username | last |
+----------------+---------+
| image.wa1k3624 | KRYSIAK |
+----------------+---------+
1 row in set (0.001 sec)
User Krysiak is Deadface’s future victim. Run Krysiak!
Student Body
We believe Lucia is trying to target a student taught by her SOCI424 professor. How many students were taught by that professor in either term? Submit the number of students as well as the professor's first and last name concatenated.
Example: flag{666_JohnSmith}
Use the file from Address Book.
Max attempts: 10
This one gets a bit more complicated… Let’s start by finding information about the course, since we know it will be unique.
MariaDB [shallowgrave]> describe courses;
+------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------+------+-----+---------+----------------+
| course_id | int(11) | NO | PRI | NULL | auto_increment |
| title | varchar(128) | NO | | NULL | |
| level | varchar(32) | NO | | NULL | |
| description | varchar(128) | NO | UNI | NULL | |
| long_description | varchar(1024) | YES | | NULL | |
| sem_hours | int(11) | NO | | NULL | |
+------------------+---------------+------+-----+---------+----------------+
6 rows in set (0.001 sec)
MariaDB [shallowgrave]> select * from courses where title='SOCI424';
+-----------+---------+-------+----------------------------------------+------------------+-----------+
| course_id | title | level | description | long_description | sem_hours |
+-----------+---------+-------+----------------------------------------+------------------+-----------+
| 6775 | SOCI424 | 0 | SOCI424 - Sociology of Death and Dying | | 3 |
+-----------+---------+-------+----------------------------------------+------------------+-----------+
1 row in set (0.001 sec)
Ok now we know that course_id=6775, this is the class that Luciafer took. We need to know who was her teacher for that course. Let’s check the term_courses and enrollments tables.
MariaDB [shallowgrave]> describe term_courses;
+-------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+----------------+
| term_crs_id | int(11) | NO | PRI | NULL | auto_increment |
| course_id | int(11) | NO | MUL | NULL | |
| term_id | int(11) | NO | MUL | NULL | |
| instructor | int(11) | NO | MUL | NULL | |
+-------------+---------+------+-----+---------+----------------+
4 rows in set (0.001 sec)
MariaDB [shallowgrave]> describe enrollments;
+-------------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------+------+-----+---------+----------------+
| enrollment_id | int(11) | NO | PRI | NULL | auto_increment |
| user_id | int(11) | NO | MUL | NULL | |
| term_crs_id | int(11) | NO | MUL | NULL | |
| payment_status_id | int(11) | NO | MUL | NULL | |
+-------------------+---------+------+-----+---------+----------------+
4 rows in set (0.004 sec)
It looks like the term_courses table is the one that will tell us who Luciafer’s teacher was. In that table we already have the course_id (6775), the term_id is irrelevant since the challenge tells us we can ignore it, but we do need the term_crs_id and that we can get through the enrollments table. We already know from the 1st challenge that Luciafer’s user_id is 49. Let’s try joining both tables with everything we know:
MariaDB [shallowgrave]>
select
*
from
term_courses tc
join
enrollments e
using
(term_crs_id)
where
tc.course_id=6775 and e.user_id=49;
+-------------+-----------+---------+------------+---------------+---------+-------------------+
| term_crs_id | course_id | term_id | instructor | enrollment_id | user_id | payment_status_id |
+-------------+-----------+---------+------------+---------------+---------+-------------------+
| 640 | 6775 | 1 | 480 | 28778 | 49 | 2 |
+-------------+-----------+---------+------------+---------------+---------+-------------------+
1 row in set (0.001 sec)
We’ve found the teacher’s user_id which is 480. Now all we need to do is find out how many students this teacher has taught to during the 2 terms.
MariaDB [shallowgrave]>
select
count(*)
from
enrollments e
join
term_courses tc
using
(term_crs_id)
where
tc.instructor=480;
+----------+
| count(*) |
+----------+
| 122 |
+----------+
1 row in set (0.001 sec)
There we have it! The list of potential students that Luciafer is targeting is 122. And the name of the teacher is:
MariaDB [shallowgrave]> select first, last from users where user_id=480;
+--------+-----------+
| first | last |
+--------+-----------+
| CLAUDE | DARRACOTT |
+--------+-----------+
1 row in set (0.001 sec)
So there we have it, the challenge solution is:
flag{122_ClaudeDarracott}
That’s it for these SQL challenges, I hope you enjoyed them as much as I did!
Don’t forget that you can download the database dumps (in the top challenge descriptions) so that you can try the challenges on your own.
Thanks again to the whole Hacktober/CyberHacktics team for putting this event together.
As always if you have better solutions to the challenges above, please brag in the comments below, I’m all ears!
Silence Mayday 