Introduction

Le 20/10/2023 j’ai pu participer au DeadFace CTF 2023 avec mon équipe Pand’hack. Nous avons fini 143ème sur 1154 équipes avec 2327 points. Je vais vous présenter les challenges que j’ai pu résoudre dans la catégorie SQL.

Set up de l’environment

Un dump SQL est disponible sur le premier challenge, le moyen le plus facile pour déployer un environment est de lancer un conteneur docker avec le dump.

sudo docker run --name mariadb-deadface -e MARIADB_ROOT_PASSWORD=deadface -e MARIADB_DATABASE=aurora -d mariadb:latest  
sudo docker exec -i mariadb-deadface mariadb -uroot -pdeadface aurora < aurora.sql
MariaDB [aurora]> show tables;
+--------------------+
| Tables_in_aurora   |
+--------------------+
| billing            |
| credit_types       |
| drugs              |
| facilities         |
| insurors           |
| inventory          |
| orders             |
| patients           |
| positions          |
| positions_assigned |
| prescriptions      |
| staff              |
| suppliers          |
| transactions       |
+--------------------+
14 rows in set (0.000 sec)

Analyse de la strucute de la base

Un modèle logique de donnée est accessible :

Challenges

Aurora Comprimise

Description du challenge

Created by: syyntax

DEADFACE has taken responsibility for a partial database hack on a pharmacy tied to Aurora Pharmaceuticals. The hacked data consists of patient data, staff data, and information on drugs and prescriptions.

We’ve managed to get a hold of the hacked data. Provide the first and last name of the patient that lives on a street called Hansons Terrace.

Submit the flag as: flag{First Last}.

Download Database Dump
SHA1: 35717ca5c498d90458478ba9f72557c62042373f

Download System Design Specification
SHA1: d6627aa2099a5707d34e26fc82bb532af6398575

Solution

SELECT first_name, last_name FROM patients WHERE street LIKE '%Hansons Terrace%';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Sandor     | Beyer     |
+------------+-----------+
1 row in set (0.006 sec)

Le flag est donc : flag{Sandor Beyer}

Foreign Key

Description du challenge

Created by: syyntax

How many foreign keys are described in the design of the inventory table?

Submit the flag as flag{number}.

Use the database dump from Aurora Compromise.

Solution

En regardant la partie Logical Database design du document fourni, nous pouvons voir que la table inventory possède 2 foreign keys.

Le flag est donc : flag{2}

Credit compromise

Description du challenge

Created by: syyntax

How many credit cards were exposed in the Aurora database hack?

Submit the flag as flag{number}.

Use the database dump from Aurora Compromise.

Solution

SELECT COUNT(DISTINCT card_num) FROM billing;
+--------------------------+
| COUNT(DISTINCT card_num) |
+--------------------------+
|                    10391 |
+--------------------------+
1 row in set (0.004 sec)

Le flag est donc : flag{10391}

Starypax

Description du challenge

Created by: syyntax

Starypax (street name STAR) is a controlled substance and is in high demand on the Dark Web. DEADFACE might leverage this database to find out which patients currently carry STAR.

How many patients in the Aurora database have an active prescription for Starypax as of Oct 20, 2023? And whose prescription expires first?

Submit the flag as flag{number_firstname lastname}.

Use the database dump from Aurora Compromise.

Solution

SELECT drug_id FROM drugs WHERE drug_name LIKE 'Starypax';
+---------+
| drug_id |
+---------+
|      26 |
+---------+
1 row in set (0.001 sec)

SELECT * FROM prescriptions WHERE drug_id = 26 AND expiration >= '2023-10-20' ORDER BY expiration;
+-----------------+------------+---------+-----------+-----------------+--------------------------------------------+---------+------------+
| prescription_id | patient_id | drug_id | doctor_id | date_prescribed | instructions                               | refills | expiration |
+-----------------+------------+---------+-----------+-----------------+--------------------------------------------+---------+------------+
|            4115 |      10042 |      26 |      1808 | 2023-06-03      | Take 200mg four times daily every 6 hours. |       4 | 2023-10-26 |
|            7710 |      12013 |      26 |      1957 | 2023-06-25      | Take 20mg once daily every 5 hours.        |       6 | 2023-10-28 |
|             311 |      10482 |      26 |      1811 | 2023-06-24      | Take 500mg once daily every 6 hours.       |       1 | 2023-10-31 |
|            4408 |      11367 |      26 |      1821 | 2023-05-16      | Take 500mg twice daily every 6 hours.      |       6 | 2023-11-04 |
|            3506 |      18086 |      26 |      1932 | 2023-06-12      | Take 20mg twice daily every 2 hours.       |       4 | 2023-11-20 |
|            3057 |      13779 |      26 |      1811 | 2023-06-20      | Take 10mg once daily every 6 hours.        |       3 | 2023-11-26 |
|            7533 |      14322 |      26 |      1912 | 2023-06-23      | Take 500mg four times daily every 2 hours. |       5 | 2023-12-19 |
+-----------------+------------+---------+-----------+-----------------+--------------------------------------------+---------+------------+
7 rows in set (0.002 sec)

SELECT first_name, last_name FROM patients WHERE patient_id = 10042;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Renae      | Allum     |
+------------+-----------+
1 row in set (0.001 sec)

Le flag est donc : flag{7_Renae Allum}

Transaction Approved

Description du challenge

Created by: syyntax

Turbo Tactical wants you to determine how many credit cards are still potentially at risk of being used by DEADFACE. How many credit cards in the Aurora database are NOT expired as of Oct 2023?

Submit the flag as flag{number}.

Use the database dump from Aurora Compromise.

Solution

SELECT COUNT(DISTINCT card_num) FROM billing WHERE exp >= '2023-10';
+--------------------------+
| COUNT(DISTINCT card_num) |
+--------------------------+
|                     8944 |
+--------------------------+
1 row in set (0.005 sec)

Le flag est donc : flag{8944}

Genovex Profits

Description du challenge

Created by: syyntax

Genovex, a pharmaceutical company, is concerned that DEADFACE will target their company based on how much money they made this year on prescriptions at the Aurora Health pharmacy. How much money did Genovex make in 2023 based on the Aurora database?

Submit the dollar value as the flag. Example: flag{$1234.56}

Note: Round to the nearest hundredths.

Use the database dump from Aurora Compromise.

Solution

SELECT SUM(d.cost) AS total_money_earned FROM prescriptions p JOIN drugs d ON p.drug_id = d.drug_id WHERE YEAR(p.date_prescribed) = 2023 AND d.supplier_id = 32;
+--------------------+
| total_money_earned |
+--------------------+
| 19249.880025863647 |
+--------------------+
1 row in set (0.002 sec)

Le flag est donc : flag{$19249.88}

City Hoard

Description du challenge

Created by: syyntax

Aurora is asking for help in determining which city has the facility with the largest inventory of Remizide based on the Aurora database.

Submit the flag as flag{city}.

Use the database dump from Aurora Compromise.

Solution

SELECT f.city, i.qty as total_quantity_in_facility
    -> FROM inventory i
    -> JOIN facilities f ON i.facility_id = f.facility_id
    -> WHERE i.drug_id = 13
    -> ORDER BY i.qty DESC
    -> LIMIT 1;
+-------+----------------------------+
| city  | total_quantity_in_facility |
+-------+----------------------------+
| Miami |                       2999 |
+-------+----------------------------+
1 row in set (0.002 sec)

Le flag est donc : flag{Miami}

Order up

Description du challenge

Created by: syyntax

Dr. Flegg prescribed Automeda to a patient in June 2023. What is the order number for this prescription?

Submit the flag as flag{order_num}.

Use the database dump from Aurora Compromise.

Solution

SELECT staff_id FROM staff WHERE last_name LIKE 'Flegg';
+----------+
| staff_id |
+----------+
|     1953 |
+----------+
1 row in set (0.001 sec)

SELECT drug_id FROM drugs WHERE drug_name = 'Automeda';
+---------+
| drug_id |
+---------+
|       9 |
+---------+
1 row in set (0.001 sec)

SELECT prescription_id FROM prescriptions WHERE drug_id = 9 AND doctor_id = 1953 AND date_prescribed > '2023-05-31' AND date_prescribed < '2023-07-01';
+-----------------+
| prescription_id |
+-----------------+
|            7170 |
+-----------------+
1 row in set (0.000 sec)

SELECT order_num FROM orders WHERE prescription_id = 7170;
+------------------+
| order_num        |
+------------------+
| DYP8AXK3QG9OTPWB |
+------------------+
1 row in set (0.001 sec)

Le flag est donc : flag{DYP8AXK3QG9OTPWB}

Counting Stars

Description du challenge

Created by: syyntax

We know DEADFACE is trying to get their hands on STAR, so it makes sense that they will try to target the doctor who prescribes the most STAR from the Aurora database. Provide the first and last name and the type of doctor (position name) that prescribed the most STAR from the database.

Submit the flag as flag{FirstName LastName Position}.

For example: flag{John Doe Podiatrist}

Use the database dump from Aurora Compromise.

Solution

SELECT doctor_id, COUNT(*) as prescriptions_count FROM prescriptions WHERE drug_id = 26 GROUP BY doctor_id ORDER BY prescriptions_count DESC LIMIT 1;
+-----------+---------------------+
| doctor_id | prescriptions_count |
+-----------+---------------------+
|      1957 |                   8 |
+-----------+---------------------+
1 row in set (0.001 sec)

SELECT first_name,last_name FROM staff WHERE staff_id = 1957;
+------------+-------------+
| first_name | last_name   |
+------------+-------------+
| Alisa      | MacUchadair |
+------------+-------------+
1 row in set (0.000 sec)

SELECT position_id FROM positions_assigned WHERE staff_id = 1957;
+-------------+
| position_id |
+-------------+
|          18 |
+-------------+
1 row in set (0.000 sec)

SELECT position_name FROM positions WHERE position_id = 18;
+---------------+
| position_name |
+---------------+
| Dermatologist |
+---------------+
1 row in set (0.000 sec)

Le flag est donc : flag{Alisa MacUchadair Dermatologist}

Clean up on aisle 5

Description du challenge

Created by: syyntax

Based on Ghost Town conversations, DEADFACE is going to try to compromise an Aurora Health pharmacy to get their hands on STAR. Turbo Tactical wants to provide security personnel at Aurora with information about which facility, aisle, and bin contains the most STAR, since it is likely what DEADFACE will target.

Provide the facility_id, aisle, and bin where the most STAR is kept in the city DEADFACE is targeting. Submit the flag as flag{facility_id-aisle-bin}.

Example: flag{123-4-8}

Use the database dump from Aurora Compromise.

Solution

Dans un premier temps, je pensais que la ville ciblée par DeadFace était Miami, mais après avoir regardé les conversations dans Ghost Town, j’ai vu que la ville ciblée était en fait Phoenix.

SELECT i.facility_id, 
LEFT(i.locator, LOCATE('B', i.locator) - 1) AS Allée,
SUBSTR(i.locator, LOCATE('B', i.locator)) AS Bac,
i.qty
FROM inventory i
JOIN facilities f ON i.facility_id = f.facility_id
WHERE i.drug_id = 26
AND f.city = 'phoenix'
ORDER BY i.qty DESC
LIMIT 1;
+-------------+--------+------+------+
| facility_id | Allée  | Bac  | qty  |
+-------------+--------+------+------+
|         412 | A11    | B44  | 2740 |
+-------------+--------+------+------+
1 row in set (0.003 sec)

Le flag est donc : flag{412-A11-B44}