Shulou(Shulou.com)05/31 Report--

How to extract messages from the WhatsApp backup database, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail, people with this need can come to learn, I hope you can gain something.

I'll show you how to use Python to recover messages from WhatsApp. And I'll focus on explaining how to find and extract conversations from WhatsApp's sqlite database and parse the fields and data there. This is by no means a comprehensive reverse / forensic analysis work-the only reason I'm writing this article is that I haven't found a free information and / or open source tool you can trust for this purpose. So I think if I can share the work I do, it will save others some time. You can use a similar process to analyze other (messaging) applications.

I will use Jupyter Notebooks and Pandas, but almost all code can be used independently of Jupyter. Code for extracting messages from WhatsApp sqlite DB (written specifically for Jupyter notebook), which you can get at github.

Background

Due to some legal problems of real estate developers (are there any honest developers? ) I need to recover whatsApp information from an old iPhone device that has been in the closet for two years.

I'm not a normal iPhone user, so I started to find a way to do this, and I found that iCloud doesn't give access to the actual backup content. But here are some tools that can help you get your login credentials and allow you to browse the full backup content, but I don't want to disclose my (wife) login credentials to these unknown tools. So I started to study how to do this without compromising the security of the account.

After booting up the iPhone, I found that WhatsApp was not working properly, and a strange message alerted Your phone date is inaccurate! Adjust your clock and try again: your phone date is not accurate! Please adjust your clock and try again. I don't know what the date problem has to do with starting WhatsApp, but the fix date and time don't solve the problem, and I've read a lot of online solutions that seem to recommend uninstalling and reinstalling. But I don't want to take that risk, because I need this information as evidence in court.

Step 1-backup and decompress files from iPhone

This step is more suitable for Mac OS, although it works similarly in other operating systems.

1. Create a virtual machine and install iTunes on it. Make sure VM is not connected to the network. (if you don't know how to create a VM, see this article).

two。 Connect the iPhone to the VM. Follow the usual steps to back up iPhone locally on VM. For those interested in Mac, you can find a backup at this location: ~ / Library/Application Support/MobileSync/Backup

See here for a description of the backup format.

3. Install the iPhone backup extraction tool (such as iExplorer by Macroplant) in the virtual machine. Which tool doesn't matter because it doesn't have access to the Internet, so your data is secure.

4. Finally, use the backup extraction tool to extract chatstorage.sqlite, which is the main whatsapp session database. To access it, you need to browse to "App Group" → "group.net.whatsapp.WhatsApp.shared"

5. In addition, you can extract media by extracting folders: "App" → "net.whatsapp.WhatsApp" → "Library" → "Media"

Step 2-connect to the database and read the message table

WhatsApp seems to store all conversations in Sqlite DB. As a first step, let's try using sqlite3's built-in python library + Pandas to see if we can connect to the database and get the columns of the table:

As you can see, there are 18 tables. By looking closely at one of the tables, I noticed that (that is, the one with your message) seemed to be ZWAMESSAGE. Let's open it:

You can see that there are 34 columns in the table:

Let's see what we have. The following is my analysis based on the data I found in the tables in my database and my inferences:

1. Z_PK-seems like a serial number2. Z_ENT to ZFILTEREDRECIPIENTCOUNT-seem less important3. ZFLAGS-seems to indicate message state4. ZGROUPEVENTTYPE-seems to be related to group chats5. ZISFROMME-message is from me... It is 1 for messages sent by this user and 0 for messages received6. ZMESSAGEERRORSTATUS to ZSPOTLIGHTSTATUS-seems like general statuses7. ZSTARRED-did we star the message8. ZCHATSESSION-unique identifier denoting a chat session9. ZGROUPMEMBER-haven't gotten to look at this one yet10. ZLASTSESSION-last chat session? Didn't dig into it11. ZMEDIAITEM-seems related to media item indexing, might be an identifier to one of the other tables12. ZMESSAGEINFO and ZPARENTMESSAGE-seem simple enough to figure out from the names13. ZMESSAGEDATE-message creation date probably (see date format discussion below) 14. ZSENTDATE-message sent date probably (see date format discussion below) 15. ZFROMJID-from who did we get it (if it is an incoming message) 16. ZMEDIASECTIONID-seems related to media storage for media messages, doesn't show in messages without media17. ZPHASH-hmmm... Not sure18. ZPUSHNAME-seems like the contact name on your phone19. ZSTANZAID-some conversation / media id indicator. Format seems different in media messages and text messages20. ZTEXT-message text21. ZTOJID-to whom did we send it (if it is an outgoing message) step 3-extract a specific conversation

Our basic goal is to extract specific conversations or communications. So, if we remember part of the text, we can search for a contact by name or phone number, or search for a specific conversation by message, and finally extract the complete conversation based on the session ID.

But before we do this, we should figure out how to convert the date and time into a human-readable format. Intuitively, I assume that zmessagedate is an unix timestamp. So I converted it to a date, and that's what I got:

Apple seems to have decided to use the date from 1.1.2001 on iPhone with its infinitely unique wisdom, so let's see what happens if we add the offset of unix-time time0 to 1.1.2001:

This seems to solve the problem. In the process, I also added a date index to data-frame to make it easier to use. Please note that if you import from Android or other operating systems, you may not need to use this date conversion.

The date is available, and now we can start to extract the conversation. First, let's extract a conversation we care about, such as a specific number:

You should notice that the lambda I created is used to search columns. Here is a disappointing Python "Wat" moment, because False | None! = None | False this is unreasonable. Anyway, moving on to the ZCHATSESSION column, we'll notice that in my example, the session ID for this number will be "104.0" (if there is a group chat, you may see multiple chat sessions ID). So the next step will be to extract all the messages in the chat session. To do this, let's add the appropriate accessors for it and the other search methods mentioned above:

Now, let's get all the messages from the chat session and display them (enable multiline format to see all the text):

Part 4-concluding remarks and points for attention in media extraction

The media file is stored in the folder mentioned in step 1, and the subfolder is based on the phone number of the contact of the shared file (and the suffix that represents the group chat? -this is a guess. The subfolders under each phone number seem to follow a similar logic to the iTunes backup format, and I suspect that the message table references links in the media table, but I don't take the time to actually verify it. Finally, it is important to note that some folders will also contain some thumb files, which are really just JPG files (renamed to open normally).

The code for Jupyter notebook can be found on Github

I have removed my personal information from it, but the code is exactly the same as the example. In addition, notebook may contain some additional information.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.