In doing some manual parsing of files from sample forensic images I came across two different databases related to information from the Google Docs application.
Cello.db
First up is the Cello folder and database found at:
data\data\com.google.android.apps.docs\app_cello\<ACCOUNT_NAME>\cello.db
The main tables of interest were:
- items
- deleted_items
For all images I had (Magnet CTF and Josh Hickman samples) "deleted_items" was empty but I would imagine it would have information on documents that were recently deleted out of Google Docs that haven't synced back. The "items" table will be the main focus for this blog as I continue to test further with my own devices. Items is a fairly large table with a bunch of columns of interest:
From the table we can get information regarding file names (title), various dates including created, modified, shared, viewed, file types, file size used against your Google storage quota, and some flags for ownership and deletion. All dates were in UNIX Epoch millisecond counter. Using the SQL query (found on my Github), it produces a nicer, more readable, output.
One thing to note, most of the data was still located in the WAL file so make sure if you include those in the export if parsing the data using a third party tool such as DB Browser.
The "proto" column, which was left out of this query at this time, contains a protobuf formatted blob of information about the file entry. I am hoping to update the query further once I figure out how to pull out anything useful. Some of this information I was able to pull out of our next database file.
DocList.db
DocList is another similar database file found in the Google Docs application that seems to also track more information about files. It can be found at the following path:data\data\com.google.android.apps.docs\databases\DocList.db
The main tables of interest for this one include:
- Account236
- DocumentView
- EntryView
Account236 doesn't have too many columns but a few useful data points about accounts using Google Docs on the device such as the "accountHolderName" and "lastSyncTime". Tables DocumentView and EntryView hold the meat of what we are looking for from this database. From what I could tell they housed all the same information except EntryView also included folders alongside documents/files. So for this blog, we will be diving into EntryView.
Since there are too many columns I created a query (found on my Github) to narrow down the scope of relevant information. We once again get file name, owner account, created, modified, opened date/times, but now we get URI paths that the files or folders would be located in the cloud. We also get MD5 hash and file sizes. I'm not sure why those last two are only populated for the default Getting Started PDF file.
Hopefully these queries and information will help in aiding investigations. As more data continues to live in the cloud, we can at least use these pieces of information to potentially prove existence of files and use of documents from Google Docs.
Sample data can be pulled from Josh Hickman's Android images (v9 was populated the best) for both instances of databases. Parsers have been implemented into ALEAPP as well (thanks Alexis!)