Re: [sqlite] Tool for extracting deleted data from unvacuumed SQLite files
Hello everyone, I've released v0.4 of Undark last night. Improves on the extraction of data out of the freeblocks as well as now allowing for blob dumps that extend beyond a single page. Also added an option to let you specify the DB page size for when trying to recover data from corrupted SQLite databases. At the moment you still have to run Undark in two passes if you want to extract both the normal payload data and the freespace data, hoping to merge this in 0.5. http://pldaniels.com/undark Regards, Paul. -- Computer Repairs for Charters towers - http://ctpc.biz A.B.N. 19 500 721 806 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Differences in row removal methods between iOS SQLite and PC?
Richard, > The use of these 2-byte values is why the maximum page size in SQLite > is 65536, btw. Nice to know about the technical reasons behind these things. One last question for now, if that's okay; regarding the freelist pages, does one include the trunk pages in the count [ I'm assuming it does, given that I come up one page short every time in my current tests ]? Many thanks -- Computer Repairs for Charters towers - http://ctpc.biz A.B.N. 19 500 721 806 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Differences in row removal methods between iOS SQLite and PC?
On Sat, 12 Oct 2013 22:15:21 -0400 Richard Hipp wrote: > On Sat, Oct 12, 2013 at 10:06 PM, Paul L Daniels > wrote: > > > > > Intact record, before removal; > > 81 16 86 30 05 1D 47 81 65 52 45 4D 4F 56 45 4D ...0..G.eREMOVEM > > > > Removed using sqlite3 cli [ Ubuntu 13.04 ], secure_delete = 0; > > 00 00 00 9A 05 1D 47 81 65 52 45 4D 4F 56 45 4D ..G.eREMOVEM > > > > It inserted a "freelist" record. 2-byte offset to the next block of > freespace (in this case zero since it is the last free block on the > chain) followed by a 2-byte size for this block (154 bytes in this > case). So even with secure_delete=OFF, the first four bytes of a > deleted record are often overwritten. Aaah, many thanks for that. Tree-from-the-forest clarity event. Curious that it doesn't seem to occur on the iOS SMS database file in the same way. Now time to get back to work on the Undark. Regards, Paul. -- Computer Repairs for Charters towers - http://ctpc.biz A.B.N. 19 500 721 806 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Differences in row removal methods between iOS SQLite and PC?
On Sun, 13 Oct 2013 02:40:03 +0100 Simon Slavin wrote: > I'm wondering whether one of those applications was in WAL mode and > the other not. This is not intended to do anything specific with the > database file but I can imagine the difference you describe being a > side-effect. > I tried with the journal mode set to off/none, no difference in this test instance ( see other reply to Richard Hip showing the data dump ). Regards, Paul. -- Computer Repairs for Charters towers - http://ctpc.biz A.B.N. 19 500 721 806 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Differences in row removal methods between iOS SQLite and PC?
On Sat, 12 Oct 2013 21:48:50 -0400 Richard Hipp wrote: > The iPhone is using the default method, which is to leave the bytes > intact and simply mark the deleted region of the page as available > for reuse by a subsequent INSERT. That is the default method because > it is faster. Zeroing out the deleted fields, while quick, does take > measurable time. > > We added the SQLITE_SECURE_DELETE compile-time option and the "PRAGMA > secure_delete=ON" command for Firefox. I've got the secure_delete option turned off, it's not zeroing the whole record/payload, only the first two fields ( payload length, rowid ). Intact record, before removal; 81 16 86 30 05 1D 47 81 65 52 45 4D 4F 56 45 4D ...0..G.eREMOVEM Removed using sqlite3 cli [ Ubuntu 13.04 ], secure_delete = 0; 00 00 00 9A 05 1D 47 81 65 52 45 4D 4F 56 45 4D ..G.eREMOVEM That said, I'm not sure what the significance of the 9A is. The 05 byte is the payload header size varint (5 bytes). Paul. -- Computer Repairs for Charters towers - http://ctpc.biz A.B.N. 19 500 721 806 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Differences in row removal methods between iOS SQLite and PC?
While testing Undark with my test DB the other night, I noticed it wasn't picking up any of the deleted rows, despite them existing in the DB still. On inspection with the hex-editor I found that the SQLite3 CLI tool has been zero-byte'ing the length and (possibly) Row-ID bytes of the header of the btree cell format record, as opposed to what's happening on the iOS edition where these two items are still intact. A quick conversation on the SQLite IRC channel bought up the idea that maybe iOS is doing things slightly differently to assist in data-recovery (NSA?). Anyone know what the *default* method should be ( I'm assuming the zero-byteing is the correct method, as it genuinely marks the payload in a way that it cannot be casually recovered by normal methods that I'm using ). For me to recover the data deleted in this manner I'm going to have to add a heuristics algorithm. Paul. -- Computer Repairs for Charters towers - http://ctpc.biz A.B.N. 19 500 721 806 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can't seem to create DB with unvacuumed data [ for testing ]
On Fri, 11 Oct 2013 23:15:24 -0500 Drake Wilson wrote: > Quoth Paul L Daniels , on 2013-10-12 > 13:57:00 +1000: > > I've tried the pragma autovacuum=0 and journalling set to none to > > no avail. Every time I exit from the command line SQlite3 tool I > > find that the data I explicitly deleted has genuinely been removed. > > > > Any suggestions? > > PRAGMA secure_delete=0 ? That did it nicely, many thanks for that. -- Computer Repairs for Charters towers - http://ctpc.biz A.B.N. 19 500 721 806 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can't seem to create DB with unvacuumed data [ for testing ]
I'm trying to create a sqlite3 db with unvacuumed data in it so I can verify the functionality of my SQLite deleted data recovery tool "Undark", but would you believe, other than the iPhone databases that I've pulled from my phones I cannot seem to create a DB that has persistent deleted rows in it. I've tried the pragma autovacuum=0 and journalling set to none to no avail. Every time I exit from the command line SQlite3 tool I find that the data I explicitly deleted has genuinely been removed. Any suggestions? Paul. -- Computer Repairs for Charters towers - http://ctpc.biz A.B.N. 19 500 721 806 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tool for extracting deleted data from unvacuumed SQLite files
Hello all, Version 0.3 of the SQLite undelete/recovery tool has been released, still very much in beta phase, no doubt a lot of bugs and core dumps on some files. http://pldaniels.com/undark/undark-0.3.tar.gz (MD5SUM:19d3183e7a0b782d658bcb631cc4146b ) Regards, Paul -- Computer Repairs for Charters towers - http://ctpc.biz A.B.N. 19 500 721 806 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tool for extracting deleted data from unvacuumed SQLite files
> If Undark decodes the metadata, it might be more useful to create a > new database instead of a CSV file. The new database would be a > mirror of the input, except that its tables would hold deleted rows > (or both deleted and extant, with a column signifying which). I may look in to adding such a feature in the future, though it'd require a few more layers of funtionality within Undark to be able to facilitate it. It should be cautioned that due to the statistical probabilities dumped data should always still be vetted by a human for now to weed out false rows that arise due to the limited size of the probability space ( the fewer columns in a table the higher the chance of a false hit ). Regards, Paul -- Computer Repairs for Charters towers - http://ctpc.biz A.B.N. 19 500 721 806 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tool for extracting deleted data from unvacuumed SQLite files
On Sun, 6 Oct 2013 15:07:06 +0100 Simon Slavin wrote: > Quick summary, most of which you probably do right: > > Numeric fields should go unquoted and string fields in a CSV files > must be quoted: Thanks for the notes, there's a few things in there I'm not doing right, so I do sincerely appreciate the input and that'll be folded in to the next release ( fairly quick cycle at the moment as the bugs/features are tacked on ). The original purpose of the whole Undark to retrieve a client's SMSs was satisfied simply from a visual dump ( as opposed to interpreted ) and as such I didn't put a lot of fine tuning in to the CSV format. I greatly appreciate the tips there, thank you. The blob details were something I wasn't having any real success in finding during my short searches around the net, so again, another very good bit of info. After my original post, I realised that Undark could also be used to pull data from corrupted db's ( missing tables or various B-tree indexes ), so I'll probably need some willing victims to test that later. Regards, Paul. -- Computer Repairs for Charters towers - http://ctpc.biz A.B.N. 19 500 721 806 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Tool for extracting deleted data from unvacuumed SQLite files
Hello everyone, Sincerely hoping this is the appropriate place to post this announcement. A while ago a client needed some important SMSs retrieved from their iPhone that had been deleted, anyhow, long story short, I've since created a tool which I now call "Undark" to pull all the data it can find in a SQLite3 file and dump it to stdout in a CSV type format. I looked at several forensics & sleuthing websites during the writing process and at this point I couldn't find anything else to do the job. Initially I wrote it so that it was specifically structured to look for iPhone iOS6 SMSs, but by the time the next revision came around I had made it work with all data. I'm offering "Undark" to the community under a BSD Revised licence. "Undark" is only at v0.2 at this time, it's still quite coarse around some corners and it does not differentiate between deleted and undeleted rows. It also has a limitation of only decoding what it finds within the SQLite page (ie, 4K in most cases), if the row extends beyond the page the data won't be retrieved ( I am working on this for v0.3 ), finally, it doesn't decode or dump BLOB data yet, simply not sure how to represent that sanely on a CSV dump. Obviously if the DB has been vacuumed then there's simply no chance to get it back; but fortunately I've found that a lot of them aren't vacuumed. Available at - http://pldaniels.com/undark -- Computer Repairs for Charters towers - http://ctpc.biz A.B.N. 19 500 721 806 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A proposal for SQLite version 3.0
> Could we please see the current behaviour set in stone? I'd like to know > that generated keys will always fit into 32 bits (provided I don't > exceed ~4.3 billion records, naturally). I think that it's a dangerous precent to fix these things in stone, certainly at the source level. Perhaps, should it become a concern, offer the option at compile time to have 32/64/n bit keys. Far too often I've seen stone-cast items such as this become the agony of others. Paul. -- Paul L Daniels - PLD Software - Xamime Unix systems Internet Development A.B.N. 19 500 721 806 ICQ#103642862,AOL:pldsoftware,Yahoo:pldaniels73 PGP Public Key at http://www.pldaniels.com/gpg-keys.pld - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Concurrency in SQLite
On Sun, 23 Nov 2003 12:22:25 -0500 "D. Richard Hipp" <[EMAIL PROTECTED]> wrote: > Lots of people seem to think that better concurrency in > SQLite would be useful. But I am having trouble understanding > why. I've only just caught onto this thread but I have to also ponder and agree... SQLite is a great system for what it's designed for and I'm not too keen on seeing it possibly pushed away from its original spirit and into some sort of split client/server/complex system. The original simplicity of SQLite is what attracted me to it and no doubt many others. Perhaps a fork into SQL-med or something would be more appropriate and leave SQLite as 'lite' for the rest. Just my 2c. Regards. -- Paul L Danielshttp://www.pldaniels.com Linux/Unix systemsInternet Development ICQ#103642862,AOL:pldsoftware,IRC:inflex irc.freenode.net A.B.N. 19 500 721 806 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
