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}.