July 14, 2022

The Workings of WhatsApp’s Backups (and Why You Should Enable End-to-End Encrypted Backups)

Table of contents

About This Blog Post

This blog post is a technical report of a presentation that I presented on June 10, 2022 for the second task of my Mobile Security course. I decided to investigate how WhatsApp backs up messages to the cloud with the “end-to-end encrypted backups” option toggled on and off. TL;DR at the bottom.

Introduction & Motivation

WhatsApp [1][2] is a cross-platform instant messaging app used by “more than 2 billion users in 180 countries” [3][5]. Available for Android, iPhone and Mac/Windows PC [4], “WhatsApp is free and offers simple, secure, reliable messaging and calling, available on phones all over the world” [3]. The focus of this blog post is to investigate a service that WhatsApp offers - backing up messages to a cloud service provider. On Android devices, WhatsApp uploads a backup of a client’s messages to Google Drive [28] whereas the backup is uploaded to iCloud for iPhones [29]. Additionally, WhatsApp has the option to “end-to-end encrypt” these backups to the cloud provider, a feature that is switched off by default and users have to switch on by themselves [44]; this is shown in Figure 1. A large part of the motivation for this blog post stems from why this isn’t toggled on by default. In this blog post, the workings of WhatsApp’s backups are delved into by performing Man-in-the-Middle attacks and the behavior of creating/restoring an end-to-end encrypted backup is explored.

Testing was done on a Google Pixel XL running Android 10 (Firmware: Marlin 10.0.0 (QP1A.191005.007.A3, Dec 2019) [6]) with WhatsApp APK version 2.22.11.70 downloaded from the WhatsApp website [4]. The testing device was supplied by the Institute of Applied Information Processing and Communications, TU Graz. The rest of this blog post will be written about WhatsApp run on Android with backups made to Google Drive.

Figure 1

Figure 1: The feature to back up messages and media on WhatsApp; Click on the three dots on the top right on the "home" page of WhatsApp (home activity) > Chats > Chat Backup; The end-to-end encrypted backup feature is off by default

Background

- The Signal Protocol

Outlined in “WhatsApp’s Encryption Overview” technical whitepaper (Version 6 November 15, 2021) [2][8] and as shown in Figure 2, WhatsApp uses the Signal Protocol [9] which is the basis for WhatsApp’s end-to-end encryption. Parts of the Signal Protocol are The XEdDSA and VXEdDSA Signature Schemes [10], The X3DH Key Agreement Protocol [11], The Double Ratchet Algorithm [12] and The Sesame Algorithm: Session Management for Asynchronous Message Encryption [13]. The X3DH Key Agreement Protocol is used to establish a shared secret key between two parties, the Double Ratchet Algorithm is used by the two parties to exchange encrypted messages based on the shared secret key and the Sesame Algorithm is used to manage message encryption sessions in an asynchronous and multi-device setting. The algorithms and specifications are not described in this blog post.

One thing that to pay heed to is that the Sesame Algorithm manages sessions between users in a multi-device setting - it is responsible for creating, deleting and the usage of sessions to support requirements. These requirements include users that may erase their session state and potentially restore from a backup. However, it is important to note that the method to backup messages and subsequently restore the backup does not have a technical specification from the signal protocol [14][15].

It is thus crucial to make a distinction between the following two points:

  • the backup/restoration of messages made to/from the cloud service provider that WhatsApp offers, and
  • the backup/restoration of messages using different sessions mentioned in the Sesame Algorithm.

Although the Signal Application [16] (not the Sesame Algorithm) offers users the ability to create backups of their messages [18][17][19], this does not abide to a technical specification nor is this a backup that is uploaded to a cloud service provider. It is up to the implementors of the Signal Protocol (WhatsApp and the Signal Application, in this case) to employ their own technique to create/restore backups, be it local backups or cloud-based backups.

Figure 2

Figure 2: The "WhatsApp Encryption Overview" technical whitepaper; on the right is the Introduction on page 3 of the technical whitepaper

- How WhatsApp Stores Data (Locally)

The database that WhatsApp uses to stores messages along with other media files can be found in the Media Store [20] - path: /storage/emulated/0/Android/media/com.whatsapp/WhatsApp/Databases/msgstore.db.crypt14. This is a SQLite Database that is encrypted [23][24] with AES-GCM-256 [22]. Since this is in the Media Store, any app with the READ_EXTERNAL_STORAGE permission can access it.

The keyfile to encrypt and decrypt this database is a 158 byte file called key that can be found in the App-Specific Storage [21] of WhatsApp at /data/data/com.whatsapp/files/key. Under normal conditions on a non-rooted Android device, obtaining this key is not possible because the App-Specific files of any app cannot be accessed by any other applications. However, it is trivial to pull these files on a rooted device using the Android Debug Bridge [25].

Once the key and the msgstore.db.crypt14 are pulled, the database can be decrypted using ElDavoo/WhatsApp-Crypt14-Crypt15-Decrypter [26]. Decrypting the database reveals 145 tables, the details of which were found using SQLite command-line shell [27] by running pragma table_info(table_name). The details of all the tables can be found in Appendix B. The content of a few select tables as well as images are shown in Figure B-1, B-2, B-3, B-4 and B-5 in the expandable sections of the tables in Appendix B.

What Is Being Backed-Up to the Cloud in the Default Mode?

On Android devices, users can create a backup of their messages to their Google Drive account [28] while iPhone users can back their messages up to their iCloud account [29]. By default, these backups are not end-to-end encrypted. The first goal is to figure out what is being backed up when the end-to-end encrypted backup option is disabled. To find this out, a Man-in-the-Middle attack [30] can be performed. Using mitmproxy [31] to launch a proxy server and installing mitmproxy’s certificate on the android device, WhatsApp will not back messages up to Google Drive because the application does not accept user-installed certificates. One method to bypass this issue is modify the Android application’s Network Security Config to accept user-installed certificates 1 [32]. After modifying, repackaging and signing, WhatsApp will not install 2. Instead of trying to figure out the reason behind the failure, an alternative that can disable SSL pinning [33] is considered. Disabling SSL pinning can be done using Frida [34] - “a dynamic instrumentation toolkit that lets users inject snippets of JavaScript on native applications on Windows, macOS, GNU/Linux, iOS, Android and QNX” [35]. It has three modes of operation [36]: injected, embedded and preloaded. Using HTTP Toolkit’s blog “Defeating Android Certificate Pinning with Frida” [37] (code directly available at: frida-script.js [38]), it is simple to bypass SSL Pinning, the output of which is shown in Figure 3.

Figure 3

Figure 3: Disabling SSL Pinning using Frida

- Analysis - Backup

After disabling SSL Pinning and intercepting communication with mitmproxy, the “backup” button in WhatsApp is pressed; the output of the mitmproxy interface is shown in Figure 4. To backup.googleapis.com, WhatsApp uploads msgstore.db.crypt14 along with chatsettingsbackup.db.crypt14, stickers.db.crypt14, commerce_backup.db.crypt14 and wallpapers.db.crypt14. It is here that an important point should be observed - the msgstore.db.crypt14 that exists on the Android device is uploaded to Google Drive. Any party that has access to this file will not be able to see any of the contents of this unless they have the key file to decrypt it. As far as the mitmproxy logs go, the actual key file is not being uploaded to Google Drive 3 4.

Figure 4

Figure 4: The output on the mitmproxy interface when the "backup" button in WhatsApp is pressed (non end-to-end encrypted); The blue rectangles hide the phone number registered to the WhatsApp account making the backup

- Analysis - Restoring

WhatsApp and all associated files are deleted before being installed again. While registering for an existing account that has a backup in Google Drive, WhatsApp asks whether a restoration is wanted. Selecting “No” would render the backup permanently unusable. Since the point of this subsection is to see what happens when a backup is being restored, the “Restore” button is selected and the communication of the ssl-disabled app (same as before) is once again intercepted with mitmproxy. The results are shown in Figure 5.

Ignoring the static.whatsapp.net/downloadable / sticker calls, the first few calls are made to v.whatsapp.net/v2/ to the /exist, /code, /register, and /client_log end-points with one query parameter: ENC. The query values are all encrypted 5 but a good guess of what it could contain can be made based on the (json) responses. The response of the call made to /register is:

{
    "login":"THE_PHONE_NUMBER_USED_TO_REGISTER",
    "security_code_set":false,
    "status":"ok",
    "type":"existing"
}

The responses of the other end-points made to v.whatsapp.net/v2/ are similar.

After the registration requests are made to v.whatsapp.net/v2/ and once successfully authenticated, requests are made to backup.googleapis.com to retrieve the same files that were uploaded in the - Analysis - Backup section and in Figure 4.

Figure 5

Figure 5: The output on the mitmproxy interface when the "Restore" button in WhatsApp is pressed (non end-to-end encrypted); The blue rectangles hide the phone number registered to the WhatsApp account and also hide encrypted data used while registering an account (preceded by ENC= in the calls)

- Where Does the Key Come From?

One observation to make here is that the key file does not appear to be transmitted to or from any server on the mitmproxy logs in Figure 4 and Figure 5. This is a particularly important problem to think about because this key decrypts the crypt14 files that are downloaded when restoring a backup. In the form of a question:

When restoring a backup, where does the key that decrypts the downloaded crypt14 files come from?

There are two broad possibilities:

  1. The key is sent from an external source
  2. The key is generated on the client’s device

The second option is extremely unlikely. For the WhatsApp application to deterministically generate the key that can decrypt the encrypted files would be huge security risk and flaw. Instead, the more likely possibility is that the key is sent from an external source. The question now turns to what the external source could be. There are a few immediate ideas that can be put forth:

  1. The key is sent from Google’s servers
  2. The key is sent from WhatsApp’s servers

Of course, this is not an exhaustive list of all the potential external sources that could hold the key. Other ideas could be to use secret sharing [40][41] to get the key; however, the more likely case is that the key is sent from Google’s servers or WhatsApp’s servers. For some time, this information was not released in any blog post or technical whitepaper [citiaton required] 6. In September 2020, user ‘defalt’ provided an answer [24] to a question on security.stackexchange.com that asked, “How can WhatsApp restore local or Google Drive Backups?”. This answer contains “filtered logs of the whatsapp.log file when the client decrypts the backup” with “information about each log in the comments”. A few days later, user “u/crawl_dht” made a post on Reddit [23] which had the same content as the answer that user “defalt” wrote. Checking the log on the forums 7 shows that the key is retrieved from WhatsApp’s servers using XMPP [46] with indications that WhatsApp may rotate keys after some period of time.

Two simple points can be stated after piecing this information together:

  1. crypt14 database files that contain messages are backed up to Google Drive
  2. The key that can decrypt the crypt14 files exist on WhatsApp’s servers in case a user restores a backup

From a privacy standpoint, this could potentially be a problem. There are many ways that WhatsApp (the company) could gain information about a user and this is one of the (scarier) ways that could let WhatsApp read all messages. Of course, this assumes that Google would hand over a backup on their servers to WhatsApp, which is highly unlikely (but not an impossibility). Users have no choice but to trust that WhatsApp won’t do this. If law enforcement authorities request information, WhatsApp will supply only limited information according to their page about “Information for Law Enforcement Authorities” [47].

Users shouldn’t have to put trust in a company; they should have complete/enough trust in the underlying protocols. Keep in mind that this is not saying that WhatsApp could listen to messages in transit - the Signal Protocol ensures end-to-end encrypted chats. This is saying that WhatsApp could gain access to messages after the transit is done, after the message is stored on a device, (more importantly) after the messages have been backed up to Google Drive and (most importantly) after Google has handed a backup over to WhatsApp. There may be numerous practical issues that could hinder a malicious actor at WhatsApp such as Google unwilling to hand over a backup or the Key protected behind extremely high access privileges, but given the right set of circumstances and/or social engineering, a way to gain access to all the messages of backup could be possible.

End-to-End Encrypted Backups

- Turning on End-to-End Encrypted Backups

This part of this blog post examines the behavior of backups when the end-to-end encrypted backups option is switched on. The steps to turn this feature on are shown in Figure 6. To turn on end-to-end encrypted backups, a password must be supplied which WhatsApp warns cannot be forgotten. In the event that a user does not wish to set their own password, WhatsApp offers to create a 64-digit encryption key instead.

Figure 6

Figure 6: Turning on end-to-end encrypted backups; a password that must not be forgotten should be set

As soon as end-to-end encrypted backups is enabled, WhatsApp immediately makes a back up to Google Drive. The intercepted HTTP communication made by mitmproxy is shown in Figure 7. At first glance, the uploaded logs look identical. On closer inspection, it can be seen that the extensions of the files have changed - instead of backing up crypt14 files, the files are now crypt15. Also to be noted is that the key file (in the app-specific storage on the Android device) changes and is called encrypted_backup.key.

Figure 7

Figure 7: mitmproxy logs when end-to-end encrypted backups is enabled. Notice that the files are no longer crypt14, but crypt15.

- Restoring an End-to-End Encrypted Backup

After deleting WhatsApp and clearing all files, the app is installed again. After following a similar registration procedure as before, WhatsApp shows that a end-to-end encrypted backup can be restored by supplying a password. There are a total of 5 incorrect attempts before a timeout is enforced [48]. The steps taken to restore a backup are shown in Figure 8.

Figure 8

Figure 8: Restoring an end-to-end encrypted backup

The corresponding HTTP communication intercepted by mitmproxy is shown in Figure 9. These logs are also identical to the logs when a non-“end-to-end encrypted backup” is being restored, the only difference being the extensions of the files that are being retrieved (crypt15 instead of crypt14).

Figure 9

Figure 9: mitmproxy logs while an end-to-end encrypted backup is being restored

WhatsApp’s Technical Paper - Security of End-to-End Encrypted Backups

On September 10, 2021, WhatsApp released a technical whitepaper title “Security of End-to-End Encrypted Backups” [7] and a corresponding blog post [49]. Paragraph 5, 6 and 7 on Page 3 in the introduction section of the technical whitepaper are below (emphasis mine):

With the introduction of end-to-end encrypted backups, WhatsApp has created an HSM (Hardware Security Module) based Backup Key Vault to securely store per-user encryption keys for user backups in tamper-resistant storage, thus ensuring stronger security of users’ message history.

With end-to-end encrypted backups enabled, before storing backups in the cloud, the client encrypts the chat messages and all the messaging data (i.e. text, photos, videos, etc) that is being backed up using a random key that’s generated on the user’s device.

The key to encrypt the backup is secured with a user-provided password. The password is unknown to WhatsApp, the user’s mobile device cloud partners, or any third party. The key is stored in the HSM Backup Key Vault to allow the user to recover the key in the event the device is lost or stolen. The HSM Backup Key Vault is responsible for enforcing password verification attempts and rendering the key permanently inaccessible after a certain number of unsuccessful attempts to access it. These security measures provide protection against brute force attempts to retrieve the key.

While it is not specified in these paragraphs, the Hardware Security Module (HSM) based Backup Key Vault is controlled by WhatsApp. Reading further along the technical document confirms that for resilience (in case one HSM based Backup Key Vault goes down), the HSM based Backup Key Vault is deployed in 5 data center sites (Page 5-6 - Section “HSM based Backup Key Vault resilience”). In the paragraphs above, it was emphasized that “The HSM Backup Key Vault is responsible for enforcing password verification attempts and rendering the key permanently inaccessible after a certain number of unsuccessful attempts to access it”. It appears that the password is sent to WhatsApp’s servers to be validated so that the HSM based Backup Key Vault prevents brute force attacks, but this is not true.

On page 7-9 in the technical whitepaper (from Section “Backup generation and backup key registration” to Section “Key retrieval”), the workings of the backup key registration and retrieval is explained. WhatsApp uses the OPAQUE [50][51] Protocol to register and retrieve the backup key. Going through the workings of the protocol reveals that it is an asynchronous Password-Authenticated Key Exchange (PAKE) - a class of protocols where two parties derive a same shared secret key based on a password without having one party ever having to transmit the password. This does, however, require a registration process beforehand which does not require the transmitting of the password. This blog post does not cover the math behind OPAQUE 8. One of the resources mentioned at the end of the technical whitepaper is the Opaque key exchange protocol implementation in rust [43] (Resource 3 in the technical whitepaper). The rest of this section in the blog post will draw parallels between WhatsApp’s technical whitepaper and the rust implementation.

The steps in Section “Backup generation and backup key registration” and Section “Key retrieval” not only explain how the backup keys are registered and retrieved, but also how they are encrypted in a way that WhatsApp’s fleet of the HSM based Backup Key Vault cannot know the key to encrypt the backup-key (using the OPAQUE Protocol). To prevent confusion and to make the terminology clearer, there are two keys:

  1. The key that is used to encrypt the backup of messages, referred to as the backup-key.
  2. The key that is used to encrypt the backup-key, henceforth referred to as the encryption-key.

One of the results of the OPAQUE protocol is a value that is generated on the client side, OPAQUE_K. This is equivalent to the export_key [56] of the rust implementation. OPAQUE_K (or rather, export_key) can only be derived with the knowledge of the password. Because the password never gets transmitted to the server, this makes OPAQUE_K suitable to be used as the encryption-key. The backup-key is encrypted using AES-GCM with the encryption-key and the result of the encryption (an encrypted key) is transmitted to the HSM based Backup Key Vault to be stored. When restoring a backup, the knowledge of the password is tested using the OPAQUE protocol and if successful, the encrypted key is returned to the client. The client derives OPAQUE_K (the encryption-key) using the password to decrypt the encrypted key, the result of which is the backup-key. The backup-key is then used to restore messages in the backup.

For further reading and for a proper security assessment of the technical report, the reader is encouraged to read the NCCGroup’s Public Report about WhatsApp’s End-to-End Encrypted Backups Security Assessment [55]. Further thoughts on the technical whitepaper are written in Appendix A - Thoughts on the Technical Whitepaper.

Conclusion

The workings of WhatsApp’s backups were investigated and a few interesting mechanisms were found. The WhatsApp application stores all data in an encrypted sqlite database and the key to decrypt this database exists on the user’s device (only the WhatsApp application can access the key). To make a backup in cloud storage (Google Drive / iCloud), the WhatsApp application sends the encrypted database from the user’s device to the cloud storage and the reverse happens when a user restores a backup. At this point, an important question was raised - during the restoring of a backup on a new device, how does the key to decrypt the encrypted database get transmitted? The first conclusion was drawn here - WhatsApp’s servers have copies of the keys. Although necessary to decrypt the backup, this is problematic because WhatsApp (the company) could decrypt backups as long as they gained access to the backup and the corresponding key, however highly unlikely as it may seem.

When a user enables the “end-to-end encrypted backups” option, the WhatsApp application requires the user to supply a password which is used as a part to encrypt the key. The resulting encrypted-key is securely stored on WhatsApp’s Hardware Security Module (HSM) based Backup Key Vault across 5 data center sites. While retrieving the encrypted-key to restore a backup, the HSM based Backup Key Vault is responsible for enforcing password validation to prevent brute force attacks. The password itself is never sent to WhatsApp’s HSM based Backup Key Vault as WhatsApp uses the OPAQUE protocol to verify that both the user and the server have the same shared secret key without having the password ever transmitted, thus ensuring that the retrieved encrypted-key can only be decrypted by parties with the knowledge of the password, i.e. only the user.

The TL;DR: Enable “end-to-end encrypted backups” in your WhatsApp application and DON’T forget the password. There is no way to decrypt the backup if the password is lost.

Acknowledgements

Thanks to The Institute of Applied Information Processing and Communications (IAIK), TU Graz for lending the testing phone that was used for this task.

For guidance, constant help and reviewing this blog post, thanks to Florian Draschbacher, IAIK, TU Graz (https://www.iaik.tugraz.at/person/florian-draschbacher/) .

For help with the OPAQUE protocol, special thanks to Lena Heimberger, IAIK, TU Graz (https://www.iaik.tugraz.at/person/lena-heimberger/) .

For helping populate the data, (in alphabetical order) thanks to Dheeraj Goli (LinkedIn), Prakruti Singh (LinkedIn), Rahul Sangamker (LinkedIn), Mohammed Abdul Khaliq (Github) and Vathsavi Boggarapu (LinkedIn).

Footnotes

1 This was my first task in my Mobile Security course. I did it (successfully) for three apps.

2 Although I’m sure this is a “me” problem (i.e., I did something wrong at some point in time), I’m confident that I did this properly twice. I’ve done this before as a part of my first task in Mobile Security and I did not run into any issues during that time. I think there may have been something going on under the hood, but I didn’t delve too deep into it as alternatives existed (which is explained in this blog post).

3 mitmproxy only intercepts HTTP/HTTPS communication (at least it did for the mode I was running in). There may be some data that is being transmitted via sockets that doesn’t follow the Hypertext Transmission Protocol. One way this can be checked is by using a packet analyzer like Wireshark (https://www.wireshark.org/) or tcpdump (https://www.tcpdump.org/). It will become apparent later in the blog post that more important communication is done with something other than HTTP. (Spoiler Alert: It is extremely likely that keys are exchanged between WhatsApp’s servers and users’ devices using XMPP)

4 A blog post on the Google Security Blog from October 12, 2018 written by Troy Kensinger titled “Google and Android have your back by protecting your backups” [39] says:

Starting in Android Pie, devices can take advantage of a new capability where backed-up application data can only be decrypted by a key that is randomly generated at the client. This decryption key is encrypted using the user’s lockscreen PIN/pattern/passcode, which isn’t known by Google. Then, this passcode-protected key material is encrypted to a Titan security chip on our datacenter floor.

I don’t know if the backups made by WhatsApp to Google Drive are backed-up with this method. If they are backed up using this method, then that’s an added security bonus that is not visible from the mitmproxy logs. The rest of (my) blog post assumes that WhatsApp does not take advantage of this capability for two reasons:

  • There is no evidence in the mitmproxy logs that any “passcode-protected key material” is being sent to Google via HTTP.
  • The msgstore.db.crypt14 doesn’t appear to change. Using mitmproxy, I downloaded the backup after it left the Android device from the mitmproxy server (my computer) and I was able to decrypt this backup using the key file to reveal all the messages. If it was encrypted with a key that is randomly generated at the client, I wouldn’t be able to decrypt it with just WhatsApp’s key.

I don’t think this capability is used, as neat as it is.

5 Note that this is different than the HTTPS encryption because this is encryption done by the WhatsApp application internally. If someone wanted to, they could meticulously go through the code to break down the encrypted data. We don’t really need to as the responses give enough clues about what the request could be.

6 I’ve spent a lot of time trying to dig through old blog posts, technical whitepapers, articles, posts, comments and any piece of literature on internet that could officially indicate how WhatsApp transferred the key from an external source/server to the client’s device. Frustratingly, I came up empty handed. If there is any text before September 2020 that indicates how WhatsApp transfers their keys from an external source/server to a client’s device that is not [23], [24] or [45], please shoot me an email - contact.

7 An independent analysis to check whether the content of the whatsapp.log matches the content mentioned in the security.stackexchange.com has not been done. If someone verifies that the log generated on your end matches/doesn’t match to the log provided by user defalt or u/crawl_dht, please send me an email with the logs and your name will be credited here - contact.

8 Here are two good resources about understanding how OPAQUE works: A YouTube video by Prof. Bill Buchanan [52] and a blog post on cloudflare by Tatiana Bradley [53].

References

[1] https://www.whatsapp.com/

[2] https://www.whatsapp.com/security/WhatsApp-Security-Whitepaper.pdf

[3] https://www.whatsapp.com/about/

[4] https://www.whatsapp.com/download

[5] (2020, February 12). Two Billion Users -- Connecting the World Privately. https://blog.whatsapp.com/two-billion-users-connecting-the-world-privately

[6] https://developers.google.com/android/ota#marlin

[7] Security of End-To-End Encrypted Backups, WhatsApp Security Whitepaper (Version 1 Originally published September 10, 2021) https://www.whatsapp.com/security/WhatsApp_Security_Encrypted_Backups_Whitepaper.pdf. In case this link goes down / it cannot be found, click here to download.

[8] https://www.whatsapp.com/security/

[9] https://signal.org/docs/

[10] Perrin, T. (2016). The xeddsa and vxeddsa signature schemes. Specification. Oct. https://signal.org/docs/specifications/xeddsa/

[11] Marlinspike, M., & Perrin, T. (2016). The x3dh key agreement protocol. Open Whisper Systems, 283. https://signal.org/docs/specifications/x3dh/

[12] Perrin, T., & Marlinspike, M. (2016). The double ratchet algorithm. GitHub wiki. https://signal.org/docs/specifications/doubleratchet/

[13] Marlinspike, M., & Perrin, T. (2017). The sesame algorithm: session management for asynchronous message encryption. Revision, 2, 2017-04. https://signal.org/docs/specifications/sesame/

[14] https://whispersystems.discoursehosting.net/t/lets-talk-about-backups/2237

[15] Jonathan Heathcote (Retrieved 2021, June 18). Decrypting Signal for Android's Backup Files. https://web.archive.org/web/20210618012018/https://jhnet.co.uk/articles/signal_backups

[16] https://github.com/signalapp

[17] https://github.com/signalapp/Signal-iOS/pull/3169

[18] https://github.com/signalapp/Signal-Android/commit/24e573e537639f6f8ff40fd774cf9ff079bbacce

[19] https://support.signal.org/hc/en-us/articles/360007059752-Backup-and-Restore-Messages

[20] https://developer.android.com/training/data-storage/shared/media

[21] https://developer.android.com/training/data-storage/app-specific

[22] McGrew, D.A., & Viega, J. (2005). The Galois/Counter Mode of Operation (GCM). https://csrc.nist.rip/groups/ST/toolkit/BCM/documents/proposedmodes/gcm/gcm-spec.pdf

[23] https://www.reddit.com/r/cybersecurity/comments/j1dahs/

[24] https://security.stackexchange.com/questions/136072/how-can-whatsapp-restore-local-or-google-drive-backups/238813#238813

[25] https://developer.android.com/studio/command-line/adb

[26] https://github.com/ElDavoo/WhatsApp-Crypt14-Crypt15-Decrypter

[27] https://www.sqlite.org/cli.html

[28] https://faq.whatsapp.com/android/chats/how-to-back-up-to-google-drive

[29] https://faq.whatsapp.com/iphone/chats/how-to-back-up-to-icloud

[30] https://en.wikipedia.org/wiki/Man-in-the-middle_attack

[31] https://mitmproxy.org/

[32] Steve Benson (2020, April 17). Modifying Android Apps to Allow TLS Intercept with User CAs. https://hurricanelabs.com/blog/modifying-android-apps-to-allow-tls-intercept-with-user-cas/

[33] Jeffery Walton, JohnSteven, Jim Manico, Kevin Wall, Ricardo Iramar (2021). Certificate and Public Key Pinning. https://owasp.org/www-community/controls/Certificate_and_Public_Key_Pinning

[34] https://frida.re/

[35] https://frida.re/docs/home/

[36] https://frida.re/docs/modes/

[37] Tim Perry (2021, July 6). Defeating Android Certificate Pinning with Frida. https://httptoolkit.tech/blog/frida-certificate-pinning/

[38] https://github.com/httptoolkit/frida-android-unpinning/blob/main/frida-script.js (commit: 91422adeda7982e32d761e7efe6ac95286eb8254)

[39] Troy Kensinger (2018, October 12). Google and Android have your back by protecting your backups. https://security.googleblog.com/2018/10/google-and-android-have-your-back-by.html

[40] Shamir, A. (1979). How to share a secret. Communications of the ACM, 22(11), 612-613. https://dl.acm.org/doi/pdf/10.1145/359168.359176

[41] Blakley, G. R. (1979, December). Safeguarding cryptographic keys. In Managing Requirements Knowledge, International Workshop on (pp. 313-313). IEEE Computer Society. https://www.semanticscholar.org/paper/Safeguarding-cryptographic-keys-Blakley/32d21ccc21a807627fcb21ea829d1acdab23be12

[43] https://docs.rs/opaque-ke/latest/opaque_ke/

[44] https://faq.whatsapp.com/general/chats/how-to-turn-on-and-turn-off-end-to-end-encrypted-backup/

[45] https://security.stackexchange.com/questions/136072/how-can-whatsapp-restore-local-or-google-drive-backups/145636#145636

[46] https://xmpp.org/

[47] https://faq.whatsapp.com/general/security-and-privacy/information-for-law-enforcement-authorities/

[48] https://faq.whatsapp.com/general/chats/cant-remember-password-for-encrypted-backup

[49] Slavik Krassovsky, Gabriel Cadden (2021, September 10). How WhatsApp is enabling end-to-end encrypted backups. https://engineering.fb.com/2021/09/10/security/whatsapp-e2ee-backups/

[50] Jarecki, S., Krawczyk, H., & Xu, J. (2018, April). OPAQUE: an asymmetric PAKE protocol secure against pre-computation attacks. In Annual International Conference on the Theory and Applications of Cryptographic Techniques (pp. 456-486). Springer, Cham. https://eprint.iacr.org/2018/163.pdf

[51] H. Krawczyk (2019, October 21). The OPAQUE Asymmetric PAKE Protocol. draft-krawczyk-cfrg-opaque-03. https://tools.ietf.org/id/draft-krawczyk-cfrg-opaque-03.html

[52] Bill Buchanan (2020, May 3). OPAQUE Asymmetric PAKE Protocol https://www.youtube.com/watch?v=4YntXt1Jobk

[53] Tatiana Bradley (2020, August 8). OPAQUE: The Best Passwords Never Leave your Device. https://blog.cloudflare.com/opaque-oblivious-passwords/

[54] https://github.com/novifinancial/opaque-ke/

[55] Gérald Doussot, Marie-Sarah Lacharité, Eric Schorn, Jennifer Fernick (2021, October 27). Public Report – WhatsApp End-to-End Encrypted Backups Security Assessment - Version 1.2 https://research.nccgroup.com/2021/10/27/public-report-whatsapp-end-to-end-encrypted-backups-security-assessment/

[56] https://docs.rs/opaque-ke/latest/opaque_ke/#export-key

Appendix

- Appendix A - Thoughts on the Technical Whitepaper

This section details my thoughts about the “Security of End-to-End Encrypted Backups” technical whitepaper by WhatsApp. Even after gaining a semi-decent understanding about the OPAQUE protocol, the technical whitepaper is quite confusing. There are a few issues with the technical whitepaper that compound into a big mess that took me much longer to figure out what was being said instead of actually understanding it. The steps in the resources section in the technical whitepaper (page 9) [50][51][43] don’t exactly correlate with the steps that WhatsApp outlines (most probably because WhatsApp tweaked it for their architecture). The steps in the technical whitepaper contain ambiguous wording, declare improperly defined variables and don’t use these variables at later stages, giving rise to more confusion.

For example, the section “Registration” (page 7-8):

  1. WhatsApp application asks the user to enter the password.
  2. The client utilizes the OpaqueClientRegistrationStart() function of the OPAQUE library to obtain byte buffer RegistrationRequest and sends it to the server. The response to RegistrationRequest will be a message RegistrationResponse, a signature over RegistrationResponse by the HSM RegistrationResponse_sig, and a challenge OPAQUE_C. The challenge is a random number generated by the server to prevent replay attacks
  3. The client calls the OpaqueClientRegistrationFinish( ) OPAQUE api, the result of which is buffer RegistrationUpload and a OPAQUE_K, a symmetric key which is used to secure backup key K. The client encrypts key K using AES-GCM with OPAQUE_K and sends it along with the buffer RegistrationUpload to the server.
  4. The registration is complete.

RegistrationResponse, RegistrationResponse_sig and OPAQUE_C are no longer used after Step 2. It is assumed that they are used as arguments in OpaqueClientRegistrationFinish() in Step 3.

More importantly, it appears as though OPAQUE_K is derived from data sent by the server as it is a result of OpaqueClientRegistrationFinish(), whose arguments in turn appear as though they are from the response from the server. Since OPAQUE_K is used as the encryption-key (the key used to encrypt the backup-key), it would be alarming that the server has the necessary pieces of information to derive OPAQUE_K. However, after a (slightly painful) comparison between the technical whitepaper and the opaque key exchange protocol implementation in rust [43], I assume that OPAQUE_K is equivalent to export_key of the rust implementation [56] (note that this is an assumption). The OPAQUE_K is (possibly) derived from one of the outputs of OpaqueClientRegistrationStart() in step 2, ClientRegistration (mentioned in the rust implementation and NOT in the technical whitepaper). The ClientRegistration in turn can only be derived with knowledge of the password. From the rust implementation documentation of export_key [56] (or OPAQUE_K in the case of the technical whitepaper) (emphasis mine):

The export key is a pseudorandomly distributed 32-byte string output by both the Client Registration Finish and Client Login Finish steps. The same export key string will be output by both functions only if the exact same password is passed to ClientRegistration::start and ClientLogin::start.

The export key retains as much secrecy as the password itself, and is similarly derived through an evaluation of the slow hashing function. Hence, only the parties which know the password the client uses during registration and login can recover this secret, as it is never exposed to the server. As a result, the export key can be used (separately from the OPAQUE protocol) to provide confidentiality and integrity to other data which only the client should be able to process. For instance, if the server is expected to maintain any client-side secrets which require a password to access, then this export key can be used to encrypt these secrets so that they remain hidden from the server

As I mentioned earlier, I think the technical whitepaper is not “technical-enough”. Something I wish I knew about earlier was the NCCGroup’s Public Report about WhatsApp’s End-to-End Encrypted Backups Security Assessment [55]. It pretty much answered everything I had questions about and I encourage the reader to go through it. The report is much more detailed and is more “proper” by security standards, as compared to this blog post.

The section “Backup generation and backup key registration” in the technical whitepaper makes sense in retrospect. However, it isn’t very clear on first glance (with a primitive understanding of the OPAQUE protocol). It may have taken me longer to catch on to what the authors of the paper were illustrating in their steps, but the list of resources at the end of the technical whitepaper did not entirely aid in the understanding of what the authors wrote. The main issue stems from the creation of OPAQUE_K and the usage of OPAQUE_C and RegistrationResponse_sig because these aren’t the same variable names that the rust implementation reference uses [54]. Also, WHY WOULD YOU MENTION VARIABLES THAT YOU DON’T USE LATER?! (gosh, I’m beginning to sound like my compiler). Only after reading through the NCCGroup’s Public Report about WhatsApp’s End-to-End Encrypted Backups Security Assessment [55] does it become painfully obvious that OPAQUE_K is only known by the client (Page 6 of the NCCGroup’s Assessment - section “Encrypted Backup Architecture”, sub-section “Overview”). In my opinion, the section “Backup generation and backup key registration” in the WhatsApp technical whitepaper isn’t entirely well written for people reading it for the first time (or even for people who know how OPAQUE works).

- Appendix B - msgstore.db.crypt14 content

Details of each table were found by running pragma table_info(table_name) in the SQLite command-line shell [27]. The results of these commands have been consolidated and displayed in collapsible groups below that can be clicked on to be expanded. A few tables of interest (according to me) are chat, jid, message, and message_quoted. Contents of these tables are shown in these five figures (in the collabsible groups):

Screenshots made on SQLite Viewer: https://inloop.github.io/sqlite-viewer/.

agent_devices
cid name type notnull dflt_value pk
0 agent_id TEXT 1 null 1
1 agent_name TEXT 1 null 0
2 device INTEGER 0 null 0
3 last_modified_time INTEGER 0 null 0
4 is_deleted BOOLEAN 0 null 0
agent_message_attribution
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 null 1
1 agent_id TEXT 1 null 0
audio_data
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 null 1
1 waveform BLOB 0 null 0
available_message_view
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 0
1 sort_id INTEGER 0 null 0
2 chat_row_id INTEGER 0 null 0
3 from_me INTEGER 0 null 0
4 key_id TEXT 0 null 0
5 sender_jid_row_id INTEGER 0 null 0
6 sender_jid_raw_string 0 null 0
7 status INTEGER 0 null 0
8 broadcast INTEGER 0 null 0
9 recipient_count INTEGER 0 null 0
10 participant_hash TEXT 0 null 0
11 origination_flags INTEGER 0 null 0
12 origin INTEGER 0 null 0
13 timestamp INTEGER 0 null 0
14 received_timestamp INTEGER 0 null 0
15 receipt_server_timestamp INTEGER 0 null 0
16 message_type INTEGER 0 null 0
17 text_data TEXT 0 null 0
18 starred INTEGER 0 null 0
19 lookup_tables INTEGER 0 null 0
20 message_add_on_flags INTEGER 0 null 0
21 data 0 null 0
22 media_url 0 null 0
23 media_mime_type 0 null 0
24 media_size 0 null 0
25 media_name 0 null 0
26 media_caption 0 null 0
27 media_hash 0 null 0
28 media_duration 0 null 0
29 latitude 0 null 0
30 longitude 0 null 0
31 thumb_image 0 null 0
32 raw_data 0 null 0
33 quoted_row_id 0 null 0
34 mentioned_jids 0 null 0
35 multicast_id 0 null 0
36 edit_version 0 null 0
37 media_enc_hash 0 null 0
38 payment_transaction_id 0 null 0
39 preview_type 0 null 0
40 receipt_device_timestamp 0 null 0
41 read_device_timestamp 0 null 0
42 played_device_timestamp 0 null 0
43 future_message_type 0 null 0
44 table_version 0 null 0
45 expire_timestamp INTEGER 0 null 0
46 keep_in_chat INTEGER 0 null 0
away_messages
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 jid TEXT 1 null 0
call_log
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 jid_row_id INTEGER 0 null 0
2 from_me INTEGER 0 null 0
3 call_id TEXT 0 null 0
4 transaction_id INTEGER 0 null 0
5 timestamp INTEGER 0 null 0
6 video_call INTEGER 0 null 0
7 duration INTEGER 0 null 0
8 call_result INTEGER 0 null 0
9 bytes_transferred INTEGER 0 null 0
10 group_jid_row_id INTEGER 1 0 0
11 is_joinable_group_call INTEGER 0 null 0
12 call_creator_device_jid_row_id INTEGER 1 0 0
13 call_random_id TEXT 0 null 0
call_log_participant_v2
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 call_log_row_id INTEGER 0 null 0
2 jid_row_id INTEGER 0 null 0
3 call_result INTEGER 0 null 0
chat
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 jid_row_id INTEGER 0 null 0
2 hidden INTEGER 0 null 0
3 subject TEXT 0 null 0
4 created_timestamp INTEGER 0 null 0
5 display_message_row_id INTEGER 0 null 0
6 last_message_row_id INTEGER 0 null 0
7 last_read_message_row_id INTEGER 0 null 0
8 last_read_receipt_sent_message_row_id INTEGER 0 null 0
9 last_important_message_row_id INTEGER 0 null 0
10 archived INTEGER 0 null 0
11 sort_timestamp INTEGER 0 null 0
12 mod_tag INTEGER 0 null 0
13 gen REAL 0 null 0
14 spam_detection INTEGER 0 null 0
15 unseen_earliest_message_received_time INTEGER 0 null 0
16 unseen_message_count INTEGER 0 null 0
17 unseen_missed_calls_count INTEGER 0 null 0
18 unseen_row_count INTEGER 0 null 0
19 plaintext_disabled INTEGER 0 null 0
20 vcard_ui_dismissed INTEGER 0 null 0
21 change_number_notified_message_row_id INTEGER 0 null 0
22 show_group_description INTEGER 0 null 0
23 ephemeral_expiration INTEGER 0 null 0
24 last_read_ephemeral_message_row_id INTEGER 0 null 0
25 ephemeral_setting_timestamp INTEGER 0 null 0
26 ephemeral_disappearing_messages_initiator INTEGER 0 null 0
27 unseen_important_message_count INTEGER 1 0 0
28 group_type INTEGER 1 0 0
29 last_message_reaction_row_id INTEGER 0 null 0
30 last_seen_message_reaction_row_id INTEGER 0 null 0
31 unseen_message_reaction_count INTEGER 0 null 0
32 growth_lock_level INTEGER 0 null 0
33 growth_lock_expiration_ts INTEGER 0 null 0
34 last_read_message_sort_id INTEGER 0 null 0
35 display_message_sort_id INTEGER 0 null 0
36 last_message_sort_id INTEGER 0 null 0
37 last_read_receipt_sent_message_sort_id INTEGER 0 null 0

Figure B-1

Figure B-1: Contents of the table 'chat' in the msgstore.db.crypt14 (column order between the table info and the image is preserved);

chat_view
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 0
1 raw_string_jid TEXT 0 null 0
2 hidden INTEGER 0 null 0
3 subject TEXT 0 null 0
4 created_timestamp INTEGER 0 null 0
5 display_message_row_id INTEGER 0 null 0
6 last_message_row_id INTEGER 0 null 0
7 last_read_message_row_id INTEGER 0 null 0
8 last_read_receipt_sent_message_row_id INTEGER 0 null 0
9 last_important_message_row_id INTEGER 0 null 0
10 archived INTEGER 0 null 0
11 sort_timestamp INTEGER 0 null 0
12 mod_tag INTEGER 0 null 0
13 gen REAL 0 null 0
14 spam_detection INTEGER 0 null 0
15 unseen_earliest_message_received_time INTEGER 0 null 0
16 unseen_message_count INTEGER 0 null 0
17 unseen_missed_calls_count INTEGER 0 null 0
18 unseen_row_count INTEGER 0 null 0
19 unseen_message_reaction_count INTEGER 0 null 0
20 last_message_reaction_row_id INTEGER 0 null 0
21 last_seen_message_reaction_row_id INTEGER 0 null 0
22 plaintext_disabled INTEGER 0 null 0
23 vcard_ui_dismissed INTEGER 0 null 0
24 change_number_notified_message_row_id INTEGER 0 null 0
25 show_group_description INTEGER 0 null 0
26 ephemeral_expiration INTEGER 0 null 0
27 last_read_ephemeral_message_row_id INTEGER 0 null 0
28 ephemeral_setting_timestamp INTEGER 0 null 0
29 ephemeral_disappearing_messages_initiator INTEGER 0 null 0
30 unseen_important_message_count INTEGER 0 null 0
31 group_type INTEGER 0 null 0
32 growth_lock_level INTEGER 0 null 0
33 growth_lock_expiration_ts INTEGER 0 null 0
34 last_read_message_sort_id INTEGER 0 null 0
35 display_message_sort_id INTEGER 0 null 0
36 last_message_sort_id INTEGER 0 null 0
37 last_read_receipt_sent_message_sort_id INTEGER 0 null 0
conversion_tuples
cid name type notnull dflt_value pk
0 jid_row_id INTEGER 0 null 1
1 data TEXT 0 null 0
2 source TEXT 0 null 0
3 biz_count INTEGER 0 null 0
4 has_user_sent_last_message BOOLEAN 0 null 0
5 last_interaction INTEGER 0 null 0
deleted_chat_job
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 chat_row_id INTEGER 1 null 0
2 block_size INTEGER 0 null 0
3 deleted_message_row_id INTEGER 0 null 0
4 deleted_starred_message_row_id INTEGER 0 null 0
5 deleted_messages_remove_files BOOLEAN 0 null 0
6 deleted_categories_message_row_id INTEGER 0 null 0
7 deleted_categories_starred_message_row_id INTEGER 0 null 0
8 deleted_categories_remove_files BOOLEAN 0 null 0
9 deleted_message_categories TEXT 0 null 0
deleted_messages_ids_view
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 0
1 sort_id INTEGER 0 null 0
2 chat_row_id INTEGER 0 null 0
3 message_type INTEGER 0 null 0
deleted_messages_view
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 0
1 sort_id INTEGER 0 null 0
2 chat_row_id INTEGER 0 null 0
3 from_me INTEGER 0 null 0
4 key_id TEXT 0 null 0
5 sender_jid_row_id INTEGER 0 null 0
6 sender_jid_raw_string 0 null 0
7 status INTEGER 0 null 0
8 broadcast INTEGER 0 null 0
9 recipient_count INTEGER 0 null 0
10 participant_hash TEXT 0 null 0
11 origination_flags INTEGER 0 null 0
12 origin INTEGER 0 null 0
13 timestamp INTEGER 0 null 0
14 received_timestamp INTEGER 0 null 0
15 receipt_server_timestamp INTEGER 0 null 0
16 message_type INTEGER 0 null 0
17 text_data TEXT 0 null 0
18 starred INTEGER 0 null 0
19 lookup_tables INTEGER 0 null 0
20 message_add_on_flags INTEGER 0 null 0
21 data 0 null 0
22 media_url 0 null 0
23 media_mime_type 0 null 0
24 media_size 0 null 0
25 media_name 0 null 0
26 media_caption 0 null 0
27 media_hash 0 null 0
28 media_duration 0 null 0
29 latitude 0 null 0
30 longitude 0 null 0
31 thumb_image 0 null 0
32 raw_data 0 null 0
33 quoted_row_id 0 null 0
34 mentioned_jids 0 null 0
35 multicast_id 0 null 0
36 edit_version 0 null 0
37 media_enc_hash 0 null 0
38 payment_transaction_id 0 null 0
39 preview_type 0 null 0
40 receipt_device_timestamp 0 null 0
41 read_device_timestamp 0 null 0
42 played_device_timestamp 0 null 0
43 future_message_type 0 null 0
44 table_version 0 null 0
45 remove_files 0 null 0
frequent
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 jid_row_id INTEGER 1 null 0
2 type INTEGER 1 null 0
3 message_count INTEGER 1 null 0
frequents
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 jid TEXT 1 null 0
2 type INTEGER 1 null 0
3 message_count INTEGER 1 null 0
group_notification_version
cid name type notnull dflt_value pk
0 group_jid_row_id INTEGER 0 null 1
1 subject_timestamp INTEGER 1 null 0
2 announcement_version INTEGER 1 null 0
3 participant_version INTEGER 1 null 0
group_participant_device
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 group_participant_row_id INTEGER 1 null 0
2 device_jid_row_id INTEGER 1 null 0
3 sent_sender_key INTEGER 0 null 0
group_participant_user
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 group_jid_row_id INTEGER 1 null 0
2 user_jid_row_id INTEGER 1 null 0
3 rank INTEGER 0 null 0
4 pending INTEGER 0 null 0
group_participants
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 gjid TEXT 1 null 0
2 jid TEXT 1 null 0
3 admin INTEGER 0 null 0
4 pending INTEGER 0 null 0
5 sent_sender_key INTEGER 0 null 0
group_participants_history
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 timestamp DATETIME 1 null 0
2 gjid TEXT 1 null 0
3 jid TEXT 1 null 0
4 action INTEGER 1 null 0
5 old_phash TEXT 1 null 0
6 new_phash TEXT 1 null 0
group_past_participant_user
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 group_jid_row_id INTEGER 1 null 0
2 user_jid_row_id INTEGER 1 null 0
3 is_leave INTEGER 1 null 0
4 timestamp INTEGER 0 null 0
invoice_transactions
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 null 1
1 pay_transaction_id INTEGER 0 null 0
jid
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 user TEXT 1 null 0
2 server TEXT 1 null 0
3 agent INTEGER 0 null 0
4 device INTEGER 0 null 0
5 type INTEGER 0 null 0
6 raw_string TEXT 0 null 0

Figure B-2

Figure B-2: Contents of the table 'jid' in the msgstore.db.crypt14 (column order between the table info and the image is preserved);

jid_map
cid name type notnull dflt_value pk
0 lid_row_id INTEGER 1 null 1
1 jid_row_id INTEGER 1 null 0
joinable_call_log
cid name type notnull dflt_value pk
0 call_log_row_id INTEGER 0 null 1
1 call_id TEXT 1 null 0
2 joinable_video_call INTEGER 1 0 0
3 group_jid_row_id INTEGER 1 0 0
keywords
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 keyword TEXT 1 null 0
labeled_jid
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 label_id INTEGER 1 null 0
2 jid_row_id INTEGER 1 null 0
labeled_jids
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 label_id INTEGER 1 null 0
2 jid TEXT 0 null 0
labeled_messages
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 label_id INTEGER 1 null 0
2 message_row_id INTEGER 1 null 0
labeled_messages_fts
cid name type notnull dflt_value pk
0 content 0 null 0
labeled_messages_fts_content
cid name type notnull dflt_value pk
0 docid INTEGER 0 null 1
1 c0content 0 null 0
labeled_messages_fts_segdir
cid name type notnull dflt_value pk
0 level INTEGER 0 null 1
1 idx INTEGER 0 null 2
2 start_block INTEGER 0 null 0
3 leaves_end_block INTEGER 0 null 0
4 end_block INTEGER 0 null 0
5 root BLOB 0 null 0
labeled_messages_fts_segments
cid name type notnull dflt_value pk
0 blockid INTEGER 0 null 1
1 block BLOB 0 null 0
labels
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 label_name TEXT 0 null 0
2 predefined_id INTEGER 0 null 0
3 color_id INTEGER 0 null 0
media_hash_thumbnail
cid name type notnull dflt_value pk
0 media_hash TEXT 0 null 1
1 thumbnail BLOB 0 null 0
media_refs
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 path TEXT 0 null 0
2 ref_count INTEGER 0 null 0
message
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 chat_row_id INTEGER 1 null 0
2 from_me INTEGER 1 null 0
3 key_id TEXT 1 null 0
4 sender_jid_row_id INTEGER 0 null 0
5 status INTEGER 0 null 0
6 broadcast INTEGER 0 null 0
7 recipient_count INTEGER 0 null 0
8 participant_hash TEXT 0 null 0
9 origination_flags INTEGER 0 null 0
10 origin INTEGER 0 null 0
11 timestamp INTEGER 0 null 0
12 received_timestamp INTEGER 0 null 0
13 receipt_server_timestamp INTEGER 0 null 0
14 message_type INTEGER 0 null 0
15 text_data TEXT 0 null 0
16 starred INTEGER 0 null 0
17 lookup_tables INTEGER 0 null 0
18 message_add_on_flags INTEGER 0 null 0
19 sort_id INTEGER 1 0 0

Figure B-3

Figure B-3: Partial contents of the table 'message' in the msgstore.db.crypt14 (column order between the table info and the image is preserved for what is shown);

Figure B-4

Figure B-4: Partial contents of the table 'message' in the msgstore.db.crypt14 (column order between the table info and the image is preserved for what is shown); Note that the 'text_data' column contains all the messages sent from the user as well as all the messages sent to the user;

message_add_on
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 chat_row_id INTEGER 0 null 0
2 from_me INTEGER 0 null 0
3 key_id TEXT 1 null 0
4 sender_jid_row_id INTEGER 0 null 0
5 parent_message_row_id INTEGER 0 null 0
6 timestamp INTEGER 0 null 0
7 status INTEGER 0 null 0
8 message_add_on_type INTEGER 0 null 0
message_add_on_orphan
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 chat_row_id INTEGER 0 null 0
2 from_me INTEGER 0 null 0
3 key_id TEXT 1 null 0
4 sender_jid_row_id INTEGER 0 null 0
5 parent_chat_row_id INTEGER 0 null 0
6 parent_from_me INTEGER 0 null 0
7 parent_key_id TEXT 1 null 0
8 parent_sender_jid_row_id INTEGER 0 null 0
9 timestamp INTEGER 0 null 0
10 orphan_message_data BLOB 0 null 0
message_add_on_poll_vote
cid name type notnull dflt_value pk
0 message_add_on_row_id INTEGER 0 null 1
1 sender_timestamp INTEGER 0 null 0
message_add_on_poll_vote_selected_option
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 message_add_on_row_id INTEGER 0 null 0
2 message_poll_option_id INTEGER 0 null 0
message_add_on_reaction
cid name type notnull dflt_value pk
0 message_add_on_row_id INTEGER 0 null 1
1 reaction TEXT 0 null 0
2 sender_timestamp INTEGER 0 null 0
message_add_on_receipt_device
cid name type notnull dflt_value pk
0 receipt_device_id INTEGER 0 null 1
1 message_add_on_row_id INTEGER 0 null 0
2 receipt_device_jid_row_id INTEGER 0 null 0
3 receipt_device_timestamp INTEGER 0 null 0
4 primary_device_version INTEGER 0 null 0
message_broadcast_ephemeral
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 null 1
1 shared_secret BLOB 1 null 0
message_ephemeral
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 null 1
1 shared_secret BLOB 1 null 0
message_ephemeral_setting
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 null 1
1 setting_duration INTEGER 0 null 0
2 setting_reason INTEGER 0 null 0
3 user_jid_row_id_csv TEXT 0 null 0
message_external_ad_content
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 null 1
1 title TEXT 0 null 0
2 body TEXT 0 null 0
3 media_type INTEGER 0 null 0
4 thumbnail_url TEXT 0 null 0
5 full_thumbnail BLOB 0 null 0
6 micro_thumbnail BLOB 0 null 0
7 media_url TEXT 0 null 0
8 source_type TEXT 0 null 0
9 source_id TEXT 0 null 0
10 source_url TEXT 0 null 0
11 render_larger_thumbnail BOOLEAN 0 null 0
12 show_ad_attribution BOOLEAN 0 null 0
13 has_icebreaker_auto_response BOOLEAN 0 null 0
message_forwarded
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 null 1
1 forward_score INTEGER 0 null 0
message_ftsv2
cid name type notnull dflt_value pk
0 content 0 null 0
1 fts_jid 0 null 0
2 fts_namespace 0 null 0
message_ftsv2_content
cid name type notnull dflt_value pk
0 docid INTEGER 0 null 1
1 c0content 0 null 0
2 c1fts_jid 0 null 0
3 c2fts_namespace 0 null 0
message_ftsv2_docsize
cid name type notnull dflt_value pk
0 docid INTEGER 0 null 1
1 size BLOB 0 null 0
message_ftsv2_segdir
cid name type notnull dflt_value pk
0 level INTEGER 0 null 1
1 idx INTEGER 0 null 2
2 start_block INTEGER 0 null 0
3 leaves_end_block INTEGER 0 null 0
4 end_block INTEGER 0 null 0
5 root BLOB 0 null 0
message_ftsv2_segments
cid name type notnull dflt_value pk
0 blockid INTEGER 0 null 1
1 block BLOB 0 null 0
message_ftsv2_stat
cid name type notnull dflt_value pk
0 id INTEGER 0 null 1
1 value BLOB 0 null 0
message_future
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 null 1
1 version INTEGER 0 null 0
2 data BLOB 0 null 0
3 future_message_type INTEGER 0 null 0
message_group_invite
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 null 1
1 group_jid_row_id INTEGER 1 null 0
2 admin_jid_row_id INTEGER 1 null 0
3 group_name TEXT 0 null 0
4 invite_code TEXT 0 null 0
5 expiration INTEGER 0 null 0
6 invite_time INTEGER 0 null 0
7 expired INTEGER 0 null 0
8 group_type INTEGER 1 0 0
message_invoice
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 null 1
1 wa_invoice_id TEXT 1 null 0
2 amount TEXT 1 null 0
3 note TEXT 1 null 0
4 token TEXT 0 null 0
5 sender_jid_row_id INTEGER 0 null 0
6 receiver_jid_row_id INTEGER 0 null 0
7 status INTEGER 0 null 0
8 status_ts INTEGER 0 null 0
9 creation_ts INTEGER 0 null 0
10 attachment_type INTEGER 0 null 0
11 attachment_mimetype TEXT 0 null 0
12 attachment_media_key BLOB 0 null 0
13 attachment_media_key_ts INTEGER 0 null 0
14 attachment_file_sha256 BLOB 0 null 0
15 attachment_file_enc_sha256 BLOB 0 null 0
16 attachment_direct_path TEXT 0 null 0
17 attachment_jpeg_thumbnail BLOB 0 null 0
18 metadata TEXT 0 null 0
message_link
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 chat_row_id INTEGER 0 null 0
2 message_row_id INTEGER 0 null 0
3 link_index INTEGER 0 null 0
message_location
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 null 1
1 chat_row_id INTEGER 0 null 0
2 latitude REAL 0 null 0
3 longitude REAL 0 null 0
4 place_name TEXT 0 null 0
5 place_address TEXT 0 null 0
6 url TEXT 0 null 0
7 live_location_share_duration INTEGER 0 null 0
8 live_location_sequence_number INTEGER 0 null 0
9 live_location_final_latitude REAL 0 null 0
10 live_location_final_longitude REAL 0 null 0
11 live_location_final_timestamp INTEGER 0 null 0
12 map_download_status INTEGER 0 null 0
message_media
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 null 1
1 chat_row_id INTEGER 0 null 0
2 autotransfer_retry_enabled INTEGER 0 null 0
3 multicast_id TEXT 0 null 0
4 media_job_uuid TEXT 0 null 0
5 transferred INTEGER 0 null 0
6 transcoded INTEGER 0 null 0
7 file_path TEXT 0 null 0
8 file_size INTEGER 0 null 0
9 suspicious_content INTEGER 0 null 0
10 trim_from INTEGER 0 null 0
11 trim_to INTEGER 0 null 0
12 face_x INTEGER 0 null 0
13 face_y INTEGER 0 null 0
14 media_key BLOB 0 null 0
15 media_key_timestamp INTEGER 0 null 0
16 width INTEGER 0 null 0
17 height INTEGER 0 null 0
18 has_streaming_sidecar INTEGER 0 null 0
19 gif_attribution INTEGER 0 null 0
20 thumbnail_height_width_ratio REAL 0 null 0
21 direct_path TEXT 0 null 0
22 first_scan_sidecar BLOB 0 null 0
23 first_scan_length INTEGER 0 null 0
24 message_url TEXT 0 null 0
25 mime_type TEXT 0 null 0
26 file_length INTEGER 0 null 0
27 media_name TEXT 0 null 0
28 file_hash TEXT 0 null 0
29 media_duration INTEGER 0 null 0
30 page_count INTEGER 0 null 0
31 enc_file_hash TEXT 0 null 0
32 partial_media_hash TEXT 0 null 0
33 partial_media_enc_hash TEXT 0 null 0
34 is_animated_sticker INTEGER 0 null 0
35 original_file_hash TEXT 0 null 0
36 mute_video INTEGER 0 0 0
message_media_interactive_annotation
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 message_row_id INTEGER 0 null 0
2 location_latitude REAL 0 null 0
3 location_longitude REAL 0 null 0
4 location_name TEXT 0 null 0
5 sort_order INTEGER 0 null 0
message_media_interactive_annotation_vertex
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 message_media_interactive_annotation_row_id INTEGER 0 null 0
2 x REAL 0 null 0
3 y REAL 0 null 0
4 sort_order INTEGER 0 null 0
message_media_vcard_count
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 message_row_id INTEGER 0 null 0
2 count INTEGER 0 null 0
message_mentions
cid name type notnull dflt_value pk
0 _id INTEGER 0 null 1
1 message_row_id INTEGER 0 null 0
2 jid_row_id INTEGER 0 null 0
message_order
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 null 1
1 order_id TEXT 0 null 0
2 thumbnail BLOB 0 null 0
3 order_title TEXT 0 null 0
4 item_count INTEGER 0 null 0
5 status INTEGER 0 null 0
6 surface INTEGER 0 null 0
7 message TEXT 0 null 0
8 seller_jid INTEGER 0 null 0
9 token TEXT 0 null 0
10 currency_code TEXT 0 null 0
11 total_amount_1000 INTEGER 0 null 0
message_orphaned_edit
cid name type notnull dflt_value pk
0 _id INTEGER 0 1
1 key_id TEXT 1 0
2 from_me INTEGER 1 0
3 chat_row_id INTEGER 1 0
4 sender_jid_row_id INTEGER 1 0 0
5 timestamp INTEGER 0 0
6 message_type INTEGER 1 0
7 revoked_key_id TEXT 0 0
8 retry_count INTEGER 0 0
9 admin_jid_row_id INTEGER 0 0
message_payment
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 sender_jid_row_id INTEGER 0 0
2 receiver_jid_row_id INTEGER 0 0
3 amount_with_symbol TEXT 0 0
4 remote_resource TEXT 0 0
5 remote_message_sender_jid_row_id INTEGER 0 0
6 remote_message_from_me INTEGER 0 0
7 remote_message_key TEXT 0 0
message_payment_invite
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 service INTEGER 0 0
2 expiration_timestamp INTEGER 0 0
message_payment_status_update
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 transaction_info TEXT 0 0
2 transaction_data TEXT 0 0
3 init_timestamp TEXT 0 0
4 update_timestamp TEXT 0 0
5 amount_data TEXT 0 0
message_payment_transaction_reminder
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 web_stub TEXT 0 0
2 amount TEXT 0 0
3 transfer_date TEXT 0 0
4 payment_sender_name TEXT 0 0
5 expiration INTEGER 0 0
6 remote_message_key TEXT 0 0
message_poll
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 enc_key BLOB 0 0
2 selectable_options_count INTEGER 0 0
message_poll_option
cid name type notnull dflt_value pk
0 _id INTEGER 0 1
1 message_row_id INTEGER 0 0
2 option_sha256 TEXT 0 0
3 option_name TEXT 0 0
4 vote_total INTEGER 0 0
message_privacy_state
cid name type notnull dflt_value pk
0 message_row_id INTEGER 1 1
1 host_storage INTEGER 0 0
2 actual_actors INTEGER 0 0
3 privacy_mode_ts INTEGER 1 0
4 business_name TEXT 0 0
message_product
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 business_owner_jid INTEGER 0 0
2 product_id TEXT 0 0
3 title TEXT 0 0
4 description TEXT 0 0
5 currency_code TEXT 0 0
6 amount_1000 INTEGER 0 0
7 retailer_id TEXT 0 0
8 url TEXT 0 0
9 product_image_count INTEGER 0 0
10 sale_amount_1000 INTEGER 0 0
11 body TEXT 0 0
12 footer TEXT 0 0
message_quote_invoice
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 amount TEXT 1 0
2 note TEXT 1 0
3 status INTEGER 0 0
4 attachment_jpeg_thumbnail BLOB 0 0
message_quoted
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 chat_row_id INTEGER 1 0
2 parent_message_chat_row_id INTEGER 1 0
3 from_me INTEGER 1 0
4 sender_jid_row_id INTEGER 0 0
5 key_id TEXT 1 0
6 timestamp INTEGER 0 0
7 message_type INTEGER 0 0
8 origin INTEGER 0 0
9 text_data TEXT 0 0
10 payment_transaction_id TEXT 0 0
11 lookup_tables INTEGER 0 0

Figure B-5

Figure B-5: Contents of the table 'message_quoted' in the msgstore.db.crypt14 (column order between the table info and the image is preserved);

message_quoted_blank_reply
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 parent_group_jid TEXT 0 0
2 group_subject TEXT 0 0
message_quoted_group_invite
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 group_jid_row_id INTEGER 1 0
2 admin_jid_row_id INTEGER 1 0
3 group_name TEXT 0 0
4 invite_code TEXT 0 0
5 expiration INTEGER 0 0
6 invite_time INTEGER 0 0
7 expired INTEGER 0 0
8 group_type INTEGER 1 0 0
message_quoted_group_invite_legacy
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 group_jid_row_id INTEGER 1 0
2 admin_jid_row_id INTEGER 1 0
3 group_name TEXT 0 0
4 invite_code TEXT 0 0
5 expiration INTEGER 0 0
6 invite_time INTEGER 0 0
7 expired INTEGER 0 0
8 group_type INTEGER 1 0 0
message_quoted_location
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 latitude REAL 0 0
2 longitude REAL 0 0
3 place_name TEXT 0 0
4 place_address TEXT 0 0
5 url TEXT 0 0
6 thumbnail BLOB 0 0
message_quoted_media
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 media_job_uuid TEXT 0 0
2 transferred INTEGER 0 0
3 file_path TEXT 0 0
4 file_size INTEGER 0 0
5 media_key BLOB 0 0
6 media_key_timestamp INTEGER 0 0
7 width INTEGER 0 0
8 height INTEGER 0 0
9 direct_path TEXT 0 0
10 message_url TEXT 0 0
11 mime_type TEXT 0 0
12 file_length INTEGER 0 0
13 media_name TEXT 0 0
14 file_hash TEXT 0 0
15 media_duration INTEGER 0 0
16 page_count INTEGER 0 0
17 enc_file_hash TEXT 0 0
18 thumbnail BLOB 0 0
message_quoted_mentions
cid name type notnull dflt_value pk
0 _id INTEGER 0 1
1 message_row_id INTEGER 0 0
2 jid_row_id INTEGER 0 0
message_quoted_order
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 order_id TEXT 0 0
2 thumbnail BLOB 0 0
3 order_title TEXT 0 0
4 item_count INTEGER 0 0
5 status INTEGER 0 0
6 surface INTEGER 0 0
7 message TEXT 0 0
8 seller_jid INTEGER 0 0
9 token TEXT 0 0
10 currency_code TEXT 0 0
11 total_amount_1000 INTEGER 0 0
message_quoted_payment_invite
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 service INTEGER 0 0
2 expiration_timestamp INTEGER 0 0
message_quoted_product
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 business_owner_jid INTEGER 0 0
2 product_id TEXT 0 0
3 title TEXT 0 0
4 description TEXT 0 0
5 currency_code TEXT 0 0
6 amount_1000 INTEGER 0 0
7 retailer_id TEXT 0 0
8 url TEXT 0 0
9 product_image_count INTEGER 0 0
10 sale_amount_1000 INTEGER 0 0
11 body TEXT 0 0
12 footer TEXT 0 0
message_quoted_text
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 thumbnail BLOB 0 0
message_quoted_ui_elements
cid name type notnull dflt_value pk
0 message_row_id INTEGER 1 1
1 element_type INTEGER 0 0
2 element_content TEXT 0 0
message_quoted_ui_elements_reply
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 element_type INTEGER 0 0
2 reply_values TEXT 0 0
3 reply_description TEXT 0 0
message_quoted_ui_elements_reply_legacy
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 element_type INTEGER 0 0
2 reply_values TEXT 0 0
3 reply_description TEXT 0 0
message_quoted_vcard
cid name type notnull dflt_value pk
0 _id INTEGER 0 1
1 message_row_id INTEGER 0 0
2 vcard TEXT 0 0
message_rating
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 rating INTEGER 1 0
message_revoked
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 revoked_key_id TEXT 1 0
2 admin_jid_row_id INTEGER 0 0
message_send_count
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 send_count INTEGER 0 0
message_status_psa_campaign
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 campaign_id TEXT 0 0
2 duration INTEGER 0 0
3 first_seen_timestamp INTEGER 0 0
4 action_link_url TEXT 0 0
5 action_link_button_title TEXT 0 0
message_streaming_sidecar
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 sidecar BLOB 0 0
2 chunk_lengths BLOB 0 0
3 timestamp INTEGER 0 0
message_system
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 action_type INTEGER 1 0
message_system_block_contact
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 is_blocked INTEGER 0 0
message_system_business_state
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 privacy_message_type INTEGER 1 0
2 business_name TEXT 0 0
message_system_chat_participant
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 0
1 user_jid_row_id INTEGER 0 0
message_system_community_link_changed
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 old_group_type INTEGER 0 0
2 new_group_type INTEGER 1 0
3 linked_parent_group_jid_row_id INTEGER 0 0
message_system_device_change
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 device_added_count INTEGER 0 0
2 device_removed_count INTEGER 0 0
message_system_ephemeral_setting_not_applied
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 setting_duration INTEGER 0 0
message_system_group
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 is_me_joined INTEGER 0 0
message_system_initial_privacy_provider
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 privacy_provider INTEGER 1 0 0
2 verified_biz_name TEXT 0 0
3 biz_state_id INTEGER 0 0
message_system_linked_group_call
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 call_id TEXT 0 0
2 is_video_call INTEGER 0 0
message_system_number_change
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 old_jid_row_id INTEGER 0 0
2 new_jid_row_id INTEGER 0 0
message_system_payment_invite_setup
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 service INTEGER 0 0
2 invite_used INTEGER 0 0
message_system_photo_change
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 new_photo_id TEXT 0 0
2 old_photo BLOB 0 0
3 new_photo BLOB 0 0
message_system_sibling_group_link_change
cid name type notnull dflt_value pk
0 message_row_id INTEGER 1 1
1 subgroup_raw_jid TEXT 1 2
2 subgroup_subject TEXT 1 0
message_system_value_change
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 old_data TEXT 0 0
message_template
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 content_text_data TEXT 1 0
2 footer_text_data TEXT 0 0
3 template_id TEXT 0 0
4 csat_trigger_expiration_ts INTEGER 0 0
message_template_button
cid name type notnull dflt_value pk
0 _id INTEGER 0 1
1 message_row_id INTEGER 0 0
2 text_data TEXT 1 0
3 extra_data TEXT 0 0
4 button_type INTEGER 0 0
5 used INTEGER 0 0
6 selected_index INTEGER 0 0
7 otp_button_type INTEGER 0 0
message_template_quoted
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 content_text_data TEXT 1 0
2 footer_text_data TEXT 0 0
message_text
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 description TEXT 0 0
2 page_title TEXT 0 0
3 url TEXT 0 0
4 font_style INTEGER 0 0
5 text_color INTEGER 0 0
6 background_color INTEGER 0 0
7 preview_type INTEGER 0 0
8 invite_link_group_type INTEGER 1 0 0
message_thumbnail
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 thumbnail BLOB 0 0
message_thumbnails
cid name type notnull dflt_value pk
0 thumbnail BLOB 0 0
1 timestamp DATETIME 0 0
2 key_remote_jid TEXT 1 0
3 key_from_me INTEGER 0 0
4 key_id TEXT 1 0
message_ui_elements
cid name type notnull dflt_value pk
0 _id INTEGER 1 1
1 message_row_id INTEGER 1 0
2 element_type INTEGER 0 0
3 element_content TEXT 0 0
message_ui_elements_reply
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 element_type INTEGER 0 0
2 reply_values TEXT 0 0
3 reply_description TEXT 0 0
message_vcard
cid name type notnull dflt_value pk
0 _id INTEGER 0 1
1 message_row_id INTEGER 0 0
2 vcard TEXT 0 0
message_vcard_jid
cid name type notnull dflt_value pk
0 _id INTEGER 0 1
1 vcard_jid_row_id INTEGER 0 0
2 vcard_row_id INTEGER 0 0
3 message_row_id INTEGER 0 0
message_view
cid name type notnull dflt_value pk
0 _id INTEGER 0 0
1 sort_id INTEGER 0 0
2 chat_row_id INTEGER 0 0
3 from_me INTEGER 0 0
4 key_id TEXT 0 0
5 sender_jid_row_id INTEGER 0 0
6 sender_jid_raw_string 0 0
7 status INTEGER 0 0
8 broadcast INTEGER 0 0
9 recipient_count INTEGER 0 0
10 participant_hash TEXT 0 0
11 origination_flags INTEGER 0 0
12 origin INTEGER 0 0
13 timestamp INTEGER 0 0
14 received_timestamp INTEGER 0 0
15 receipt_server_timestamp INTEGER 0 0
16 message_type INTEGER 0 0
17 text_data TEXT 0 0
18 starred INTEGER 0 0
19 lookup_tables INTEGER 0 0
20 message_add_on_flags INTEGER 0 0
21 data 0 0
22 media_url 0 0
23 media_mime_type 0 0
24 media_size 0 0
25 media_name 0 0
26 media_caption 0 0
27 media_hash 0 0
28 media_duration 0 0
29 latitude 0 0
30 longitude 0 0
31 thumb_image 0 0
32 raw_data 0 0
33 quoted_row_id 0 0
34 mentioned_jids 0 0
35 multicast_id 0 0
36 edit_version 0 0
37 media_enc_hash 0 0
38 payment_transaction_id 0 0
39 preview_type 0 0
40 receipt_device_timestamp 0 0
41 read_device_timestamp 0 0
42 played_device_timestamp 0 0
43 future_message_type 0 0
44 table_version 0 0
message_view_once_media
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 state INTEGER 1 0
messages_hydrated_four_row_template
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 message_template_id TEXT 0 0
missed_call_log_participant
cid name type notnull dflt_value pk
0 _id INTEGER 0 1
1 call_logs_row_id INTEGER 0 0
2 jid TEXT 0 0
3 call_result INTEGER 0 0
missed_call_logs
cid name type notnull dflt_value pk
0 _id INTEGER 0 1
1 message_row_id INTEGER 0 0
2 timestamp INTEGER 0 0
3 video_call INTEGER 0 0
4 group_jid_row_id INTEGER 1 0 0
5 is_joinable_group_call INTEGER 0 0
mms_thumbnail_metadata
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 direct_path TEXT 0 0
2 media_key BLOB 0 0
3 media_key_timestamp INTEGER 0 0
4 enc_thumb_hash TEXT 0 0
5 thumb_hash TEXT 0 0
6 thumb_width INTEGER 0 0
7 thumb_height INTEGER 0 0
8 transferred INTEGER 0 0
9 micro_thumbnail BLOB 0 0
10 insert_timestamp INTEGER 0 0
pay_transaction
cid name type notnull dflt_value pk
0 _id INTEGER 0 1
1 message_row_id INTEGER 0 0
2 remote_jid_row_id INTEGER 0 0
3 key_id TEXT 0 0
4 interop_id TEXT 0 0
5 id TEXT 0 0
6 timestamp INTEGER 0 0
7 status INTEGER 0 0
8 error_code TEXT 0 0
9 sender_jid_row_id INTEGER 0 0
10 receiver_jid_row_id INTEGER 0 0
11 type INTEGER 0 0
12 currency_code TEXT 0 0
13 amount_1000 0 0
14 credential_id TEXT 0 0
15 methods TEXT 0 0
16 bank_transaction_id TEXT 0 0
17 metadata TEXT 0 0
18 init_timestamp INTEGER 0 0
19 request_key_id TEXT 0 0
20 country TEXT 0 0
21 version INTEGER 0 0
22 future_data BLOB 0 0
23 service_id INTEGER 0 0
24 background_id TEXT 0 0
payment_background
cid name type notnull dflt_value pk
0 background_id TEXT 0 1
1 file_size INTEGER 0 0
2 width INTEGER 0 0
3 height INTEGER 0 0
4 mime_type TEXT 0 0
5 placeholder_color INTEGER 0 0
6 text_color INTEGER 0 0
7 subtext_color INTEGER 0 0
8 fullsize_url TEXT 0 0
9 description TEXT 0 0
10 lg TEXT 0 0
11 media_key BLOB 0 0
12 media_key_timestamp INTEGER 0 0
13 file_sha256 TEXT 0 0
14 file_enc_sha256 TEXT 0 0
15 direct_path TEXT 0 0
payment_background_order
cid name type notnull dflt_value pk
0 background_id TEXT 0 1
1 background_order INTEGER 0 0
played_self_receipt
cid name type notnull dflt_value pk
0 message_row_id INTEGER 0 1
1 to_jid_row_id INTEGER 1 0
2 participant_jid_row_id INTEGER 0 0
3 message_id TEXT 1 0
primary_device_version
cid name type notnull dflt_value pk
0 user_jid_row_id INTEGER 0 1
1 version INTEGER 1 0 0
props
cid name type notnull dflt_value pk
0 _id INTEGER 0 1
1 key TEXT 0 0
2 value TEXT 0 0
quick_replies
cid name type notnull dflt_value pk
0 _id INTEGER 0 1
1 title TEXT 1 0
2 content TEXT 1 0
quick_reply_attachments
cid name type notnull dflt_value pk
0 _id INTEGER 0 1
1 quick_reply_id TEXT 1 0
2 uri TEXT 1 0
3 caption TEXT 0 0
4 media_type INTEGER 0 0
quick_reply_keywords
cid name type notnull dflt_value pk
0 _id INTEGER 0 1
1 quick_reply_id TEXT 1 0
2 keyword_id TEXT 1 0
quick_reply_usage
cid name type notnull dflt_value pk
0 _id INTEGER 0 1
1 quick_reply_id TEXT 1 0
2 usage_date DATE 0 0
3 usage_count INTEGER 0 0
receipt_device
cid name type notnull dflt_value pk
0 _id INTEGER 0 1
1 message_row_id INTEGER 1 0
2 receipt_device_jid_row_id INTEGER 1 0
3 receipt_device_timestamp INTEGER 0 0
4 primary_device_version INTEGER 0 0
receipt_orphaned
cid name type notnull dflt_value pk
0 _id INTEGER 0 1
1 chat_row_id INTEGER 1 0
2 from_me INTEGER 1 0
3 key_id TEXT 1 0
4 receipt_device_jid_row_id INTEGER 1 0
5 receipt_recipient_jid_row_id INTEGER 0 0
6 status INTEGER 0 0
7 timestamp INTEGER 0 0
receipt_user
cid name type notnull dflt_value pk
0 _id INTEGER 0 1
1 message_row_id INTEGER 1 0
2 receipt_user_jid_row_id INTEGER 1 0
3 receipt_timestamp INTEGER 0 0
4 read_timestamp INTEGER 0 0
5 played_timestamp INTEGER 0 0
receipts
cid name type notnull dflt_value pk
0 _id INTEGER 0 1
1 key_remote_jid TEXT 1 0
2 key_id TEXT 1 0
3 remote_resource TEXT 0 0
4 receipt_device_timestamp INTEGER 0 0
5 read_device_timestamp INTEGER 0 0
6 played_device_timestamp INTEGER 0 0
sqlite_sequence
cid name type notnull dflt_value pk
0 name 0 0
1 seq 0 0
status
cid name type notnull dflt_value pk
0 _id INTEGER 0 1
1 jid_row_id INTEGER 0 0
2 message_table_id INTEGER 0 0
3 last_read_message_table_id INTEGER 0 0
4 last_read_receipt_sent_message_table_id INTEGER 0 0
5 first_unread_message_table_id INTEGER 0 0
6 autodownload_limit_message_table_id INTEGER 0 0
7 timestamp INTEGER 0 0
8 unseen_count INTEGER 0 0
9 total_count INTEGER 0 0
status_list
cid name type notnull dflt_value pk
0 _id INTEGER 0 1
1 key_remote_jid TEXT 0 0
2 message_table_id INTEGER 0 0
3 last_read_message_table_id INTEGER 0 0
4 last_read_receipt_sent_message_table_id INTEGER 0 0
5 first_unread_message_table_id INTEGER 0 0
6 autodownload_limit_message_table_id INTEGER 0 0
7 timestamp INTEGER 0 0
8 unseen_count INTEGER 0 0
9 total_count INTEGER 0 0
user_device
cid name type notnull dflt_value pk
0 _id INTEGER 0 1
1 user_jid_row_id INTEGER 0 0
2 device_jid_row_id INTEGER 0 0
3 key_index INTEGER 1 0 0
user_device_info
cid name type notnull dflt_value pk
0 user_jid_row_id INTEGER 0 1
1 raw_id INTEGER 1 0
2 timestamp INTEGER 1 0
3 expected_timestamp INTEGER 1 0
4 expected_ts_last_device_job_ts INTEGER 1 0
5 expected_timestamp_update_ts INTEGER 1 0