Pages

Monday, January 5, 2015

Viewing SQLite Databases

How to view user data


All blog posts to date
Introduction Acquisition Analysis
Introduction Imaging an Android Device Examining the image
Picking a Toolkit Live imaging an Android device Some hidden artifacts in a physical image
Why not load ClockworkMod or TWRP to image a device? Using Autopsy to examine an Android image
Identifying your Userdata Partition Some artifacts in the /data/system/ directory
Some non-root methods to learn about a device Viewing SQLite Databases
A quick note on imaging newer Android devices Facebook for Android Artifacts
Using Windows to Live Image an Android device Interpreting data from apps
Obtaining all files in the data partition without a physical image Waze for Android forensics
Magnet Forensics App Simulator
App Reversing Other Topics
Reverse Engineering an Android App File The differences between a physical image and a logical extraction
Fun with Apktool Dirty cow
Deep dive into an app Imaging and examining an Android car stereo
Unpacking boot and recovery kernels
MTPwn
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:

  • 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)
Here is a sample row from this database: "1","hobbit battle of five armies trailer",,"hobbit battle of five armies trailer","1406574949951"
  • _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
Well there you go.  This database file appears to contain the user's YouTube search history, along with the date and time of each search.  Nifty, huh?
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
As always, if you have any specific questions about how to read user data, such as text messages, call logs, calendar entries, or more, do not hesitate to contact me.
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)
Out of the current screenshot is another column called "body".  This column stores the actual text of the message.  There are other columns which indicate the status of the message (draft, sent, received, etc) and if the message has been read or not (only applicable to "received" messages).
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
Questions, comments, suggestions, or experiences?  Hobbit movie reviews?  Leave a comment below, or send me an email.

7 comments:

  1. Use SQLite Android Viewer application to browse databases that is the perfect option to view overall SQLite components.
    http://www.androidnewsindia.com/sqlite-viewer-android/

    ReplyDelete
    Replies
    1. Looks like a decent tool. Here's a link to the tool's download page ...http://www.sqliteviewer.org/database/android/
      The basic version is free. It has the same feature set as the tool I use in this post and also the same feature set as this sqlite browser - http://sourceforge.net/projects/sqlitebrowser/. There are a lot of SQLite browsers out there. SQLite is a free, open standard so anybody can write a SQLite browser if they wish.
      The tool you mentioned also has a paid version which claims support for recovering a SQLite database. I haven't used the paid version so I cannot speak to the paid functionality. I only go over free methods for Android forensic acquisition and analysis here so I will not be covering the paid version of this tool.
      Thanks for posting a link to this tool. I'll add it to my personal arsenal of tools I use in my work.

      Delete
  2. Hie,
    Very nice and informative blog post, i found out another similar but quiet different informative blog post. Visit to explore more http://sqliteviewer.com/blog/android-sqlite-database-and-its-file-location.html

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. Hi,
    I am following forensic to carry out college project but i am stuck in between where the userdata in my phone is in f2fs format if I make an image of it and trying to import on Autopsy its not working and it says unknown file sytem. Can u help me with this? Because i have only 2 more days to do my final review.

    ReplyDelete
    Replies
    1. So you imaged the phone but the image isn't showing up due to file system issues? Try pulling the files logically instead of the image.
      The phone is rooted right? Gain root (su) and cd to the specific files you care about. Do a cp -r of the files you care about to the sdcard, then use adb pull to get those files. You'll get the files but not the slack and it's not forensically sound one bit, but for research it should do.

      Delete
  5. This comment has been removed by the author.

    ReplyDelete