Android’s external.db – Everything Old Is New Again

“Sooner or later, everything old is new again.” – Stephen King, The Colorado Kid

I have a really bad DFIR habit: sometimes I get overly excited when searching through my test images and find something new and shiny I haven’t seen before, and then it ends up just being new to me…

…which is what happened here. While poking through my Android 11 image, I stumbled across a folder that had a database in it that was keeping tracks of files stored in the emulated storage area of the file system on my Pixel 3. File names, file paths, information about parent directories, created timestamps, modified timestamps, file types, and the list goes on. It is very $MFT-like.

As it turns out, this database, external.db, is not new, and I would have known that had I looked at the SANS 585 poster  :facepalm:. And, if I had caught Paul Lorentz on his Life Has No Ctrl+Alt+Del episode where he discusses external.db, I would have also known some of this. There are, however, some nuances to this database (entries correlate to the Files by Google app) and there is at least one new thing about it, so I thought I would write about it anyway.

For testing I used my Android 11 public image along with some test data I created using the same Pixel 3 on Android 11.  The phone was rooted.

Keeping Track

external.db has been around for a while, and lives in one of two places depending on what version of Android you are examining, with the determining factor being whether or not you’re examining pre-Android 11 or Android 11 (and presumably future versions of Android). I threw in “future versions of Android” because external.db, as of Android 11, has been made part of Project Mainline. For those that are not familiar, Project Mainline is Google’s attempt at being able to update core Android system components quickly without having to wait on OEMs, SoC manufacturers, or carriers to test them before deployment. This allows for more timely updates (think more timely security updates) and helps alleviate some of the fragmentation that still plagues the Android ecosystem. Google modularized 13 components of Android starting with Android 10, and added another 12 with Android 11. If you’re interested in how this all works, read here and here. The takeaway is that these modules are a required part of Android, so expect to see them on any handsets going forward that uses Google Mobile Services (GMS).

One of the required modules is com.google.android.providers.media.module, which is where external.db now resides in Android 11; it previously lived in com.android.providers.media. The reason this database is important is Android’s Scoped Storage feature. Scoped Storage was introduced in Android 10, and its aim is to de-clutter phone storage, and keep apps from accessing data from other apps. Scoped storage is optional for apps that target Android 10 or lower, but once an app is updated for Android 11, it is required. Apps will need somewhere to keep track of what user data it stores, where it stores it, what other data it has access to, and external.db is the right database for the job.

The database is found in the ./databases folder.  See Figure 1.

Notice there is a -wal file associated with it, so if you’re needing to examine it make sure you take proper precautions not to trample it. I have not been able to determine when the -wal data is written to the main database, but I did discover a phone reboot will trigger it. Obviously, that isn’t always an option, so make sure you capture the -wal and handle with care. I do know Cellebrite Physical Analyzer’s SQL Wizard can run queries against a database taking the -wal into account.

A Deep Dive

If you do Windows forensics you should be familiar with the $MFT. Here is a snippet of how Microsoft describes it:

“All information about a file, including its size, time and date timestamps, permissions, and data contents, is stored either in MFT entries, or in space outside the MFT that is described by MFT entries.”

If you were to remove the part about resident files and permissions, the definition could apply to external.db. I spend a lot of my time at work looking at output from Eric Zimmerman’s MFTECmd utility, so when I opened external.db things looked very familiar: file names and attributes. See Figure 2 for the top of external.db.

The table I am discussing in this post is files, although there are many others in this database. The _data column corresponds to paths of files and folders, and _size is self explanatory. The format column is interesting. One of the things this database does is that it categorizes files, and the format column documents the type of each file, which seems a bit redundant as you can look at the file extension to figure out the file types…or can you (more on this later)? The codes I have observed and their corresponding file type are as follows:

12288: file (not in one of the following categories)
12289: folder
14336: picture
47360: audio
47488: video

There is another column, media_type (not pictured), that is similarly grouped, although the codes are different:

0 = data (file or folder)
1 = picture
2 = audio
3 = video

If you use or are familiar with the Files by Google app, or any file management-type app for that matter, these categories should look familiar. Figure 3 is from the Files by Google app on my Pixel 3 running Android 11, Figure 4 is from the Files app on my OnePlus 7T running Android 10, and Figure 5 is from the File Manager app (OnePlus’ file management app) on the same OnePlus 7T.

The categories in the apps almost align 1:1 to the categories in external.db. Document files appear to be grouped with files that don’t fall in the audio, picture, or video categories.

The parent column refers to the parent directory for the entry. For example, see Figure 6. The parent for /storage/emulated/0/Download/boot.img (_id 18 – blue box) is /storage/emulated/0/Download (_id 11 – red box). Folders also have parent entries.

The date_added column has timestamps (in Unix Epoch) for when a file was added to the device. Interestingly, not all files have a timestamp in this column. Along the same lines, the date_modified column will contain date last modified; however, not all files have a date_modified value. So, while some entries have data in both columns, there are many that have data in one column only.

I will discuss modification dates later in the post.

As a side note, if you look at the lone timestamp in the database entry for /storage/emulated (_id 2) it converts to 2020-09-11 at 18:59:34 (-0400), which is the time the phone first booted into the OS after I flashed it. See Figure 7.

The column mime-type is self-explanatory, and just adds an extra level of categorization to files. The final column of note is _display_name. This column contains the name of files as they appear to the user.

Where a file is stored on external storage is totally dependent on the app. Some files are categorized and stored as you would expect (a picture stored in the Pictures folder), but others are not so clear. A prime example of this is the Imgur app. During the data generation period I downloaded a few pictures and one GIF via the Imgur app. See Figure 8.

As can be seen, the files were stored in the /storage/emulated/0/Download/Imgur folder. Many of the apps from which I saved files were stored in the parent of the Download folder (/storage/emulated/0). Because Imgur created its own folder in the Download folder, pictures I had downloaded appeared in the Download folder in the Files app See Figure 9.

The takeaway here is that apps may store data in places that may not necessarily be “common sense.”

Traversing across the database entries for the files finds the area seen in Figure 10.

In the blue box in Figure 10 is the column owner_package_name, which contains data about what package “owns” the file (i.e. the package that wrote the file to disk). In this instance, it is Imgur. Sometimes, the file path can give some indication about the file owner, but at other times the file owner may not be so clear. For example, if the entry was for a downloaded document, the owner could be a web browser. What happens if there are multiple browsers on the device? The owner_package_name can help with such a scenario.

Speaking of downloaded files, the column in the red box, is_download, can indicate if the file was downloaded. The codes are simple: 0=no and 1=yes. Entries for files downloaded by a browser also contain the URI for the downloaded asset (the file) and the referring URI, which are seen in the orange and red boxes in Figure 11, respectively.

This entry is for a paper on the DFRWS DFIR Review website submitted by Ryan Benson on TikTok Timestamps. You can see the URI for the paper and the URI for the website, and the fact that it was downloaded via Chrome (owner_package_name).

Deleted or Are They Hidden?

If you have gotten this far I bet one question has crossed your mind: “Does this keep track of deleted files?” The short answer is “not really.” During testing, any file I deleted via the Files app was immediately deleted from the database; there was no deleted file “purgatory” (i.e. a Recycle Bin). It’s always possible there is data stored in the database free pages, but I wasn’t able to test that as my tools are a bit limited in that aspect. There was, however, one exception: pictures and the Google Photos app.

Google Photos has had a Trash function for a while now (five years I think), but back in June of this year, it got an upgrade which would allow Photos to notify a user when items would be deleted from the Trash, and thus, permanently deleted. The “Trash” folder is accessible in the Photos app, but you will not see it in the Files app. Pictures deleted via the Photos app, though, will appear in external.db if it has been been downloaded to the local device, and it is fairly easy to pick them out of the database. See Figure 12.

In the red box you can see the paths for the deleted pictures. Notice the file names have been prepended with .trashed- and an Epoch timestamp. Remember the timestamp because it will be seen again. For now, see Figure 13.

If you’re familiar with *nix operating systems you will know exactly why the trashed files are not appearing in Figure 11: they are dot files. Since Android is based on Linux, the dot at the beginning of the file tells Android not to display the file. If you were to run the command “ls” with the flags “-la” then you would see what is in Figure 14.

The prepended timestamps are actually the expiration date of the file, that is, the time it will be deleted from the Trash in Photos. Traversing further into the external.db entries for this he files finds the same Epoch timestamps in the date_expires column, and an indicator that the files are trashed in the is_trashed column (value of 1). See the red box in Figure 15.

Pictures in the Trash expire 60 days after they are there, so an examiner could easily deduce when the picture was placed in the Trash by looking at either the prepended timestamp or the timestamp in the date_expires column.

“Secure”

Just like Samsung, OnePlus, and other OEMs who have file explorer solutions, Google has a “secure” folder in Files by Google. In the app, there is a feature called “Safe folder.” See Figure 16.

Files in the Safe Folder operate in a similar fashion to the deleted files in Photos, but with a twist. See Figure 17.

A few things happen when placing a file in this folder. The original name of this file was floppy.png, but you do not see that here. The Files by Google app changed the name of the file to a GUID and prepended the file name with a dot, making this a dot file. Additionally, it placed it in a folder that is a dot folder, and placed the .nomedia file in the directory, which is an Android feature that tells file viewers to ignore the media files contained within the folder where .nomedia resides.

It also deleted floppy.png. A new file create date was given to the new file in lieu of merely giving it a new file modification time. So, while the date on which the file was placed into the Safe Folder is known, there would be no way of determining the original file name or the original creation date of the original file.

And, one more thing:  it encrypted the file in the Safe Folder. Take a look at Figure 18.

The file header fgsf, I believe, is short for Files by Google Safe Folder. I did not spend more than a few minutes on this, but I was unable to open the file in anything other than Files by Google app. While the file and original file name are not available, an examiner can at least determine that files are present.

File Modifications

The good news is file modifications work the same way as those in Microsoft Windows. Merely changing a file name or extension does not update the date_modified field. A change to the file itself must occur. See Figures 19 and 20.

I merely converted this picture to black and white, seen in Figure 20, and the date_modified timestamp was updated as seen in Figure 21.

Evasion Technique

One of the things I wanted to test was how well the files in external.db were categorized. So, I took a picture, renamed it including changing the file extension from .jpg to .txt, and then pushed it to the device over ADB. The file is seen in Figure 22 and a hex view of it is seen in Figure 23.

The resulting database entry can be seen in Figure 24.

It appears Android is merely taking the file’s word for it that it is a text file (i.e. only looking at the file extension). I can see two problems here: one, this is an OK way to hide files. Changing files extensions in an attempt to hide data is an obfuscation technique that is as old as dirt, but many people still do it.

The second problem: this could be a potential infection vector for mobile malware. Disguising a file as picture, for example, when it’s really an APK could be a great way to infect an Android device. A user downloads or receives via email what they think is a picture, when in reality it is malicious APK. A user wants to open the picture, so they will click through whatever they need to to open it, including granting that picture permission(s) (what picture needs permissions???) so they can view it. Instead, the malicious APK file is granted the permission(s) it needs to conduct its activities.

Figure 25 shows the database entry after changing the file extension back to .jpg via the Files by Google UI.

A couple of things to note here. First, the format column did not update; it still shows 12288 (a file) and not 14336 (a picture). Second, the date_modified field is now populated and the timestamp pre-dates the date_added field. There was no date in the EXIF data in this file, so where is 2020-09-24 14:35:27 (then Epoch timestamp in date_modified) coming from? As it turns out, the file system date modified timestamp for this picture on my Mac Mini bled over when I pushed the file to the phone over ADB, and was, hence, populated in the date_modified column as seen in Figure 26. My only question is why the date_modified timestamp did not appear when this file was originally pushed to the phone as a text file. This was an interesting find and I would have to do more testing in order to determine what exactly caused this. Just be aware that if you see timestamps that do not make sense or the format or media_type codes do not match the file extension, as here, then you should probably scrutinize the file(s) further.

Conclusion

While external.db is not new, it was worth exploring, especially now that it is part of Android’s Project Mainline. There are some interesting bits to it, and a few nuances that examiners should be aware of, but if examiners have conducted Windows forensics at all, it should be fairly easy to understand. As a plus, external.db can also give some insight to hidden files stored by apps, which is always a good thing.

In his customary fashion, Alexis Brignoni has already updated his ALEAPP tool to parse external.db.  Go check it out!