At 2:24 PM +0100 7/4/05, Tim Browse wrote:
Hi,
Before I go diving into the SQLite source code, can anyone tell me
what happens to unused space? (i.e. from deleted data in the db)
It gets reused unless you use VACUUM in which case it could end up
reused in the DB or reused in the OS file system
Specifically, I'm interested in whether it gets over-written by
zeroes, or something. I'm storing information in a database (in a
blob field), which the user can then decide to encrypt, and then the
plaintext blob is set to NULL, and the ciphertext blob (i.e. a
different column) is set to contain the encrypted data.
So what I'm asking is, in this scenario, is it possible that
sometimes I could load the sqlite db into a hex editor, and see the
original unencrypted data? (i.e. the old deleted data from the
plaintext blob)
Yes and, in fact, you might also see it in a disk drive hex editor on
some free sector or in the VM swap area.
If so, a workaround is obviously to set the original blob data to
contain a block of zeroes (of the same length), and *then* set it to
NULL. But then it's also within the realms of possibility (not
wishing to criticise anyone's code; only guessing) that SQLite might
reallocate a block for the blob even if it's the same length as the
old data, in which case my cunning plan wouldn't work either.
Actually, as long as you don't release the space you have control of
it during your rewriting.
So in summary, when I delete a blob, I want it to stay deleted, even
from prying eyes with a hex editor - what's the best way to do this
in the SQLite model?
Overwrite exactly and commit the update before you release the
storage by committing NULL Depending on how paranoid you are, you
will have to overwrite all disk based data with all zeros, all ones
and then alternating ones and zeros or else one can reconstruct the
data if one physically has possession of the media. Indeed, you will
also need to zero all buffers in your normal course of business
because they may be staged to your VM disk.
NB. To pre-empt various invigorating arguments, I know about the
various 'encrypt the whole db' solutions that are available, but I
don't need them; I have my own encryption framework. I just want to
know how to *really* delete data from the DB (without calling VACUUM
after every update).
Calling VACUUM just moves the problem to the OS' file system.
Tom
