Take a look at these presentations
http//
specifically, presentation 3 deals with the structure of SQLite Rows.
SQLite seems to keep the rows intact (mostly) until the space is required for new data, so you should be able to manually decode each of the rows you're interested in.
I wrote a python script a while back which may also do the job, if you're interested (might need a bit of tweaking) - PM me your details, and i can email it to you.
Hex editor will show you everything - but in the way it is stored. I do not know about SQL Lite but if the text is compressed, then a hex viewer will not help.
SQL will contain lots of control data, pointers etc.
One way to see if your required text does exist in plain form is to search for the file for possible key words, may be the user name, or even just 'the' or 'and'. It is possible you will find an area where text can be read. With a large file (and you do not give the size) it can be hard to find this area by just scrolling through.
Glad my presentation has been useful to someone!
There's a couple of different ways to pull deleted data from SQLite, understanding the internal structure of a record is intrinsic to the most complete and flexible solutions.
But if you only want to pull strings back from a binary file (even as a starting point) grab strings.exe from sysinternals http//live.sysinternals.com/
Just point it at the binary file and redirect the output back to a text file at the command prompt eg
strings.exe your_bin_file.bin > recoveredstrings.txt
I use this for quick checks all the time. If you grab a windows version of grep (eg. from cygwin) you can perform filtering in place, if you're looking for keywords or patterns for example
strings your_bin_file.bin | grep (key)?words? > filteredstrings.txt
One thing you should know about the sms tables from the iPhone is that the dates and times are stored as 32bit unix epoch times, you should be able to pick them out by hand near the message content (if you only need a couple anyhow).
The SQLite file format is pretty generous with deleted data - it's like a little file system microcosm, records just get removed from the pointer map, the records themselves stick around until overwritten (and actually, the way SQLite writes records it makes overwriting less likely than it might be otherwise.)
Good luck, it can get pretty complicated, but keep a cool head (and notes) and I'm sure the understanding will come together.
SQLite doesn't compress text, but numbers are in an interesting format, as described in the presentation I liked to before. Might take a little work, but you should be able to decode it all with a hex editor.
SQLite doesn't compress text, but numbers are in an interesting format, as described in the presentation I liked to before. Might take a little work, but you should be able to decode it all with a hex editor.
Not all numbers - the numbers used by SQLite to describe itself are "interesting" as you point out, however numbers in the records themselves are stored as straight integers or floats - and always big endian. A hex editor with a value inspector will decode them on the fly, which is super useful!
You're probably right - it's been a while since I had to do it manually, I just remember tearing my hair out (and wasting lots of paper) the first time I tried to manually calculate the VarInts…
I wrote a script for it. It's one of those things that no matter how many times you do it by hand, it doesn't get any easier!
I've seen similar structures elsewhere - Google use them internally in their protocol serialization files, Safari (the full fat OSX version) uses a little endian style varint - which is even more of a brain-ache to understand!
First off I want to say thank you all so much for sharing your knowledge. I appreciate it more than I can express.
I'm still very confused, so I decided to take pics of my monitor and hopefully this will help
I opened up notepad++ and opened SMS.db and the complete 3d0d file and did a search for the phone number. The results are in this pic for both searches.
there are so many options within notepad++
I have tried converting (basically just played with it any way I could) but it's always jibberish
here it's translated to hex
here it is opened up in HexEdit
here it is opened in WordPad
here it is in TextPad
I've read and read til I'm on overload… So now that y'all see these pics, can you explain how to use either hexedit or notepad++ to translate it into something readable?
I've read so much… Like it's base64, endian, etc etc etc… I'm sooooo confused now.
When this is opened in sqlite, the texts that were not deleted appear, but the deleted ones aren't there.
I've also used iPhone backup extractor (which is why I have the SMS.db file) I also was able to view pictures using that too. (pictures that were in this 3d0d file)
the SMS.db file did not exist before I ran iPhone backup extractor. To get to it, I open the main file, then click Library, SMS, then SMS.db
The size of the files are roughly 4500 kb
so now, does this extra info help you at all?
Again, thank you! I've gone from only playing on facebook and email and word to messing with all this… It's quite overwhelming!
Ignore notepad.
To extract the data and metadata you will need to have special program, or write one. Personally, having spent 30 years doing this type of work, the section you show looks quite straightforward, but the jump from Facebook to this type of analysis is BIG.
The easiest way to start is to look at an entry that display correctly in SQL Lite, and then work out the record structure. Determine which fields will be useful to you. Then apply the same rules to all of the database. There is probably a flag somewhere to say the data has been deleted, though this may be a table stored elsewhere in the database.
I think that maybe a better place to start is to back off away from SQLite and go back to basics.
If you're not comfortable in binary data you might want to take a step back and learn a little bit more about how computers store data, whether it's text, numbers, timestamps, common data structures (SQLite uses a pointer map for example - worth knowing how that works if you want to optimise how you're dealing with the data.) Even starting from what the "hex" actually is, how it relates to the bits of data it represents.
Pretty much no reason to use a text editor for any of this, a good hex editor will be the way to go
(PS. If you were trying to make the data anonymous you should probably cover up the corresponding hex. I think most people on this could read the phone numbers directly from the hex!)