TL;DR: Breakdown of our answers to Rene Gade’s questions from the Cellebrite 2020 CTF using only free, open source tools.
Cellebrite just finished up its first Capture the Flag (CTF) event, running from October 26, 2020 through October 29, 2020. The introductory information about our team’s participation in that event can be found here, specifically it links to many of the free, open-source tools we used, which is worth a read to understand the commands you may see below. This post focuses on the questions relating to Rene Gade, which seemed to have been set up by Cellebrite to be a masterclass on
Social Media - 10 points
What is the Snapchat username used by the device owner?
I’ll be honest, I don’t know Snapchat. I have never used it and have even less desire to do so. As a result, I don’t really have a good starting point for answering this question, so I started with a softball attempt by using recursive grep (
grep -r) and the keyword “username”. I was expecting a list of SQLite databases to troll through, but this appeared to give me the answer immediately from an XML file:
shared_prefs/user_session_shared_pref.xml. Since I didn’t know the application, I doublechecked by looking at the rest of the file to get the line in context and understand what else it held.
The right answer was “renegade7696” and we learned a new file!
User Identification - 20 points
When analyzing the device extraction, determine the Facebook username being used on this device by this user.
Unfortunately, you’ll find the term “username” in a LOT more files in the Facebook application folders if you try the previous trick, which means we’ll just have to learn some more. In this case, you might start opening SQLite databases up in SQLite Browser until something jumped out, but there’s probably a more elegant solution. The value you want happens to be saved in
data/data/com.facebook.katana/databases/prefs_db as JSON data in the value whose key is
/auth/auth_device_based_login_credentials[user id]. So maybe you open that file, click to view the blob, scroll in the tiny window to find the answer and are satisfied, or you could learn more about piping commands to each other.
One of the foundational ideas behind *nix commands is they are tiny programs that can be chained together to do a lot more. Instead of installing, say, a large forensic toolkit and having to use that one thing to do everything, you might have a program that reads an archive, one that opens files and dumps specific contents, and on that creates reports. You could chain them, using different options each time, to have it do exactly what you need each time, not waiting for a developer to add that feature to code you can touch. This is the power of piping commands (
command_1 | command_2) on the command line.
To answer this, we used
sqlite3 to run a query against the
prefs_db database to get our value, piped that output into
json_pp to print the JSON data nicely, and then piped that output to grep to give us the username. We could do the same to look for any other specific value we wanted and you could use
find to find every one of these files across datasets to execute it on all of them. But that is overkill for now, let’s just get the answer.
The answer was “andy.rod.3910” and you might want to store that chain somewhere for ease of reference next time a device has Facebook installed.
User Activity - 20 points
Provide the date the user of this device joined Zoom. Answer must be entered in MM-DD-YYYY format Use the date associated to UTC+0 timezone for this flag.
We turned to an old favorite, the Library in
com.android.vending. This tells you when the user purchased the application from the Google Play Store, so it makes it a likely guess for when they joined1. We can actually use a similar approach to the above and practice our command-line sqlite foo. After running the
sqlite3 command this time, we’ll use
date to convert our epoch timestamp into the right answer, making sure to do it in UTC (
Now, I’m lazy and like doing things in as few steps as possible. If we can get the value we want in the output of a command, that means we can feed it into another command. In the previous example we were piping the output from one command to the next, but in this case the relevant command
date won’t take the value as a pipe. What we need to do here is another powerful idea called command substitution. If you take any valid command and wrap it in
$(), when that executes it will be replaced with the value of the command. So we could take our long
sqlite3 command above and put that directly into the
date command and not have to type it twice2.
You can see in the above that the argument which
date uses to identify the epoch timestamp to convert (
--date=@...) now has the
sqlite3 statement in place of the actual numbers from the first command. This is the command substitution showing its power.
With both of these methods, the right answer is “05-01-2020”.
Database Analysis - 20 points
What is the name of the database table that contains direct messages involving the instagram user id 38106270876?
The Instagram database containing direct messages is located at
/data/data/com.instagram.android/databases/direct.db. Knowing that, you could either eyeball that database, or continue using
sqlite3. Opting for the latter, we used
sqlite3 to identify how many tables were in the database and what their names were, leading to the obvious answer.
Here we look at every value in the
name column of a special table that every SQLite database has,
sqlite_schema, which describes all the tables. Of the tables,
messages jumps out as likely being relevant and you could take a look at some of the values to make sure of it.
“Messages” was the right answer.
Files - 20 points
Ruth sent a video to Rene of a rocket launch. What is the size of the video file in bytes?
We already know Ruth’s phone number from her device, +16104572655, now we just need to know where she sent the file. I started in Rene’s SMS database
data/user_de/0/com.android.providers.telephony/databases/mmssms.db and looked for files sent by Ruth of a video type using
sqlite3. Once I had the relevant filename(s), I could watch them with
mplayer to see if they contained a rocket launch and, if so, snag the file size with
ls -al. With only one result, it was pretty easy.
We could do some fun command substitution in this one as well, but I’ll leave that as an exercise to the reader. “650880” is the right answer.
MMS Analysis - 20 points
The hash value a8eb9547d95f569dfde4bceded3f9867 is associated to a file sent to Rene Gade. What is the timestamp of the MMS message associated with this file? ANSWER MUST BE FORMATTED AS: MM-DD-YYYY HH:MM:SS - use the 24-hour clock and do not include time offset. For example, for January 16, 2020 at 10:01:52 PM, the correct answer would be: 01-16-2020 22:01:52
On Rene’s phone, the MMS files are in
/data/user_de/0/com.android.providers.telephony/app_parts/. We can calculate the MD5 sum of any file using the cleverly named
md5sum command. That command, piped into the
grep command shows the filename of the file we need.
At this point, ordinarily I would advocate for the use of
stat to give us the file creation date, however the question asks for the timestamp associated with the MMS. There could be (and turns out there is) a lag between when the MMS is sent and when the recipient downloads the file, so we need to dig deeper.
Thankfully, even if we have to dig deeper, we can return to a previous method and use
sqlite3 to identify the appropriate timestamp, then use
date to convert it into our answer. This time, however, we’re giving
date an explicit format to follow so that it does the work to generate the answer as Cellebrite wants it, using the format string “+%m-%d-%Y %T” to kick out the month (
%m), day (
%d), year (
%Y) and the standard timestamp (
%T). No, I can never remember those format strings either, keep the man page handy.
No need to ever try to convert timestamps again, or try to embed them into your SQLite queries and have to change the query depending on the format you need. Just standardize your queries on spitting out an epoch timestamp and let
date do what it was made to do.
In this case, the answer was “08-14-2020 20:36:27”.
Application Analysis - 20 points
What is the most recent Uber code received by the device?
It should come as no surprise, given that we were just looking in the MMS database, that the answer can be found there. Rather than continue to beat the dead horse that is
sqlite3, this answer would already have come from your initial understanding of the forensic image. If you didn’t take the time to read as much of the messaging as you could, you should. This could be a great place for iLEAPP to give you the nice graphical display you may want after all this
Or, you could give your eyeballs a break and just run
sqlite3 with a
LIMIT 1 to only show one result and sort it by
date DESC to get the newest. This would be really useful if you wanted to have a rough idea of the last time someone logged into an account with two factor authentication turned on. Just plug it into the date trickery from above instead of looking for the content itself.
Our answer is “3784”.
User Identification - 20 points
A ‘cashtag’ is an individual user’s Cash App username. Determine Rene Gade’s ‘cashtag’.
I didn’t know Cash App prior to this CTF so I went with the old eyeball method and used
SQLite Browser to do so. Of course, I used
sqlite3 first to narrow down the number of tables I had to look through, using the same
sqlite_schema as we looked at before, but now checking the
sql column for any columns named
Within the customer table, the
renegader2020 had a
R G which fits the mannerism of our suspect.
The answer was “renegader2020”.
Financial Information - 100 points
Rene sent Juan bank account information in a less than conventional manner. What is the Bank of America routing and account number sent to Juan? ANSWER MUST BE FORMATTED AS: routing:account (no spaces, use colon to separate the numbers provided. For example: 1234567:1234567890
This was the question wherein I violated the second rule of our team: “Do justice to it.3” All three members on our team ended up clicking on the right file for the answer on Monday and all three of us gave up when the beginning didn’t seem interesting. Here is a direct quote from yours truly in our Discord chat from 5:55 PM on October 26:
[5:55 PM] there’s mention in SMS between Rene and Juan of providign a link in another app
[5:55 PM] in telegram the same day they then discuss whether that’s the best place, and suggest signal instead
[5:55 PM] and she sends a dropbox link for the w33d book two days later
[5:55 PM] might be a red herring?
“Might be a red herring” is a fantastic way to lose 100 points. Yes, this was a CTF and yes, Cellebrite threw a lot of fun “extra” data into it, but taking a 50+ page document and determining it might be a “red herring” on page (I can’t remember, let’s say 8) and then quickly scrolling through is not analysis, it is failure. This oversight cost us 11 positions, since we finished the rest of the questions that first night and to say I am disappointed in myself for phoning it in on that file is an understatement.
While I realize this is a friendly competition, what is represented by 11 positions in a CTF could be the difference between seeing a crucial piece of evidence and not in the real world. Had there not been a question asking explicitly about Bank of America routing numbers, we would not have seen this information pass between the suspects and the knowledge of that oversight is far worse than finishing 13th.
Rant aside, how do you find the right answer to this question? You could use the same “just know the suspect” mentality that helped us with Juan’s “boat” answer. In the SMS between Juan and Rene there is a really weird line on June 22, 2020 immediately after a phone call. Rene tells Juan “Thanks for the call. The info is, MGB31PDF”, which Juan responds to with a “Like” in iMessage.
Our team had keyed in on the June 22 - June 24 time frame as having a lot of discussion between the two of them and that’s when talk of money picks up. Unfortunately, we kept looking for additional communications around that time frame, because we had already convinced ourselves that is what we were looking for. However, on June 22 Rene also downloaded the file
Marijuana Grow Bible.pdf. We had gone so far as to look for this file on the other devices and a
find piped into
grep pointed us at a “hidden folderr” on Juan’s phone as well, but we didn’t pay attention to it.
Even with all this evidence that Juan was trying to hide it and knowledge that we had gone through the communications applications on both phones really solidly, we discounted this file. Thankfully, the first rule we played by was “Click on Everything”, so that is what we reverted to and that is how even we were able to cross the finish line.
In the spirit of “Click on Everything” we started hitting every single Splashboard we could (after many other types of data). I would love to say I used some awesome command line magic, but at this point we were tired and frustrated, so we just turned to
iLEAPP. We clicked on “App Snapshots”, changed the number of entries to “All” and scrolled through literally all of them.
Once we got to August 14, 2020, we saw a SplashBoard entry for
com.apple.DocumentsApp4 which appeared to be the PDF in question and with Bank of America account details right in the center. After kicking ourselves for a good long while, we finally put two and two together and realized that we had correctly identified “MGB31PDF” as some sort of key, but where we had been expecting some sort of modern encrypted application, this was basically a good old fashioned book cipher. The screenshot in question was from page 31 of the Marijuana Growth Bible (MGB), in PDF form (MGB31PDF). Because of how the information was entered as an image, it wasn’t text searchable, hence why our recursive
grep -r for some of these keywords didn’t hit.
The answer was “121000358:9879982234471” and this was a fantastic lesson in sticking to our agreed upon rules and not letting our assumptions drive our analysis.
Rene Gade was a great device to practice and improve our command line
sqlite3 skills. If you get through this device and don’t know
sqlite3 very well, you’ve missed an awesome opportunity to add to your toolbox and it would be worth returning to try some of these commands, piping, and command substitution. In particular, if you ever find yourself changing your saved queries because the date format you need differs, or you need to see the date in a different timezone5 from the one you’re in now (more than just UTC), you should seriously consider using the one-liner above and using
Yes, I realize the user might have already joined, but this is a CTF and it seems likely they created the user account on this device when preparing the materials. ↩
Yes, you could also write the SQLite statement to do the conversion as well, but you already know that trick. ↩
The idea is, one must click on everything to truly understand, but it doesn’t matter if you click on everything if you don’t give that file a fair shake and reach understanding. ↩
The full filepath was
datewill even give you the output in a different timezone. ↩