[sqlite] Destroy all evidence of a database

[Eduardo Morras]

On Wed, 22 Apr 2015 20:07:45 +0100
Simon Slavin  wrote:

> 
> On 22 Apr 2015, at 7:18pm, Scott Hess  wrote:
> 
> > The only way SQLite can get to the disk is using the vfs, so if the
> > vfs encrypts things, all of the files (main db, temp db, journal,
> > everything) will be encrypted.
> 
> Guys.  Guys.  Guys.  My app doesn't have access to any level below
> standard file system calls.  This is a highly secure system.  Any
> calls which talk directly to hardware (e.g. turn the caps lock light
> on, access SMART diagnostics, try to count the number of displays)
> will fail because my app isn't allowed to do that stuff.  Any attempt
> from my app to mount anything will fail.  My app has access to just
> GUI and files.  I don't have to worry about the security setup at OS
> level, merely not leave files about with sensitive information in
> them.

You can reference count the number of files it creates and deletes. If it 
creates more files than it deletes, you have a problem. It doesn't involve big 
changes in sqlite vfs code and even may be implemented in sqlite3 core code as 
a debug feature.

> 
> Simon.

---   ---
Eduardo Morras 

[sqlite] Destroy all evidence of a database

[James K. Lowden]

On Wed, 22 Apr 2015 16:56:07 +0100
Simon Slavin  wrote:

> You have made me realise, however, that a nice attack against
> encrypted SQLite databases might be to crash a SQLite application
> while it's processing and examine any journal files, shared memory
> file and temporary index files.  It might be interesting to review
> the various encryption systems widely available for SQLite and figure
> out which of them would be vulnerable to such an attack.

Encryption found its way into DBMS featuredom about 10 years ago, I
guess.  I've always thought it was patently stupid.  A DBMSs job is
store data.  Encryption probably should be done in the application.
Failing that, whole-filesystem encryption solves the problem in a
general way.  For SQLite, an encrypted loopback filesystem would solve
you problem neatly, except for that "no virtual filesystem"
stipulation.  

Coming back to the problem at hand, Scott Hess suggested that you modify
the SQLite VFS to trap all calls to the underlying open(2).  In that
way ISTM you could add each opened filename to a list processed by
a function whose address is passed to atexit(3).  Assuming the task
terminates nomally (unsignalled) all registered files would be deleted
by that function. If signals also need to be dealt with (IIUC they do
not) then I would fork the process that uses SQLite and arrange for the
parent to delete the files when the child terminates.  

What I like about the VFS idea is that it's minimally dependent on
SQLite particulars.  However the code changes in the future, its
relationship to the VFS will be quite stable.  You don't need to know
how many OS files are opened, or by what name.  Just trap and record
each one.  

HTH.  

--jkl

[sqlite] Destroy all evidence of a database

[Simon Slavin]

On 22 Apr 2015, at 9:40pm, John McKown  wrote:

> ?only his app has access to this directory,
> so there are no worries about "somebody else" putting files in it. So I am
> _guessing_ that there are files in that directory which need to persist
> between executions of his application.

Correct.

> Now, if the OP can verify that only
> one instance of his app can run at a time (which means only that execution
> instance can create/update/delete files in the directory while that
> instance is running),

Correct.

> then perhaps it would be possible for the app to keep
> a list of all file names in the given directory which exist before he
> starts up the SQLite portion of the application. Then, after he closes out
> the SQLite portion of the application, he again scans the directory again
> and deletes all files in the directory which did not exist at the time of
> the initial scan. This might work in a single threaded, single use at a
> time, type application. It would not work if multiple copies/instances of
> the app can run concurrently (hopefully using different SQLite data bases).?

The app itself can create and delete other files while it runs.  When it is 
quit it is possible that some files which previously existed have been deleted 
and others created.  These files are not SQLite-related files and can be in a 
few different formats.  But a variation on your solution is workable: the app 
keeps a perfect record of which files should exist in its folder and deletes 
everything it doesn't know about before quitting.  A strange sort of thing to 
do but in some situations, maybe this one, it makes sense.  I'll ponder it.

Simon.

PS: Well done Graham.

[sqlite] Destroy all evidence of a database

[Graham Holden]

> ?Well, the best that I can think of is to have your application create a
> new, randomly named, directory...

I can't help directly (I don't the innards of SQLite) but can
hopefully clarify what *I* think Simon's asking: he's already said in
his original message that he used to use a temporary directory, but
now cannot do this.  Neither is he asking about how to encrypt files,
nor how to securely delete them.  He just wants to know which files
SQLite *might* create that he needs to be concerned about deleting on
exit. 

Sorry for the rant, but there's been a lot of probably well-meaning
responses that don't seem to have read Simon's questions.

Graham.

[sqlite] Destroy all evidence of a database

[Simon Slavin]

On 22 Apr 2015, at 7:18pm, Scott Hess  wrote:

> The only way SQLite can get to the disk is using the vfs, so if the
> vfs encrypts things, all of the files (main db, temp db, journal,
> everything) will be encrypted.

Guys.  Guys.  Guys.  My app doesn't have access to any level below standard 
file system calls.  This is a highly secure system.  Any calls which talk 
directly to hardware (e.g. turn the caps lock light on, access SMART 
diagnostics, try to count the number of displays) will fail because my app 
isn't allowed to do that stuff.  Any attempt from my app to mount anything will 
fail.  My app has access to just GUI and files.  I don't have to worry about 
the security setup at OS level, merely not leave files about with sensitive 
information in them.

I know a lot about computer security -- problems with drivers and caching and a 
ton of stuff I won't discuss here.  I asked a question on this list because I 
wanted details of what SQLite does.  Probably stuff I could get if I knew 
SQLite source code well.  I just don't have time to read and understand the 
right parts of the SQLite source.

And no, it's not Windows.  Or any operating system more than perhaps a couple 
of you have knowingly used.

Simon.

[sqlite] Destroy all evidence of a database

[R.Smith]

Actually, I assumed SQLite made the duplicates / alternates, it may well 
have been the anti-virus doing it. I doubt anything else had a motive 
though.

On 2015-04-22 06:20 PM, R.Smith wrote:
>
>
> On 2015-04-22 05:56 PM, Simon Slavin wrote:
>> On 22 Apr 2015, at 4:46pm, Michael Stephenson  
>> wrote:
>>
>>> Simon, if the data in the database is sensitive, could you encrypt 
>>> the database (ala something like https://www.zetetic.net/sqlcipher/)?
>> Unfortunately, this doesn't help.  I'm not concerned with the 
>> database file itself.  I know exactly what that's called, and I can 
>> check it has been correctly deleted.  I'm concerned with the data in 
>> several external files that SQLite creates and deletes as it does its 
>> work.  Some of those would contain unencrypted data.  And some of 
>> them have unpredictable names, or, at least since the filenames are 
>> not documented they may change in future versions of SQLite.
>>
>> You have made me realise, however, that a nice attack against 
>> encrypted SQLite databases might be to crash a SQLite application 
>> while it's processing and examine any journal files, shared memory 
>> file and temporary index files.  It might be interesting to review 
>> the various encryption systems widely available for SQLite and figure 
>> out which of them would be vulnerable to such an attack.
>
> I've experienced some things you may need to take note of. I've had 
> SQLite files created and amended on a Windows system with an 
> anti-virus that held on to the journal files making them either 
> read-only or not-deletable. (I did not check nor cared which, my 
> solution was to remove the anti-virus).  The point is that when this 
> happened, SQLite made duplicate/alternate journal files with what 
> seemed to be random hashes in the name near the end. (Who knows if 
> they were random, hashes all look random).
>
> These files were often empty (0-byte-length) after a session but 
> whatever writings went on in there certainly still reflected on the 
> disk surface.
>
> Maybe a dev can shed more light - I do not know the mechanism for this 
> - but I do know it was easy to find and remove them for me, they 
> always had the same base name as the DB file with some extra bits 
> (what seemed like a hash) sprinkled on at the end and terminating in 
> the usual journal extensions.
>
> This was not a great concern for me so my observations/research is 
> full of holes and unknowns, but I know that it happened.
>
> Good luck.
> Ryan
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Destroy all evidence of a database

[R.Smith]

On 2015-04-22 05:56 PM, Simon Slavin wrote:
> On 22 Apr 2015, at 4:46pm, Michael Stephenson  
> wrote:
>
>> Simon, if the data in the database is sensitive, could you encrypt the 
>> database (ala something like https://www.zetetic.net/sqlcipher/)?
> Unfortunately, this doesn't help.  I'm not concerned with the database file 
> itself.  I know exactly what that's called, and I can check it has been 
> correctly deleted.  I'm concerned with the data in several external files 
> that SQLite creates and deletes as it does its work.  Some of those would 
> contain unencrypted data.  And some of them have unpredictable names, or, at 
> least since the filenames are not documented they may change in future 
> versions of SQLite.
>
> You have made me realise, however, that a nice attack against encrypted 
> SQLite databases might be to crash a SQLite application while it's processing 
> and examine any journal files, shared memory file and temporary index files.  
> It might be interesting to review the various encryption systems widely 
> available for SQLite and figure out which of them would be vulnerable to such 
> an attack.

I've experienced some things you may need to take note of. I've had 
SQLite files created and amended on a Windows system with an anti-virus 
that held on to the journal files making them either read-only or 
not-deletable. (I did not check nor cared which, my solution was to 
remove the anti-virus).  The point is that when this happened, SQLite 
made duplicate/alternate journal files with what seemed to be random 
hashes in the name near the end. (Who knows if they were random, hashes 
all look random).

These files were often empty (0-byte-length) after a session but 
whatever writings went on in there certainly still reflected on the disk 
surface.

Maybe a dev can shed more light - I do not know the mechanism for this - 
but I do know it was easy to find and remove them for me, they always 
had the same base name as the DB file with some extra bits (what seemed 
like a hash) sprinkled on at the end and terminating in the usual 
journal extensions.

This was not a great concern for me so my observations/research is full 
of holes and unknowns, but I know that it happened.

Good luck.
Ryan

[sqlite] Destroy all evidence of a database

[Simon Slavin]

On 22 Apr 2015, at 4:46pm, Michael Stephenson  wrote:

> Simon, if the data in the database is sensitive, could you encrypt the 
> database (ala something like https://www.zetetic.net/sqlcipher/)?  

Unfortunately, this doesn't help.  I'm not concerned with the database file 
itself.  I know exactly what that's called, and I can check it has been 
correctly deleted.  I'm concerned with the data in several external files that 
SQLite creates and deletes as it does its work.  Some of those would contain 
unencrypted data.  And some of them have unpredictable names, or, at least 
since the filenames are not documented they may change in future versions of 
SQLite.

You have made me realise, however, that a nice attack against encrypted SQLite 
databases might be to crash a SQLite application while it's processing and 
examine any journal files, shared memory file and temporary index files.  It 
might be interesting to review the various encryption systems widely available 
for SQLite and figure out which of them would be vulnerable to such an attack.

By the way, if you want good (paid, not free) SQLite encryption you want to 
check out



Simon.

[sqlite] Destroy all evidence of a database

[John McKown]

On Wed, Apr 22, 2015 at 3:50 PM, Simon Slavin  wrote:

>
> On 22 Apr 2015, at 9:40pm, John McKown 
> wrote:
>
> > ?only his app has access to this directory,
> > so there are no worries about "somebody else" putting files in it. So I
> am
> > _guessing_ that there are files in that directory which need to persist
> > between executions of his application.
>
> Correct.
>
> > Now, if the OP can verify that only
> > one instance of his app can run at a time (which means only that
> execution
> > instance can create/update/delete files in the directory while that
> > instance is running),
>
> Correct.
>
> > then perhaps it would be possible for the app to keep
> > a list of all file names in the given directory which exist before he
> > starts up the SQLite portion of the application. Then, after he closes
> out
> > the SQLite portion of the application, he again scans the directory again
> > and deletes all files in the directory which did not exist at the time of
> > the initial scan. This might work in a single threaded, single use at a
> > time, type application. It would not work if multiple copies/instances of
> > the app can run concurrently (hopefully using different SQLite data
> bases).?
>
> The app itself can create and delete other files while it runs.  When it
> is quit it is possible that some files which previously existed have been
> deleted and others created.  These files are not SQLite-related files and
> can be in a few different formats.  But a variation on your solution is
> workable: the app keeps a perfect record of which files should exist in its
> folder and deletes everything it doesn't know about before quitting.  A
> strange sort of thing to do but in some situations, maybe this one, it
> makes sense.  I'll ponder it.
>

?Glad it was of some help. You didn't specify the language in which your
app is written. If it is C, then you might be able use use #define in such
a way that an internal version of creat(), open(), and unlink() are used to
"front end" the normal C library routines such that they can update the
global list of files created and deleted for tracking.

In an extreme situation, I would create my own "port" of SQLite which used
this type of logic to ensure that all files were cleaned up. But that is
getting very labor intensive.



>
> Simon.
>
> PS: Well done Graham.
>

-- 
If you sent twitter messages while exploring, are you on a textpedition?

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Destroy all evidence of a database

[Simon Slavin]

On 22 Apr 2015, at 3:46pm, Jim Callahan  
wrote:

> Can you turn off logging and overwrite the database with unencrypted zeros
> or nulls;
> just before deleting it?

The operating system overwrites disks and memory with random bits both just 
after it is released and just before it is allocated again.  It also allocates 
new memory and disk space randomly, rather than just handing you the next 20 
blocks/sectors.  It is a /very/ paranoid operating system.  And the numerous 
precautions it takes make it very slow and a little annoying to use.

> Encrypting the overwrite character(s) would give the encryption attacker a
> cleartext -- a bad move right out of the "Imitation Game".

This is the sort of reason I'm reluctant to freak the underlying platform.  The 
people who designed/wrote it are good at their jobs and I have to worry about 
only programmer-level things.  If the platform itself (OS/hardware) leaks 
information that's not my concern.

Simon.

[sqlite] Destroy all evidence of a database

[John McKown]

On Wed, Apr 22, 2015 at 2:58 PM, Graham Holden 
wrote:

>
> > ?Well, the best that I can think of is to have your application create a
> > new, randomly named, directory...
>
> I can't help directly (I don't the innards of SQLite) but can
> hopefully clarify what *I* think Simon's asking: he's already said in
> his original message that he used to use a temporary directory, but
> now cannot do this.  Neither is he asking about how to encrypt files,
> nor how to securely delete them.  He just wants to know which files
> SQLite *might* create that he needs to be concerned about deleting on
> exit.
>

?You're right, that was in the original posting. I either missed that one
sentence, or forgot it in the interim. Unfortunately, it does not appear
that the question can be answered without some more information (which may
be restricted). He did say that only his app has access to this directory,
so there are no worries about "somebody else" putting files in it. So I am
_guessing_ that there are files in that directory which need to persist
between executions of his application. Now, if the OP can verify that only
one instance of his app can run at a time (which means only that execution
instance can create/update/delete files in the directory while that
instance is running), then perhaps it would be possible for the app to keep
a list of all file names in the given directory which exist before he
starts up the SQLite portion of the application. Then, after he closes out
the SQLite portion of the application, he again scans the directory again
and deletes all files in the directory which did not exist at the time of
the initial scan. This might work in a single threaded, single use at a
time, type application. It would not work if multiple copies/instances of
the app can run concurrently (hopefully using different SQLite data bases).?



>
> Sorry for the rant, but there's been a lot of probably well-meaning
> responses that don't seem to have read Simon's questions.
>
> Graham.
>
>
-- 
If you sent twitter messages while exploring, are you on a textpedition?

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Destroy all evidence of a database

[Simon Slavin]

On 22 Apr 2015, at 3:23pm, John McKown  wrote:

> If it is
> a POSIX compliant, perhaps what you could do is create a "temporary"
> (mktemp) file of "appropriate" size.

I had never considered that idea.  Thank you very much.  Unfortunately it won't 
work in this situation because the people in control of the system would either 
say "No virtual file systems" or leap at the idea and insist that everyone uses 
virtual encrypted file systems for all data files at all times.  I'm not sure 
which would be worse.


On 22 Apr 2015, at 3:14pm, Richard Hipp  wrote:

> Can you add the SQLITE_OPEN_MEMORY option to the sqlite3_open() call,
> forcing SQLite to keep all content exclusively in memory and never
> writing to disk?

Sorry, the data can potentially get too big to keep it in memory (even virtual 
memory).  But a memory database would be a great solution if I could rely on it 
being small.  Thank for that too.

I'm guessing that since you didn't point out any persistent SQLite temporary 
file that I'd missed there are no obvious problems with the procedure I 
included in my post.  That's good enough for the SQLite-related part of this 
problem.

Simon.

[sqlite] Destroy all evidence of a database

[Eduardo Morras]

On Wed, 22 Apr 2015 13:50:43 +0100
Simon Slavin  wrote:

> Dear folks,

> 
> Assuming no hardware/OS faults is it possible for any other
> SQLite-created files to still exist ?  Journal ?  Temp index ?
> Shared memory ?  Anything ?

a) If the app crash, it may create a dump file with sqlite cache information.
b) If memory is low and depending of the OS, perhaps part of app code/data may 
reside on swap for a time after quit the app.
c) In Windows, antivirus software may lock a temp file and don't allow delete it
d) If use ZFS or similar filesystem, a snapshot of file system is taken each 
10-30 secs., you can go back in time and recover filesystem state from 2 days 
ago (up to 6 months IIRC)

> I have read .
> 
> Simon.

---   ---
Eduardo Morras 

[sqlite] Destroy all evidence of a database

[Scott Hess]

On Wed, Apr 22, 2015 at 12:07 PM, Simon Slavin  wrote:
> On 22 Apr 2015, at 7:18pm, Scott Hess  wrote:
>> The only way SQLite can get to the disk is using the vfs, so if the
>> vfs encrypts things, all of the files (main db, temp db, journal,
>> everything) will be encrypted.
>
> Guys.  Guys.  Guys.  My app doesn't have access to any level below
> standard file system calls.

Yes.  I understand.  That's why I suggested you use the SQLite vfs
layer.  Because at that layer you can intercept calls SQLite makes
before they hit the filesystem.  If you want to make sure SQLite isn't
storing files you don't know about, you can easily enforce that in the
vfs.  Of course enforcing that may cause SQLite to break if it
requires that file for some reason, but I'm assuming that your
environment includes test coverage to verify that everything you need
continues to work.  Assuming such test coverage, you could also use a
vfs to enumerate the entire set of files SQLite ever accesses that
you'll need to deal with, if you prefer not to have it running in
production.

> I know a lot about computer security -- problems with drivers and caching
> and a ton of stuff I won't discuss here.  I asked a question on this list
> because I wanted details of what SQLite does.  Probably stuff I could get
> if I knew SQLite source code well.  I just don't have time to read and
> understand the right parts of the SQLite source.

These are somewhat contradictory claims, so it's hard for me to tell
what level you can code things at, but I can tell you that I was able
to write a proxy vfs for SQLite in a few hours, including verifying
assumptions by browsing SQLite's os_unix.c.  One of them is in
Chromium's blink repo (Google for chromium_vfs and it's the top hit).

-scott

[sqlite] Destroy all evidence of a database

[John McKown]

On Wed, Apr 22, 2015 at 2:07 PM, Simon Slavin  wrote:

>
> On 22 Apr 2015, at 7:18pm, Scott Hess  wrote:
>
> > The only way SQLite can get to the disk is using the vfs, so if the
> > vfs encrypts things, all of the files (main db, temp db, journal,
> > everything) will be encrypted.
>
> Guys.  Guys.  Guys.  My app doesn't have access to any level below
> standard file system calls.  This is a highly secure system.  Any calls
> which talk directly to hardware (e.g. turn the caps lock light on, access
> SMART diagnostics, try to count the number of displays) will fail because
> my app isn't allowed to do that stuff.  Any attempt from my app to mount
> anything will fail.  My app has access to just GUI and files.  I don't have
> to worry about the security setup at OS level, merely not leave files about
> with sensitive information in them.
>
> I know a lot about computer security -- problems with drivers and caching
> and a ton of stuff I won't discuss here.  I asked a question on this list
> because I wanted details of what SQLite does.  Probably stuff I could get
> if I knew SQLite source code well.  I just don't have time to read and
> understand the right parts of the SQLite source.
>
> And no, it's not Windows.  Or any operating system more than perhaps a
> couple of you have knowingly used.
>
> Simon.
>

?Well, the best that I can think of is to have your application create a
new, randomly named, directory. Make that the current working directory. Do
all your SQLite work in that directory. Just before exiting, delete _all_
files in the directory, then change to the parent directory ( chdir("..") )
and delete the subdirectory entry. From a quick look at the source, SQLite
does not put any file in any directory other than the current working
directory, unless the application code specifies a specific directory.



-- 
If you sent twitter messages while exploring, are you on a textpedition?

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Destroy all evidence of a database

[Simon Slavin]

On 22 Apr 2015, at 2:07pm, Paul Sanderson  
wrote:

> You haven't said what operating system you are using

Sorry, but I can't.  However, the OS is strongly oriented towards security 
paranoia.  As long as the proper OS calls are used to delete files and release 
memory, you can assume that they are unrecoverable, with all the stuff a 
forensic expert would hate.  The same is true of any caching done at OS level 
or below (e.g. device drivers, storage which automatically manages sector 
usage, etc.).

> This might be a level of access you are not concerned with - I guess
> that all depends on how sensitive sensitive is :)

You got it.  But the platform (OS and hardware) is specially designed for this. 
 As you suspected I'm concerned only at the level of filespace which is still 
in use.

Simon.

[sqlite] Destroy all evidence of a database

[Paul Sanderson]

You haven't said what operating system you are using but I strongly
suspect that there will be plenty of pages from your database thrown
around by the OS itself in various caches/pagefiles etc. all of course
outside of the ability of SQLite to prevent.

Getting at these cached pages is not difficult (and I write software
to do this) but it does require low level access to the device (my
clients would normally have an image of the system). Given the DB
schema it is relatively straight forward to search for and "carve"
these pages and put the recovered records into a new blank database.

This might be a level of access you are not concerned with - I guess
that all depends on how sensitive sensitive is :)
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 22 April 2015 at 13:50, Simon Slavin  wrote:
> Dear folks,
>
> I have a setup where an app creates a single-user SQLite database by opening 
> a connection, uses it for a number of complicated things, closes the 
> connection, then deletes the database before quitting.  The data which goes 
> into this database is highly sensitive and it's very important that my app 
> deletes all files which might contain this data (journal, temp index, etc.) 
> before my application quits.
>
> For the purposes of this question, you can assume SQLite version 3.8.0 or 
> later, that my app never crashes (because I am JUST THAT GOOD), and that if a 
> file is deleted using remove()/unlink() the disk space it was in is not 
> recoverable, e.g. it is immediately overwritten with random bits and other 
> anti-forensic stuff.  You can also assume that if a fault is caused by a bug 
> in the operating system, or by malfunctioning hardware, or by the user 
> resetting the hardware while my app is running, that's not my problem.
>
> I used to solve this problem by creating a special folder to contain the 
> database.  After the connection to the database was severed it would delete 
> the folder.  This worked perfectly.  Unfortunately an update to the 
> underlying operating system means that my app is allocated one folder to work 
> in and cannot create subfolders.  The good parts of this change are that no 
> other app can access files stored in that folder, and that the 'temp folder' 
> is a subfolder of this folder.
>
> So my procedure has changed to this:
>
> 1) Set the journal mode to DELETE
> 2) Do a SELECT command because sometimes this forces (1) to happen.
> 3) Close the database connection, checking the value returned
> 4) Delete the database file
> 5) Generate an error if database file still exists
> 6) Quit
>
> The operating system guarantees that if an app quits or crashes, all its file 
> handles are closed and all pending deletes for those files happen.
>
> Assuming no hardware/OS faults is it possible for any other SQLite-created 
> files to still exist ?  Journal ?  Temp index ?  Shared memory ?  Anything ?
>
> I have read .
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Destroy all evidence of a database

[Simon Slavin]

Dear folks,

I have a setup where an app creates a single-user SQLite database by opening a 
connection, uses it for a number of complicated things, closes the connection, 
then deletes the database before quitting.  The data which goes into this 
database is highly sensitive and it's very important that my app deletes all 
files which might contain this data (journal, temp index, etc.) before my 
application quits.

For the purposes of this question, you can assume SQLite version 3.8.0 or 
later, that my app never crashes (because I am JUST THAT GOOD), and that if a 
file is deleted using remove()/unlink() the disk space it was in is not 
recoverable, e.g. it is immediately overwritten with random bits and other 
anti-forensic stuff.  You can also assume that if a fault is caused by a bug in 
the operating system, or by malfunctioning hardware, or by the user resetting 
the hardware while my app is running, that's not my problem.

I used to solve this problem by creating a special folder to contain the 
database.  After the connection to the database was severed it would delete the 
folder.  This worked perfectly.  Unfortunately an update to the underlying 
operating system means that my app is allocated one folder to work in and 
cannot create subfolders.  The good parts of this change are that no other app 
can access files stored in that folder, and that the 'temp folder' is a 
subfolder of this folder.

So my procedure has changed to this:

1) Set the journal mode to DELETE
2) Do a SELECT command because sometimes this forces (1) to happen.
3) Close the database connection, checking the value returned
4) Delete the database file
5) Generate an error if database file still exists
6) Quit

The operating system guarantees that if an app quits or crashes, all its file 
handles are closed and all pending deletes for those files happen.

Assuming no hardware/OS faults is it possible for any other SQLite-created 
files to still exist ?  Journal ?  Temp index ?  Shared memory ?  Anything ?

I have read .

Simon.

[sqlite] Destroy all evidence of a database

[Donald Griggs]

Regarding:

...A nice attack against encrypted SQLite databases might be to crash a
SQLite application while it's processing and examine any journal files,
shared memory file and temporary index files.  It might be interesting to
review the various encryption systems widely available for SQLite and
figure out which of them would be vulnerable to such an attack.

Both SEE and SQLCipher both mention meta-data concerns.



>From SEE:  http://www.hwaci.com/sw/sqlite/see.html

"SEE encrypts the entire database file - both data and metadata. To an
outside observer, an encrypted SQLite database file appears to be white
noise. Both the database file itself and its rollback journal are encrypted.
"
>From SQLCipher:  https://www.zetetic.net/sqlcipher/design/"Database
Encryption and Temporary Files

All data in the main database file is encrypted. In addtion[sic], SQLCipher
encrypt[sic] data pages in journal files. Provided that you taken the
important step of disabling file base temporary stores (i.e.
--enable-tempstore=yes during configuration and define
SQLITE_TEMP_STORE=2 during
build), we are primarily concerned with the following:

   - Rollback journals - Pages in the rollback journal are encrypted using
   the same key as the main database. Note that there is an unencrypted header
   in a rollback journal, but it doesn't contain any data. The journal created
   by a vacuum run is encrypted in the same way as a rollback journal.
   *Verification:* create an encrypted database, start a transaction, make
   changes, and then inspect the -journal file using hexdump or a similar
   program.
   - Write Ahead Log Files - Using the new WAL mode (i.e. PRAGMA
   journal_mode = WAL;), page data stored in the WAL file is encrypted
   using the datbase[sic] key. Pages in the rollback journal are encrypted
   using the same key as the main database. *Verification:* create an
   encrypted database, start a transaction, make changes, and then inspect the
   -wal file using hexdump or a similar program.
   - Statement journals - Statement journals are also encrypted. This is
   harder to "observe" because they are only created under very limited
   circumstances, and even then they use temporary files that are immediately
   deleted after use. Note that statement journals are maintained in memory if
   temporary files are disabled. *Verification: *Compile a build under
   linux that forces a minimal temp cache size (so that pages are actually
   written to disk) and allows the use of temporary files, start a transaction
   with updates that cause a statement journal to be written, and then inspect
   the file descriptor of the temporary journal in the /proc//fd directory.
   - Master journals - The master journal does not contain data (see
   http://www.sqlite.org/atomiccommit.html). Unlike the rollback journals,
   the master journal does not contain any original database page content.
   Instead, the master journal contains the full pathnames for rollback
   journals for every database that is participating in the transaction.

Other transient files are not encrypted, so you must disable file based
temporary storage if your application will use temp space, as noted above."

[sqlite] Destroy all evidence of a database

[tabris]

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
-- next part --
On 04/22/2015 12:07 PM, Simon Slavin wrote:
> On 22 Apr 2015, at 7:18pm, Scott Hess  wrote:
>
>> The only way SQLite can get to the disk is using the vfs, so if the
>> vfs encrypts things, all of the files (main db, temp db, journal,
>> everything) will be encrypted.
> Guys.  Guys.  Guys.  My app doesn't have access to any level below standard 
> file system calls.

They mean the _sqlite_ VFS, not the OS VFS.


-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 263 bytes
Desc: OpenPGP digital signature
URL: 

[sqlite] Destroy all evidence of a database

[Michael Stephenson]

Simon, if the data in the database is sensitive, could you encrypt the database 
(ala something like https://www.zetetic.net/sqlcipher/)?  

That way if the file is left around for some reason, it's much less of a 
concern.  Your app could generate a new (random) key each time it creates a new 
database.  When the program exits, the key is not saved anywhere so getting at 
the data means breaking the encryption if the file is left hanging around for 
some reason.

If sqlcipher itself is not that palatable or won't work on your mystery OS, 
it's pretty easy to write your own pager-based encryption based on the hooks 
that are provided when SQLITE_HAS_CODEC is defined. 

I did something like this.  I got started by diffing the sqlite source with the 
sqlcipher source which showed me what they had done.  I wasn't pleased that 
sqlcipher linked in the full OpenSSL library due mostly to the size (around 
500K packed) and complexity, neither of which I needed. 

It was pretty easy to just create my own encryption based on the same general 
approach as sqlcipher and used OpenSSL's AES implementation directly for the 
encryption and decryption.  Using the optimized ASM version of the OpenSSL AES 
code, all of this came in at around 10K packed (including some small libs for 
key derivation, HMAC, etc.) and the performance is almost identical to plain 
vanilla sqlite.  

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of John McKown
Sent: Wednesday, April 22, 2015 11:12 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Destroy all evidence of a database

On Wed, Apr 22, 2015 at 9:34 AM, Simon Slavin  wrote:

>
> On 22 Apr 2015, at 3:23pm, John McKown 
> wrote:
>
> > If it is
> > a POSIX compliant, perhaps what you could do is create a "temporary"
> > (mktemp) file of "appropriate" size.
>
> I had never considered that idea.  Thank you very much.  Unfortunately 
> it won't work in this situation because the people in control of the 
> system would either say "No virtual file systems" or leap at the idea 
> and insist that everyone uses virtual encrypted file systems for all 
> data files at all times.  I'm not sure which would be worse.
>
>
?Oh, my condolences. I've had that type of management too. If a teaspoon of 
medicine is good, then a tablespoon is better, but let's just take the entire 
bottle and be done with it.?


--
If you sent twitter messages while exploring, are you on a textpedition?

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


---
This email has been checked for viruses by Avast antivirus software.
http://www.avast.com

[sqlite] Destroy all evidence of a database

[Scott Hess]

The only way SQLite can get to the disk is using the vfs, so if the
vfs encrypts things, all of the files (main db, temp db, journal,
everything) will be encrypted.

I think in your case you can probably even get away without the more
elaborate encrypted systems, because it sounds like you don't want
things to be persistent across app restarts.  So you can just use a
random key at startup, and implement xRead and xWrite in the obvious
way without needing additional SQLite-level changes.  You can
initialize the random key before telling SQLite about the VFS.  You
can either not use additional per-page data, or if you do, you can
ignore the page atomicity issues because you don't want the data to be
persistent.

Another option might be to implement a proxy VFS to wrap the default
VFS, and whitelist only the files you have written code to handle.  If
SQLite wants to create db-random-made-up-file, it will fail because it
isn't on the list.

WRT your concerns about having it all be in-memory, short of that you
could use PRAGMA journal_mode = MEMORY (-journal file in memory) and
PRAGMA temp_store = MEMORY (temp tables and the like in memory).  IMHO
WAL journaling probably doesn't make sense, here, but if it does, I
think you need the -wal file on disk, but you can set PRAGMA
locking_mode = EXCLUSIVE to have the WAL's index kept in memory
without an anonymous shm file.

-scott


On Wed, Apr 22, 2015 at 8:56 AM, Simon Slavin  wrote:
> On 22 Apr 2015, at 4:46pm, Michael Stephenson  
> wrote:
>> Simon, if the data in the database is sensitive, could you encrypt the 
>> database (ala something like https://www.zetetic.net/sqlcipher/)?
>
> Unfortunately, this doesn't help.  I'm not concerned with the database file 
> itself.  I know exactly what that's called, and I can check it has been 
> correctly deleted.  I'm concerned with the data in several external files 
> that SQLite creates and deletes as it does its work.  Some of those would 
> contain unencrypted data.  And some of them have unpredictable names, or, at 
> least since the filenames are not documented they may change in future 
> versions of SQLite.
>
> You have made me realise, however, that a nice attack against encrypted 
> SQLite databases might be to crash a SQLite application while it's processing 
> and examine any journal files, shared memory file and temporary index files.  
> It might be interesting to review the various encryption systems widely 
> available for SQLite and figure out which of them would be vulnerable to such 
> an attack.
>
> By the way, if you want good (paid, not free) SQLite encryption you want to 
> check out
>
> 
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Destroy all evidence of a database

[Jim Callahan]

Can you turn off logging and overwrite the database with unencrypted zeros
or nulls;
just before deleting it?

Encrypting the overwrite character(s) would give the encryption attacker a
cleartext -- a bad move right out of the "Imitation Game".

Jim

On Wed, Apr 22, 2015 at 10:34 AM, Simon Slavin  wrote:

>
> On 22 Apr 2015, at 3:23pm, John McKown 
> wrote:
>
> > If it is
> > a POSIX compliant, perhaps what you could do is create a "temporary"
> > (mktemp) file of "appropriate" size.
>
> I had never considered that idea.  Thank you very much.  Unfortunately it
> won't work in this situation because the people in control of the system
> would either say "No virtual file systems" or leap at the idea and insist
> that everyone uses virtual encrypted file systems for all data files at all
> times.  I'm not sure which would be worse.
>
>
> On 22 Apr 2015, at 3:14pm, Richard Hipp  wrote:
>
> > Can you add the SQLITE_OPEN_MEMORY option to the sqlite3_open() call,
> > forcing SQLite to keep all content exclusively in memory and never
> > writing to disk?
>
> Sorry, the data can potentially get too big to keep it in memory (even
> virtual memory).  But a memory database would be a great solution if I
> could rely on it being small.  Thank for that too.
>
> I'm guessing that since you didn't point out any persistent SQLite
> temporary file that I'd missed there are no obvious problems with the
> procedure I included in my post.  That's good enough for the SQLite-related
> part of this problem.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Destroy all evidence of a database

[Richard Hipp]

On 4/22/15, Simon Slavin  wrote:
> Dear folks,
>
> I have a setup where an app creates a single-user SQLite database by opening
> a connection, uses it for a number of complicated things, closes the
> connection, then deletes the database before quitting.  The data which goes
> into this database is highly sensitive and it's very important that my app
> deletes all files which might contain this data (journal, temp index, etc.)
> before my application quits.

Can you add the SQLITE_OPEN_MEMORY option to the sqlite3_open() call,
forcing SQLite to keep all content exclusively in memory and never
writing to disk?


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Destroy all evidence of a database

[John McKown]

On Wed, Apr 22, 2015 at 9:34 AM, Simon Slavin  wrote:

>
> On 22 Apr 2015, at 3:23pm, John McKown 
> wrote:
>
> > If it is
> > a POSIX compliant, perhaps what you could do is create a "temporary"
> > (mktemp) file of "appropriate" size.
>
> I had never considered that idea.  Thank you very much.  Unfortunately it
> won't work in this situation because the people in control of the system
> would either say "No virtual file systems" or leap at the idea and insist
> that everyone uses virtual encrypted file systems for all data files at all
> times.  I'm not sure which would be worse.
>
>
?Oh, my condolences. I've had that type of management too. If a teaspoon of
medicine is good, then a tablespoon is better, but let's just take the
entire bottle and be done with it.?


-- 
If you sent twitter messages while exploring, are you on a textpedition?

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Destroy all evidence of a database

[John McKown]

On Wed, Apr 22, 2015 at 9:23 AM, John McKown 
wrote:

> On Wed, Apr 22, 2015 at 8:14 AM, Simon Slavin 
> wrote:
>
>>
>> On 22 Apr 2015, at 2:07pm, Paul Sanderson 
>> wrote:
>>
>> > You haven't said what operating system you are using
>>
>> Sorry, but I can't.  However, the OS is strongly oriented towards
>> security paranoia.  As long as the proper OS calls are used to delete files
>> and release memory, you can assume that they are unrecoverable, with all
>> the stuff a forensic expert would hate.  The same is true of any caching
>> done at OS level or below (e.g. device drivers, storage which automatically
>> manages sector usage, etc.).
>>
>> > This might be a level of access you are not concerned with - I guess
>> > that all depends on how sensitive sensitive is :)
>>
>> You got it.  But the platform (OS and hardware) is specially designed for
>> this.  As you suspected I'm concerned only at the level of filespace which
>> is still in use.
>>
>> Simon.
>>
>
> ?Well, it is difficult, if not completely impossible to say without
> knowing the OS involved. But since you indicated "strongly oriented towards
> security paranoia", I am certain that it cannot be from Microsoft. If it is
> a POSIX compliant, perhaps what you could do is create a "temporary"
> (mktemp) file of "appropriate" size. Now create an encrypted filesystem
> into that file. Mount this filesystem "somewhere". Make it the current
> directory. Create all your databases
>
?inside it.?


?OOPS my example is not encrypted. But there are things such as LUKS to do
that as well, if necessary. After re-reading your post, I don't think
encryption is needed on your OS.



>
> # Example in Linux
> cd /somewhere
> tfile=$(mktemp -p .) #create temporary file
> dd if=/dev/zero of=${tfile} bs=1024 count=1048576 # 1 gig
> mkdir tempfs
> sudo mount ${tfile} tempfs #mount the temporary file system
> rm ${tfile} #remove temp file while leaving it mounted
> cd tempfs #make tempfs the working directory
> ... do sqlite3 program(s) here
> cd ..
> sudo umount tempfs #unmount, which will remove inode as well
>
>
> ?Of course, I don't know if your OS can do the above because you can't say
> anything about it. Also, I showed this as a shell script, but you could do
> the equivalent in your application program before initializing the SQLite
> environment. If you want/need to, you could do the unlink() (rm command)
> _after_ terminating the the SQLite environment, unmounting the filesystem,
> and overwrite the file contents before doing the unlink() to delete it.?
>
> ?In any case, even if you can't do exactly the above, perhaps it will
> spark some other idea for you.
>
> --
> If you sent twitter messages while exploring, are you on a textpedition?
>
> He's about as useful as a wax frying pan.
>
> 10 to the 12th power microphones = 1 Megaphone
>
> Maranatha! <><
> John McKown
>



-- 
If you sent twitter messages while exploring, are you on a textpedition?

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Destroy all evidence of a database

[John McKown]

On Wed, Apr 22, 2015 at 8:14 AM, Simon Slavin  wrote:

>
> On 22 Apr 2015, at 2:07pm, Paul Sanderson 
> wrote:
>
> > You haven't said what operating system you are using
>
> Sorry, but I can't.  However, the OS is strongly oriented towards security
> paranoia.  As long as the proper OS calls are used to delete files and
> release memory, you can assume that they are unrecoverable, with all the
> stuff a forensic expert would hate.  The same is true of any caching done
> at OS level or below (e.g. device drivers, storage which automatically
> manages sector usage, etc.).
>
> > This might be a level of access you are not concerned with - I guess
> > that all depends on how sensitive sensitive is :)
>
> You got it.  But the platform (OS and hardware) is specially designed for
> this.  As you suspected I'm concerned only at the level of filespace which
> is still in use.
>
> Simon.
>

?Well, it is difficult, if not completely impossible to say without knowing
the OS involved. But since you indicated "strongly oriented towards
security paranoia", I am certain that it cannot be from Microsoft. If it is
a POSIX compliant, perhaps what you could do is create a "temporary"
(mktemp) file of "appropriate" size. Now create an encrypted filesystem
into that file. Mount this filesystem "somewhere". Make it the current
directory. Create all your databases inside it.?

# Example in Linux
cd /somewhere
tfile=$(mktemp -p .) #create temporary file
dd if=/dev/zero of=${tfile} bs=1024 count=1048576 # 1 gig
mkdir tempfs
sudo mount ${tfile} tempfs #mount the temporary file system
rm ${tfile} #remove temp file while leaving it mounted
cd tempfs #make tempfs the working directory
... do sqlite3 program(s) here
cd ..
sudo umount tempfs #unmount, which will remove inode as well


?Of course, I don't know if your OS can do the above because you can't say
anything about it. Also, I showed this as a shell script, but you could do
the equivalent in your application program before initializing the SQLite
environment. If you want/need to, you could do the unlink() (rm command)
_after_ terminating the the SQLite environment, unmounting the filesystem,
and overwrite the file contents before doing the unlink() to delete it.?

?In any case, even if you can't do exactly the above, perhaps it will spark
some other idea for you.

-- 
If you sent twitter messages while exploring, are you on a textpedition?

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown