How to view user data
All blog posts to date
Introduction
Android apps by default use SQLite databases to store user data. When you think Android apps, you may think about Netflix and Pandora and ask yourself why you care about user data associated with those apps. The reason you care about user data associated with apps is because nearly all user interaction with the device is user interaction with an app. Have you ever made a phone call on an Android phone? The phone dialer is an app, and that app stores call logs. Have you ever sent a text message on an Android phone? The SMS interface is an app, and the app stores text message history.
SQLite is a lite implementation of a Structured Query Language (SQL) database. I could go into detail about how SQL works and how the file is formed, but I'll skip the low level details and instead focus on Android relevant SQL information. This post will cover two main topics:
Finding a SQLite database First, Android security prohibits users from accessing the userdata partition, which is where SQLite databases storing user data associated with apps are stored. You either need an image of the device (and you can create an image using my post on live imaging an Android device) or you need a rooted device. In this post, I am working from an image of a device.
Android by default stores user data in the /userdata partition in the directory /data. The below screenshot is from a screenshot of FTK Imager looking at the data directory.
(Note: some older devices store userdata in a separate location. I've seen both older Samsung and Motorola devices that have a partition called dbdata. This partition would store user databases. But newer devices are pretty standard at this point. Look to the userdata partition in the data directory first.)
You'll see that within the data directory are directories containing package names. The directory air.WatchESPN stores user data associated with the WatchESPN app. The directory com.google.android.youtube stores data associated with the YouTube app.
In fact, let's drill into YouTube for now. The below screenshot is from the directory /(userdata partition)/data/com.google.android.youtube.
Within this directory, you'll see a directory called databases. Within the databases directory below ...
.... you can see 14 files. These files represent the databases associated with YouTube. I'm willing to bet that the file history.db contains YouTube history. I extracted this history.db file from the image and opened it in a SQLite browser (more on how to do that below), and I see a table called "suggestions" containing five columns (and in parentheses what I interpret each column to represent):
I could deep dive into a few other databaes on the device, but instead I'd rather let you, the reader, explore. So ... if you have an Android device image to explore, check out the following files:
Reading a SQLite database file
In the above section, I showed where SQLite databases may be on the device. You can find and extract a SQLite database either from a rooted device or from an image of a device. Now, how do you actually explore the database file? It is actually incredibly easy.
First, you'll need the SQLite database file extracted away from your image or you rooted device and stored on your computer in a location you will remember.
There are a few Windows and Linux applications to read SQLite databases. Autopsy also includes SQLite functionality, so if you are using Autopsy to examine an image, you can use the built-in SQLite plugin. You can read more about using Autopsy to analyze Android images on a previous post on Autopsy.
My personal favorite method of reading SQLite databases is a plugin for Firefox. I will demonstrate this plugin tool in this post, but if requested I can show other tools also. The SQLite Manager is a free download for the Firefox web browser. If you browse to this page page in firefox you can download and add the add-on to your browser.
NOTE: The SQLite Manager add-on is a developer tool. The intended audience is developers, not forensic examiners. This tool has the ability to both read and write to SQLite database files. Now we are working with an extracted version of the file. This file is extracted from an image or from a device, so the original is intact even if the file you extracted is altered. If you choose to use the SQLite Manager tool and need to prove that you have not altered data, it would be wise to create an MD5 or SHA hash of the the database file in the original image before extracting and take another hash of the extracted file on your computer after you examine it in SQLite Manager. Or, if you are in such an environment where using a non-forensic tool is unacceptable, I would personally recommend opening the image of the device in Autopsy and using the built-in SQLite reader to read your database files.
Once the SQLite Manager add-on is installed, open your Firefox browser and go to the add-ons list. The SQLite Manager will be there.
Click SQLite Manager. If you have used SQLite Manager before, you may get this annoying pop-up:
I always click on Cancel because if you click OK you open the last opened database file. Once the add-on is active, click the open icon. If you browse over the icon, the text "Connect Database hovers." In the choose file window, make sure you view all files instead of just SQLite files because most Android SQLite database files have the extension .db. Browse to where you are storing your extracted SQLite database file and open it. SQLite Manager will now show a list of all tables associated with the database. In the below screenshot, I opened /(userdata partition)/data/com.android.providers.telephony/databases/mmssms.db, which stores SMS and MMS messages.
On the left side of the interface is a clickable list of tables. Click on one. I will be exploring the table sms. The table then opens up so you can see the data stored in this database file's table.
As you can see, I've blacked out data, but there are three columns in the screenshot of interest:
SQLite Manager allows you to export a table. Check out the drop down menu Table -> Export Table. You can export the table you are currently exploring as a .CSV file, which you can open in Excel if you prefer that interface.
And that's how to explore a SQLite database. It is straightforward. Now have fun browsing around user data!
Summary
Android apps by default use SQLite databases to store user data. When you think Android apps, you may think about Netflix and Pandora and ask yourself why you care about user data associated with those apps. The reason you care about user data associated with apps is because nearly all user interaction with the device is user interaction with an app. Have you ever made a phone call on an Android phone? The phone dialer is an app, and that app stores call logs. Have you ever sent a text message on an Android phone? The SMS interface is an app, and the app stores text message history.
SQLite is a lite implementation of a Structured Query Language (SQL) database. I could go into detail about how SQL works and how the file is formed, but I'll skip the low level details and instead focus on Android relevant SQL information. This post will cover two main topics:
- The locations of SQLite databases, including a few keys ones
- How to read a SQLite database from an Android device
Finding a SQLite database First, Android security prohibits users from accessing the userdata partition, which is where SQLite databases storing user data associated with apps are stored. You either need an image of the device (and you can create an image using my post on live imaging an Android device) or you need a rooted device. In this post, I am working from an image of a device.
Android by default stores user data in the /userdata partition in the directory /data. The below screenshot is from a screenshot of FTK Imager looking at the data directory.
(Note: some older devices store userdata in a separate location. I've seen both older Samsung and Motorola devices that have a partition called dbdata. This partition would store user databases. But newer devices are pretty standard at this point. Look to the userdata partition in the data directory first.)
You'll see that within the data directory are directories containing package names. The directory air.WatchESPN stores user data associated with the WatchESPN app. The directory com.google.android.youtube stores data associated with the YouTube app.
In fact, let's drill into YouTube for now. The below screenshot is from the directory /(userdata partition)/data/com.google.android.youtube.
Within this directory, you'll see a directory called databases. Within the databases directory below ...
.... you can see 14 files. These files represent the databases associated with YouTube. I'm willing to bet that the file history.db contains YouTube history. I extracted this history.db file from the image and opened it in a SQLite browser (more on how to do that below), and I see a table called "suggestions" containing five columns (and in parentheses what I interpret each column to represent):
- _id (an auto-generated id for each entry in the database)
- display1 (the stored search suggestion based off user input)
- display2 (contains nothing)
- query (the actual text the I typed into the YouTube app to search for a video)
- date (epoch date / time stamp of the time I searched for a given video)
- _id in the above example is "1", which I'm going to say is the least recent search in this database
- display1 is "hobbit battle of five armies trailer" is the stored search suggestion based off a search I made. Apparently I was interested in seeing the Hobbit movie in December 2014 but as of my writing this I have not seen the movie
- display2 is blank
- query is the actual text, which is "hobbit battle of five armies trailer"
- date is stored as epoch time. 1406574949951 correlates to Mon, 28 Jul 2014 19:15:49 GMT, which I converted using an online epoch time stamp converter
I could deep dive into a few other databaes on the device, but instead I'd rather let you, the reader, explore. So ... if you have an Android device image to explore, check out the following files:
- /(userdata partition)/data/com.android.email/databases/EmailProvider.db
- /(userdata partition)/data/com.android.email/databases/EmailProviderBody.db
- /(userdata partition)/data/com.android.providers.calendar/databases/calendar.db
- /(userdata partition)/data/com.android.providers.contacts/databases/contacts2.db (a bit difficult to parse through this one but incredibly useful)
- /(userdata partition)/data/com.android.providers.downloads/databases/downloads.db
- /(userdata partition)/data/com.android.providers.settings/databases/settings.db
- /(userdata partition)/data/com.android.providers.telephony/databases/mmssms.db (also check out the directory /(userdata partition)/data/com.android.providers.telephony/app_parts
- /(userdata partition)/data/explore the entire com.facebook.katana/databases directory if the Facebook app is installed
- /(userdata partition)/data/explore the entire com.facebook.orca/databases directory if the Facebook Messanger app is installed
Reading a SQLite database file
In the above section, I showed where SQLite databases may be on the device. You can find and extract a SQLite database either from a rooted device or from an image of a device. Now, how do you actually explore the database file? It is actually incredibly easy.
First, you'll need the SQLite database file extracted away from your image or you rooted device and stored on your computer in a location you will remember.
There are a few Windows and Linux applications to read SQLite databases. Autopsy also includes SQLite functionality, so if you are using Autopsy to examine an image, you can use the built-in SQLite plugin. You can read more about using Autopsy to analyze Android images on a previous post on Autopsy.
My personal favorite method of reading SQLite databases is a plugin for Firefox. I will demonstrate this plugin tool in this post, but if requested I can show other tools also. The SQLite Manager is a free download for the Firefox web browser. If you browse to this page page in firefox you can download and add the add-on to your browser.
NOTE: The SQLite Manager add-on is a developer tool. The intended audience is developers, not forensic examiners. This tool has the ability to both read and write to SQLite database files. Now we are working with an extracted version of the file. This file is extracted from an image or from a device, so the original is intact even if the file you extracted is altered. If you choose to use the SQLite Manager tool and need to prove that you have not altered data, it would be wise to create an MD5 or SHA hash of the the database file in the original image before extracting and take another hash of the extracted file on your computer after you examine it in SQLite Manager. Or, if you are in such an environment where using a non-forensic tool is unacceptable, I would personally recommend opening the image of the device in Autopsy and using the built-in SQLite reader to read your database files.
Once the SQLite Manager add-on is installed, open your Firefox browser and go to the add-ons list. The SQLite Manager will be there.
Click SQLite Manager. If you have used SQLite Manager before, you may get this annoying pop-up:
I always click on Cancel because if you click OK you open the last opened database file. Once the add-on is active, click the open icon. If you browse over the icon, the text "Connect Database hovers." In the choose file window, make sure you view all files instead of just SQLite files because most Android SQLite database files have the extension .db. Browse to where you are storing your extracted SQLite database file and open it. SQLite Manager will now show a list of all tables associated with the database. In the below screenshot, I opened /(userdata partition)/data/com.android.providers.telephony/databases/mmssms.db, which stores SMS and MMS messages.
On the left side of the interface is a clickable list of tables. Click on one. I will be exploring the table sms. The table then opens up so you can see the data stored in this database file's table.
As you can see, I've blacked out data, but there are three columns in the screenshot of interest:
- thread_id (The thread, or conversation. This ID is a number which references the table "threads" in the same database)
- address (The phone number with which I am texting)
- date (The date in epoch time of the message)
SQLite Manager allows you to export a table. Check out the drop down menu Table -> Export Table. You can export the table you are currently exploring as a .CSV file, which you can open in Excel if you prefer that interface.
And that's how to explore a SQLite database. It is straightforward. Now have fun browsing around user data!
Summary
- Android stores user data from apps, including phone logs and SMS, in SQLite databases
- You need to pull SQLite files from an image of a device or a rooted device in order to see the contents
- SQLite database files can be viewed using free viewers
