RE: [sqlite] Encryption and fixed header values (was Re: [sqlite] Why can i open a textfile?)

[Mrs. Brisby]

On Fri, 2005-08-26 at 12:53 -0400, Griggs, Donald wrote:
> Regarding Mrs. Brisby's ending comment:
>"A better defense: use a different key each time. Encrypt the session key
> separately."
> 
> I may way off in asking this, but:
>   Since we're talking about the encyption of data at rest, and aren't in a
> client-server situation, how could sqlite make session keys work?   What
> would constitute a session?

The lifetime of the session is the time between encrypting the data and
decrypting it. It doesn't have to be on line.

PGP (for example) encrypts the message using symmetric ciphers (like RC4
or Blowfish) using a completely random key. It then encrypts the key
using the public-key algorithms desired (RSA, DSA) and writes the
encrypted key.

This works because the receiver decrypts the encrypted random key and
uses it to decode the rest of the message. It has some fantastic
benefits:

1. assymetric ciphers are slow, this is certainly much faster
2. if any information is leaked about the "real key", it's very little.
Often very few bits of the real key are needed.
3. Brute-force attacks on the message will yield the session key, but
brute-force attacks on the encrypted session key won't yield enough
information to reconstruct the key.


This is similar to how SSL works as well, although the exchange is done
"on line" - which is probably why you're confused about it.

This is almost certainly what people are talking about when they refer
to session keys.


With some ciphers (like RC4), the session key can be obtained through
other means. Because RC4's encryption/decryption routine permutes the
keyspace, encrypting _anything_ causes permutations to occur.

But because those permutations are wildly predictable (at first), many
people discard the beginning of the RC4 keystream.

I disagree with this. I suggest taking some random junk and encrypt it.
Throw away the result. This permutes the keystream faster, and it means
that no plaintext is encrypted twice accidentally.

The random junk isn't secret. You can store it at the head of your
ciphertext. This "random junk" is often called an initialization vector-
and is a common way to reuse keys with more safety.

This "IV" and "key" combination are often considered "the session key".

Re: [sqlite] Encryption and fixed header values (was Re: [sqlite]Why can i open a textfile?)

[Mrs. Brisby]

On Fri, 2005-08-26 at 16:21 +0200, F.W.A. van Leeuwen wrote:
> > 
> > The usual defense against this attack is to mix some random information
> > into the beginning of the plaintext.
> > 
> > A better defense: use a different key each time. Encrypt the session key
> > separately.
> > 
> 
> And /or start encrypting after the fixed header.

Agreed. Thanks for pointing this out.

RE: [sqlite] Encryption and fixed header values (was Re: [sqlite] Why can i open a textfile?)

[Griggs, Donald]

Regarding Mrs. Brisby's ending comment:
   "A better defense: use a different key each time. Encrypt the session key
separately."

I may way off in asking this, but:
  Since we're talking about the encyption of data at rest, and aren't in a
client-server situation, how could sqlite make session keys work?   What
would constitute a session?


Donald Griggs

Opinions are not necessarily those of Misys Healthcare Systems nor its board
of directors.

Re: [sqlite] Encryption and fixed header values (was Re: [sqlite]Why can i open a textfile?)

[F.W.A. van Leeuwen]

> 
> The usual defense against this attack is to mix some random information
> into the beginning of the plaintext.
> 
> A better defense: use a different key each time. Encrypt the session key
> separately.
> 

And /or start encrypting after the fixed header.

Best regards,
Frank.

Re: [sqlite] Encryption and fixed header values (was Re: [sqlite] Why can i open a textfile?)

[Mrs. Brisby]

On Wed, 2005-08-24 at 22:55 -0400, D. Richard Hipp wrote:
>   Weaknesses in RC4 have been found where
> the first few numbers coming out of the PRNG leak information about the
> key.  If an attacker can guess the first few bytes of plaintext, and
> hence guess the first few numbers from the PRNG, and can do this many
> many times (millions of times) then the attacker can eventually
> reconstruct
> the key.

I noticed this. You understate how much it helps. The first few cycles
of RC4 are so bad that key recovery is easy for modern general purpose
computers.

> The usual defense against this attack (and the one used by SQLite)
> is to discard the first 1000 bytes or so of information coming out
> of the PRNG.  No key information leaks into later bytes of the
> PRNG stream (at least as far as we know) so this secures the cypher
> from attack.

It doesn't need to leak information about the key. A combination
known-plaintext and known-ciphertext attack works very well against RC4.

http://groups.google.com/group/sci.crypt/browse_frm/thread/2716ac20a3fc9971/64eba041932a98ae?lnk=st=1=en

Since the header is well known, convincing the program to encrypt the
database (by say, making a change to it) several times allows the user
to collect some known-plaintext and lots of ciphertext very quickly.

The usual defense against this attack is to mix some random information
into the beginning of the plaintext.

A better defense: use a different key each time. Encrypt the session key
separately.

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

Maybe we should explain the term 'wrapper'.
I'm using wrappers around the salite dll in my own code.
But a wrapper might be seen over here as additional library (dll or so) 
others can use.

A redistribute part, which i don't like.
(Like the VB 'wrapper' dll)


- Original Message - 
From: "Walter Meerschaert" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Thursday, August 25, 2005 3:43 AM
Subject: Re: [sqlite] Why can i open a textfile?



D. Richard Hipp wrote:

Most people who are using SQLite successfully have, I imagine, either 
written their own wrappers around the core API  (which

is not hard as I do provide you with a lot of helper routines
such as sqlite3_vmprintf and friends) or they are using an existing
wrapper written by someone else.
Indeed I wrote a thin wrapper when I started using SQLite 6 months ago. I 
now retract my request for more control over the open function, as one of 
the features of SQLite that I really liked was that it didn't have an 
extensive set of "features". I can and will implement the read/only and 
exclusive tests in my own open wrapper, and return or throw where 
appropriate.

Re: [sqlite] Encryption and fixed header values (was Re: [sqlite] Why can i open a textfile?)

[D. Richard Hipp]

On Wed, 2005-08-24 at 22:32 -0400, Mrs. Brisby wrote:
> > There are several attacks that can be used to derive the original key 
> > state, but they all require huge samples of data to analyze.
> 
> No they don't. That's the problem. They only need a large number of
> _uses_ of the key, not large amounts of data. Adding some random junk to
> the beginning of the plaintext would help some, but I don't think SQLite
> does this.
> 

RC4 is a pseudorandom number generator (PRNG) against which the
plaintext
is XORed to yield cyphertext.  Weaknesses in RC4 have been found where
the first few numbers coming out of the PRNG leak information about the
key.  If an attacker can guess the first few bytes of plaintext, and
hence guess the first few numbers from the PRNG, and can do this many
many times (millions of times) then the attacker can eventually
reconstruct
the key.

The usual defense against this attack (and the one used by SQLite)
is to discard the first 1000 bytes or so of information coming out
of the PRNG.  No key information leaks into later bytes of the
PRNG stream (at least as far as we know) so this secures the cypher
from attack.

-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Encryption and fixed header values (was Re: [sqlite] Why can i open a textfile?)

[Mrs. Brisby]

On Mon, 2005-08-22 at 16:39 -0500, Dennis Jenkins wrote:
> RC4 is basically an XOR against a huge one-time pad.

No it's not. The only thing like a one-time pad is a one-time pad.

What makes an OTP secure isn't the protocol, but where the bits come
from.

If they come from ANYWHERE but a truly random source then it is not
secure.

RC4/ARCFOUR isn't a random source of bits, so it isn't a one-time pad.


> That pad is creating using a "key scheduling algorithm".

No it's not. The key scheduling system moves S-boxes around. There are
exactly 256 S-boxes with RC4/ARCFOUR. If you really did perform an XOR
operation on those SBOXes you would have no security unless:
* your key was exactly 256 bytes
* your data stream was exactly 256 bytes

Nevertheless, it still wouldn't be much security because you probably
didn't chose a strong key to begin with.


> The key state for the random number generator is 256 bytes of data and two 
> 8-bit indicies.  

No it's not. The substitution array (S-boxes) is 256 bytes. It
essentially translates one byte into another byte, then performs a
permutation on the S-boxes. That permutation is fixed. The S-boxes are
moved around each step, thus increasing the run-length. Unfortunately,
its not enough- getting the initial key bytes of the stream makes it
much easier to reconstruct the rest of the swapping routine.

> There are several attacks that can be used to derive the original key 
> state, but they all require huge samples of data to analyze.

No they don't. That's the problem. They only need a large number of
_uses_ of the key, not large amounts of data. Adding some random junk to
the beginning of the plaintext would help some, but I don't think SQLite
does this.


> RC4 is not the strongest encryption available, but for most of us, it is good 
> enough.

... and it wouldn't matter anyway because the key exists in the software
that decrypts it. That's much easier to get to anyway.

> The best way to attack any system using sqlite w/ the crypto extension 
> is to hook the call to "sqlite3_key()" and just steal the rc4 key 
> directly.  Much easier than crunhcing numbers on a super computer.

At least on this, I agree.

Re: [sqlite] Why can i open a textfile?

[Walter Meerschaert]

D. Richard Hipp wrote:

Most people who are using SQLite successfully have, I imagine, 
either written their own wrappers around the core API  (which

is not hard as I do provide you with a lot of helper routines
such as sqlite3_vmprintf and friends) or they are using an existing
wrapper written by someone else. 
 

Indeed I wrote a thin wrapper when I started using SQLite 6 months ago. 
I now retract my request for more control over the open function, as one 
of the features of SQLite that I really liked was that it didn't have an 
extensive set of "features". I can and will implement the read/only and 
exclusive tests in my own open wrapper, and return or throw where 
appropriate.

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

Wow, that suprises me, i just stepped of the _gettable() call to get me the 
result using prepare and step.

So i finally could read the $NUL BLOB data.
I can't imagne what would be better since a forward only behaviour is the 
best there is.

Besides it's side-effects of not being able to navigate back.

There are people like me they never heard about TCL, i don't know what it is 
and also what schema's are.

It's an area i never visit since it's not familiar to me.
I can not imagne to do things like this not using your API.

I don't think i'm blind for other things but there siomply are programmers 
used to use plain functions.


To make sure you understand, through PowerBASIC i'm able to connect to your 
sqlite through api's.

The issue is that conversion from your api to a BASIC syntax is pretty hard.
Espec. if calls have pointers which are in fact only interesting for 
including the lib into c but is irrellevant to dll users.


Don't get me wrong, it's not to annoy you, just want to show my perspective 
and might even help to consider this for the future.
Like a major rewrite to standard calls and making use of not-so-c syntax 
(pointers/casting or whatever removed, just an example)


(Again: it's not handy to show *sqlite in a param but simply have a byval 
hstmt as long (but then in c syntax :) ) would be much better since it does 
not tell unecessary info that *sqlite is actually a pointer to a structure 
or similar, this might reach more end-users)


I usually keep in mind a 'dumb' VB should be able to access it.
If that works it usually works with anything.

Using wrappers is no option to me unless it's for dotnet (asp.net), i fell 
dumb if i would use a wrapper lib.
Of course the PowerBASIC code being spread for sqlite gives me a headstart 
to access the dll.

But the code is not my taste so i do it myself.

Is there any reason not to stick with prepare() and step()?
Works for me at this time.

:)



- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Tuesday, August 23, 2005 1:28 AM
Subject: Re: [sqlite] Why can i open a textfile?



On Tue, 2005-08-23 at 00:04 +0200, Edwin Knoppert wrote:
All i meant is that sqlite is created by a c programmer, like my 
colleague

he seems to forget there are different languages but he doesn't care.



Actually, I'm a TCL programmer.  I only resort to C code to write
new TCL extensions.  I personally never use the SQLite C API except
when I am working on internals of the TCL bindings.  I use SQLite
in my professional work a lot, but I always access it through the
TCL bindings.  Even when I am working on SQLite itself, I almost
always access it through the TCL bindings.  (All of the test
scripts are writtne in TCL.)

And this brings up an important point:  I'm increasingly aware of
the fact that the C API to SQLite really wants to be wrapped.  There
are a lot of loose ends that can cause complication and trouble if
you try to use the C API directly in your application.  Things like
the infamous SQLITE_SCHEMA returns.  And the notorious SQLITE_BUSY.
And then the recent conversation about how to tell if the file is
really a database or not.  These kinds of things are best dealt with
once in a wrapper and then forgotten.  You do not want to have to
be worrying about error return codes when you are trying to make
the next great klller app.  Programmers need to save their brain
cycles to focus on the really hard problems, not on handling lots
of pesky return codes.

Most people who are using SQLite successfully have, I imagine,
either written their own wrappers around the core API  (which
is not hard as I do provide you with a lot of helper routines
such as sqlite3_vmprintf and friends) or they are using an existing
wrapper written by someone else.

If you are not doing this - if you are trying to make low-level
SQLite API calls like sqlite3_prepare and sqlite3_step for each
bit of SQL you want to run, then let me suggest that you are using
the library incorrectly.  That is way too much work.  I expose
those calls because they provide you with a lot of control and
give wrapper writers the freedom to do lots of interesting things
with the database.  If you are working on an application that just
needs access to data, sqlite3_prepare and sqlite3_step are way
too low level.  Find a wrapper suitable for your needs and use it
instead.  Or write your own wrapper if an appropriate one can't
be found.  This will result in a program that is easier to write,
easier to maintain, has fewer bugs, and just works better.
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Why can i open a textfile?

[D. Richard Hipp]

On Tue, 2005-08-23 at 00:04 +0200, Edwin Knoppert wrote:
> All i meant is that sqlite is created by a c programmer, like my colleague 
> he seems to forget there are different languages but he doesn't care.
> 

Actually, I'm a TCL programmer.  I only resort to C code to write
new TCL extensions.  I personally never use the SQLite C API except
when I am working on internals of the TCL bindings.  I use SQLite
in my professional work a lot, but I always access it through the 
TCL bindings.  Even when I am working on SQLite itself, I almost
always access it through the TCL bindings.  (All of the test 
scripts are writtne in TCL.)

And this brings up an important point:  I'm increasingly aware of
the fact that the C API to SQLite really wants to be wrapped.  There
are a lot of loose ends that can cause complication and trouble if
you try to use the C API directly in your application.  Things like
the infamous SQLITE_SCHEMA returns.  And the notorious SQLITE_BUSY.
And then the recent conversation about how to tell if the file is
really a database or not.  These kinds of things are best dealt with
once in a wrapper and then forgotten.  You do not want to have to
be worrying about error return codes when you are trying to make
the next great klller app.  Programmers need to save their brain
cycles to focus on the really hard problems, not on handling lots
of pesky return codes.

Most people who are using SQLite successfully have, I imagine, 
either written their own wrappers around the core API  (which
is not hard as I do provide you with a lot of helper routines
such as sqlite3_vmprintf and friends) or they are using an existing
wrapper written by someone else. 

If you are not doing this - if you are trying to make low-level
SQLite API calls like sqlite3_prepare and sqlite3_step for each
bit of SQL you want to run, then let me suggest that you are using
the library incorrectly.  That is way too much work.  I expose
those calls because they provide you with a lot of control and
give wrapper writers the freedom to do lots of interesting things
with the database.  If you are working on an application that just
needs access to data, sqlite3_prepare and sqlite3_step are way
too low level.  Find a wrapper suitable for your needs and use it
instead.  Or write your own wrapper if an appropriate one can't 
be found.  This will result in a program that is easier to write,
easier to maintain, has fewer bugs, and just works better.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

Yes, the master thing is what i tried with the previous version.
I guess i'll use it on the newest dll again.
Not so important from now on.


- Original Message - 
From: "Drew, Stephen" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>; <sqlite-users@sqlite.org>
Sent: Tuesday, August 23, 2005 1:02 AM
Subject: RE: [sqlite] Why can i open a textfile?


Point taken about viruses perhaps, but there are other reasons one might 
want to encrypt data - which by its very nature could be related to 
anything.


For example, in a commercially competitive environment, it might be easy 
for a competitor to gain access to files, or even colleagues within the 
same organisation who do not have the privilege to see such data. A file 
that can be easily read, often will be - whereas an encrypted file, of any 
reasonable level, will often be enough to deter prying eyes.


If one needs to know whether a file is a database or not immediately after 
opening it, surely a user-written wrapper function around sqlite(3)_open 
which does a simple select on sqlite_master is the way to go?  Keeps 
everybody happy Personally I'd wrap the calls to sqlite anyway but C++ 
sort of suggests doing this regardless


Steve

-Original Message- 
From: Edwin Knoppert [mailto:[EMAIL PROTECTED]

Sent: Mon 22/08/2005 23:20
To: sqlite-users@sqlite.org
Cc:
Subject: Re: [sqlite] Why can i open a textfile?



At the other hand, this is database stuff, what on earth would you encrypt
in real life business databases.
No one cares, except for a few purposes.
(Now i done it :) )
Encrypting a header, like if any virus writer is busy with a tool like
sqlite..
pfffttt.


- Original Message -
From: "Dennis Jenkins" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Tuesday, August 23, 2005 12:07 AM
Subject: Re: [sqlite] Why can i open a textfile?


> Mike Shaver wrote:
>
>>On 8/22/05, Dennis Jenkins <[EMAIL PROTECTED]> wrote:
>>
>>>I very much disagree.  I want the entire file, header included, to be
>>>encrypted.  Sometimes you don't want anyone to know what the file type
>>>is.  Security through obscurity is not secure.  However, you don't want
>>>to give the bad guys a road map either...
>>>
>>
>>Finding out that it's a sqlite file is not a hard problem for an
>>attacker who has any interesting access to your machine, since your
>>programs must find that file somehow.  Once they find it, are you not
>>concerned about lightening their cryptanalysis burden through
>>known-plaintext attacks?
>>
>>Mike
>>
> No, not really.  The sqlite crypto engine consumes the first several
> hundred bytes of the rc4 random number generator output.  It is my
> understanding that this would significantly complicate the plain-text
> attack.  But I'm not a crytologist.  I do find it facinating though.
>
> I do not understand how "finding the file" would give the attackers any
> clue to what kind of file it is (unless I make the filename something 
> like

> "sqlite3.db3").  If the file were named "jimbob.dat", and the contents
> looked like gibberish, then what do they know?  They must analyze the
> program that accesses the file.
>
> I once thought that I could remove all text strings from the sqlite code
> that would give the attacker any clues.  I then realized that the 
> strings

> are important to the proper functioning.  The ones that need to be left
> behind are significant enough to be good clues that the program uses
> sqlite technology.  So, I do agree with you, that it is not too 
> difficult

> to determine if a data file _might_ be an sqlite database, even if it in
> encrypted.
>
> That being said, I still like having the header encrypted as it is. 
> Maybe

> it just makes me feel warm and fuzzy on the inside :)
>
> In the end, I feel that our software is much more vulnerable to someone
> attacking it with a debugger than with crypto analytic attacks.  At some
> point, you must call "sqlite3_key()" and pass it three things: the 
> sqlite

> handle, a void* to the key initializer and an "int" (# of bytes in the
> key).  All the attacker has to do is locate that code and determine what
> those last two arguments are.  Personally, I find this to be an easier
> approach.  But then, I've been coding in assembly since I was 8 and C 
> for

> the last 10 years.  I'm not much of a mathematician or code breaker.
>
> I have often wondered how difficult it would be to derive the rc4
> initialization key given a known plain text and a known cipher text
> generated from the unknown key and known plain text.  I imagine it as a
> breadth-first search of the key space.
> Lets say that it is computa

RE: [sqlite] Why can i open a textfile?

[Drew, Stephen]

Point taken about viruses perhaps, but there are other reasons one might want 
to encrypt data - which by its very nature could be related to anything.  
 
For example, in a commercially competitive environment, it might be easy for a 
competitor to gain access to files, or even colleagues within the same 
organisation who do not have the privilege to see such data. A file that can be 
easily read, often will be - whereas an encrypted file, of any reasonable 
level, will often be enough to deter prying eyes.
 
If one needs to know whether a file is a database or not immediately after 
opening it, surely a user-written wrapper function around sqlite(3)_open which 
does a simple select on sqlite_master is the way to go?  Keeps everybody 
happy Personally I'd wrap the calls to sqlite anyway but C++ sort of 
suggests doing this regardless
 
Steve

-Original Message- 
From: Edwin Knoppert [mailto:[EMAIL PROTECTED] 
Sent: Mon 22/08/2005 23:20 
To: sqlite-users@sqlite.org 
Cc: 
Subject: Re: [sqlite] Why can i open a textfile?



At the other hand, this is database stuff, what on earth would you 
encrypt
in real life business databases.
No one cares, except for a few purposes.
(Now i done it :) )
Encrypting a header, like if any virus writer is busy with a tool like
sqlite..
pfffttt.


- Original Message -
From: "Dennis Jenkins" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Tuesday, August 23, 2005 12:07 AM
    Subject: Re: [sqlite] Why can i open a textfile?


> Mike Shaver wrote:
>
>>On 8/22/05, Dennis Jenkins <[EMAIL PROTECTED]> wrote:
>>
>>>I very much disagree.  I want the entire file, header included, to be
>>>encrypted.  Sometimes you don't want anyone to know what the file 
type
>>>is.  Security through obscurity is not secure.  However, you don't 
want
>>>to give the bad guys a road map either...
>>>
>>
>>Finding out that it's a sqlite file is not a hard problem for an
>>attacker who has any interesting access to your machine, since your
>>programs must find that file somehow.  Once they find it, are you not
>>concerned about lightening their cryptanalysis burden through
>>known-plaintext attacks?
>>
>>Mike
>>
> No, not really.  The sqlite crypto engine consumes the first several
> hundred bytes of the rc4 random number generator output.  It is my
> understanding that this would significantly complicate the plain-text
> attack.  But I'm not a crytologist.  I do find it facinating though.
>
> I do not understand how "finding the file" would give the attackers 
any
> clue to what kind of file it is (unless I make the filename something 
like
> "sqlite3.db3").  If the file were named "jimbob.dat", and the contents
> looked like gibberish, then what do they know?  They must analyze the
> program that accesses the file.
>
> I once thought that I could remove all text strings from the sqlite 
code
> that would give the attacker any clues.  I then realized that the 
strings
> are important to the proper functioning.  The ones that need to be 
left
> behind are significant enough to be good clues that the program uses
> sqlite technology.  So, I do agree with you, that it is not too 
difficult
> to determine if a data file _might_ be an sqlite database, even if it 
in
> encrypted.
>
> That being said, I still like having the header encrypted as it is.  
Maybe
> it just makes me feel warm and fuzzy on the inside :)
>
> In the end, I feel that our software is much more vulnerable to 
someone
> attacking it with a debugger than with crypto analytic attacks.  At 
some
> point, you must call "sqlite3_key()" and pass it three things: the 
sqlite
> handle, a void* to the key initializer and an "int" (# of bytes in the
> key).  All the attacker has to do is locate that code and determine 
what
> those last two arguments are.  Personally, I find this to be an easier
> approach.  But then, I've been coding in assembly since I was 8 and C 
for
> the last 10 years.  I'm not much of a mathematician or code breaker.
>
> I have often wondered how difficult it would be to derive the rc4
> initialization key given a know

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

At the other hand, this is database stuff, what on earth would you encrypt 
in real life business databases.

No one cares, except for a few purposes.
(Now i done it :) )
Encrypting a header, like if any virus writer is busy with a tool like 
sqlite..

pfffttt.


- Original Message - 
From: "Dennis Jenkins" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Tuesday, August 23, 2005 12:07 AM
Subject: Re: [sqlite] Why can i open a textfile?



Mike Shaver wrote:


On 8/22/05, Dennis Jenkins <[EMAIL PROTECTED]> wrote:


I very much disagree.  I want the entire file, header included, to be
encrypted.  Sometimes you don't want anyone to know what the file type
is.  Security through obscurity is not secure.  However, you don't want
to give the bad guys a road map either...



Finding out that it's a sqlite file is not a hard problem for an
attacker who has any interesting access to your machine, since your
programs must find that file somehow.  Once they find it, are you not
concerned about lightening their cryptanalysis burden through
known-plaintext attacks?

Mike

No, not really.  The sqlite crypto engine consumes the first several 
hundred bytes of the rc4 random number generator output.  It is my 
understanding that this would significantly complicate the plain-text 
attack.  But I'm not a crytologist.  I do find it facinating though.


I do not understand how "finding the file" would give the attackers any 
clue to what kind of file it is (unless I make the filename something like 
"sqlite3.db3").  If the file were named "jimbob.dat", and the contents 
looked like gibberish, then what do they know?  They must analyze the 
program that accesses the file.


I once thought that I could remove all text strings from the sqlite code 
that would give the attacker any clues.  I then realized that the strings 
are important to the proper functioning.  The ones that need to be left 
behind are significant enough to be good clues that the program uses 
sqlite technology.  So, I do agree with you, that it is not too difficult 
to determine if a data file _might_ be an sqlite database, even if it in 
encrypted.


That being said, I still like having the header encrypted as it is.  Maybe 
it just makes me feel warm and fuzzy on the inside :)


In the end, I feel that our software is much more vulnerable to someone 
attacking it with a debugger than with crypto analytic attacks.  At some 
point, you must call "sqlite3_key()" and pass it three things: the sqlite 
handle, a void* to the key initializer and an "int" (# of bytes in the 
key).  All the attacker has to do is locate that code and determine what 
those last two arguments are.  Personally, I find this to be an easier 
approach.  But then, I've been coding in assembly since I was 8 and C for 
the last 10 years.  I'm not much of a mathematician or code breaker.


I have often wondered how difficult it would be to derive the rc4 
initialization key given a known plain text and a known cipher text 
generated from the unknown key and known plain text.  I imagine it as a 
breadth-first search of the key space.
Lets say that it is computationally feasible to do just that.  The sqlite 
header string is.. um, heck, I don't know, let's say 20 bytes.  Then you 
can derive the exact values for at most 20 values of the key state vector 
(it might be less if a value gets muted more than once).  What do you know 
about the remaining bytes of the first 256 bytes of the sqlite file?  Some 
of those bytes have "sane" values or other constraints.  I think that it 
would be too difficult to fully derive the key b/c you don't know much of 
the plain text.


This is the extent of what I know about rc4.  If someone else knows more, 
please enlighten me. :)

Re: [sqlite] Why can i open a textfile?

[Dennis Jenkins]

Mike Shaver wrote:


On 8/22/05, Dennis Jenkins <[EMAIL PROTECTED]> wrote:
 


I very much disagree.  I want the entire file, header included, to be
encrypted.  Sometimes you don't want anyone to know what the file type
is.  Security through obscurity is not secure.  However, you don't want
to give the bad guys a road map either...
   



Finding out that it's a sqlite file is not a hard problem for an
attacker who has any interesting access to your machine, since your
programs must find that file somehow.  Once they find it, are you not
concerned about lightening their cryptanalysis burden through
known-plaintext attacks?

Mike
 

No, not really.  The sqlite crypto engine consumes the first several 
hundred bytes of the rc4 random number generator output.  It is my 
understanding that this would significantly complicate the plain-text 
attack.  But I'm not a crytologist.  I do find it facinating though.


I do not understand how "finding the file" would give the attackers any 
clue to what kind of file it is (unless I make the filename something 
like "sqlite3.db3").  If the file were named "jimbob.dat", and the 
contents looked like gibberish, then what do they know?  They must 
analyze the program that accesses the file.


I once thought that I could remove all text strings from the sqlite code 
that would give the attacker any clues.  I then realized that the 
strings are important to the proper functioning.  The ones that need to 
be left behind are significant enough to be good clues that the program 
uses sqlite technology.  So, I do agree with you, that it is not too 
difficult to determine if a data file _might_ be an sqlite database, 
even if it in encrypted.


That being said, I still like having the header encrypted as it is.  
Maybe it just makes me feel warm and fuzzy on the inside :)


In the end, I feel that our software is much more vulnerable to someone 
attacking it with a debugger than with crypto analytic attacks.  At some 
point, you must call "sqlite3_key()" and pass it three things: the 
sqlite handle, a void* to the key initializer and an "int" (# of bytes 
in the key).  All the attacker has to do is locate that code and 
determine what those last two arguments are.  Personally, I find this to 
be an easier approach.  But then, I've been coding in assembly since I 
was 8 and C for the last 10 years.  I'm not much of a mathematician or 
code breaker.


I have often wondered how difficult it would be to derive the rc4 
initialization key given a known plain text and a known cipher text 
generated from the unknown key and known plain text.  I imagine it as a 
breadth-first search of the key space. 

Lets say that it is computationally feasible to do just that.  The 
sqlite header string is.. um, heck, I don't know, let's say 20 bytes.  
Then you can derive the exact values for at most 20 values of the key 
state vector (it might be less if a value gets muted more than once).  
What do you know about the remaining bytes of the first 256 bytes of the 
sqlite file?  Some of those bytes have "sane" values or other 
constraints.  I think that it would be too difficult to fully derive the 
key b/c you don't know much of the plain text.


This is the extent of what I know about rc4.  If someone else knows 
more, please enlighten me. :)

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

50% o well just have us an indication, nothing counted.
I meant 50% of this forum users, or... this planet, whatever.

All i meant is that sqlite is created by a c programmer, like my colleague 
he seems to forget there are different languages but he doesn't care.


Well, to keep cdecl functions and provide examples with pointers is not 
really a benefit to other kinds of users.
c is hard to read and/or convert for BASIC users, oops now i said it, BASIC 
is often poison in c perspective :)


Well, i can not complain, sqlite is performing well and it's free.
But just wanted to make note to inform you there are other kind of 
programmers in the world.
Wrapper libraries wouldn't not be necessary to access sqlite directly with a 
little thought.

With my language i can safely access it, it's just all to much c imo.

The remark about the encrypted header is really a thing never experianced by 
myself.
I don't think any fileformat supporting an encryption will also encrypt 
these kinds of thing.

You are over-doing it imo.
I can imagne you want a totally safe file(format) this way.
But then, we are in disagreement :)






- Original Message - 
From: "Dennis Jenkins" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Monday, August 22, 2005 11:31 PM
Subject: Re: [sqlite] Why can i open a textfile?



Edwin Knoppert wrote:


Hmm, but every known file format has an header.
Sqlite has a string, not really a header as it seems.



Sqlite has a header.  Part of it is plain text, part binary.  You can 
learn about it by reading the source code.



Maybe for v4 to implement a real header (if not yet)
A header doesn't need to be encrypted.
(A bit for testing if it's encrypted might have it use as well)



I very much disagree.  I want the entire file, header included, to be 
encrypted.  Sometimes you don't want anyone to know what the file type is. 
Security through obscurity is not secure.  However, you don't want to give 
the bad guys a road map either...




Sqlite seems to be created espec. for c programmers.
c programmers are truly a different breed :)


And we love it.


But it's not always handy to follow these directions or doings.
Iow, i never looked at the sqlite source how it works.


Sad, it is truely neat-o code.

I guess at least 50 percent over here does not really care and would 
never explore how to solve these kind of things.


50 percent of whom?  Programmers who use sqlite?  Where is "over here"?



I hope examples and such will eventually get more polished towards other 
languages as well.
Examples for instance show *sqlite as hstmt and there is really no use to 
understand it's internally a pointer to a structure or so.

hstmt as Long would do.

Well long story but understanding functions (and even using it as cdecl) 
is not that trival due conversion and such.

Re: [sqlite] Why can i open a textfile?

[D. Richard Hipp]

On Mon, 2005-08-22 at 23:12 +0200, Edwin Knoppert wrote:
> Hmm, but every known file format has an header.
> Sqlite has a string, not really a header as it seems.
> Maybe for v4 to implement a real header (if not yet)
> A header doesn't need to be encrypted.
> (A bit for testing if it's encrypted might have it use as well)
> 

An encrypted SQLite database appears to be white noise
from beginning to end.  There is no header.  Many users 
prefer it that way.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Encryption and fixed header values (was Re: [sqlite] Why can i open a textfile?)

[D. Richard Hipp]

On Mon, 2005-08-22 at 17:19 -0400, Mike Shaver wrote:
> On 8/22/05, Edwin Knoppert <[EMAIL PROTECTED]> wrote:
> > Hmm, but every known file format has an header.
> > Sqlite has a string, not really a header as it seems.
> > Maybe for v4 to implement a real header (if not yet)
> > A header doesn't need to be encrypted.
> > (A bit for testing if it's encrypted might have it use as well)
> 
> In fact, I was wondering about this very issue when I was working on
> my own encryption layer for Mozilla's storage use.  Does the reliable
> format of the first page (known sqlite header string, various other
> fields that are very likely to be zero or in some way related to the
> size of the DB file) not make known-plaintext attacks on the encrypted
> database much easier?  

The key schedule is perturbed by a randomly chosen nonce that is added
to each page.  The size of the nonce is selectable when the database
is created and defaults to 4 bytes.  This makes an encrypted database
about 0.4% larger than an unencrypted database (since the nonce has to
be stored, thus reducing the space available to store real data) but 
it also make known or chosen plaintext attacks considerably more
difficult.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Why can i open a textfile?

[Mike Shaver]

On 8/22/05, Dennis Jenkins <[EMAIL PROTECTED]> wrote:
> I very much disagree.  I want the entire file, header included, to be
> encrypted.  Sometimes you don't want anyone to know what the file type
> is.  Security through obscurity is not secure.  However, you don't want
> to give the bad guys a road map either...

Finding out that it's a sqlite file is not a hard problem for an
attacker who has any interesting access to your machine, since your
programs must find that file somehow.  Once they find it, are you not
concerned about lightening their cryptanalysis burden through
known-plaintext attacks?

Mike

Re: [sqlite] Encryption and fixed header values (was Re: [sqlite] Why can i open a textfile?)

[Dennis Jenkins]

Mike Shaver wrote:


On 8/22/05, Edwin Knoppert <[EMAIL PROTECTED]> wrote:
 


Hmm, but every known file format has an header.
Sqlite has a string, not really a header as it seems.
Maybe for v4 to implement a real header (if not yet)
A header doesn't need to be encrypted.
(A bit for testing if it's encrypted might have it use as well)
   



In fact, I was wondering about this very issue when I was working on
my own encryption layer for Mozilla's storage use.  Does the reliable
format of the first page (known sqlite header string, various other
fields that are very likely to be zero or in some way related to the
size of the DB file) not make known-plaintext attacks on the encrypted
database much easier?  Given that the encryption is limited to a page
at a time due to the pager implementation (and placement of the hooks
in sqlite), it would seem that that would be fairly worrisome.

I can mitigate it a bit by having the page number affect the key
selection, but I'm still sort of nervous about it.

Mike
 


I hope that I don't spill too many beans here

My company has licensed the RC4 encryption add-on for sqlite. 

RC4 is basically an XOR against a huge one-time pad.  That pad is 
creating using a "key scheduling algorithm".  The key state for the 
random number generator is 256 bytes of data and two 8-bit indicies.  
There are several attacks that can be used to derive the original key 
state, but they all require huge samples of data to analyze.  RC4 is not 
the strongest encryption available, but for most of us, it is good enough.


The best way to attack any system using sqlite w/ the crypto extension 
is to hook the call to "sqlite3_key()" and just steal the rc4 key 
directly.  Much easier than crunhcing numbers on a super computer.


http://en.wikipedia.org/wiki/Rc4

Re: [sqlite] Why can i open a textfile?

[Dennis Jenkins]

Edwin Knoppert wrote:


Hmm, but every known file format has an header.
Sqlite has a string, not really a header as it seems.



Sqlite has a header.  Part of it is plain text, part binary.  You can 
learn about it by reading the source code.



Maybe for v4 to implement a real header (if not yet)
A header doesn't need to be encrypted.
(A bit for testing if it's encrypted might have it use as well)



I very much disagree.  I want the entire file, header included, to be 
encrypted.  Sometimes you don't want anyone to know what the file type 
is.  Security through obscurity is not secure.  However, you don't want 
to give the bad guys a road map either...




Sqlite seems to be created espec. for c programmers.
c programmers are truly a different breed :)


And we love it.


But it's not always handy to follow these directions or doings.
Iow, i never looked at the sqlite source how it works.


Sad, it is truely neat-o code.

I guess at least 50 percent over here does not really care and would 
never explore how to solve these kind of things.


50 percent of whom?  Programmers who use sqlite?  Where is "over here"?



I hope examples and such will eventually get more polished towards 
other languages as well.
Examples for instance show *sqlite as hstmt and there is really no use 
to understand it's internally a pointer to a structure or so.

hstmt as Long would do.

Well long story but understanding functions (and even using it as 
cdecl) is not that trival due conversion and such.

[sqlite] Encryption and fixed header values (was Re: [sqlite] Why can i open a textfile?)

[Mike Shaver]

On 8/22/05, Edwin Knoppert <[EMAIL PROTECTED]> wrote:
> Hmm, but every known file format has an header.
> Sqlite has a string, not really a header as it seems.
> Maybe for v4 to implement a real header (if not yet)
> A header doesn't need to be encrypted.
> (A bit for testing if it's encrypted might have it use as well)

In fact, I was wondering about this very issue when I was working on
my own encryption layer for Mozilla's storage use.  Does the reliable
format of the first page (known sqlite header string, various other
fields that are very likely to be zero or in some way related to the
size of the DB file) not make known-plaintext attacks on the encrypted
database much easier?  Given that the encryption is limited to a page
at a time due to the pager implementation (and placement of the hooks
in sqlite), it would seem that that would be fairly worrisome.

I can mitigate it a bit by having the page number affect the key
selection, but I'm still sort of nervous about it.

Mike

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

Hmm, the latter might have sounded a bit harsh.
It must be seen as reminder, no pointing finger or so :)




- Original Message - 
From: "Edwin Knoppert" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Monday, August 22, 2005 11:12 PM
Subject: Re: [sqlite] Why can i open a textfile?



Hmm, but every known file format has an header.
Sqlite has a string, not really a header as it seems.
Maybe for v4 to implement a real header (if not yet)
A header doesn't need to be encrypted.
(A bit for testing if it's encrypted might have it use as well)

Sqlite seems to be created espec. for c programmers.
c programmers are truly a different breed :)
But it's not always handy to follow these directions or doings.
Iow, i never looked at the sqlite source how it works.
I guess at least 50 percent over here does not really care and would never 
explore how to solve these kind of things.


I hope examples and such will eventually get more polished towards other 
languages as well.
Examples for instance show *sqlite as hstmt and there is really no use to 
understand it's internally a pointer to a structure or so.

hstmt as Long would do.

Well long story but understanding functions (and even using it as cdecl) 
is not that trival due conversion and such.





- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Monday, August 22, 2005 9:23 PM
Subject: Re: [sqlite] Why can i open a textfile?



On Mon, 2005-08-22 at 20:58 +0200, Mark de Vries wrote:

I must say I agree. Perhaps there is some verry good reason to delay
actually opening the DB untill the first real access.



There is a good reason, actually.  SQLite has the ability
to read and write encrypted databases.  The hooks to do this
are there in the public-domain code, though the encryption
code itself is missing.  Nevertheless, it is a capability
of SQLite.

The encryption key is specified after the database is
opened.  But without an encryption key, SQLite has no
way of knowing if the file it opened is a valid database.
It might *look* like a text file, but that just might
be because the database is encrypted in a way that makes
it appear so.  Hence, SQLite cannot pass judgement on
a file until you actually try to do something with it.
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

Hmm, but every known file format has an header.
Sqlite has a string, not really a header as it seems.
Maybe for v4 to implement a real header (if not yet)
A header doesn't need to be encrypted.
(A bit for testing if it's encrypted might have it use as well)

Sqlite seems to be created espec. for c programmers.
c programmers are truly a different breed :)
But it's not always handy to follow these directions or doings.
Iow, i never looked at the sqlite source how it works.
I guess at least 50 percent over here does not really care and would never 
explore how to solve these kind of things.


I hope examples and such will eventually get more polished towards other 
languages as well.
Examples for instance show *sqlite as hstmt and there is really no use to 
understand it's internally a pointer to a structure or so.

hstmt as Long would do.

Well long story but understanding functions (and even using it as cdecl) is 
not that trival due conversion and such.





- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Monday, August 22, 2005 9:23 PM
Subject: Re: [sqlite] Why can i open a textfile?



On Mon, 2005-08-22 at 20:58 +0200, Mark de Vries wrote:

I must say I agree. Perhaps there is some verry good reason to delay
actually opening the DB untill the first real access.



There is a good reason, actually.  SQLite has the ability
to read and write encrypted databases.  The hooks to do this
are there in the public-domain code, though the encryption
code itself is missing.  Nevertheless, it is a capability
of SQLite.

The encryption key is specified after the database is
opened.  But without an encryption key, SQLite has no
way of knowing if the file it opened is a valid database.
It might *look* like a text file, but that just might
be because the database is encrypted in a way that makes
it appear so.  Hence, SQLite cannot pass judgement on
a file until you actually try to do something with it.
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Why can i open a textfile?

[Mark de Vries]

On Mon, 22 Aug 2005, Edwin Knoppert wrote:

> Confirmed but open still opens the textfile, i wish it cancelled that as
> well.
> There is no use to an open textfile for sqlite.
>

I must say I agree. Perhaps there is some verry good reason to delay
actually opening the DB untill the first real access.

But it sounds like optimizing for the "open, do nothing, close" case,
which is probably not that common. So most of the time the database will
eventually be opened, why not do right away? Is there any real benefit?

This way it makes things harder for the application programmer. Instead of
beeing able to determine from the result of the open call (I don't know
the C API) if the database was succesfully opened and can be used, you
have to perform some (dummy) access to the database in order to find out.

Again, maybe there is a verry good reason to do it the way it is. But if
the common case is that the database will be opened anyway, why not do it
at a time and in a way that can be put to good use by the application
(programmer)?

Just my 0.02

Regards,
Mark.

> - Original Message -
> From: "D. Richard Hipp" <[EMAIL PROTECTED]>
> To: <sqlite-users@sqlite.org>
> Sent: Monday, August 22, 2005 5:54 PM
> Subject: Re: [sqlite] Why can i open a textfile?
>
>
> > On Mon, 2005-08-22 at 08:42 -0700, Clark Christensen wrote:
> >>
> >> --- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:
> >> > ...
> >> > SQLite never "blows away" a file that is not a database.
> >> >
> >> > --
> >> > D. Richard Hipp <[EMAIL PROTECTED]>
> >>
> >> Sorry to report this doesn't seem to be the case.  At least
> >> not under Windows.  Using v3.2.2, I can "sqlite3 junk.txt",
> >> then "create table junk (a,b)", and the text file becomes a
> >> SQLite3 database :-(
> >>
> >> FWIW, junk.txt contained several lines of text before
> >> becoming a database.
> >>
> >
> > See http://www.sqlite.org/cvstrac/tktview?tn=1370
> > The problem has been fixed in version 3.2.3.
> > --
> > D. Richard Hipp <[EMAIL PROTECTED]>
> >
> >
>
>


Regards,
Mark

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

Confirmed but open still opens the textfile, i wish it cancelled that as 
well.

There is no use to an open textfile for sqlite.


- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Monday, August 22, 2005 5:54 PM
Subject: Re: [sqlite] Why can i open a textfile?



On Mon, 2005-08-22 at 08:42 -0700, Clark Christensen wrote:


--- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:
> ...
> SQLite never "blows away" a file that is not a database.
>
> -- 
> D. Richard Hipp <[EMAIL PROTECTED]>


Sorry to report this doesn't seem to be the case.  At least
not under Windows.  Using v3.2.2, I can "sqlite3 junk.txt",
then "create table junk (a,b)", and the text file becomes a
SQLite3 database :-(

FWIW, junk.txt contained several lines of text before
becoming a database.



See http://www.sqlite.org/cvstrac/tktview?tn=1370
The problem has been fixed in version 3.2.3.
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Why can i open a textfile?

[Clark Christensen]

--- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:

> On Mon, 2005-08-22 at 08:42 -0700, Clark Christensen
> wrote:
> > 
> > --- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:
> > > ...
> > > SQLite never "blows away" a file that is not a
> database.
> > > 
> > > -- 
> > > D. Richard Hipp <[EMAIL PROTECTED]>
> > 
> > Sorry to report this doesn't seem to be the case.  At
> least
> > not under Windows.  Using v3.2.2, I can "sqlite3
> junk.txt",
> > then "create table junk (a,b)", and the text file
> becomes a
> > SQLite3 database :-(
> > 
> > FWIW, junk.txt contained several lines of text before
> > becoming a database.
> > 
> 
> See http://www.sqlite.org/cvstrac/tktview?tn=1370
> The problem has been fixed in version 3.2.3.
> -- 
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 
Yup.  Late to the party.  That's what I get for replying
before reading the remaining messages :-))

Thanks!

 -Clark

Re: [sqlite] Why can i open a textfile?

[D. Richard Hipp]

On Mon, 2005-08-22 at 08:42 -0700, Clark Christensen wrote:
> 
> --- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:
> > ...
> > SQLite never "blows away" a file that is not a database.
> > 
> > -- 
> > D. Richard Hipp <[EMAIL PROTECTED]>
> 
> Sorry to report this doesn't seem to be the case.  At least
> not under Windows.  Using v3.2.2, I can "sqlite3 junk.txt",
> then "create table junk (a,b)", and the text file becomes a
> SQLite3 database :-(
> 
> FWIW, junk.txt contained several lines of text before
> becoming a database.
> 

See http://www.sqlite.org/cvstrac/tktview?tn=1370
The problem has been fixed in version 3.2.3.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Why can i open a textfile?

[Clark Christensen]

--- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:
> ...
> SQLite never "blows away" a file that is not a database.
> 
> -- 
> D. Richard Hipp <[EMAIL PROTECTED]>

Sorry to report this doesn't seem to be the case.  At least
not under Windows.  Using v3.2.2, I can "sqlite3 junk.txt",
then "create table junk (a,b)", and the text file becomes a
SQLite3 database :-(

FWIW, junk.txt contained several lines of text before
becoming a database.

 -Clark

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

O well, read-only?
Then exclusive as well :)


- Original Message - 
From: "Walter Meerschaert" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Sunday, August 21, 2005 2:04 AM
Subject: Re: [sqlite] Why can i open a textfile?


I agree, since that makes error/exception handling easier. On the 
subject of open(), I also would like it to have a read_only option, if 
that is possible. I am not even sure that a read-only state is tracked 
through the library, or if the writing attempts just fail with an file 
access error.


Or is there already a way to open the database read only?

Edwin Knoppert wrote:

I haven't test on exactly 1kb file but larger and indeed an error is 
shown.


May i stress again that the test should be during open() imo?
And rather not using a 2nd function to examine the db.

Re: [sqlite] Why can i open a textfile?

[Walter Meerschaert]

I agree, since that makes error/exception handling easier. On the 
subject of open(), I also would like it to have a read_only option, if 
that is possible. I am not even sure that a read-only state is tracked 
through the library, or if the writing attempts just fail with an file 
access error.


Or is there already a way to open the database read only?

Edwin Knoppert wrote:

I haven't test on exactly 1kb file but larger and indeed an error is 
shown.


May i stress again that the test should be during open() imo?
And rather not using a 2nd function to examine the db.

Re: [sqlite] Why can i open a textfile?

[Will Leshner]

On Aug 20, 2005, at 1:38 PM, D. Richard Hipp wrote:


There might be a bug.  If SQLite sees a file that is less
than 1024 bytes in length, it might assume (mistakenly) that
it is empty and then overwrite it.  I'll have to check on
that.  If it does, this should be considered a bug.



Ok. It does appear I wrote an email about this about a month ago. I  
managed to dig it up from the archives:

Re: [sqlite] Why can i open a textfile?

[Will Leshner]

On Aug 20, 2005, at 1:38 PM, D. Richard Hipp wrote:


There might be a bug.  If SQLite sees a file that is less
than 1024 bytes in length, it might assume (mistakenly) that
it is empty and then overwrite it.  I'll have to check on
that.  If it does, this should be considered a bug.



Yes. That was it. I even traced this through the code in SQLite and  
figure out it was files that were less than 1024 bytes that were the  
problem. I think I posted something to the mailing list, but perhaps  
I never filed a bug report. Sorry about that.

Re: [sqlite] Why can i open a textfile?

[D. Richard Hipp]

On Sat, 2005-08-20 at 13:36 -0700, Will Leshner wrote:
> On Aug 20, 2005, at 9:21 AM, D. Richard Hipp wrote:
> 
> > SQLite never "blows away" a file that is not a database.
> 
> Sorry to use such crude language :) But I believe I have run into an  
> issue with SQLite reformatting a non-sqlite file as a SQLite  
> database. I will try to reconstruct the problem and report back if I  
> can.
> 

There might be a bug.  If SQLite sees a file that is less
than 1024 bytes in length, it might assume (mistakenly) that
it is empty and then overwrite it.  I'll have to check on
that.  If it does, this should be considered a bug.

Certainly if the file is 1024 bytes or larger SQLite
should never overwrite it.  If you find a case where
it does, please fill out a bug report.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Why can i open a textfile?

[Will Leshner]

On Aug 20, 2005, at 11:33 AM, Edwin Knoppert wrote:


Oops.. good bye simple telephone list!
I just added a table and the list is replaced by an sqlite db.


Right. That's what I thought. The wrapper I develop is very careful  
to check to make sure a file is really a SQLite database before  
trying to open it as such for this reason.

Re: [sqlite] Why can i open a textfile?

[Will Leshner]

On Aug 20, 2005, at 9:21 AM, D. Richard Hipp wrote:


SQLite never "blows away" a file that is not a database.


Sorry to use such crude language :) But I believe I have run into an  
issue with SQLite reformatting a non-sqlite file as a SQLite  
database. I will try to reconstruct the problem and report back if I  
can.


--
REALbasic database options: http://sqlabs.net
REALbasic news and tips: http://rbgazette.com
KidzMail & KidzLog: http://haranbanjo.com

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

Oops.. good bye simple telephone list!
I just added a table and the list is replaced by an sqlite db.



- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Saturday, August 20, 2005 6:21 PM
Subject: Re: [sqlite] Why can i open a textfile?



On Sat, 2005-08-20 at 09:06 -0700, Will Leshner wrote:

On Aug 20, 2005, at 1:25 AM, Edwin Knoppert wrote:

> v3.2.1, i opened a textfile containing a simple list not related to  
> sqlite.

> Was testing my error handler but it still opens it.
>

Do you mean you called sqlite3_open? I believe sqlite3_open does not
yet test to see if the database is truly a SQLite 3 database. And I
can't remember the exact details, but I think it is possible that if
you open a non-database and then start trying to use it, SQLite blows
away whatever is in it and turns it into a SQLite 3 database. So you
might want to be kinda careful about opening non-databases with SQLite.



Not quite correct.

sqlite3_open just sets up the database handle.  It does not
actually open the database file or check to see if the file
really contains a database.  The file is not actually opened
until you try to do something with it.

SQLite never "blows away" a file that is not a database.

--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

This does not result in an error but returns the fields (no records of 
course):


SELECT * FROM sqlite_master where [type]='table'


- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Saturday, August 20, 2005 6:21 PM
Subject: Re: [sqlite] Why can i open a textfile?



On Sat, 2005-08-20 at 09:06 -0700, Will Leshner wrote:

On Aug 20, 2005, at 1:25 AM, Edwin Knoppert wrote:

> v3.2.1, i opened a textfile containing a simple list not related to
> sqlite.
> Was testing my error handler but it still opens it.
>

Do you mean you called sqlite3_open? I believe sqlite3_open does not
yet test to see if the database is truly a SQLite 3 database. And I
can't remember the exact details, but I think it is possible that if
you open a non-database and then start trying to use it, SQLite blows
away whatever is in it and turns it into a SQLite 3 database. So you
might want to be kinda careful about opening non-databases with SQLite.



Not quite correct.

sqlite3_open just sets up the database handle.  It does not
actually open the database file or check to see if the file
really contains a database.  The file is not actually opened
until you try to do something with it.

SQLite never "blows away" a file that is not a database.

--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

I could do a simple test myself of course, like checking the master or so..


- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Saturday, August 20, 2005 6:21 PM
Subject: Re: [sqlite] Why can i open a textfile?



On Sat, 2005-08-20 at 09:06 -0700, Will Leshner wrote:

On Aug 20, 2005, at 1:25 AM, Edwin Knoppert wrote:

> v3.2.1, i opened a textfile containing a simple list not related to  
> sqlite.

> Was testing my error handler but it still opens it.
>

Do you mean you called sqlite3_open? I believe sqlite3_open does not
yet test to see if the database is truly a SQLite 3 database. And I
can't remember the exact details, but I think it is possible that if
you open a non-database and then start trying to use it, SQLite blows
away whatever is in it and turns it into a SQLite 3 database. So you
might want to be kinda careful about opening non-databases with SQLite.



Not quite correct.

sqlite3_open just sets up the database handle.  It does not
actually open the database file or check to see if the file
really contains a database.  The file is not actually opened
until you try to do something with it.

SQLite never "blows away" a file that is not a database.

--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Why can i open a textfile?

[Edwin Knoppert]

I ever read the suggestion to read in the db myself and check for a 
signature.

Well, if i strongly suggest an extra test is done by the system itself.
Sure if 0 bytes it's a go as well, as it is now but if > 0 bytes.. test..
I see no point in opening the database while other commands will fail.

Thanks,



- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Saturday, August 20, 2005 6:21 PM
Subject: Re: [sqlite] Why can i open a textfile?



On Sat, 2005-08-20 at 09:06 -0700, Will Leshner wrote:

On Aug 20, 2005, at 1:25 AM, Edwin Knoppert wrote:

> v3.2.1, i opened a textfile containing a simple list not related to
> sqlite.
> Was testing my error handler but it still opens it.
>

Do you mean you called sqlite3_open? I believe sqlite3_open does not
yet test to see if the database is truly a SQLite 3 database. And I
can't remember the exact details, but I think it is possible that if
you open a non-database and then start trying to use it, SQLite blows
away whatever is in it and turns it into a SQLite 3 database. So you
might want to be kinda careful about opening non-databases with SQLite.



Not quite correct.

sqlite3_open just sets up the database handle.  It does not
actually open the database file or check to see if the file
really contains a database.  The file is not actually opened
until you try to do something with it.

SQLite never "blows away" a file that is not a database.

--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Why can i open a textfile?

[D. Richard Hipp]

On Sat, 2005-08-20 at 09:06 -0700, Will Leshner wrote:
> On Aug 20, 2005, at 1:25 AM, Edwin Knoppert wrote:
> 
> > v3.2.1, i opened a textfile containing a simple list not related to  
> > sqlite.
> > Was testing my error handler but it still opens it.
> >
> 
> Do you mean you called sqlite3_open? I believe sqlite3_open does not
> yet test to see if the database is truly a SQLite 3 database. And I
> can't remember the exact details, but I think it is possible that if
> you open a non-database and then start trying to use it, SQLite blows
> away whatever is in it and turns it into a SQLite 3 database. So you
> might want to be kinda careful about opening non-databases with SQLite.
> 

Not quite correct.

sqlite3_open just sets up the database handle.  It does not
actually open the database file or check to see if the file
really contains a database.  The file is not actually opened
until you try to do something with it.

SQLite never "blows away" a file that is not a database.

-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Why can i open a textfile?

[Will Leshner]

On Aug 20, 2005, at 1:25 AM, Edwin Knoppert wrote:

v3.2.1, i opened a textfile containing a simple list not related to  
sqlite.

Was testing my error handler but it still opens it.



Do you mean you called sqlite3_open? I believe sqlite3_open does not
yet test to see if the database is truly a SQLite 3 database. And I
can't remember the exact details, but I think it is possible that if
you open a non-database and then start trying to use it, SQLite blows
away whatever is in it and turns it into a SQLite 3 database. So you
might want to be kinda careful about opening non-databases with SQLite.

--
REALbasic database options: http://sqlabs.net
REALbasic news and tips: http://rbgazette.com
KidzMail & KidzLog: http://haranbanjo.com