Initialization vectors: 2018

Sunday, December 30, 2018

Update on identifying installed and uninstalled apps in iOS

In my last post  I asked the following regarding the values within applicationState.db:
Are the key_tab table values the same for all iOS devices? In another phone, would ID 1 still stand for compatabilityInfo and ID 13 stand for _UninstallDate data? If every phone has different key_tab relationships there would not be a way to scale this analysis using a universal SQL query for it.
 I'm am happy to say the community came to my rescue. Among the great multiple responses I got the ones from the awesome Sarah Edwards stand out.


As seen in the screenshot above the ID to key relationship within the key_tab table can, and many times is, different on each iOS device. Also, as seen on the screenshot, her suggestion of identifying the ID by key name instead was totally on point. With that in mind I remade the query as seen here:
https://github.com/abrignoni/DFIR-SQL-Query-Repo/tree/master/iOS/INSTALLED-APPS

The fact that the uninstalledapp key is so random that it did not appear in many of the data sets from folks that reached out to me tells me that the query for unistalled apps is not useful at all, hence I nixed it.

As of tonight this is where I stand:

1) The applicationState.db is a good way of getting a current list of installed apps with their corresponding app directory locations and names. It is super useful for the purposes of validating a list of installed apps that a mobile forensic tool tells you. Any app in the applicationState.db list that is not in the installed app list from the tool requires a look at that app directory. Since it is a simple SQL query it is fast and simple to implement.

Sarah did not limit herself to that. She pointed me to another location rich in app installation data.

2) The MobileInstallation logs are fantastic! They have all that and more. Still, to pry a list of current installed apps and directory names out of these logs requires some work. There is no script that automates it. Time permitting I will try and take a crack at it. If the reader hasn't looked at those logs before then they (you!) definitely should.

Christopher Vance chimed in with another set of forensically relevant items.

Great info for future use.

3) Last but not least, Sarah provided a way to track uninstalled apps.
Good stuff all around.

Conclusions

My original requirement was to produce a list of installed apps and their corresponding app directory names and locations in order to identify any apps that would require parsing when a commercial tool misses them. As of today the applicationState.db method from my previous post seems to fulfill that requirement.

If a historical look at installed apps is required the MobileInstallation logs provide a full picture. Sadly until a script is made to parse it getting that information out will require some grepping/lots-of-eyeballing-the-logs to get what is needed.

For network usage Netusage and datausage analysis fills that gap. 

UninstalledApplications.plist rounds out the picture as the name of the plist clearly indicates.

I can't thank enough all the folks who took time out of their weekends to respond and share their knowledge. Super grateful for all of you. I'm sure glad to be part of the digital forensic community.

As always I can be reached on twitter @alexisbrignoni and email 4n6[at]abrignoni[dot]com.

Saturday, December 29, 2018

Identifying installed and uninstalled apps in iOS


Short Version

In this post I look at the applicationState.db SQLite database in detail and ask for help on testing/validating some of the findings.

Long Version

As stated in previous blog posts the applicationState.db located at
/private/var/mobile/Library/FrontBoard/
keeps track of app bundle IDs as well as the path and long alphanumeric folder name ID where the app keeps its data. For an example of how this looks in practice see the previous post here.

As I was bouncing around some concepts with Phill Moore regarding my upcoming Magnet User Summit he asked me what would happen to the data in the applicationState.db if an app was deleted. In all my iOS third party app research I have used the database to find the app data directory names but I never thought of finding out what happened to it when an app was deleted. This post is me attempting to find out. Thanks Phill. Here we go.

For an example on how to obtain a file system extraction from a jailbroken iOS device see here.

Testing Platform

For analysis I am using the following device and equipment:
  • iPhone SE - A1662
  • iOS 11.2.1
  • Jailbroken - Electra
  • Forensic workstation with Windows 10 and SSH software.
Testing Procedure

  1. Connect to jailbroken device via SSH and extract a copy of the applicationState.db file.
  2. Deleted three apps from the device.
  3. Connect again and extract a copy of the database after deletions.
  4. Compare the databases to see what changes, if any, took place after the deletions.
Findings 

The applicationState.db SQLite database has 3 tables of interest. The first one is the application_identifier_tab table.

application_identifier_tab
It contains the bundle IDs for the apps and a unique ID for each one. I have always used the ID value here to identify where the path and app data folder name in the second table of interest named kvs.

kvs
Note how each value in the application_identifier column is the foreign key of the ID column values in  application_identifier_tab table. For example when the value for the kvs.application_identifier filed is 110 the bundle ID name in the application_identifier_tab table is com.spotify.client.

Look at highlighted area


If you look back at the kvs table notice there is also a column named key. For the 110 ID there are multiple key values like 2, 9, 8 and others. What are these? These values map to the third table of interest, key_tab.

key_tab
This is where it gets interesting and questions arise. On my test device the blobs contained in the value field within the kvs table tell me where the app keeps its data as long as the key = 1. For the com.spotify.client it would look like so:

Selected blob where key value equal 1



Blob content
Notice the blob content has the Spotify app name and the path for the application directory to include the long alphanumeric name of the folder. The blob is a binary plist that can be exported and view more cleanly with a binary plist viewer like Sanderson Forensics BPlister.

What then do the other blobs contain when the key =/= 1? The key_tab table mentioned previously is the guide. It maps the values to what data it contains. If one looks at the contents of the table, value 1 stands for compatabilityInfo while 13 represents _UnistallDate.

In my test data application ID 112 maps to the com.valvesoftware.steam bundle ID and it has a a key value of 13 which indicates a deletion date binary plist. The extracted bplist blob looks like this:

Deletion date. Nice.

Easy right? The bundle ID (app) in question does not have a key value of 1 which tells me that it has no associate app directory folder which is consistent with the app being deleted. So logic follows that if we identify which apps (bundle IDs) don't have a key value of 1 (indicating deletion) and extracting the key value of 13 (giving us the deletion date) we should have a list of deleted apps with the deletion date of each. Neat right? Well the devil is in the details. Of my three deleted apps one (com.microsft.rdc.ios) did not have a key value of 13 hence no deletion date for it. I knew it was deleted since there was no ID value of 1 (and I made the deletion myself). No ID value of 1 then no app directory (I checked and it was gone) for the bundle ID which does remain in the database. When I thought how could I automate this analysis the following questions surfaced:

  1. Are the key_tab table values the same for all iOS devices? In another phone, would ID 1 still stand for compatabilityInfo and ID 13 stand for _UninstallDate data? If every phone has different key_tab relationships there would not be a way to scale this analysis using a universal SQL query for it.
  2. Why out of the three deleted apps one had no deletion date recorded?

I made the following queries that identify installed applications (the ones with ID value of 1) and uninstalled applications (the ones without the required ID value if 1 entry and/or the additional ID value of 13). The problem on these queries ties back to question #1. Would the queries still work in another iOS device? My guess is that no, most likely it would not. This is something a reader could test and validate. I plan on doing validation on another iOS device in the near future as well. Here are the queries as stored in the DFIR SQL Query Repository:

https://github.com/abrignoni/DFIR-SQL-Query-Repo/tree/master/iOS/INSTALLED-UNINSTALLED-APPS

  • iOS Installed Apps
  • iOS Uninstalled Apps
  • iOS Uninstalled Apps (That have deletion dates)
Updated query:
https://github.com/abrignoni/DFIR-SQL-Query-Repo/tree/master/iOS/INSTALLED-APPS


Conclusion

The applicationState.db SQLite database is super useful when one wants to list installed applications and find out which obscure alphanumeric app directory name matches an app of interest in the list. This is really important if one is to identify any apps that automated vendor tools do not parse and to locate the corresponding app data stores in the app directory. 

This database seems to track app deletions in some form. For some apps it will have a deletion date and for others it might not. I have yet to find out why.

It is still pending to validate if the ID values for compatabilityInfo, _UnistallDate and others are the same across iOS devices. 

As always I can be reached on twitter @alexisbrignoni and email 4n6[at]abrignoni[dot]com.

PD:

My apologies to Jessica Hyde for my half baked emails on the topic before the creation of this blog post. Sorry...

Tuesday, December 4, 2018

Profiling user activity in Dropbox for iOS

Dropbox for iOS

Dropbox is one of the most well known cloud storage services in the planet. It needs little to no introduction. In this post I look into what relevant digital forensic artifacts can be found for Dropbox in iOS.



This post will differ from previous ones in that I will not discuss how to locate the application data directories or how to extract them. For an example on how to go about doing so follow the steps (which are for the Discord app but work for any app) here.

Short Summary
Dropbox SQLite databases contain information regarding files stored in the cloud even when the files themselves are not downloaded to the device. Two of my favorite findings are:
  1.  The ability of matching app generated thumbnails on the device to the full metadata information from the original remotely stored files.
  2. Database tracking of how many times a file was viewed via the app even if the file wasn't downloaded or synced.

Additional data was contained within the app directories outside of SQLite databases. If the account in use is linked to a third party provider, like Google, a JSON file is generated with all your contacts and any Dropbox interaction that took place with the target user account. Some of the data in the JSON file are names, usernames, profile pics URLs, and timestamp of the latest Dropbox interaction like the sharing of a folder. In addition the Dropbox target user account information can be found in one of these JSON files.

Important Information 

All the queries and analysis in this blog post is provided as a guide for the reader's own testing and validation.

Testing Platform

For analysis I am using the following device and equipment:
  • iPhone SE - A1662
  • iOS 11.2.1
  • Jailbroken - Electra
  • Forensic workstation with Windows 10 and SSH software.
  • Magnet Forensics Axiom 2.71.12070 with custom queries made by the author.

SQL Queries

Queries that can can be used as templates to extract pertinent data can be found at:

https://github.com/abrignoni/DFIR-SQL-Query-Repo/tree/master/iOS/DROPBOX

Here is a list of the individual queries in the aforementioned repository:
Analysis of SQLite databases

The Dropbox app has the following file directory structure:

Most of the databases of interest are in the /Documents  and /Documents/Users/UserID directories where UserID is the numeric representation of the user account. The contents of the /Documents directory are as seen in the next image.

First database of interest.
Spotlight.db

The first database of interest, as seen above, is spotlight.db. This database seems to be the partial aggregation of the data contained in two other databases within the /Documents/Users/UserID directory. These will be discussed shortly. After running the query above for this database (Spotlight - recent actions) we get the following:

Spotlight.db selection.

Salient items:
  • Path: Path and filename of remotely stored file.
  • Source: From where the activity information came from. I found no files named as such in the Dropbox app directories.
  • Title: Filename.
  • Last action time: Timestamp. For files that end in .remote in the source field these timestamps match when the app was opened. For files that end in .local in the source field these timestamps match when the user had interaction last with those files.

Based on the files names it seemed to me that this list is composed of the items one sees in the recents screen after login into the app. Older files that I did not scroll down to are not in the list. The content of Spotlight.db mirrors the content in two other databases called recent_actions_local.db and recent_actions_server.db. These databases are contained in the following location:

/Documents/Users/UserID/



Recent_actions_local.db

This database contains the same entries that end in .local within the spotlight.db source column. The only addition is the user id column. Here is how running the query above (Recent actions local) looks like:

Salient items:
  • Path: Path and filename of remotely stored file.
  • Timestamp: Time user interacted with the file last. I clicked on the thumbnails of these items in the app to view them. One of them I made available offline. The times are the same ones as contained in spotlight.db.
  • User ID: User identifier.
There might be more user actions that will flag a file as local recent actions. The databases do not specify what that action was or could be. So far I have only been able to flag the files by the actions stated previously.

Recent_actions_server.db

This database contains the same entries that end in .remote within the spotlight.db source column. The only addition is the action column. The timestamp is different from the spotlight.db time. Here is how running the query above (Recent actions server) looks like:

Salient items:
  • Path: Path and filename of Dropbox stored file. Not local.
  • Action: Human readable message.
  • Timestamp: The times in this column follow closely the time the image was taken (see next section for details.) By comparing the filenames with the timestamp column I concluded that the timestamp reflect when the image was added to Dropbox. I have my Dropbox set up to only upload images when connected to WiFi.  
Camera uploads and naming convention

My testing showed the following regarding file naming convention and timestamps for the camera uploads directory:

  • Images taken by the mobile device camera and uploaded automatically by the Dropbox app are named in Dropbox as the creation date of the image on the device.
Sample image and metadata on the device. Notice the original filename and creation timestamp.
Now notice the filename Dropbox gives it after it uploads it.

  • If the user herself uploads files and images to the Camera Uploads folder, as opposed to the app itself, these will retain their original names.
  • Files can come from different devices and land in the same Dropbox remote storage account. These databases do not tell us what device produced or uploaded the images originally.
Thumbnails
Be aware that by default the app will create thumbnails for the items on the list at the following location:

/Library/Caches/Uers/UserID/FileCache/Loaded/

The directory has a collection of folders named in a simple pattern of lowercase letter p followed by a number.


Inside these folders thumbnail files will be stored with the following filenames:
  • 256x256_fit_one_bestfit
  • 960x640_bestfit
If one of the images was downloaded to the device or placed as available offline the folder will contain an additional image named:
  • original.png
The following image shows the contents when all three are present for the p5 folder.

The obvious question becomes, how are these images matched to the items previewed, viewed, or downloaded by the user in the Spotlight database? The answer is that there is no link to the thumbnails in that database. The link is found in a different database called Dropbox.sqlite.

Dropbox.sqlite

The Dropbox.sqlite database has metadata on all cached files. Here is how running the query above (All cached files metadata) looks like:


The column named Cached File ID has the number needed in order to match the thumbnail to the full size image stored within Dropbox. It is as simple as putting a p in front of the number and looking for it in the thumbnails directory.

Salient items:
  • Cached File ID: Number that corresponds to the thumbnails folder. Just add a 'p' in front of the number.
  • Path: Path and filename of Dropbox stored file. Not local.
  • Cached File Size: In bytes.
  • File size: Actual file size. Item not local.
  • Times viewed: How many times the user click on that thumbnail to view a larger version of it.
  • Last time viewed: Just what it means.
In order to isolate the viewed files and their times a query (Viewed files only)
was generated that looks as so:


This is one of the most direct ways of demonstrating user activity tied to specific items.

Additional databases of interest in the /Documents/Users/UserID/ directory are metadata.db, offline.db, and starred_infos_local.db. 

Metadata.db

This database is interesting because the contents mirror user activity when files are browsed through the Files menu in the app. It will record folders and file names. A query was generated (Browsed files via the 'Files' menu option) to show these contents.


Salient items:
  • Path: Path and filename of Dropbox stored file. Not local per testing conditions.
  • Last Modified Date: Notice how the root folder and Dropbox generated directories (Camera Uploads, Public, etc...) do not have a Last Modified timestamp while user generated directories and uploaded files do. My testing shows that the last modification timestamp reflects when the files was placed in its current Dropbox location. By interacting with the files via the app there are no timestamp changes. Further testing is needed to see if any changes are reflected from outside the app interaction with the files in the remote storage location.
  • Client Modified Date: Time when the file was created. Dropbox will use the file creation time at upload as the Client Modified Date. If the files are in the Camera Uploads folder and follow the naming convention discussed in a previous section, the Client Modified Date is the creation date of the image in real time as long as both values match. This might seem like a distinction with no difference but there is and it is a key one. A creation date for a file is not necessarily the same as when the image was taken in real time. Depending on where the file is in the remote storage location and how it is named one can then determine if the file creation time is the same as when the image was possibly taken in real time. 
  • Shared Folder ID: One of the folders was shared by me and another shared to me. I find no way to determine directionality by looking at the contents of the database.
I want to emphasize how useful it is to understand the differences in meaning regarding Client Modified Date and Last Modified Date in the previous database. That same understanding will apply to the next database we will analyze. The next database is key because it contains metadata on a large number of images and videos uploaded to the Dropbox account no matter if the files where browsed or accessed by the user via the application on the mobile device.

Metadata/cache.db

As stated previously this database contains information on a large number of multimedia items in the remote storage location. A query was generated (Images and videos metadata) to show the contents of the /Library/Application Support/Dropbox/alphanumeric sequence/Files/cache.db database.


The explanations for the Path, Bytes (for file size), Client Modified Date, and Last Modified Date columns are the same as the ones used in the previous database, metadata.db. As seen in the image above some Client Modified Date data might not be moved over. If one accesses the Dropbox storage location via the web interface those dates can be seen. This means that the modified date in the web interface for the file is the Client Modified Date in the database.

My shorthand, that is not so short, is as follows:
  • If file is in the Camera Uploads folder, and it is date timestamp named, and the Client Modified Date is equal to the date timestamp name then the Client Modified date IS the time the picture was taken via a mobile device camera and would reflect the time given by such device. All conditions are necessary for this assumption to be true based on my testing. The underlined word in the last sentence is important because it assumes the mobile device that generated the image or video is keeping time accurately in relation to real time. It is true that science doesn't work in assumptions but it is guided and informed by them. Always do your own tests on your own particular case scenario. 
  • For all other files the Client Modified Date is the creation date of the file without any assumptions on when the picture or video was taken in real time.
  • Last Modified Date is when the files were uploaded to remote storage absent some yet unknown mechanism that affects this timestamp after file upload.
What happens if a file is downloaded from Dropbox to Windows using a browser? Are the Client Modified Date values carried over? If so, where? On Windows systems, and using Chrome as the browser, the following occurs:

  • When multiple files are selected for download a zipped files is downloaded to the system containing the selected items. After decompression the files created and accessed times are the same as the Client Modified Date in the database.
  • If you download a single file all modified, accessed and created times will be the same and will reflect the moment the file was downloaded from cloud storage locally.
The file properties on the left are from a zipped file whereas the file properties on the right are from a single file download. Both done via Chrome browser on Windows 10.



Here are the values for the same file as recorded in the database.


The difference in hours and minutes is due to setting the time values in the database viewer (Axiom) to UTC-5:00. Notice how time recorded in the database is exactly the same as the created and accessed dates in the unzipped files.

Offline.db

Database records items selected for offline viewing. A query was generated (Files available offline) to show the contents. The database is located in the /Documents/Users/UserID/ directory.



Starred_infos_local.db

Database records starred items. A query was generated (Starred files) to show the contents. The database is located in the /Documents/Users/UserID/ directory.



Cache.db / User notifications

Database records user notifications. A query was generated (User notifications) to show the contents. The database is located in the /Library/Application Support/Dropbox/Alphanumeric string/notifications directory.


Notice the feed timestamp on the left as well as the content of the notification in JSON format. Since the JSON notifications in the database did not follow a consistent key:value pair pattern they are presented as is. They are small enough for any examiner to read and figure out what they are about.

JSON Files

Dropbox keeps data in JSON files in addition to SQLite databases. The following two files are of great interest. They are located in the /Library/Application Support/Dropbox/Alphanumeric string/Account/contact_cache directory.

All_searchable

This JSON file contained all the user Gmail contacts for the Dropbox account user. For my sample data I am using my own Dropbox account which is tied to my Gmail. Some of the data kept for the user contacts in the JSON file are:

  • Email address
  • Interaction info
    • Last used time
    • Total interactions
    • Use type
  • Last used time
  • Name
  • Service types
  • Total interactions
  • Dropbox profile pic URL if any
Here is a sample contact from the list.


The JSON file was processed for viewing using a python script located at:
https://github.com/abrignoni/Discord-JSON-chat-conversions

Me

This JSON file contained the Dropbox user information. Some of the data kept for the user in the JSON file are:

  • Email address
  • Name
  • Dropbox profile pic URL
  • Is_me field with a value of True.


Conclusion

Due to now being able to obtain file system extractions from devices we couldn't in the past it is important we revisit our analysis of what we might think are already well known applications. This is even more relevant when the data we are examining is multi-platform and sometimes we might have to look at it from different vantage points, for example mobile device data in contrast to the same data viewed through a desktop browser.

The eternal caveat still applies. Always test, test and test. And when done, test some more.

As always I can be reached on twitter @alexisbrignoni and email 4n6[at]abrignoni[dot]com.

Thursday, November 22, 2018

Finding Blizzard Battle.net messages in iOS

Short version

The iOS Battle.net app keeps message related data in the ZMESSAGE and ZUSER tables from the following SQLite database:
/private/var/mobile/Containers/Data/Application/UU-ID/Documents/Social.sqlite
 The process to obtain the correct UU-ID number is presented in brief in the long version section. For a detailed example see here.

Queries that can be used as templates to extract messages from the database can be found at:
https://github.com/abrignoni/DFIR-SQL-Query-Repo/tree/master/iOS/BATTLE.NET
 Long version

Blizzard properties are one of the most popular in the computer and video gaming community. The Battle.net app was developed as a way for gamers to communicate via chat messages.

Stay connected with your friends wherever you are.
For an example on how to obtain a file system extraction from a rooted iOS device see here.

Testing Platform

For analysis I am using the following device and equipment:
  • iPhone SE - A1662
  • iOS 11.2.1
  • Jailbroken - Electra
  • Forensic workstation with Windows 10 and SSH software.
Acquisition

A brief overview of the identification and extraction of  Battle.net user data app directory is as follows. For an detailed example see here.

1. Locate bundle id name.


2. Access the 'applicationState.db' file located at:
/private/var/mobile/Library/FrontBoard/ 

This SQLite database provided the connection between the bundle id and the UU-ID numbers in the 'Application' directory.

Open the SQLite database with a SQLite browser. Look for the bundle id name in the 'application_identifier_tab' table. Take note of the corresponding id number.

3. Look for it in the 'kvs' table in the 'application_identifier' field . Export the blob in the value field for the id. The exported data is a bplist that maps all pertinent UU-ID numbers to the application name and/or bundle id. The data can also be seen in the preview pane in binary mode without the need to export the blob content. If the bplist is exported a viewer, like Sanderson Forensics BPlister, can be used to see the relationship between UU-ID and application we are looking for.


4. With the correct application directory identified I copied it via SSH to the forensic workstation.

In this particular instance I did not make a full file system extraction of the device. I only copied the app directory of interest for testing purposes. Do follow generally accepted forensic principles when doing similar work on your case work.

Messages and user data

Messages and user data are contained in the Social.sqlite database. Using the templates located at the links at the beginning of the blog the following results are obtained:


The column data is as follows:

  1. ZCONVERSATION = Keeps tracks of messages between a unique set of users.
  2. ZID = Internal user ID number.
  3. ZREALID = Real ID for contact. For details on Real ID see here.
  4. ZBATTLETAG = ID used to add other app users to your contacts list.
  5. ZDATE = Message date.
  6. ZBODY = The message.
  7. ZPROGRAM = Program used.
  8. ZRICHPRESENCE = Details regarding the program used.
For user details the query at the beginning of the post produces the following:

 
The column data is as follows:
  1. ZID = Internal user ID number.
  2. ZREALID = Real ID for contact. For details on Real ID see here.
  3. ZBATTLETAG = ID used to add other app users to your contacts list.
  4. ZISFAVORITE = User favorites.
  5. ZNOTE = User generated description of another user in their list.
  6. ZPROGRAM = Program used.
  7. ZRICHPRESENCE = Details regarding the program used.
While using the app I was not able to find a way to send other media types, like videos or pictures, to another user.

Conclusion

Gaming and gaming communication apps have exploded in regards to the amount of users in the last few years. Some of these platforms are rivaling other more established ones whose only purpose is communication. Examiners need to aware that these apps might contain relevant information. We should not be deceived by their gaming-centric presentation. Plenty, if not most of this apps, have all sorts of user generated content that we should take into consideration.

As a way of facilitating access to the content these databases hold I have submitted and had approved custom artifacts for the Magnet Forensics Axiom software. These custom artifacts can be found in the Artifact Exchange page.

As always I can be reached on twitter @alexisbrignoni and email 4n6[at]abrignoni[dot]com.

Saturday, November 10, 2018

Finding TikTok messages in iOS

Short version

The iOS TikTok app keeps message related data the TIMMessageORM table from the following SQLite database:
/private/var/mobile/Containers/Data/Application/UU-ID/Library/Application Support/ChatFiles/User-ID/db.sqlite
Be aware that in the path UU-ID should be replaced by the application identifier and the User-ID  for TikTok user identifier of interest. The process to obtain the correct UU-ID number is presented in brief in the long version section. For a detailed example see here. To obtain the correct User-ID directory number for a user of interest see the contents of the awemecontacts table from the following SQLite database:
/private/var/mobile/Containers/Data/Application/UU-ID/Documents/AwemeIM.db
Queries that can be used as templates to extract messages from the database can be found at:
https://github.com/abrignoni/DFIR-SQL-Query-Repo/tree/master/iOS/TIKTOK
In order to view the public TikTok profiles of the users found in the awemecontacts table in the AwemeIM.db database add the user name ID to the end of the following URL:
https://m.tiktok.com/h5/share/usr/(insert username ID number from DB).html
For one of the test accounts used in this blog post the URL looks like this:
https://m.tiktok.com/h5/share/usr/6619782258185388037.html 
Videos created with the app can be found in the following directory with the .mp4 extension:
/private/var/mobile/Containers/Data/Application/UU-ID/temp/
 Long version

TikTok is one of the most popular apps in the iOS App Store.

For an example on how to obtain a file system extraction from a rooted iOS device see here.

Testing Platform

For analysis I am using the following device and equipment:
  • iPhone SE - A1662
  • iOS 11.2.1
  • Jailbroken - Electra
  • Forensic workstation with Windows 10 and SSH software.
Acquisition

A brief overview of the identification and extraction of  TikTop user data app directory is as follows. For an detailed example see here.

1. Locate bundle id name.


2. Access the 'applicationState.db' file located at:
/private/var/mobile/Library/FrontBoard/ 

This SQLite database provided the connection between the bundle id and the UUID numbers in the 'Application' directory.

Open the SQLite database with a SQLite browser. Look for the bundle id name in the 'application_identifier_tab' table. Take note of the corresponding id number.


3. Look for it in the 'kvs' table in the 'application_identifier' field . Export the blob in the value field for the id. The exported data is a bplist that maps all pertinent UUID numbers to the application name and/or bundle id. The data can also be seen in the preview pane in binary mode without the need to export the blob content. If the bplist is exported a viewer, like Sanderson Forensics BPlister, can be used to see the relationship between UUID and application we are looking for.


4. With the correct application directory identified I copied it via SSH to the forensic workstation.


In this particular instance I did not make a full file system extraction of the device. I only copied the app directory of interest for testing purposes. Do follow generally accepted forensic principles when doing similar work on your case work.

Chats and Media

As stated in the Short version portion of the blog post the message data can be accessed by joining the contents of two different databases and two tables in them.

1. TIMMessageORM.db.sqlite
2. awemecontacts.AwemeIM.db

The paths for these files are respectively:

1. Support/ChatFiles/User-ID/db.sqlite
2. /private/var/mobile/Containers/Data/Application/UU-ID/Documents/AwemeIM.db

The messages query found in the DFIR SQL Query Repo for TikTok produces the following results:


The column data is as follows:
1. sender = The numeric user id. The value is used to join the two tables in order to access usernames.

2. profilepicURL = Is the link for the user profile pic.

3. customID = Account username.

4. nickname = Precisely what it says.

5. Local_create_Time = Local/device time for a particular message.

6. servercreatedat = Server/remote time for a particular message. A value of zero indicates the message did not leave the device.

7. message = The content of the message.

8. localresponse = Additional information for a particular message. For example for messages that did not leave the device this field will provide some diagnostic information.

9. links_display_name = If the user responds with an image or a gif this field will have the display name of the file.

10. links_gif_url = The link for the sent image of gif. Contents can be accessed without authentication.

The user data query found in the DFIR SQL Query Repo for TikTok produces the following results:


The column data is as follows:
1. uid = Numeric user id. 

2. customID = Account username.

3. nickname = Precisely what it says.

4. latestchattimestamp = Last timestamp for a chat.

5. url1 = Link for the profile pic of the user.

One can use the uid number to access the public profile of the user over a browser. Just user the following URL and fill it with the uid of interest.
https://m.tiktok.com/h5/share/usr/(insert username ID number from DB).html
Here is one of my test account profiles as an example:

 All public shared videos can be seen in the profile.

Videos created with the app can be found in the following directory with the .mp4 extension:
/private/var/mobile/Containers/Data/Application/UU-ID/temp/
Conclusion

The TikTop app, both in Android and iOS, stores JSON data within SQLite databases. Currently I don't know of any mayor forensic tool vendor that has the json_extract function enabled in their SQLite implementations. This means that queries that can handle JSON data can't be incorporated into their artifact/template generation tools except via the use of more complex JSON handling python scripts.

For the time being exporting the databases of interest and executing SQL queries on them via a third party SQLite browser tool will be my preferred choice.

As always I can be reached on twitter @alexisbrignoni and email 4n6[at]abrignoni[dot]com.

Friday, November 9, 2018

Finding TikTok messages in Android

Short version

The Android TikTok app keeps message related data in SQLite databases located in the following location:
userdata/data/com.zhiliaoapp.musically/databases/
The database containing user data, both the local user and friends, is named db_im.xx.
The database containing the messages is named in the following regex format: ([0-9]{19})(_im.db)$ where the filename is 19 character numeric sequence ending in the _im.db extension.

Queries that can be used as templates to extract messages from the database can be found at:
github.com/abrignoni/DFIR-SQL-Query-Repo/tree/master/Android/TIKTOK


In order to view the public TikTok profiles of the users found in the db_im.xx table add the user name to the end of the following URL:
https://m.tiktok.com/h5/share/usr/(insert username number from DB).html
For one of the test accounts used in this blog post the URL looks like this:
https://m.tiktok.com/h5/share/usr/6619791930123403269.html 
Multiple XML app files can be located at:
userdata/data/com.zhiliaoapp.musically/shared_prefs
Some of the app related info contained within the XML files includes:

  • Total traffic
  • Collect traffic time
  • Recent search history 
  • Mobile traffic 
  • Language
  • Region
  • First open time
  • App install time
  • Last update time
  • Mac address
  • Last wifi bssid
  • Last time check bssid
The previous are just a few examples of the type of content the XML stores. Additional user info can be found in the aweme_user.xml file.

Videos created with the app can be found in three files. One contains the video, another the audio for it and a third one combines both. The files are located at:
userdata/data/com.zhiliaoapp.musically/files/
The filenames are a dash separated timestamp followed by a numeric sequence that ends with -concat-v for the video and -concat-a for the audio. A sample audio filename would be something like this: 2018-11-03-210557702-mix-concat-a.

For the combined video and audio file the filename will follow the previous format with the addition of the synthetise_ prefix. For example: synthetise_2018-11-03-210556218-concat-v.


Long version

The Android TikTok app is one of the more popular apps in the Google Play store with over 100,000,000 downloads. The app is used to create short videos where the user can easily edit the sounds, visuals, and share them in within the social media environment it provides.

This app is ridiculously popular with teens.
 As a most social media platforms the app provides a way for user to send messages to each other.


Testing platform

Details of hardware and software used can be found here.

Extraction and processing

Magnet Forensics Acquire - Full physical extraction. 
Autopsy 4.8.0
DB Browser for SQLite

Analysis

The TikTok app directory structure looks as follows:

Usual Android app file structure.
As stated at the beginning of the post the main messaging content SQLite database is named by the following pattern  ([0-9]{19})(_im.db)$. The 19 character number at the start of the file name is the same as the logged in user of the app. The messages table does not contain the actual user names, that information resides in a second table called db_im.xx. The table name for the message is appropriately called msg.

The following image shows some of the more relevant fields in the msg table:

JSON, we meet again!!!!
As expected the creation time of the messages is unix epoch and the actual test content is in JSON format. The extract messages query at the top of the blog uses the json_extract function to separate the relevant JSON into its own database response columns.

It is of not that some of the messages have the read_status value in the last column set to zero. This means that the message did not reached the server. In my test those messages were sent before the target account had followed account initiating the message. The local info column contained relevant information that will help the analyst understand the reason for a read_status of zero. Again in this instance the local info message read as "This person hasn't followed you yet and may not be able to receive your messages."

Local_info column value. Long hand for sorry can't do that Dave.
Next is the user data table contents named SIMPLE_USER in the db_im.xx database.

Notice the user id, nickname and unique_id values. The avatar thumb url, in JSON format, is there as well. The SQL query for messages joins both the messages and user data tables to present a unified result for all messages sent and received. As always it calculates the time from unix epoch to local time and extract all the relevant JSON to its own result columns. For the query to work one of the tables needs to be attached so the query can have access to both databases and the necessary tables from each.

The next image show a portion of the message extract messages query results. See how if the user responds with a GIF the URL and display name are extracted from the JSON and given their own columns. These URLs are accessible without authentication.

Love it when a plan comes together.
One useful trick is to take the UID values and insert them into a specific URL in the following manner:
https://m.tiktok.com/h5/share/usr/(insert username number from DB).html
 If the account is not private you will be able to see the shared content. Here is an example:

This is the account.

The app also maintains the shared content as described in the first section of the blog post as well as multiple XML files that can be of use to the analyst. In my sample data set there were 77 XML files. Going through them here is beyond scope but it is highly recommended to take the time and understand their content.

Next post will be about finding TikTok messages in iOS. When those queries are completed they will be added to the DFRI SQL Query Repo as well.

As always I can be reached on twitter @alexisbrignoni and email 4n6[at]abrignoni[dot]com.