Address Book
Challenge:
Shallow Grave University has provided us with a dump of their database. Find luciafer's email address and submit it as the flag in this format:
flag{username@email.com}
Solution:
The Zip archive contained a MySQL dump file named "shallowgraveu.sql" for a database named "westridge".
-- MySQL dump 10.13 Distrib 5.7.30, for Linux (x86_64)
--
-- Host: 192.168.1.183 Database: westridge
My first order of business was to recreate the DB in a local MySQL database instance and access it for analysis.
echo "create database westridge" | sudo mysql
sudo mysql westridge < shallowgraveu.sql
$ sudo mysql -s westridge
I then reviewed the available tables to get a rough idea of the database structure:
mysql> SHOW TABLES;
+---------------------+
| Tables_in_westridge |
+---------------------+
| countries |
| courses |
| degree_types |
| enrollments |
| passwords |
| payment_statuses |
| programs |
| roles |
| roles_assigned |
| states |
| term_courses |
| terms |
| users |
+---------------------+
There isn't a table specific to email so I reviewed the fields in the users table and found an email field.
mysql> SHOW COLUMNS IN users;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| user_id | int | 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 | NO | MUL | NULL | |
| zip | varchar(10) | NO | | NULL | |
| gender | varchar(8) | NO | | NULL | |
| dob | date | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
At this point I spent awhile executing queries against both the "username" and "email" fields without any results. Eventually, I had success when performing a query for a partial match on email address. Luciafer's email address had been partially converted to "Leet Speak" with the i replaced with the digit 1, which explained the difficulty I encountered with my initial searches.
mysql> SELECT username,email FROM users WHERE LOWER(email) LIKE "luc%";
+------------+-----------------------------------+
| username | email |
+------------+-----------------------------------+
| luchav1987 | luc1afer.h4vr0n@shallowgraveu.com |
+------------+-----------------------------------+
The completed flag was flag{uc1afer.h4vr0n@shallowgraveu.com}
.
Leave a comment