Viber SQlite Database Extraction / Forensics

by

Well you can use this to pull messages out of a Viber Desktop database. I personally had to do it when I re-installed Viber and it deleted my entire message history, which wasn't so friendly, so I pulled this out of the database so I could refer to it in a spreadsheet. Maybe there is a way of pumping it back into the new database …

I may end up putting more queries here when I discover them but below you'll find the basic one to dump out a summary of messaging. It converts the Unix timestamp dates into readable ones, and tells you which way the messsage went too. If you have any other such queries, let me know, and I'll include them: there didn't seem to be much on this around the internet.

How you run the query is up to you. I previously used an SQlite extension for Firefox, but recently found the DBeaver cross platform Database app, and its working a treat across sqlite, mysql, postgresql, so that's my favourite right now. Just run the query and export to CSV, using a strange delimiter such as '|' so the message isn't split into many fields at each comma.
You could also run the query from the commandline: copy the command to a file, say viber.sql, and then use the following, suitable for a backup script perhaps.

sqlite3 -header -csv -separator '|' viber.db < viber.sql > output.csv

On a standard install on Linux you'll find the viber.db under ~/.ViberPC/<phonenumber>/viber.db
Obviously make a copy first if you're nervous. Here is the query:

SELECT strftime('%Y-%m-%d %H:%M:%S',Events.TimeStamp,'unixepoch') AS Time, 
 Contact.Name AS Name,
 Contact.ClientName AS AltName,
 Contact.Number As Cellphone,
 CASE Direction WHEN 0 THEN 'rcvd' ELSE 'sent' END AS Direction,
 Messages.Body AS Message
FROM Events 
 INNER JOIN Contact ON Events.ContactID = Contact.ContactID
 INNER JOIN Messages ON Events.EventID = Messages.EventID
ORDER BY Time;

After a comment about the query no longer working, I discovered that Viber has changed the database format, and the Events.Timestamp field now contains thousandths of a second (although it doesn't seem to use them). So if the one above returns null values for Time, then try dividing it by 1000 like this. 

SELECT strftime('%Y-%m-%d %H:%M:%S',(Events.TimeStamp/1000),'unixepoch') AS Time, 
 Contact.Name AS Name,
 Contact.ClientName AS AltName,
 Contact.Number As Cellphone,
 CASE Direction WHEN 0 THEN 'rcvd' ELSE 'sent' END AS Direction,
 Messages.Body AS Message
FROM Events 
 INNER JOIN Contact ON Events.ContactID = Contact.ContactID
 INNER JOIN Messages ON Events.EventID = Messages.EventID
ORDER BY Time;

If you have the same query for other versions of Viber and they're different, let me know.

25 thoughts on “Viber SQlite Database Extraction / Forensics”

  2. Sorry for the late reply. I'm not used to getting comments here that aren't spam!
    With Dbeaver, first you have to set up a connection. For Sqlite, that's basically pointing it at the file you want to open. I'd recommend copying the database to another file, so you don't mess up the original.
    https://www.youtube.com/watch?v=fmq6-wvbxyA
    You'd also have to make sure the dbeaver sqlite driver extension is installed. And you may have to install support in your OS too. For debian/ubuntu that would be "apt install sqlite3"

  4. I think the database contains some extra information that ties it to your current installation. I did try copying the db, but it didn't work.
    Possibly the way to do this would be to insert the old messages into the new database. But that looked like a lot of work. In the end I just settled for a historic, archival record of the messages.

  5. Hi there, thanks for this. I managed to extract the messages, however the time is null.
    If I put Event.TimeStamp infornt of the Select statement, I can get the timestamp in Unix time.
    What is the correct way to convert the time stamp?

    Thanks

  6. Hi Rob. I've updated the query above. Seems that they're now using a different unixepoch format that includes an extra three zeros on the end, which I haven't seen before. Dividing by 1000 solves this.

  7. Sorted, I used this script:
    SELECT datetime(Events.timestamp/1000,'unixepoch','localtime') AS Time,
    Important to divide the timestamp by 1000 as Unix time is stored in milliseconds.

  9. Hi, so can you confirm, that the ViberPC viber.db file is not encrypted? Can I open it just like that?
    My problem is that the Viber on PC, which I had been using for 5 years kicked me out for some reason, invalid registration etc. and now asks me sign in as I would not be an existing user. So I am afraid now, if it will destroy my old messages, and sync them with my Viber mobile, which has history only for 2 years, not 5 years. So 1) I'm happy if I can access, read, search etc. viber.db messages, but 2) extremely happy, if I could restore the old viber.db in place in case the new installation deletes the old messages.

    Do you have any experience on this kind of problems? Or where could I find help? After 5 hours of browsing the internet with multiple specific key words I find very little information. Maybe because Viber is not close as popular program as WhatsApp. But I like Viber quite a lot because of the crazy stickers and more relaxed environment than WhatsApp.:)

    Thanks!

  10. Hi, quick update after my previous post, I could actually use your script quite easily. First time I play with sql database, but it is not so difficult. I installed also the DBeaver, but I didn't find the Query window, so I figured out to try with old fashioned way with the Windows cmd and the sqlite3 script. It did the work very fast. Then it was just regular excel work to transfer text to columns, and the '|' helped a lot. By the way, the only problem was, that the cmd did not approve the '|', it gave strange error message. But luckily I tried to change that to "|" and it worked.

    How could I get also the following items to get the columns in the script?
    – PayloadPath
    – ThumbnailPath
    – StickerID

    Thanks a lot! I could donate you some small fee for this good help!

  11. Actually I could figure out something by myself. I tried to change like this:
    —
    CASE Direction WHEN 0 THEN 'rcvd' ELSE 'sent' END AS Direction,
    Messages.Body AS Message,
    Messages.PayloadPath AS ImagePath,
    Messages.ThumbnailPath AS ThumbnailPath,
    Messages.StickerID AS StickerID
    —
    It worked for the StickerID nicely, but the images didn't show up there in any lines. And I also figured out that the EventID in csv file don't match with the sql file. Is it somehow because it is missing the image rows?

    What does this mean?
    FROM Events
    INNER JOIN Contact ON Events.ContactID = Contact.ContactID
    INNER JOIN Messages ON Events.EventID = Messages.EventID

  12. Hi Pekde,
    Well it looks as though you figured most things out yourself while I was asleep! To answer some of your questions
    – Yes, as you've now figured out, the file is a normal non-encrypted sqlite database, and of course, work on a copy of it just in case.
    – I couldn't find a way to restore the DB instead of the old one. I think when you change phone, or set up a new desktop it wipes the old database. One thing to look at is that perhaps Viber on your phone made a backup in your Google drive, which would contain the messages.
    – You figured out the Stickers and Images. The ID numbers you get refer to the filenames of files on your disk, also in the .ViberPC directory. I don't think there's a way of displaying them, but you can probably find them on disk.
    – The INNER JOIN statements are linking together the different tables, to create one long record with all the data in. This is standard SQL stuff, and there are better resources than me to explain it!

  13. I just used your SQL above on a WIN 10 PC and it worked perfectly- Thankyou
    SELECT strftime('%Y-%m-%d %H:%M:%S',(Events.TimeStamp/1000),'unixepoch') AS Time,
    Contact.Name AS Name,
    Contact.ClientName AS AltName,
    Contact.Number As Cellphone,
    CASE Direction WHEN 0 THEN 'rcvd' ELSE 'sent' END AS Direction,
    Messages.Body AS Message
    FROM Events
    INNER JOIN Contact ON Events.ContactID = Contact.ContactID
    INNER JOIN Messages ON Events.EventID = Messages.EventID
    ORDER BY Time;

  14. One question is there a way to extract the result to Excel ? I'm not that technical – I tried to cntrl A and copy but there is a lot of data and it didn't seem to work

  15. sorry and is there a way to extract a specific bit of text from a message using part of the message text? e.g If the message was "This is the message text I am looking for and don't know how to do it"; can I use part of the text as an example: "This is the message text"

  16. Hi John H. If you used this bit of the sqlite command
    -csv -separator '|'
    Then it will separate fields with that character. If you import the file into Excel, just tell it to use that character as the delimiter instead of a comma, and it should import the file fine.

    Finding a line in the file once its in Excel should be easy enough for you I imagine? Or maybe you could also do it by opening the file in a text editor and searching there.
    Personally I'd use a command line tool like 'grep' or sed, or as you're Windows, you could use findstr perhaps? erm … findstr "This is the message text" output.csv … something like that?

  17. The purpose being to find the date of the text and those around it. The SQL as used gives a display that is fine, just would be handy to find instances where a phrase was used.

  18. Thankyou for responding.I will try the .csv, and of course you are right finding a string in Excel is easier to do. I cannot thank you enough
    Regards
    John

  19. Umm I tried, no result, not sure where to put the
    -csv -separator '|'

    I assume I have to somehow in SQL say mode .csv not sure what the commands are I put them before the select but can't seem to get it right

  20. John H. Sorry for the confusion. The "-csv-separator '|'" was referring to the command-line method of extracting the data. But if you're using the DBeaver method that won't be relevant. It's a long time since I wrote the original post, so I'd forgotten that I was giving you two ways to get the data.

    If you've used the DBeaver method to load the data, you can actually search in there. Its a database after all! Something like this

    SELECT strftime('%Y-%m-%d %H:%M:%S',(Events.TimeStamp/1000),'unixepoch') AS Time,
    Contact.Name AS Name,
    Contact.ClientName AS AltName,
    Contact.Number As Cellphone,
    CASE Direction WHEN 0 THEN 'rcvd' ELSE 'sent' END AS Direction,
    Messages.Body AS Message
    FROM Events
    INNER JOIN Contact ON Events.ContactID = Contact.ContactID
    INNER JOIN Messages ON Events.EventID = Messages.EventID
    WHERE Message LIKE "%text to search for%"
    ORDER BY Time;

    Or, from DBeaver, export the results to CSV, and I think you should be able to specify a delimiter such as | (which is an uncommon character unlikely to be used in messages).

    Or, there's even an extension to DBeaver which will let you export to Excel, I find. https://github.com/dbeaver/dbeaver/wiki/Extension-Office

    Is any of that helping, or am I just making it more confusing?

  21. I really thankyou for your time.
    Oh Dear, I am using SQL Lite, I tried as you said in last suggestion, but it doesn't output anything, whereas the original works fine to extract data, just need to either be able to export to SQL or find a specific part of a message
    SELECT strftime('%Y-%m-%d %H:%M:%S',(Events.TimeStamp/1000),'unixepoch') AS Time,
    Contact.Name AS Name,
    Contact.ClientName AS AltName,
    Contact.Number As Cellphone,
    CASE Direction WHEN 0 THEN 'rcvd' ELSE 'sent' END AS Direction,
    Messages.Body AS Message
    FROM Events
    INNER JOIN Contact ON Events.ContactID = Contact.ContactID
    INNER JOIN Messages ON Events.EventID = Messages.EventID
    ORDER BY Time;

    Appreciate the help

  22. OK, so you're using the commandline sqlite command, like this, where viber.sql contains the query?
    sqlite3 -header -csv -separator '|' viber.db < viber.sql > output.csv

    … and it outputs a csv file?
    … and you just want to search it for some text?

    I don't see your problem in importing it into Excel. This guide should be fairly good. https://www.copytrans.net/support/how-to-open-a-csv-file-in-excel/
    At stage 5, just put | in the Other box.

    If that still isn't working, its just a text file. You could open it in a text editor, or even in Word and just use the Find button to look for text.

  23. Sorry to be such a pest. Thankyou in advance for your patience. I am a novice….. 🙂
    I understand that if I can extract the whole database into .csv / excel I can find the text I want. My problem is I cannot get that to work. The original SQL extracts the data within SQLite easily. I need that to extract to .csv

    I tried to run by itself the SQL:
    sqlite3 -header -csv -separator '|' viber.db output.csv
    but get syntax error
    I then tried to put the SQL:
    sqlite3 -header -csv -separator '|' viber.db output.csv
    in to the original SQL you provided: (tried several places, but get syntax error)
    SELECT strftime('%Y-%m-%d %H:%M:%S',(Events.TimeStamp/1000),'unixepoch') AS Time,
    Contact.Name AS Name,
    Contact.ClientName AS AltName,
    Contact.Number As Cellphone,
    CASE Direction WHEN 0 THEN 'rcvd' ELSE 'sent' END AS Direction,
    Messages.Body AS Message
    FROM Events
    INNER JOIN Contact ON Events.ContactID = Contact.ContactID
    INNER JOIN Messages ON Events.EventID = Messages.EventID
    ORDER BY Time;

  24. Hi

    I used this to filter by sender

    SELECT strftime('%Y-%m-%d %H:%M:%S',(Events.TimeStamp/1000),'unixepoch') AS Time,
    Contact.Name AS Name,
    Contact.ClientName AS AltName,
    Contact.Number As Cellphone,
    CASE Direction WHEN 0 THEN 'rcvd' ELSE 'sent' END AS Direction,
    Messages.Body AS Message
    FROM Events
    INNER JOIN Contact ON Events.ContactID = Contact.ContactID
    INNER JOIN Messages ON Events.EventID = Messages.EventID
    WHERE Name = 'personsname'
    ORDER BY Time;

    How do I query for msgs between 2 contacts please?

    Thanks in advance

  25. I came across this and really need some help. You would probably come the closest to someone who would understand. I have an iPhone 6 (I realize it's different, but) and due to my own fault, my storage got really full and overloaded the phone. It corrupted the photos app (iphone's own photos app that contains camera roll) somehow and I couldn't even open the app- it would just be a blank screen and crash. However, all the pictures remain and take up the same storage (100 gb). Yesterday, my Viber app glitched too and suddenly I couldn't scroll back up to my old messages like I could before- it just stopped at a very recent date. Tragically this happened but the app data still takes up the same amount of storage (400 mb) so I'm hoping somehow, I can do something to repair the Viber cache/database/sqlite/file/whatever it is, reboot it, and the app will magically repopulate all the messages. Problem is I can't back up my phone to itunes or any program before I start on a solution. Because of the glitches/corruption, I can't even do that. Would you know any solution? Or know anyone who could?? I would be willing to pay.

Leave a Comment