SQL Challenges

Setup Environment

Database dump is a MySql dump file, easiest way to recover it is to create database and use dump as source. Database sql file in Aurora Compromise.

└─# mariadb -u root -p
Enter password: 
...
Server version: 11.1.2-MariaDB Arch Linux
...

MariaDB [(none)]> CREATE DATABASE aurora;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> USE aurora;
Database changed
MariaDB [aurora]> SOURCE aurora.sql
...
Query OK, X rows affected (0.000 sec)
...

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)

Aurora Compromise

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
    @PatientName     := first_name,
    @PatientLastname := last_name
FROM patients
WHERE street LIKE '%Hansons Terrace%';

SELECT CONCAT(
    'flag{',
    @PatientName, ' ',
    @PatientLastname, '}'
) AS Flag;

Flag: flag{Sandor Beyer}

Foreign Keys

Created by: syyntax

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

Submit the flag as flag{#}.

Use the database dump from Aurora Compromise.

Solution

If you take a look in the given spec you can easily find (in design or data models) that table Inventory has 2 foreign keys: drug_id, facility_id.

You can also find how many foreign keys the table has with following query:

SELECT COUNT(*)
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'aurora' -- Database Name
AND TABLE_NAME = 'inventory'  -- Table    Name
AND REFERENCED_TABLE_NAME IS NOT NULL;

Flag: flag{2}

Credit Compromise

Created by: syyntax

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

Submit the flag as flag{#}.

Use the database dump from Aurora Compromise.

Solution

SELECT COUNT(*) FROM deadface_sql.billing;

Flag: flag{10391}

Starypax

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{#_firstname lastname}.

Use the database dump from Aurora Compromise.

Solution

The hardest part of this challenge for me was understanding as of Oct 20, 2023 🤣, I thought it meant before october, but meant after...

SELECT drug_id FROM drugs  WHERE drug_name = 'Starypax';
+---------+
| drug_id |
+---------+
|      26 |
+---------+

SELECT 
     p.first_name,
     p.last_name,
     pr.date_prescribed,
     pr.expiration
FROM patients p
JOIN prescriptions pr
ON   p.patient_id = pr.patient_id
WHERE 
     pr.drug_id = 26 AND 
     pr.expiration > "2023-10-20"
ORDER BY pr.expiration
;

+------------+-----------+-----------------+------------+
| first_name | last_name | date_prescribed | expiration |
+------------+-----------+-----------------+------------+
| Renae      | Allum     | 2023-06-03      | 2023-10-26 |
| Chrissie   | Hargraves | 2023-06-25      | 2023-10-28 |
| Eolanda    | Maciaszek | 2023-06-24      | 2023-10-31 |
| Rodi       | Godfery   | 2023-05-16      | 2023-11-04 |
| Chic       | Abrashkov | 2023-06-12      | 2023-11-20 |
| Appolonia  | Benda     | 2023-06-20      | 2023-11-26 |
| Lenci      | Springett | 2023-06-23      | 2023-12-19 |
+------------+-----------+-----------------+------------+
7 rows in set (0.002 sec)

Flag: flag{7_Renae Allum}

Transaction Approved

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

Use the database dump from Aurora Compromise.

Solution

SELECT COUNT(*) FROM billing WHERE exp > "2023-10";
+----------+
| COUNT(*) |
+----------+
|     8785 |
+----------+

Flag: flag{8785}

Genovex Profits

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(drugs.cost) AS "Genovex Total Income"
FROM prescriptions
JOIN drugs
ON prescriptions.drug_id = drugs.drug_id
WHERE drugs.supplier_id = ( -- Grab Supplier Dynamically
    SELECT supplier_id 
    FROM suppliers 
    WHERE supplier_name = 'Genovex'
) 
AND date_prescribed LIKE '2023%'
;

+----------------------+
| Genovex Total Income |
+----------------------+
|   19249.880025863647 |
+----------------------+

Flag: flag{$19249.88}

City Hoard

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, SUM(i.qty) AS qty
FROM deadface_sql.inventory  i
JOIN deadface_sql.facilities f
ON   i.facility_id = f.facility_id
WHERE drug_id = ( -- Grab Drug Id Dynamically
    SELECT drug_id 
    FROM deadface_sql.drugs 
    WHERE drug_name = 'Remizide'
)
GROUP BY f.facility_id
ORDER BY qty DESC
LIMIT 3
;

+---------+------+
| city    | qty  |
+---------+------+
| Miami   | 2999 |
| Tulsa   | 2996 |
| Seattle | 2992 |
+---------+------+

Flag: flag{Miami}

Order Up

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 order_num
FROM orders
WHERE prescription_id = ( -- Get Prescription Id
    SELECT prescription_id
    FROM prescriptions
    WHERE 
        doctor_id = ( -- Get Doctor Id
            SELECT staff_id 
            FROM staff
            WHERE last_name = 'Flegg'
        )
        AND
        drug_id = ( -- Get Drug Id
            SELECT drug_id
            FROM drugs 
            WHERE drug_name = 'Automeda'
        )
        AND 
        date_prescribed LIKE "%-06-%" 
);

+------------------+
| order_num        |
+------------------+
| DYP8AXK3QG9OTPWB |
+------------------+

Flag: flag{DYP8AXK3QG9OTPWB}

Counting STARs

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

From Starypax: Starypax (street name STAR) meaning we need Starypax drug.

SELECT @Starypax := drug_id # Save Drug Id
FROM deadface_sql.drugs 
WHERE drug_name = 'Starypax'
;

# Save Doctor Id
SELECT @DoctorStarypax := doctor_id, COUNT(prescription_id) prescription_count
FROM deadface_sql.prescriptions
WHERE drug_id = @Starypax
GROUP BY doctor_id
ORDER BY prescription_count DESC
LIMIT 1
;

# Save Doctor Position
SELECT @DoctorStarypaxPosition := position_name
FROM deadface_sql.positions
WHERE position_id = (
    SELECT position_id
    FROM deadface_sql.positions_assigned
    WHERE staff_id = @DoctorStarypax
)
;

# Profit
SELECT CONCAT(
    'flag{', 
    first_name, ' ', 
    last_name, ' ', 
    @DoctorStarypaxPosition, '}'
) AS Flag 
FROM deadface_sql.staff
WHERE staff_id = @DoctorStarypax
;

+---------------------------------------+
| Flag                                  |
+---------------------------------------+
| flag{Alisa MacUchadair Dermatologist} |
+---------------------------------------+

Flag: flag{Alisa MacUchadair Dermatologist}

Clean Up on Aisle 5

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

Some osint is required for this challenge. Visit Ghost Town and try to find post which matches given description.

Top -> Get after those northern lights -> lilith: Umm let me check… There are a bunch of facilities in Phoenix. I’d have to look at which ones have the most STAR.

For "aisle" and "bin" you need to refer the given spec, in the Inventory description you can find:

locator: The aisle (represented as “A”) and the bin (represented as “B”) where the drug is stored in the associated facility.

DELIMITER //

CREATE FUNCTION IF NOT EXISTS Extract_Aisle(input VARCHAR(24))
RETURNS VARCHAR(24)
BEGIN
    DECLARE aisle VARCHAR(24);
    SET aisle = SUBSTRING_INDEX(SUBSTRING_INDEX(input, 'A', -1), 'B', 1);
    RETURN aisle;
END// 

CREATE FUNCTION IF NOT EXISTS Extract_Bin(input VARCHAR(24))
RETURNS VARCHAR(24)
BEGIN
    DECLARE result VARCHAR(24);
    SET result = SUBSTRING_INDEX(input, 'B', -1);
    RETURN result;
END//

DELIMITER ;

-- Defined functions to make parsing easier

SELECT @Starypax := drug_id -- Get drug id
FROM drugs 
WHERE drug_name = 'Starypax' 
;

-- Get locator and facility id
SELECT @StarypaxFacilityLocator := i.locator, @StarypaxFacilityID := i.facility_id
FROM inventory i
JOIN facilities f
ON   i.facility_id = f.facility_id
WHERE drug_id = @Starypax -- Starypax drug id
AND  f.city = 'Phoenix'   -- City found from Ghost Town post
ORDER BY qty DESC
LIMIT 1;

SELECT CONCAT( -- Profit
    'flag{',
    @StarypaxFacilityID, '-',
    Extract_Aisle(@StarypaxFacilityLocator), '-',
    Extract_Bin(@StarypaxFacilityLocator), '}'
) AS Flag;

+-----------------+
| Flag            |
+-----------------+
| flag{412-11-44} |
+-----------------+

Flag: flag{412-11-44}

SHAttered Dreams

Created by: syyntax

DEADFACE is on the brink of selling a patient's credit card details from the Aurora database to a dark web buyer. Investigate Ghost Town for potential leads on the victim's identity.

Submit the flag as flag{Firstname Lastname}. Example: flag{John Smith}.

Use the database dump from Aurora Compromise.

Solution

Go to Ghost Town -> Top -> We got a potential buyer

lilith: I’ll let him know! I told him to put this SHA1 hash in the notes of the transaction so we have a record of what was sold: 911d1fc5930fa5025dbc2d3953c94de9e4773584 ... No I’m actually including almost the full billing and patient data. I’m just concatenating the following: card number, expiration, CCV, patient_id, patient first name, patient last name, patient middle initial, patient sex, patient email, patient address (street, city, state, zip), patient dob

SELECT 
    @BuyerFirstname := first_name AS FirstName,
    @BuyerLastname  := last_name  AS LastName,
    buyer
FROM (
    SELECT 
        p.first_name, p.last_name,
        SHA1(CONCAT( # Create Hash
            b.card_num, b.exp, b.ccv,
            p.patient_id, p.first_name, p.last_name,
            p.middle, p.sex, p.email, 
            p.street, p.city, p.state, p.zip, p.dob
        )) AS buyer
    FROM patients p
    JOIN billing  b
    ON p.patient_id = b.patient_id
) AS subquery
WHERE buyer = '911d1fc5930fa5025dbc2d3953c94de9e4773584'; # Find Buyer

SELECT CONCAT( # Profit
    'flag{',
    @BuyerFirstname, ' ', 
    @BuyerLastname, '}'
) AS Flag;

+-----------------------+
| Flag                  |
+-----------------------+
| flag{Berton Luchetti} |
+-----------------------+

Flag: flag{Berton Luchetti}