TL;DR: Breakdown of our answers to Rene Gade’s questions from the Cellebrite 2020 CTF using only free, open source tools.

Background

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 sqlite3.

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.

[notta@cuppa com.snapchat.android]$ grep -r username

Binary file databases/main.db matches
Binary file databases/feature matches
shared_prefs/user_session_shared_pref.xml:    <string name="key_username">renegade7696</string>

[notta@cuppa com.snapchat.android]$ cat shared_prefs/user_session_shared_pref.xml

<?xml version='1.0' encoding='utf-8' standalone='yes' ?>
<map>
    <string name="key_ip_based_country_code">US</string>
    <boolean name="key_needs_verification_in_reg" value="false" />
    <string name="key_display_name">Rene Gade</string>
    <string name="key_username">renegade7696</string>
    <string name="key_registration_country_code">US</string>
    <long name="key_timestamp" value="0" />
    <long name="key_birthday" value="641534400000" />
    <string name="key_bitmoji_selfie_id">10220069</string>
    <string name="key_phone">+14708001223</string>
    <long name="key_created_timestamp" value="1588272283180" />
    <string name="key_bitmoji_avatar_id">99169575610_1-s5</string>
    <string name="key_auth_token">049912B3FE3998B8C638973337CA7588</string>
    <string name="key_user_id">a4e248a2-f548-4f32-bcc9-df925ef4736e</string>
</map>

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.

[notta@cuppa Dump]$ sqlite3 data/data/com.facebook.katana/databases/prefs_db \
'SELECT value FROM preferences WHERE key LIKE "/auth/auth_device_based_login_credentials%"' \
| json_pp \
| grep username

   "username" : "andy.rod.3910"

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 joined¹. 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 (-u).

[notta@cuppa Dump]$ sqlite3 data/data/com.android.vending/databases/library.db \
"SELECT purchase_time/1000 FROM ownership WHERE doc_id='us.zoom.videomeetings'"

1588299097021

[notta@cuppa Dump]$ date -u --date=@1588299097
Fri May  1 02:11:37 AM UTC 2020

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 twice².

[notta@cuppa Dump]$ date -u \
--date=@$(sqlite3 data/data/com.android.vending/databases/library.db \
"SELECT purchase_time/1000 FROM ownership WHERE doc_id='us.zoom.videomeetings'")

Fri May  1 02:11:37 AM UTC 2020

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.

[notta@cuppa Dump]$ sqlite3 data/data/com.instagram.android/databases/direct.db \
'SELECT name FROM sqlite_schema'

android_metadata
threads
sqlite_sequence
messages
threadId
session
sqlite_autoindex_session_1
mutations

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.

[notta@cuppa Dump]$ sqlite3 data/user_de/0/com.android.providers.telephony/databases/mmssms.db \
"SELECT content FROM messages WHERE content_type LIKE 'video/%' AND address LIKE '%16104572655'"

/data/user_de/0/com.android.providers.telephony/app_parts/PART_1591209120638_IMG_0036.3gp

[notta@cuppa Dump]$ mplayer data/user_de/0/com.android.providers.telephony/app_parts/PART_1591209120638_IMG_0036.3gp

[notta@cuppa Dump]$ ls -al data/user_de/0/com.android.providers.telephony/app_parts/PART_1591209120638_IMG_0036.3gp

-rw-r--r-- 1 notta notta 650880 Jun  3 14:32 data/user_de/0/com.android.providers.telephony/app_parts/PART_1591209120638_IMG_0036.3gp

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.

[notta@cuppa Dump]$ md5sum data/user_de/0/com.android.providers.telephony/app_parts/* \
| grep a8eb9547d95f569dfde4bceded3f9867

a8eb9547d95f569dfde4bceded3f9867  data/user_de/0/com.android.providers.telephony/app_parts/PART_1597437395220

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.

[notta@cuppa Dump]$ date -u --date=\
@$(sqlite3 data/user_de/0/com.android.providers.telephony/databases/mmssms.db \
"SELECT date/1000 FROM messages WHERE content='/data/user_de/0/com.android.providers.telephony/app_parts/PART_1597437395220'") \
"+%m-%d-%Y %T"

08-14-2020 20:36:27

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 sqlite3 talk.

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.

[notta@cuppa Dump]$ sqlite3 data/user_de/0/com.android.providers.telephony/databases/mmssms.db \
"SELECT content FROM messages WHERE content LIKE '%Uber code%' ORDER BY date DESC LIMIT 1"

<#> Your Uber code: 3784. Reply STOP to unsubscribe. qlRnn4A1sbt

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 cashtag.

[notta@cuppa Dump]$ sqlite3 data/data/com.squareup.cash/databases/cash_money.db \
"SELECT name FROM sqlite_schema WHERE sql LIKE '%cashtag%'" \
&& sqlitebrowser data/data/com.squareup.cash/databases/cash_money.db

recipientConfig
cardDesign
customer
profile
activityRecipient
recipients
activityCustomer
cardStudio
cashActivity

Within the customer table, the cashtag of renegader2020 had a customer_display_name of 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

Side Rant

This was the question wherein I violated the second rule of our team: “Do justice to it.³” 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.

The Answer

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.

[notta@cuppa var]$ find | grep Marijuana
./private/var/mobile/Containers/Shared/AppGroup/327D4661-580C-4390-A240-BF497CEAB8C6/File Provider Storage/Hidden folderr/Marijuana Grow Bible.pdf

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.DocumentsApp⁴ 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.

Conclusion

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 timezone⁵ from the one you’re in now (more than just UTC), you should seriously consider using the one-liner above and using sqlite3 and date.

Footnotes