DeadFaceCTF2023 - SQL
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
Le fichier de dump MySql est disponible sur le premier challenge, le moyen le plus facile pour le récupérer est de lancer un container docker avec le dump.
1 |
|
1 |
|
1 |
|
Une fois cela fait, j’ai récupéré les données dans la base :
1 |
|
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
Après avoir analysé le modèle de la base de données jai pu construire ma requête SQL :
1 |
|
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
1 |
|
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
1 |
|
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
1 |
|
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
1 |
|
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
1 |
|
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
1 |
|
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
1 |
|
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.
1 |
|
Le flag est donc : flag{412-A11-B44}