[sqlite] How do non-SQLite DBMS communicate?

[Drago, William @ CSG - NARDA-MITEQ]

All,

I'm not sure how to ask this question.

When using SQLite the application program accesses the SQLite DBMS via its .dll 
file. When using something like Oracle Express (a local DBMS) the application 
program is communicating with Oracle Express via some sort of network protocol 
even though there's no network involved. What is that called?

My reason for asking is, I'm writing a short white paper describing my use of 
SQLite (to encourage other engineers where I work to use it too) and I'm trying 
to explain the differences between SQLite and other local database systems. So, 
SQLite databases are accessed via .dll where as other local databases run a 
server that is accessed via ???

Can anyone help me fill in those question marks?
Thanks,

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Error while creating view through command line

[Simon Slavin]

On 22 Apr 2015, at 4:49pm, Manoj Kumar Pasumarthi  
wrote:

> attach 'SP_R3.s3db' as sprdb;
> 
> BEGIN TRANSACTION;
> 
> 
> CREATE view sprdb.[view1] as select model from sprdb.[windturbine];
> 
> COMMIT;
> 
> .exit
> 
> After executing this script, DB is getting corrupted.

Please run

PRAGMA integrity_check;

on 'SP_R3.s3db' before you create the view to check that it is not already 
corrupt.

Do the commands corrupt the script if you type them into the shell tool 
manually instead of running them as a script ?

If so, please try this instead of your text:

ATTACH 'SP_R3.s3db' AS sprdb;
CREATE VIEW sprdb.view1 AS SELECT model FROM sprdb.windturbine;

and tell us whether that works.

Simon.

[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] json_* functions in sqlite

[Eric Rubin-Smith]

On Wed, Apr 22, 2015 at 2:17 PM, Eric Rubin-Smith  wrote:

>
> On Tue, Apr 21, 2015 at 9:09 PM, Ashish Sharma 
> wrote:
>
>> Many times I store JSON data in sqlite. It will be useful if sqlite came
>> with functions which understand JSON. Presto has a nice set
>> https://prestodb.io/docs/current/functions/json.html
>>
>
> I wrote a little tool that more or less allows the user to ingest JSON
> into an SQLite database, for use in tiny platforms (think a VM running
> inside a low-power home router).  I was considering open-sourcing it.
>
> Below is the man page for the tool.  If there is enough interest, I'll do
> the requisite work to remove dependencies on other bits of my
> infrastructure and publish it somewhere.  Let me know.
>

Apologies for self-replying.  The formatting of my man page copy-paste
looks pretty bad in a lot of places.  Here's a PDF-ized version for easier
reading: https://www.aterlo.com/wp-content/uploads/2015/04/json2sqlite.pdf

Eric

[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] Regression in 3.8.9.0? Same row and rowid returned 2-3 times in SELECT with two ORDER BY keys

[Eric Boesch]

(Apologies if this gets posted twice.)

The following ORDER BY query returns between 0 and 2 duplicates of each row,
for 1 to 3 copies total, in 3.8.9 for Linux (CentOS). Otherwise, the
returned rows are complete and correctly ordered. This database worked fine
under 3.8.6. I can test other OSes or versions between 3.8.6 and 3.8.9 if
requested.

Table ICSD has 50 columns. I can probably produce a copy if requested. The
BIN_DATA column holds strings 150 characters long. EL_COUNT and COLL_CODE
contain integers.

If I select a column other than BIN_DATA, or if I limit the ORDER BY clause
to a single term, then the query works.

The query fails the same way when 3.8.9 is accessed several different ways:
sqlite-amalgamation-3080900.zip, sqlite-autoconf-3080900.tar.gz, and also
when the Sqlite library is accessed via the Ruby Sqlite gem.

-- works, returns 156679
SELECT COUNT(*) FROM ICSD;

-- fails, returning 322747 rows
SELECT BIN_DATA FROM ICSD
ORDER BY EL_COUNT, COLL_CODE;

-- also fails. Including rowid to show that it really is
-- the exact same row that gets returned twice.
SELECT ROWID, BIN_DATA FROM ICSD
ORDER BY EL_COUNT, COLL_CODE;

-- works, returns 156679 rows
SELECT ROWID, SGR_NUM FROM ICSD
ORDER BY EL_COUNT, COLL_CODE;

-- works, returns 156679 rows
SELECT BIN_DATA FROM ICSD
ORDER BY EL_COUNT;

[sqlite] json_* functions in sqlite

[Eduardo Morras]

On Tue, 21 Apr 2015 18:09:33 -0700
Ashish Sharma  wrote:

> Hi
> 
> Many times I store JSON data in sqlite. It will be useful if sqlite
> came with functions which understand JSON. Presto has a nice set
> https://prestodb.io/docs/current/functions/json.html
> 
> I have two questions
> 
> 1. Will sqlite overlords consider adding this to sqlite core?
> 2. If so, what steps are needed for this?

Check unql.sqlite.org. It's abandonware AFAIK, but you can use part of the 
code. Stephen Beal colister has cson 
(http://fossil.wanderinghorse.net/wikis/cson/?page=cson), you can use it too.

Also, you can make a module/functions to work with BLOBs and TEXTs. Check how 
sqlar works (http://www.sqlite.org/sqlar/doc/trunk/README.md)


> Thanks
> Ashish
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


---   ---
Eduardo Morras 

[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] Error while creating view through command line

[Manoj Kumar Pasumarthi]

Hi,

I am trying to create a new view through command line script (sqlite3) as 
follow:


attach 'SP_R3.s3db' as sprdb;

BEGIN TRANSACTION;


CREATE view sprdb.[view1] as select model from sprdb.[windturbine];

COMMIT;

.exit

After executing this script, DB is getting corrupted.

Can anyone please tell me how to create a new view through scripts. Sample view 
is fine.

Please tell me whether it is possible or not.


Regards,
Manoj



::DISCLAIMER::


The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
E-mail transmission is not guaranteed to be secure or error-free as information 
could be intercepted, corrupted,
lost, destroyed, arrive late or incomplete, or may contain viruses in 
transmission. The e mail and its contents
(with or without referred errors) shall therefore not attach any liability on 
the originator or HCL or its affiliates.
Views or opinions, if any, presented in this email are solely those of the 
author and may not necessarily reflect the
views or opinions of HCL or its affiliates. Any form of reproduction, 
dissemination, copying, disclosure, modification,
distribution and / or publication of this message without the prior written 
consent of authorized representative of
HCL is strictly prohibited. If you have received this email in error please 
delete it and notify the sender immediately.
Before opening any email and/or attachments, please check them for viruses and 
other defects.

[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] json_* functions in sqlite

[Eric Rubin-Smith]

On Tue, Apr 21, 2015 at 9:09 PM, Ashish Sharma 
wrote:

> Hi
>
> Many times I store JSON data in sqlite. It will be useful if sqlite came
> with functions which understand JSON. Presto has a nice set
> https://prestodb.io/docs/current/functions/json.html
>

I wrote a little tool that more or less allows the user to ingest JSON into
an SQLite database, for use in tiny platforms (think a VM running inside a
low-power home router).  I was considering open-sourcing it.

Below is the man page for the tool.  If there is enough interest, I'll do
the requisite work to remove dependencies on other bits of my
infrastructure and publish it somewhere.  Let me know.

Eric

json2sqlite(1)   Aterlo Networks, Inc.
json2sqlite(1)

NAME
   json2sqlite - convert JSON to SQLite database rows

SYNOPSIS
   json2sqlite [OPTION]...

DESCRIPTION
   Read  arbitrary  JSON  from  a  file  (or standard input) and emit
some
   SQLite rows.  The form the rows take is dictated by the options.

   The entire run of the program is wrapped in a single SQLite
transaction
   that is only committed if we do not encounter any critical errors.

   This  program's  original  intent  was  to  be  run on small,
low-power
   devices.  It can be provided a strict memory cap with the -M flag.

   Input selection
   -i, --input-file=FILE
  Read JSON from FILE.  If FILE is '-', then  read  from
standard
  input.  If no value is passed, then '-' is assumed.

  If  you  want to read from a file whose literal name is '-',
you
  can qualify the path to the file, e.g. './-'.

   -P, --input-path=JSON_PATH
  Specify the path of the object you wish to import.

  The root-level JSON element has the  path  '.'.   If  the
root-
  level element is an object, then JSON_PATH may contain a
hierar-
  chy of key names at which the input array  can  be  found.
For
  example,  if  we have JSON {"a": [  ]} then the array
con-
  taining  can be imported by saying '--input-path .a'.
The
  leading  dot  may  be omitted.  (This leading-dot syntax is
just
  provided to make addressing the root-level object look a
little
  nicer on the command-line).

  If  left  unspecified,  then  '.'  (i.e.  the whole document)
is
  assumed.

   -T, --input-type=[array|object]
  If 'array' is given, then we import the JSON array that  can
be
  found at the JSON_PATH specified by --input-path.

  When importing an array, it is assumed that each of the
elements
  of the array is itself a JSON object.  These objects may
either
  be  a  simple  name->scalar  mapping, or they may have some
more
  complex nested form.  The objects are usually either imported
as
  one row per object, or one row per name/value pair, depending
on
  the other arguments to this program.

  Otherwise, if 'object' is  given,  then  import  a  single
JSON
  object  whose  path  is the JSON_PATH specified by
--input-path.
  The object is imported just as if it were the unique element
in
  a  containing  array and the array had been specified for
import
  via --input-type=array.

  If left unspecified, then 'array' is assumed.

   Output Selection
   -o, --output-database=DATABASE_FILENAME
  Emit rows to the database file DATABASE_FILENAME.  This
argument
  is mandatory.

   -t, --output-table=TABLE_NAME
  Emit rows to the given TABLE_NAME.  This argument is
mandatory.

   Initialization
   -s, --schema-file=SCHEMA_FILE
  Run  the  SQL  statements  in  SCHEMA_FILE before doing
anything
  else.

   -D, --delete-first
  Run 'DELETE FROM ;' before ingesting  any
records
  (but still within the process's global transaction, so that
sub-
  sequent failures will not lead to data loss).

   Transforming input to output
   Note that the input selection mechanism you chose above  with
--input-
   type  and  --input-path  has given us a list of objects (where the
list
   has length 0, 1, or more).  This is called the 'initial result set'
in
   the  below.   The following options instruct json2sqlite how to
convert
   each of those objects to some list of table rows.  There are two
over-
   all  structures  that  each  object  can have (nested or flat), and
two
   overall approaches for  converting  those  structures  to  SQLite
rows
   (either one row per name-value pair or one row per object).

   Input structure specification

   You  must  indicate  to json2sqlite the structure of the objects in
the
   initial result set.  If you don't specify one, then --flat is
assumed.

   

[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] json_* functions in sqlite

[Stephen Chrzanowski]

I'm no where near the level of an overlord, except maybe to the wifes dog.

I'm in a debate mood, so why not?  I'm open to the firing squad today. :]

IMHO, there are four (I initially started with two) problems with this
request in making it part of the core dealings Dr Hipp provides us all.  I
say this without knowing what the OPs purposes are to have SQLite start
understanding what JSON is and how it is handled.

Information Exchange Technologies (IET) shouldn't be part of a database
language.  STORED in a Database, sure, of course, no problem.  Technologies
like XML, JSON, HTML, BMP, JPG, MP3, TXT, DOC, XLS, and anything else that
contains information that is presented AS A WHOLE to a client/recipient is
part of the IET group.  SQLite isn't part of that group.  SQLite is a
*organizational
*and (hopefully) *organized *storage medium.  You put data in, it stores
that data.  All of it.  In one container.  It doesn't matter what that data
is, it just stores it and knows how to get it when needed.  With IETs, all
data is exchanged, not just select parts of it.  With a DBMS, changing one
or two pages in in a terrabyte sized data doesn't cause a terrabyte worth
of data to be rewritten when you change a 1 to a 0 or an Z to an F, unlike
any IET.  I'll cede to that you can open up a hex editor and single
byte/char write to change that 1 to a 0, but, change that 1 to a the
content of this email thread in an XML file without writing out the whole
file again.

DBMS shouldn't ever deal with an outside technology other than a Structured
Query Language which boils down to a table containing rows and fields.  It
shouldn't ever need to think about what is in a 'cell' in a table and how
to handle it.  The outside world says "I want this information, gimme" and
the DBMS should get it.  The DBMS should only ever play within its own
sandbox, and not allow the other kids to play with it.  JSON specifically
is for client use, configuration use, or rarely changing data in kilobyte
chunks of information at most.  If you're going to start throwing the
overhead of interpreting what JSON is within the DBMS, you might as well
just use the DBMS language for better storage and retrieval of information,
then have your n-tier application handle the "manglement" of the JSON for
whatever you need.

Since JSON is setup as a key:value standard (Value being number, string, or
array of sorts), you could stretch this into the thought that this is a
valid structure to a database.  After all, you have a field:value
relationship with SQL.  However, storing a database within a database seems
to be quite a bit redundant to me.  XML and JSON should rendered upon
output from a database query, and any JSON/XML that is to be put into a
database should be dissecting the data outside the database engine with
appropriate tables.  The exception to this might be for static
configuration files based on a users preference, system configuration,
etc.  But changing data?  I'm seriously iffy.  It'll eventually get to the
point where you'll get into 'database'ception and that is a whole other
puddle of mud.  Theory would be to make a database to take the stored
database to make the change then re-store the database in a cell of a
database.

Lastly, part of SQLites title says it all.  Lite.  If every technology were
added to handle information exchange, might as well start calling it
SQLarge.  SQL-Bloat or SQL-Fat just don't have the same ring to it, eh?  If
JSON gets in, who's to say XML doesn't go in?  Or HTML as a query?  Or if
someone figures out how to convert a PNG to a database structure, why not
throw that in?  My point here is that IET changes all the time, and it
isn't the responsibility of a database engine to keep up which has its own
kids to feed.

Now, with those four points, that doesn't exclude that the possibility
exists to add such functionality.  If you use the amalgamation, and you
know C, you can always add the additional functionality on your own.  IIRC,
there are methods to include calls to external modules/DLLs/whatever that
you'll have to write on your own, so you may not have to be entirely
reliant on C.  I've not checked, but there may already be modules that
'plug in' to the SQLite DLL that'll do exactly that JSON interpretation.

Myself, if you've not figured out, I feel that a database should store raw
information.  BMP grade level of storage.  Raw, unaltered, factual, bottom
line information.  Anything that could be considered an interpreted piece
of information, such understanding what a PNG is versus a BMP shouldn't be
part of the DBMS.  It doesn't matter how convinient it may be to have the
DBMS understand the difference, the purpose of a DBMS isn't to know, but
just acknowledge that something is there (Or not there in the case of NULL)

On Tue, Apr 21, 2015 at 9:09 PM, Ashish Sharma 
wrote:

> Hi
>
> Many times I store JSON data in sqlite. It will be useful if sqlite came
> with functions which understand JSON. Presto has a 

[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] Index causing very slow queries

[Hamish Symington]

Hello,

> On 10 Apr 2015, at 15:38, Hamish Symington  
> wrote:
> 
>>> A follow up to this. If I run ANALYZE on the ?fast? version of the database
>>> - ie *after* I?ve recreated the index - performance drops back to the
>>> original slow speed.
>> 
>> Please run ".fullschema" using the sqlite3.exe command-line shell on
>> your original database and send us the output.

I've trimmed down the tables, triggers and indexes to a much smaller database; 
the problem still persists. 

The result of .fullschema is as follows:

CREATE TABLE Txn (Txn_ID integer NOT NULL PRIMARY KEY, Txn_UUID varchar NOT 
NULL DEFAULT '', Txn_Modified integer NOT NULL DEFAULT 0, Txn_Created timestamp 
NOT NULL DEFAULT '', Txn_Type integer NOT NULL DEFAULT 0, Txn_Type_m integer 
NOT NULL DEFAULT 0, Txn_ContactUUID varchar NOT NULL DEFAULT '', 
Txn_ContactUUID_m integer NOT NULL DEFAULT 0, Txn_NameFull varchar NOT NULL 
DEFAULT '' COLLATE NOCASE, Txn_NameFull_m integer NOT NULL DEFAULT 0, 
Txn_Company varchar NOT NULL DEFAULT '' COLLATE NOCASE, Txn_Company_m integer 
NOT NULL DEFAULT 0, Txn_Address1 varchar NOT NULL DEFAULT '' COLLATE NOCASE, 
Txn_Address1_m integer NOT NULL DEFAULT 0, Txn_Address2 varchar NOT NULL 
DEFAULT '' COLLATE NOCASE, Txn_Address2_m integer NOT NULL DEFAULT 0, 
Txn_Address3 varchar NOT NULL DEFAULT '' COLLATE NOCASE, Txn_Address3_m integer 
NOT NULL DEFAULT 0, Txn_City varchar NOT NULL DEFAULT '' COLLATE NOCASE, 
Txn_City_m integer NOT NULL DEFAULT 0, Txn_County varchar NOT NULL DEFAULT '' 
COLLATE NOCASE, Txn_County_m integer NOT NULL DEFAULT 0, Txn_Postcode varchar 
NOT NULL DEFAULT '' COLLATE NOCASE, Txn_Postcode_m integer NOT NULL DEFAULT 0, 
Txn_Country varchar NOT NULL DEFAULT '' COLLATE NOCASE, Txn_Country_m integer 
NOT NULL DEFAULT 0, Txn_Date date NOT NULL DEFAULT '', Txn_Date_m integer NOT 
NULL DEFAULT 0, Txn_Amount currency NOT NULL DEFAULT 0, Txn_Amount_m integer 
NOT NULL DEFAULT 0, Txn_Method varchar NOT NULL DEFAULT '' COLLATE NOCASE, 
Txn_Method_m integer NOT NULL DEFAULT 0, Txn_Reference integer NOT NULL DEFAULT 
0, Txn_Reference_m integer NOT NULL DEFAULT 0, Txn_PaymentReference varchar NOT 
NULL DEFAULT '' COLLATE NOCASE, Txn_PaymentReference_m integer NOT NULL DEFAULT 
0, Txn_PaidInDate date NOT NULL DEFAULT '', Txn_PaidInDate_m integer NOT NULL 
DEFAULT 0, Txn_VATReportDate date NOT NULL DEFAULT '', Txn_VATReportDate_m 
integer NOT NULL DEFAULT 0, Txn_Notes varchar NOT NULL DEFAULT '' COLLATE 
NOCASE, Txn_Notes_m integer NOT NULL DEFAULT 0);
CREATE INDEX idx_Txn_UUID ON Txn (Txn_UUID);
CREATE INDEX idx_Txn_ContactUUID ON Txn (Txn_ContactUUID);
CREATE INDEX idx_Txn_Reference ON Txn (Txn_Reference);
CREATE TABLE Appointment (Appointment_ID integer NOT NULL PRIMARY KEY, 
Appointment_UUID varchar NOT NULL DEFAULT '', Appointment_Modified integer NOT 
NULL DEFAULT 0, Appointment_Created timestamp NOT NULL DEFAULT '', 
Appointment_AppointmentTypeUUID varchar NOT NULL DEFAULT '', 
Appointment_AppointmentTypeUUID_m integer NOT NULL DEFAULT 0, 
Appointment_StartDate date NOT NULL DEFAULT '', Appointment_StartDate_m integer 
NOT NULL DEFAULT 0, Appointment_StartTime timestamp NOT NULL DEFAULT '', 
Appointment_StartTime_m integer NOT NULL DEFAULT 0, Appointment_EndTime 
timestamp NOT NULL DEFAULT '', Appointment_EndTime_m integer NOT NULL DEFAULT 
0, Appointment_AddressUUID varchar NOT NULL DEFAULT '', 
Appointment_AddressUUID_m integer NOT NULL DEFAULT 0, Appointment_Description 
varchar NOT NULL DEFAULT '' COLLATE NOCASE, Appointment_Description_m integer 
NOT NULL DEFAULT 0, Appointment_ShootUUID varchar NOT NULL DEFAULT '', 
Appointment_ShootUUID_m integer NOT NULL DEFAULT 0, Appointment_SessionUUID 
varchar NOT NULL DEFAULT '', Appointment_SessionUUID_m integer NOT NULL DEFAULT 
0, Appointment_SessionStartOrEnd varchar NOT NULL DEFAULT '' COLLATE NOCASE, 
Appointment_SessionStartOrEnd_m integer NOT NULL DEFAULT 0, 
Appointment_ContactUUID varchar NOT NULL DEFAULT '', Appointment_ContactUUID_m 
integer NOT NULL DEFAULT 0, Appointment_SaleUUID varchar NOT NULL DEFAULT '', 
Appointment_SaleUUID_m integer NOT NULL DEFAULT 0, Appointment_QuoteUUID 
varchar NOT NULL DEFAULT '', Appointment_QuoteUUID_m integer NOT NULL DEFAULT 
0, Appointment_PurchaseUUID varchar NOT NULL DEFAULT '', 
Appointment_PurchaseUUID_m integer NOT NULL DEFAULT 0, Appointment_TxnUUID 
varchar NOT NULL DEFAULT '', Appointment_TxnUUID_m integer NOT NULL DEFAULT 0, 
Appointment_ResourceUUID varchar NOT NULL DEFAULT '', 
Appointment_ResourceUUID_m integer NOT NULL DEFAULT 0, Appointment_iCalUID 
varchar NOT NULL DEFAULT '' COLLATE NOCASE, Appointment_iCalUID_m integer NOT 
NULL DEFAULT 0, Appointment_iCalMD5 varchar NOT NULL DEFAULT '' COLLATE NOCASE, 
Appointment_iCalMD5_m integer NOT NULL DEFAULT 0, Appointment_SaleItemUUID 
varchar NOT NULL DEFAULT '', Appointment_SaleItemUUID_m integer NOT NULL 
DEFAULT 0, Appointment_GoogleCalendarUID varchar NOT NULL DEFAULT '' COLLATE 
NOCASE, Appointment_GoogleCalendarUID_m integer NOT 

[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: 
<http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20150422/e7bbc8ea/attachment.pgp>

[sqlite] Problems with pragma journal_mode

[Janke, Julian]

My xFileControl() method has actually returned a SQLITE_OK and not as stated in 
the documentation, a SQLITE_NOTFOUND. 
I fixed it and now all the PRAGMAs worked fine. 

I thank you very much


-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Janke, Julian
Sent: Mittwoch, 22. April 2015 13:13
To: ambrus at math.bme.hu; General Discussion of SQLite Database
Subject: Re: [sqlite] Problems with pragma journal_mode

Thank you for the hint. I'll check that.

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Zsb?n Ambrus
Sent: Mittwoch, 22. April 2015 10:58
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Problems with pragma journal_mode

On Wed, Apr 22, 2015 at 10:46 AM, Janke, Julian  
wrote:
> I have tested some of the other pragmas and none worked. After 
> discussing with you now I come to the conclusion that the problem is 
> caused more by my local setup. I

In that case, as you have a custom vfs, could you check if it's your vfs that 
is handling those pragmas?  The documentation at 
"http://sqlite.org/c3ref/c_fcntl_busyhandler.html#sqlitefcntlpragma;
describes that when you run a PRAGMA statement on a database, sqlite will call 
the xFileControl method of the vfs file handle (as given in a 
sqlite3_io_methods structure) with SQLITE_FCNTL_PRAGMA as the second parameter. 
 If that method returns SQLITE_OK, then sqlite will assume the vfs has handled 
the pragma, and will not handle it itself.  This could cause pragmas to fail 
silently.

-- Ambrus
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



Firma: Capgemini Deutschland GmbH
Aufsichtsratsvorsitzender: Antonio Schnieder ? Gesch?ftsf?hrer: Dr. Michael 
Schulte (Sprecher) ? Jost F?rster ? Dr. Peter Lempp ? Dr. Volkmar Varnhagen

Amtsgericht Berlin-Charlottenburg, HRB 98814 This message contains information 
that may be privileged or confidential and is the property of the Capgemini 
Group. It is intended only for the person to whom it is addressed. If you are 
not the intended recipient, you are not authorized to read, print, retain, 
copy, disseminate, distribute, or use this message or any part thereof. If you 
receive this message in error, please notify the sender immediately and delete 
all copies of this message.
___
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

[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] Problems with pragma journal_mode

[Janke, Julian]

Okay, thanks for your reply. I had been concerned that it is the only way to 
implement it by myself in the custom vfs.


-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
Sent: Mittwoch, 22. April 2015 12:59
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Problems with pragma journal_mode

On 4/22/15, Janke, Julian  wrote:
> The embedded system has various storage media, with different read /
> write speeds.
> A quick and dirty test shows that the outsourcing of the journal files
> on a faster medium may be a significant speed boost.
> Is there a possibility to write journal(temporarly) (and later wal
> files when I found the error) files to another directory as the main database?
> (temp_store_directory is marked as deprecated, and thus seems to be no
> option).
>

You can do this, and get away with it, in the highly controlled environment of 
a specific embedded application.  Just modify your custom VFS to automatically 
move journal files to a different directory.  Or even to give them completely 
different names.  As long as the same journal file always gets the same 
modified name whenever that name is used, everything should work.

Generic SQLite does *NOT* make this an option for a good reason.  When 
recovering from a power loss, it is critical that SQLite be able to check for 
the existence of and read the journal file, otherwise database corruption may 
result.  If journal files are on a different volume from the database files, 
then that volume might not be remounted upon reboot, or it might be mounted in 
a different place, thus preventing SQLite from locating the journal file.  We 
have no way to control this in the general case, except to ensure that journal 
files are always in exactly the same directory as the database.  But you can 
control this in your highly controlled and constrained environment, and hence 
you can get away with putting journal files on different volumes from the 
database.
--
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



Firma: Capgemini Deutschland GmbH
Aufsichtsratsvorsitzender: Antonio Schnieder ? Gesch?ftsf?hrer: Dr. Michael 
Schulte (Sprecher) ? Jost F?rster ? Dr. Peter Lempp ? Dr. Volkmar Varnhagen

Amtsgericht Berlin-Charlottenburg, HRB 98814
This message contains information that may be privileged or confidential and is 
the property of the Capgemini Group. It is intended only for the person to whom 
it is addressed. If you are not the intended recipient, you are not authorized 
to read, print, retain, copy, disseminate, distribute, or use this message or 
any part thereof. If you receive this message in error, please notify the 
sender immediately and delete all copies of this message.

[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] Problems with pragma journal_mode

[Janke, Julian]

Thank you for the hint. I'll check that.

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Zsb?n Ambrus
Sent: Mittwoch, 22. April 2015 10:58
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Problems with pragma journal_mode

On Wed, Apr 22, 2015 at 10:46 AM, Janke, Julian  
wrote:
> I have tested some of the other pragmas and none worked. After
> discussing with you now I come to the conclusion that the problem is
> caused more by my local setup. I

In that case, as you have a custom vfs, could you check if it's your vfs that 
is handling those pragmas?  The documentation at 
"http://sqlite.org/c3ref/c_fcntl_busyhandler.html#sqlitefcntlpragma;
describes that when you run a PRAGMA statement on a database, sqlite will call 
the xFileControl method of the vfs file handle (as given in a 
sqlite3_io_methods structure) with SQLITE_FCNTL_PRAGMA as the second parameter. 
 If that method returns SQLITE_OK, then sqlite will assume the vfs has handled 
the pragma, and will not handle it itself.  This could cause pragmas to fail 
silently.

-- Ambrus
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



Firma: Capgemini Deutschland GmbH
Aufsichtsratsvorsitzender: Antonio Schnieder ? Gesch?ftsf?hrer: Dr. Michael 
Schulte (Sprecher) ? Jost F?rster ? Dr. Peter Lempp ? Dr. Volkmar Varnhagen

Amtsgericht Berlin-Charlottenburg, HRB 98814
This message contains information that may be privileged or confidential and is 
the property of the Capgemini Group. It is intended only for the person to whom 
it is addressed. If you are not the intended recipient, you are not authorized 
to read, print, retain, copy, disseminate, distribute, or use this message or 
any part thereof. If you receive this message in error, please notify the 
sender immediately and delete all copies of this message.

[sqlite] Problems with pragma journal_mode

[Zsbán Ambrus]

On Wed, Apr 22, 2015 at 10:46 AM, Janke, Julian
 wrote:
> I have tested some of the other pragmas and none worked. After discussing 
> with you now I come to the conclusion that the problem is caused more by my 
> local setup. I

In that case, as you have a custom vfs, could you check if it's your
vfs that is handling those pragmas?  The documentation at
"http://sqlite.org/c3ref/c_fcntl_busyhandler.html#sqlitefcntlpragma;
describes that when you run a PRAGMA statement on a database, sqlite
will call the xFileControl method of the vfs file handle (as given in
a sqlite3_io_methods structure) with SQLITE_FCNTL_PRAGMA as the second
parameter.  If that method returns SQLITE_OK, then sqlite will assume
the vfs has handled the pragma, and will not handle it itself.  This
could cause pragmas to fail silently.

-- Ambrus

[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] building SQLite DLL with Visual C++

[Adam Devita]

Good day,


Why are you compiling a dll instead of using the pre-compiled windows
binaries at http://www.sqlite.org/download.html?

Are you adding some sort of extra wrapper?

Why are you not adding the amalgamated c source in your project (turn
off use pre-compiled headers for that file) ?

If you insist on creating your own dll, try the vs wizard to create a
dll project, then add code to it.

regards,
Adam DeVita


On Wed, Apr 22, 2015 at 9:51 AM, Igor Tandetnik  wrote:
> On 4/21/2015 11:01 AM, Jay Smith wrote:
>>
>> Before I sent the last message I had signed up to become a user.
>> My previous message was bounced.  WHY
>
>
> I, for one, have received both your original and this new message.
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[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] building SQLite DLL with Visual C++

[Igor Tandetnik]

On 4/21/2015 11:01 AM, Jay Smith wrote:
> Before I sent the last message I had signed up to become a user.
> My previous message was bounced.  WHY

I, for one, have received both your original and this new message.
-- 
Igor Tandetnik

[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

[sqlite] Problems with pragma journal_mode

[Janke, Julian]

I have tested some of the other pragmas and none worked. After discussing with 
you now I come to the conclusion that the problem is caused more by my local 
setup. I had hoped to be able to avoid it, but I will now try to debug the 
database and find the error. If I find the error, I will report you.

However, I have one more question:
The embedded system has various storage media, with different read / write 
speeds.
A quick and dirty test shows that the outsourcing of the journal files on a 
faster medium may be a significant speed boost.
Is there a possibility to write journal(temporarly) (and later wal files when I 
found the error) files to another directory as the main database? 
(temp_store_directory is marked as deprecated, and thus seems to be no option).

Thansk a lot or your help


-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
Sent: Montag, 20. April 2015 16:32
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Problems with pragma journal_mode

If the compile-time options you have shown are complete, then the PRAGMA 
command ought to be working.  But clearly PRAGMA is not working.

Can you do an experimental build that omits all of your -DSQLITE options and 
see if "PRAGMA journal_mode;" and "PRAGMA compile_options;" work then?

What about other PRAGMAs, like "PRAGMA database_list;" and "PRAGMA 
table_info=TABLE;".  See the complete list at 
(https://www.sqlite.org/pragma.html#toc).  Are any pragmas working on your 
system?

On 4/20/15, Janke, Julian  wrote:
> I'm pretty sure that they are completely. Later this discussion I 
> added
> -DSQLITE_DEFAULT_LOCKING_MODE=1
>
> There are only a few non-SQLite-specific options still available.
> Tell me if I'm wrong, but I do not think that are the cause of my problem.
>
> -mcpu=603e
> -fno-common
> -msdata=none
> -fno-jump-tables
> -fno-section-anchors
> -fno-merge-constants
> -fno-builtin
> -nostdlib
> -Werror-implicit-function-declaration
> -Wconversion
> -fstack-usage
> -std=c99
> -c
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of 
> Richard Hipp
> Sent: Montag, 20. April 2015 15:55
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Problems with pragma journal_mode
>
> On 4/20/15, Janke, Julian  wrote:
>>
>> PRAGMA compile_options; shows 0
>
> This makes me think that the list of compile-time options you showed 
> us earlier is incomplete:
>
> -DSQLITE_ENABLE_MEMSYS5
> -DSQLITE_ENABLE_8_3_NAMES=2
> -DSQLITE_THREADSAFE=0
> -DSQLITE_OS_OTHER=1
> -DSQLITE_ENABLE_API_ARMOR
> -DSQLITE_DEFAULT_MMAP_SIZE=0
> -DSQLITE_TEMP_STORE=0
> -DSQLITE_DEFAULT_CACHE_SIZE=500
>
> Please double-check to ensure that you do not have additional SQLITE 
> defines stuck in a configuration file someplace.
>
>>
>>
>> -Original Message-
>> From: sqlite-users-bounces at mailinglists.sqlite.org
>> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of 
>> Richard Hipp
>> Sent: Montag, 20. April 2015 12:18
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] Problems with pragma journal_mode
>>
>> On 4/20/15, Janke, Julian  wrote:
>>> 'EXPLAIN PRAGMA journal_mode=WAL' returns 2 rows
>>>
>>> 0: 0 Init 0 0 0
>>> 1: 1 Halt 0 0 0
>>>
>>
>> You should get this:
>>
>> addr  opcode p1p2p3p4 p5  comment
>>   -        -  --  -
>> 0 Init   0 0 000
>> 1 JournalMode0 1 500
>> 2 ResultRow  1 1 000
>> 3 Halt   0 0 000
>>
>> What does: "PRAGMA compile_options;" and "SELECT sqlite_source_id();"
>> show?
>>
>>
>>
>>> Is it the result of what you expected?
>>>
>>> -Original Message-
>>> From: sqlite-users-bounces at mailinglists.sqlite.org
>>> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of 
>>> Richard Hipp
>>> Sent: Freitag, 17. April 2015 16:59
>>> To: General Discussion of SQLite Database
>>> Subject: Re: [sqlite] Problems with pragma journal_mode
>>>
>>> On 4/17/15, Janke, Julian  wrote:
 Hello,
 Thanks for your reply.

 I changed my code again:

 1) rc = sqlite3_open(dbPath, ); --> SQLITE_OK
 2) rc = sqlite3_exec(db, "DROP TABLE IF EXISTS nosuchtable;", 
 testCallbackPrint, 0, );
 3) rc = sqlite3_prepare_v2(db, "PRAGMA journal_mode=WAL;", 24, 
 , NULL);
 --> SQLITE_OK
 4) rc = sqlite3_step(stmt); --> SQLITE_DONE
>>>
>>> I do not understand this.  "PRAGMA journal_mode" should always give 
>>> a return value, even when it fails.  sqlite3_step() should have 
>>> returned SQLITE_ROW.
>>>
>>> Please try instead, "EXPLAIN PRAGMA journal_mode=WAL".  Verify that 
>>> you get multiple rows of 

[sqlite] Problems with pragma journal_mode

[Richard Hipp]

On 4/22/15, Janke, Julian  wrote:
> The embedded system has various storage media, with different read / write
> speeds.
> A quick and dirty test shows that the outsourcing of the journal files on a
> faster medium may be a significant speed boost.
> Is there a possibility to write journal(temporarly) (and later wal files
> when I found the error) files to another directory as the main database?
> (temp_store_directory is marked as deprecated, and thus seems to be no
> option).
>

You can do this, and get away with it, in the highly controlled
environment of a specific embedded application.  Just modify your
custom VFS to automatically move journal files to a different
directory.  Or even to give them completely different names.  As long
as the same journal file always gets the same modified name whenever
that name is used, everything should work.

Generic SQLite does *NOT* make this an option for a good reason.  When
recovering from a power loss, it is critical that SQLite be able to
check for the existence of and read the journal file, otherwise
database corruption may result.  If journal files are on a different
volume from the database files, then that volume might not be
remounted upon reboot, or it might be mounted in a different place,
thus preventing SQLite from locating the journal file.  We have no way
to control this in the general case, except to ensure that journal
files are always in exactly the same directory as the database.  But
you can control this in your highly controlled and constrained
environment, and hence you can get away with putting journal files on
different volumes from the database.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Problems with pragma journal_mode

[Richard Hipp]

On 4/22/15, Zsb?n Ambrus  wrote:
>
> In that case, as you have a custom vfs, could you check if it's your
> vfs that is handling those pragmas?  The documentation at
> "http://sqlite.org/c3ref/c_fcntl_busyhandler.html#sqlitefcntlpragma;
> describes that when you run a PRAGMA statement on a database, sqlite
> will call the xFileControl method of the vfs file handle (as given in
> a sqlite3_io_methods structure) with SQLITE_FCNTL_PRAGMA as the second
> parameter.  If that method returns SQLITE_OK, then sqlite will assume
> the vfs has handled the pragma, and will not handle it itself.  This
> could cause pragmas to fail silently.

Ah - Nice insight, Ambrus.  This theory nicely covers the facts.
Thank you for thinking of this!
-- 
D. Richard Hipp
drh at sqlite.org