deadface23_shattered-dreams.md (7111B)
1 title: DEADFACE CTF 2023 Shattered Dreams Writeup 2 date: 2023-10-26 12:00 3 --- 4 > DEADFACE is on the brink of selling a patient's credit card details from the 5 Aurora database to a dark web buyer. Investigate Ghost Town for potential leads 6 on the victim's identity. 7 8 A huge hint was dropped immediately, so I went to Ghost Town to find a thread 9 titled "We got a potential buyer". 10 11 The flag's format is `flag{Firstname Lastname}`. 12 13 lilith, the original poster of the thread, said the victim's SHA1 hash we need 14 to look for is "911d1fc5930fa5025dbc2d3953c94de9e4773584" and showed how she 15 calculated that, including the (lack of) delimeter. 16 17  18 19 So, we can easily bruteforce getting this SHA1 hash by repeating what lilith 20 did. 21 22 The first three fields (card number, expiration, CCV) are values from the 23 billing table and the rest of the fields is all the fields in the patient 24 table. 25 26 ```sql 27 CREATE TABLE `billing` ( 28 `billing_id` int(11) NOT NULL AUTO_INCREMENT, 29 `patient_id` int(11) NOT NULL, 30 `credit_type_id` int(11) NOT NULL, 31 `card_num` varchar(24) NOT NULL, 32 `exp` varchar(8) NOT NULL, 33 `ccv` varchar(4) NOT NULL, 34 PRIMARY KEY (`billing_id`), 35 UNIQUE KEY `card_num` (`card_num`), 36 KEY `fk_billing_patient_id` (`patient_id`), 37 KEY `fk_billing_credit_type_id` (`credit_type_id`), 38 CONSTRAINT `fk_billing_credit_type_id` FOREIGN KEY (`credit_type_id`) REFERENCES `credit_types` (`credit_type_id`) ON DELETE CASCADE, 39 CONSTRAINT `fk_billing_patient_id` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`patient_id`) ON DELETE CASCADE 40 ) ENGINE=InnoDB AUTO_INCREMENT=14443 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; 41 ... 42 CREATE TABLE `patients` ( 43 `patient_id` int(11) NOT NULL AUTO_INCREMENT, 44 `first_name` varchar(32) NOT NULL, 45 `last_name` varchar(64) NOT NULL, 46 `middle` varchar(8) DEFAULT NULL, 47 `sex` varchar(8) NOT NULL, 48 `email` varchar(128) NOT NULL, 49 `street` varchar(64) NOT NULL, 50 `city` varchar(64) NOT NULL, 51 `state` varchar(8) NOT NULL, 52 `zip` varchar(12) NOT NULL, 53 `dob` date NOT NULL, 54 PRIMARY KEY (`patient_id`), 55 UNIQUE KEY `email` (`email`) 56 ) ENGINE=InnoDB AUTO_INCREMENT=18542 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; 57 ``` 58 59 Since there are no delimeters, they can just be concatenated with each other 60 and then piped to sha1. The difficult part I had was properly concatenating 61 those values because I was not able to read the MySQL dump with sqlite3 nor 62 mariadb. 63 64 I noticed that each of the rows that were inserted into the tables were 65 delimited by a comma, similar to CSV. 66 67 ```sql 68 INSERT INTO `patients` VALUES (8151,'Lorrayne','Covey','E','Female','lcovey0@wunderground.com','40411 Old Shore Street','Houston','TX','77201','1985-02-08'),(8152,'Eddy','Omand','S','Female','eomand1@mysql.com','454 Pine View Alley','Columbus','OH','43226','1959-09-29'),(8153,'Renard','Berre','O','Male','rberre2@friendfeed.com','3496 Merrick Center','Pittsburgh','PA','15235','1978-12-05'),(8154,'Galven','Nardrup','M','Male','gnardrup3@mac.com','0 American Road','Denver','CO','80241','1984-04-09'), ... 69 ``` 70 71 So I was able to easily convert it into a CSV with the following command: 72 73 ```bash 74 grep 'INSERT INTO `patients`' aurora.sql \ 75 | sed 's/^INSERT[^(]*//' \ 76 | awk '{gsub(/,\(/, "\n"); gsub(/\)*/, ""); print}' 77 ``` 78 79 Part of the output is now: 80 81 ``` 82 ...18532,'Becka','Hurlin','T','Female','bhurlin80d@yolasite.com','58 Amoth Way','Ventura','CA','93005','1953-11-14' 83 18533,'Aluin','Horwell','O','Male','ahorwell80e@cbc.ca','3 Oriole Terrace','Miami','FL','33190','1984-06-24' 84 18534,'Glennis','Walder','R','Female','gwalder80f@cnet.com','966 Packers Hill','Topeka','KS','66617','1950-01-21' 85 ... 86 ``` 87 88 One problem I had when I used tr to replace ( with \n was that one of the names 89 had () in their name for some reason, which messed up the concatenating of the 90 two files to get the hash. I originally just manually edited it, but the above 91 with awk is cleaner. The same goes with the 'INSERT INTO ...' part messing things up for the same reason. I didn't save my ~/.ksh_history file with the 92 commands I ran, so this is a non-ugly version I remade with more awk. 93 94 To properly concatenate the data fields, the comma and single quotes should 95 also be removed, which tr can be used for unlike before. 96 97 ```bash 98 grep 'INSERT INTO `patients`' aurora.sql \ 99 | sed 's/^INSERT[^(]*//' \ 100 | awk '{gsub(/,\(/, "\n"); gsub(/\)*/, ""); print}' \ 101 | tr -d "'," >patients_.txt 102 ``` 103 104 Part of the output prior to being written to a file is now: 105 106 ``` 107 18527TannerMasselinAMaletmasselin808@google.es10080 Reindahl CourtBoca RatonFL334871957-08-15 108 18528MerrelYeudeDMalemyeude809@ca.gov7934 Katie PassSaint PaulMN551881951-04-07 109 18529JeffVan BaarenMMalejvanbaaren80a@sphinn.com58097 Autumn Leaf DriveNew OrleansLA701421984-01-13 110 ``` 111 112 It is written to a file so that it can be easy to concatenate both the billing 113 and patient data by using the paste command. 114 115 The next part is doing the same with the billing data. Unlike with the patient 116 data, only three fields from the billing table is used instead of all, so cut 117 or awk can be used with the delimeter set to a comma. 118 119 Before that, we need to know what index (base 1) the three fields are at. The 120 credit card number, expiry date, and ccv are fields 4, 5, and 6. 121 122 ```bash 123 grep 'INSERT INTO `billing`' aurora.sql \ 124 | sed 's/^INSERT[^(]*//' \ 125 | awk '{gsub(/,\(/, "\n"); gsub(/\)*/, ""); print}' \ 126 | cut -d, -f4,5,6 \ 127 | tr -d "'," >billing_.txt 128 ``` 129 130 Some of the output looks like 131 132 ``` 133 51087507745678202025-01403 134 50483770976260922023-07242 135 50483739134688352023-12501 136 ``` 137 138 Both tables with the fields have been parsed and saved to two different files, but simply using cat on both the files would simply print the contents of the second file after the first file has finished printing, but we need each line of both the files to be joined together! That's where the paste command comes in. It combines each line of its input files, which is exactly what is required. 139 140 These merged lines are then fed into sha1, and finally grep can be used to look for the hash we need and print the victim's name if it shows up. 141 142 ```bash 143 target_hash="911d1fc5930fa5025dbc2d3953c94de9e4773584" 144 145 paste billing_.txt patients_.txt | tr -d '\t' \ 146 | while read line; do 147 echo -n "$line" | sha1 \ 148 | grep "$target_hash" && echo "$line" && break 149 done 150 ``` 151 152 The outputted line of the victim who had the same hash is: 153 154 ``` 155 50483743238485412026-0498316314BertonLuchettiXMalebluchetti6ar@taobao.com39 Meadow Ridge TerraceClevelandOH441251964-10-29 156 ``` 157 158 So, the victim is Berton X. Luchetti, and the flag is `flag{Berton Luchetti}`. 159 160 This challenge would probably have been easier if I was able to use proper SQL 161 commands, but I couldn't do that and standard UNIX tools saved the day. I did 162 the exact same process of parsing the tables for all the other SQL challenges 163 and I found it funny that I solved all of them without needing to run a single 164 SQL command (partly because they didn't load the file). 165 166 ```