SQL Challenges
Like last year, there were several SQL Challenges that build on each other and whose solutions are discussed below.
Prepare SQL
To work with the SQL files we suggest to import the backup into a MySQL DBMS, so you can connect to the database and select the infos out of it 🙂
I used a maria-db docker container. I pulled the docker image mariadb:latest and started it with
docker run --name deadfacectf -e MYSQL_ROOT_PASSWORD=password -p 3306:3306 -d mariadb:latestNow you can connect to localhost:3306 with user root and create a table for the database backup. Just google how to import the backup 🙂
Counting Heads
DEADFACE compromised a database from Eastern State University. They fired their security team, and now they're reaching out to you to see if you can help them figure out the scope of the breach. Below is a link to the compromised database.
How many users are in the database? Submit the flag as flag{#}.
Solving
This challenge is the first one. We just need to count the users in the database. Therefore we'll look at the database schema. Before you start, use the database. use your-database-name.
show tables;
## that will give you the tables
Tables_in_deadface|
------------------+
countries |
courses |
degree_types |
enrollments |
passwords |
payment_statuses |
programs |
roles |
roles_assigned |
states |
term_courses |
terms |
users |Okay lets count the entries in the users table.
SELECT COUNT(*) FROM users;
COUNT(*)|
--------+
2400|This is the flag!
flag{2400}
The Faculty
How many of the compromised users in the database are not students?
Submit the flag as flag{#}.
Use the database from Counting Heads.
Solving
Now we need to find out, which users are not students and count them as well. Therefore we should have a look into the database relations... or table relations.
Okay, lets have a look into the roles:
SELECT * FROM roles;
role_id|role_name |
-------+-------------------+
5|Adjunct Professor |
8|Administration |
4|Associate Professor|
2|Instructor |
3|Professor |
6|Research Assistant |
7|Research Associate |
1|Student |We don't need to count users with role_id=1 assigned.
With this relations in mind, we need to select all users where the assigned role is not 1. For this we need to join at least the two tables roles_assigned and users.
SELECT COUNT(*) FROM users INNER JOIN roles_assigned
ON users.user_id = roles_assigned.user_id
WHERE roles_assigned.role_id != 1;
COUNT(*)|
--------+
627|This ist the flag!
flag{627}
Let`s hash it out
DEADFACE discussed what users they were going to target out of the database dump obtained. Look around on Ghost Town and submit the password hash of the user they are targeting.
Submit the flag as flag{hash}.
Use the database from Counting Heads.
Solving
Okay then, first we need to check the ghost town forum, this was a special forum provided for the CTF.
There we will find a threat where the DEADFACE hacker are discussing the approach and which user they should focus on.
This post is the final hint!
So let's check which users have the admin role assigned.
We know, that the admin role id is 8 (previous challenge). So this query will show the id of the user.
SELECT * FROM users INNER JOIN roles_assigned ON users.user_id = roles_assigned.user_id WHERE roles_assigned.role_id = 8;
user_id|username |first|last |middle|email |street |city |state_id|zip |gender|dob |role_assigned_id|user_id|role_id|
-------+-----------------+-----+-----------+------+--------------------------------------------+------------------+------------+--------+-----+------+----------+----------------+-------+-------+
1440|nikia.manderfield|Nikia|Manderfield|T |nikia.manderfield@easternstateuniversity.com|4047 Liberty Route|Brackenridge| 45|15014|m |1957-12-04| 1440| 1440| 8|Yes - just one entry 🙂
Okay let's get the hash from the passwords table where the id is 1440.
SELECT * FROM passwords WHERE user_id = '1440';
password_id|password |user_id|
-----------+----------------------------------------+-------+
1440|b487af41779cffb9572b982e1a0bf83f0eafbe05| 1440|There is our hash.
flag{b487af41779cffb9572b982e1a0bf83f0eafbe05}
Fall Classes
How many unique Fall courses are present in the database dump? Submit the flag as flag{#}
Use the database from Counting Heads.
Solving
For this we need to count all uniq fall courses that are in the database.
Luckily there is another table term_courses where the fall semster and sommer semster is stored.
select * from terms;
term_id|term_name |start_date|end_date |description |
-------+----------+----------+----------+--------------------+
1|SPRING2022|2022-04-04|2022-07-29|Spring semester 2022|
2|FALL2022 |2022-08-01|2022-11-25|Fall semester 2022 |Okay we need to check for the courses which are assigned to term_id 2.
The table, were the courses and the terms are matched is term_courses, with this and an inner join we can do the trick.
select COUNT(distinct description) from courses
inner join term_courses on courses.course_id = term_courses.course_id
where term_id = 2;
COUNT(distinct description)|
---------------------------+
405|flag{405}
Information Security Enthusiasts
How many Fall enrollments are there in Information Systems Security (ISSC) courses? Submit the flag as flag{#}.
Use the database from Counting Heads.
Solving
For this we need two inner joins... because we need to add the infos from the table enrollments. Just count the courses where the ISSC is in the titles:
select COUNT(enrollments.term_crs_id) from courses
inner join term_courses on
courses.course_id = term_courses.course_id
inner join enrollments on
term_courses.term_crs_id = enrollments.term_crs_id
where courses.title like 'ISSC%' and term_id = 2;
COUNT(enrollments.term_crs_id)|
------------------------------+
526|flag{526}