Re: [sqlite] SEE Temp Files

[Scott Perry]

> On Jan 29, 2019, at 10:12, Jens Alfke  wrote:
> 
>> On Jan 28, 2019, at 3:35 PM, Richard Hipp  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SEE Temp Files

[Jens Alfke]

> On Jan 28, 2019, at 3:35 PM, Richard Hipp  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SEE Temp Files

[Richard Hipp]

On 1/28/19, Jim Borden  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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" 
 
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  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?
>
> 
> 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.
> 
>
> Thanks,
> Jim Borden
>
>
>
> Privacy Policy
> Marketing
> Preferences
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



Privacy Policy
Marketing 
Preferences
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SEE Temp Files

[Richard Hipp]

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  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?
>
> 
> 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.
> 
>
> Thanks,
> Jim Borden
>
>
>
> Privacy Policy
> Marketing
> Preferences
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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?


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.


Thanks,
Jim Borden



Privacy Policy
Marketing 
Preferences
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users