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: 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: 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: 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 34.
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:
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: 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:
The key is sent from an external source
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:
The key is sent from Google’s servers
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:
crypt14 database files that contain messages are backed up to Google Drive
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: 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: 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: 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: 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:
The key that is used to encrypt the backup of messages, referred to as the backup-key.
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 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].↩
[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/
[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
- 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):
WhatsApp application asks the user to enter the password.
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
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.
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):
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: 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: 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: 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: Contents of the table 'message_quoted' in the msgstore.db.crypt14 (column order between the table info and the image is preserved); ↩