Maksym Boiko, mboiko25@gmail.com, Kyiv, 2018
TABLE OF CONTENTS
INTRODUCTION
1. LOCATION AND TYPICAL FILE STRUCTURE OF TELEGRAM FOR ANDROID
3.1. MESSAGES AND MEDIA_V2 TABLES
3.2. ENC_CHATS, CHATS AND USERS TABLES
3.3. USER_CONTACTS_V7 AND USER_PHONES_V7 TABLES
4.1. METHOD 1. SEARCHING FOR INFORMATION BY STRUCTURE OF DATABASE TABLES
4.1.1. CONVERSION OF THE "UID" FIELD VALUE
4.1.2. TABLES RELATIONSHIPS
4.2. METHOD 2. SEARCHING FOR INFORMATION BY INTERNAL STRUCTURE OF DATABASE FIELDS
CONCLUSION
REFERENCES
APPENDIX A
APPENDIX B
INTRODUCTION
Telegram is a partially open source software that offers text, voice messages and audio call services. Nowadays more and more people use messengers as a means for communication in everyday life, negotiating of meetings, planning their actions, sending photos and audio messages. As of 2018, Telegram has more than 200 million active users in the world. In Ukraine, Telegram is especially popular in the capital and cities-millionaires. For this reason, it is very important to examine sensitive data during investigation that can provide information about suspect actions, motives, intentions [3].
Telegram can be used on different platforms (mobile - Android, iOS, Windows Phone, fixed - Windows, MacOS, Linux and web platform). According to results of the study of devices on which users install a messenger, the most popular operating system is Android (various estimates shows 70 to 85 percent for mobile devices).
The object of study is the problem of examining forensically important information, which is stored in databases of Telegram for Android (hereinafter Telegram). The emphasis is on identifying texts of messages and data on the interlocutors.
The subject of study is the analysis of the “cache4.db” database of the Telegram software and its rollback journal – the “cache4.db-wal” file.
Research methods. The method used in the work is based on a search for common trends and principles of an organization of an information in the main SQLite3 database of Telegram messenger with different input data. To achieve the mentioned goal, an experiment with test data was conducted, also it was checked and investigated a number of databases “cache4.db” and “cache4.db-wal” with known data about interlocutors and their correspondence.
Software. The study used the following software: operating systems Windows 10, Android 5.1.1, 6.0.1; Telegram for Android 3.12.0 (8421), 4.8.5 (12427), 4.9.0 (13371), 4.9.1 (13611), Microsoft Office 365 Home 16.0.10730.20102 (© 2012 Microsoft); SQLite Expert Personal 5.3.0.339 (© 2018 Coral Creek Software), Python 2.7, adb 1.0.31, Double Commander 0.7.3, AccessData FTK Imager 4.2.0.13 (© 2016 AccessData Group, Inc.), HexEdit v.4.0 (© 1999-2012 Expert Commercial Software Pty Ltd).
1. LOCATION AND TYPICAL FILE STRUCTURE OF TELEGRAM FOR ANDROID
Telegram version 4.9.1 (13611) was released in August 2018. At the same time, new releases of Telegram come out even several times a month. For instance, Telegram version 4.0.0 was released in May 2017, and Telegram 3.12.0 (8421) was relevant in the fall of 2016. There were dozens of other releases of the messenger between the outputs of the mentioned earlier versions.
In this paper, the focus was on the examining of databases of the latest versions of the messenger on examples of Telegram 4.8.5 (12427), 4.9.0 (13371), 4.9.1 (13611).
The experiment was conducted using the operating system Android 5.1.1 and Telegram for Android 4.9.1 (13611): a new account “Ukraine1991 123454321” was created, two contacts “Grygir27” and “Dmytro35” were added to the phonebook, two normal and two secret chats were created with them, two groups “Football1927”, “Volleyball234” and two channels “Sumy1665”, “Kyiv2018” were created with contacts “Grygir27” and “Dmytro35” added. Messages (text, graphics and files) were sent to chats, groups and channels that were created, as well as audio calls were made[1].
Further, a number of the “cache4.db” and “cache4.db-wal” files with pre-known information about interlocutors and their correspondence were examined and researched.
As a result, it was found that the Telegram data are stored in the logical partition “userdata” [1,2]:
- the “\data\org.telegram.messenger” folder - this directory contains the Telegram log files and the main SQLite database file (“cache4.db”) with the data of user's conversations;
- the “media\0\telegram” folder - this directory holds files sent and received via the messenger.
Figures 1, 2, 3, 4 show typical structures of the “org.telegram.messenger” and “media\0\telegram” folders, their “files” and “Telegram Images” subfolders respectively (for Telegram 4.9.1).
Figure 1 Typical structure of the “com.telegram.org” folder (Telegram 4.9.1)
Figure 2 Typical structure of the “~\org.telegram.messenger\files” folder with message-related information (Telegram 4.9.1).
Figure 3 Typical structure of the “media\0\telegram” folder with transmitted files (Telegram 4.9.1)
Figure 4 Typical structure of the “Telegram Images” folder with sent/received images
The Telegram message-related data is contained in the “~\org.telegram.messenger\files\cache4.db” file (hereinafter “cache4.db”) and in the “~\org.telegram.messenger\files\cache4.db –wal” file (further “cache4.db-wal”).
Figure 5 Typical structure of the “cache4.db” file (Telegram 4.9.1)
