SEE Temp Files

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

SEE Temp Files

Jim Borden
Having moved from SQLCipher to SEE some time ago, the old advice that SQLCipher gave has stuck with me (from https://www.zetetic.net/sqlcipher/design/ in the Database Encryption and Temporary Files section):  “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)”

The reasoning behind this is that certain temporary files are not encrypted when being written and thus are a violation of the security provided by encryption of the database.  I couldn’t find any equivalent warnings regarding SQLite Encryption Extension (or anything to assure me that this was *not* the case with SEE) so I thought I would ask here if the same advice applies or can we be assured that anything SQLite with SEE writes to the disk relating to the encrypted database will also be encrypted?

<superfluous background (not required reading)>
The reason why I ask this is because there is a debate going on about the appropriate setting of SQLITE_TEMP_STORE on mobile devices and whether or not it will end up using too much memory and getting an application terminated.  However, the above advice would pretty much force our hand on the version of our product that uses SEE.
</superfluous background>

Thanks,
Jim Borden



Privacy Policy<http://www.couchbase.com/privacy-policy>
Marketing Preferences<http://info.couchbase.com/unsubscribe-or-manage-preferences>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SEE Temp Files

Richard Hipp-3
Intermediate results may be written into a temp file, if they overflow
memory.  This could result in an information leak, yes.  On the other
hand, if you set SQLITE_TEMP_STORE=2 so that intermediate results are
held in memory, then intermediate results may be written to swap space
when the device gets under memory pressure.  So, I'm not sure it makes
that much difference.  I kind of suspect that a explicit TEMP file
would be safer, as it seems to me it will be harder to find forensic
traces of a TEMP file amid the clutter of a massive volume, and TEMP
files are likely to be overwritten quickly, which I'm not sure happens
with the swap file.  But maybe a security geek can correct me on this.
And maybe it depends a lot on what OS you are running, or what version
of that OS, or how you have the system configured.

So it is hard to know what the right thing to do is.

On 1/28/19, Jim Borden <[hidden email]> wrote:

> Having moved from SQLCipher to SEE some time ago, the old advice that
> SQLCipher gave has stuck with me (from
> https://www.zetetic.net/sqlcipher/design/ in the Database Encryption and
> Temporary Files section):  “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)”
>
> The reasoning behind this is that certain temporary files are not encrypted
> when being written and thus are a violation of the security provided by
> encryption of the database.  I couldn’t find any equivalent warnings
> regarding SQLite Encryption Extension (or anything to assure me that this
> was *not* the case with SEE) so I thought I would ask here if the same
> advice applies or can we be assured that anything SQLite with SEE writes to
> the disk relating to the encrypted database will also be encrypted?
>
> <superfluous background (not required reading)>
> The reason why I ask this is because there is a debate going on about the
> appropriate setting of SQLITE_TEMP_STORE on mobile devices and whether or
> not it will end up using too much memory and getting an application
> terminated.  However, the above advice would pretty much force our hand on
> the version of our product that uses SEE.
> </superfluous background>
>
> Thanks,
> Jim Borden
>
>
>
> Privacy Policy<http://www.couchbase.com/privacy-policy>
> Marketing
> Preferences<http://info.couchbase.com/unsubscribe-or-manage-preferences>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SEE Temp Files

Jim Borden
I see.  That complicates things a bit.  What happens to SQLite temp files when they are "done being used" (if such a concept exists).  Are they deleted or simply left there for the OS to clean up?

Jim Borden


On 2019/01/29 8:35, "sqlite-users on behalf of Richard Hipp" <[hidden email] on behalf of [hidden email]> wrote:

    Intermediate results may be written into a temp file, if they overflow
    memory.  This could result in an information leak, yes.  On the other
    hand, if you set SQLITE_TEMP_STORE=2 so that intermediate results are
    held in memory, then intermediate results may be written to swap space
    when the device gets under memory pressure.  So, I'm not sure it makes
    that much difference.  I kind of suspect that a explicit TEMP file
    would be safer, as it seems to me it will be harder to find forensic
    traces of a TEMP file amid the clutter of a massive volume, and TEMP
    files are likely to be overwritten quickly, which I'm not sure happens
    with the swap file.  But maybe a security geek can correct me on this.
    And maybe it depends a lot on what OS you are running, or what version
    of that OS, or how you have the system configured.

    So it is hard to know what the right thing to do is.

    On 1/28/19, Jim Borden <[hidden email]> wrote:
    > Having moved from SQLCipher to SEE some time ago, the old advice that
    > SQLCipher gave has stuck with me (from
    > https://www.zetetic.net/sqlcipher/design/ in the Database Encryption and
    > Temporary Files section):  “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)”
    >
    > The reasoning behind this is that certain temporary files are not encrypted
    > when being written and thus are a violation of the security provided by
    > encryption of the database.  I couldn’t find any equivalent warnings
    > regarding SQLite Encryption Extension (or anything to assure me that this
    > was *not* the case with SEE) so I thought I would ask here if the same
    > advice applies or can we be assured that anything SQLite with SEE writes to
    > the disk relating to the encrypted database will also be encrypted?
    >
    > <superfluous background (not required reading)>
    > The reason why I ask this is because there is a debate going on about the
    > appropriate setting of SQLITE_TEMP_STORE on mobile devices and whether or
    > not it will end up using too much memory and getting an application
    > terminated.  However, the above advice would pretty much force our hand on
    > the version of our product that uses SEE.
    > </superfluous background>
    >
    > Thanks,
    > Jim Borden
    >
    >
    >
    > Privacy Policy<http://www.couchbase.com/privacy-policy>
    > Marketing
    > Preferences<http://info.couchbase.com/unsubscribe-or-manage-preferences>
    > _______________________________________________
    > sqlite-users mailing list
    > [hidden email]
    > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
    >


    --
    D. Richard Hipp
    [hidden email]
    _______________________________________________
    sqlite-users mailing list
    [hidden email]
    http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



Privacy Policy<http://www.couchbase.com/privacy-policy>
Marketing Preferences<http://info.couchbase.com/unsubscribe-or-manage-preferences>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SEE Temp Files

Richard Hipp-3
On 1/28/19, Jim Borden <[hidden email]> wrote:
> I see.  That complicates things a bit.  What happens to SQLite temp files
> when they are "done being used" (if such a concept exists).  Are they
> deleted or simply left there for the OS to clean up?

SQLite calls unlink() immediately after open().  So the OS deallocates
the file automatically when it closes.  The O_EXCL, O_CREAT, and
O_NOFOLLOW flags are set on open(). The filename includes 64-bits of
randomness.

Windows is similar except the file is opened with the
FILE_FLAG_DELETE_ON_CLOSE flag because you cannot unlink() an open
file on Windows.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SEE Temp Files

Jens Alfke-2
In reply to this post by Richard Hipp-3


> On Jan 28, 2019, at 3:35 PM, Richard Hipp <[hidden email]> wrote:
>
> On the other
> hand, if you set SQLITE_TEMP_STORE=2 so that intermediate results are
> held in memory, then intermediate results may be written to swap space
> when the device gets under memory pressure.

Mobile OSs don’t swap. (iOS for certain; I don’t have confirmation of this for Android, but our local Android dev believes it’s so.)

On the plus side, this means no traces of heap memory in the swap file. On the downside, the OS is really aggressive about killing app processes that consume too much RAM, so developers learn to be careful about heap usage. If SQLite is using memory for temp storage, then it sounds like  a complex enough query will crash an app.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SEE Temp Files

Scott Perry

> On Jan 29, 2019, at 10:12, Jens Alfke <[hidden email]> wrote:
>
>> On Jan 28, 2019, at 3:35 PM, Richard Hipp <[hidden email]> wrote:
>>
>> On the other
>> hand, if you set SQLITE_TEMP_STORE=2 so that intermediate results are
>> held in memory, then intermediate results may be written to swap space
>> when the device gets under memory pressure.
>
> Mobile OSs don’t swap. (iOS for certain; I don’t have confirmation of this for Android, but our local Android dev believes it’s so.)

While iOS 12 does not swap memory in the traditional sense, there are conditions under which it may write an idle application's memory to disk.

Scott
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users