The Slack app for iOS keeps message related data in the following database location.Be aware that in the path *UU-ID* should be replaced by the application identifier and *DB-ID* for the Slack work-space identifier of interest.
/private/var/mobile/Containers/Data/Application/*UU-ID*/Library/Application Support/Slack/*DB-ID*/Database/main_db |
The following queries at https://github.com/abrignoni can be used as templates to extract relevant information from the database.
- Extract messages
- User data
- Files & attachments ( all metadata and some file content)
- Work-space data
- Channels Data
The following database is a repository of CFURL links, some in bplist format, in use by the application.
/private/var/mobile/Containers/Data/Application/*UU-ID*/Library/Library/Caches/com.tinyspeck.chatlyio/Cache.db Images in use by the application, including pictures send as attachments as well as avatars, can be found in the following location: /private/var/mobile/Containers/Data/Application/*UU-ID*/Library/Application Support/Library/Caches/default/com.hackemist.SDWebImageCache.default/Currently I know of no way to reverse the file names to their original form as found by the Files & attachments query. Long version Slack is one of the more successful corporate/work messaging apps in the world with over 8 million daily active users and 4 million paid users.
Extraction For a detailed explanation on how to extract user data file system level information from a jail-broken device see the following post. The steps, even though centered on another app, will be applicable to most if not all current iOS applications. Here is the abridged version for my Slack app analysis. Not to belabor the point but if the short explanation that follows does not make sense please see the full example in the following post. 0. Looked up the bundle id for Slack here.
1. Connected to device using SSH. 2. Made a local copy of the applicationState.db located at /private/var/mobile/Library/FrontBoard/ 3. Located the Slack id number in the application_identifier_tab table in the aforementioned database. 4. Used the application_identifier number to look up the value blobs in the kvs table. Exported the blob which contains a bplist. The contents can be seen by using the hex viewer function in your SQLite viewer tool of choice or after exporting. 5. Open the extracted bplist with a bplist viewer. The Slack directory UU-ID can be seen.
6. Copied the Slack application folder as identified in the bplist.
The copied folder contains the relevant files for our objective. Analysis of message related databases The directory structure of the Slack application is as follows: Under the /Library/Application Support/Slack/ are the team id numbers. For my testing team the number is TCJRXQDB1. In every team id folder there will be the relevant database (/Database/main_db) to that team. In Android the actual database for the app has the id as its name. Using a SQLite viewer one can see that the main_db database has 21 tables. For my analysis I formulated five queries that identify what I believe to be pertinent information in most cases. It is of note that these queries are only templates. Each table has multiple fields and some of these might be relevant to your case and not be selected in the queries below.
Some notes on the values encountered for the extracted messages query:
1. User IDs start with the letter U, Channel IDs start with the letter C and Direct Message channel IDs start with the letter D. The linking of user messages to channels and channel metadata took some work since public channel ids, like General or Random, reside in a column of their own in one of the tables. Hence the need for the OR condition in the SELECT clause.
2. The ZLSKMESSAGE table had repeated rows in it. Not sure why. I used a distinct clause at the beginning to clean it up. Be aware that you should look at the data manually first and then try the query with the DISTINCT clause and also without it. I believe it is important to have a clear understanding of the data and the state it was found.
3. The query sets the time to local time. Adjust as needed for your purposes.
4. The ZFILEIDS indicates that the message was sent with a file attached to it. If sent the field will have a value in it. The actual sent data might reside in the ZSLKFILE table. If it is a picture it will not. The picture will be located in the following folder:
/private/var/mobile/Containers/Data/Application/*UU-ID*/Library/Application Support/Library/Caches/default/com.hackemist.SDWebImageCache.default/The filenames bare no relation to the ones found in the ZSLKFILE table. Currently I know of no way to reverse the filenames in the cache to the original filenames.
Some notes on the values encountered for the user data query:
1. Notice the ZTEAMID value. It is the current working team. In iOS it is used to name the directory for the team database. In Android it is how the database itself is named. Additional relevant data will be discussed in the channels data query portion below.
2. The ZADMIN and ZOWNER identify the user/s that wields such designations. Boolean value.
Some notes on the values encountered for the files & attachments query:
1. If the content of the message attachment is available it will reside in the ZPREVIEW field. As explained previously images themselves are not found in the table. They reside in another directory named in a way that has no obvious relation to the original filename as stated in the query.
2. If proper consent/legal authorization is obtained the images can be downloaded from the server using the permalink URL. Validation via username and password will be needed. More on login username and password below.
Some notes on the values encountered for the work-space data query:
1. The ZDOMAIN value is the one used to name and connect to the work-space. In this instance it would be dfirtesting.slack.com. Internally the work-space is identified by the ID located in the ZTSID field.
Some notes on the values encountered for the channels data query:
1. There are tons of interesting timestamps in this table. Among them the time the channel was created, when was a topic and/or purpose for the channel set, the last message in the channel and the last read of the channel.
2. The purpose and topic string is found under ZPURPOSETEXT and ZTOPICTEXT respectively.
3. The ZSHAREDTEAMIDS field has the current work-space listed as a list of one item. Haven't tested the shared teams functionality but my educated guess is that the list would be populated with the respective IDs from other work-spaces. This will require further testing.
Analysis of non-message related databases
The following database is a repository of CFURL links, some in bplist format, in use by the application.
|