Re: [sqlite] to encrypt sqlite db

[Ulrich Telle]

Am 02.09.2013 06:11, schrieb Etienne:

wxSQLite3 does implement AES in ECB mode


Wrong. CBC mode is used.

Regards,

Ulrich


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] to encrypt sqlite db

[Etienne]

On Sun, Sep 1, 2013, at 19:59, Ulrich Telle wrote:
> Am 01.09.2013 18:40, schrieb Etienne:
> > wxSQLite is free, while SEE is definitively not.
> 
> The original poster searched for a free encryption extension, of which 
> there exist several: System.Data.SQLite (RC4), wxSQLite3 (AES-128 or 
> AES-256), SQLCipher (AES-256 with nonce) to name a few.
> 
> Depending on the security requirements one of the free solutions may be 
> good enough for the OP.
> 
> > wxSQLite means "pseudo" encryption (as formerly discussed), while SEE is 
> > real encryption.
> 
> This statement is nonsense. Certainly SEE provides stronger encryption 
> than wxSQLite3, however, the encryption used by wxSQLite3 is also "real" 
> AES encryption (not "pseudo", which implies "easy to  break").
> 
> Regards,
> 
> Ulrich

wxSQLite3 does implement AES in ECB mode and the SQLite file header is well 
known... so yes, it is definitively pseudo encryption.

It might be enough for the OP's purpose (unknown so far), though.

Regards,
Etienne

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query problems

[Joseph L. Casale]

> Have you tried using '=' ?
> 
> Also if you declare the columns as COLLATE NOCASE in your table definition,
> then using '=' will definitely work the way you want it to.  An example would 
> be
> 
> CREATE TABLE myTable (myName TEXT COLLATE NOCASE)

Simon,
That took this query from not finishing in 5 hours to producing results in 
under a
minute, many thanks for everyone's guidance!

jlc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query problems

[Joseph L. Casale]

> > 0   0   1   SCAN TABLE d_table_b AS da (~10 rows)
> >
> 
> Is this the index you referenced in you reply to Simon?
> Maybe you are using wrong index/column?

I'll recheck, I am also reading up on indexes as they relate to optimizing
queries. Could be I made a mistake.

> I had the same problem (kind of) and got the answer here to create a
> different index...
> 
> Thank you.
> 
> Can you post you schema?

Sure, it's not mine technically so I have to sanitize portions.

Thanks,
jlc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query problems

[Joseph L. Casale]

> LIKE is used when comparing strings with wildcards.  For example, val LIKE
> 'abra%' (which will match 'abraCaDAbra' and 'abrakadee'.
> 
> If there are no wildcards you should be using =, not LIKE.  LIKE will/should
> always indicate that a table or index scan is required, perhaps of the whole
> table/index if the like expression is not a constant (there is no other 
> choice since
> the wildcarded expression could evaluate to '%d%' which would return every
> row with a 'd' anywhere in the value.  This means that the query planner must
> assume that this join will require a full table/index scan for each 
> inner-loop and
> may return all rows because no other plan assumption would be valid.  This 
> will
> result in really crappy performance.
> 
> Are the columns declared as COLLATE NOCASE, or just the index?  If just the
> index, why?

Was just the index as I didn't know better, but its corrected now.

> If there is some (really strange) reason why the table column is not declared
> with COLLATE NOCASE, then you can always override the collation of the
> column in the expression itself:
> 
> CollateBinaryColumn COLLATE NOCASE =
> SomeOtherColumnCollationDoesNotMatter

This insight is much appreciated, thanks!
jlc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query problems

[Igor Korot]

Hi, Joseph,

On Sun, Sep 1, 2013 at 6:21 PM, Joseph L. Casale
wrote:

> > Hi,
> > Can you do "DESCRIBE QUERY PLAN " and post results here?
> >
> > Also, what do you mean by "unbearable at scale"? Did you measure it? What
> > is the result?
> >
> > Thank you.
>
> It doesn't finish with maybe 4 or 5 hours run time.
>
> Sorry, do you mean "explain query plan ..."?
> 0   0   1   SCAN TABLE d_table_b AS da (~10 rows)
>

Is this the index you referenced in you reply to Simon?
Maybe you are using wrong index/column?

I had the same problem (kind of) and got the answer here to create a
different index...

Thank you.

Can you post you schema?

> 0   1   3   SEARCH TABLE d_table_a AS d USING INTEGER PRIMARY
> KEY (rowid=?) (~1 rows)
> 0   2   0   SEARCH TABLE s_table_b AS sa USING AUTOMATIC
> COVERING INDEX (key=?) (~7 rows)
> 0   3   2   SEARCH TABLE s_table_a AS s USING INTEGER PRIMARY
> KEY (rowid=?) (~1 rows)
>
> Thanks,
> jlc
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query problems

[Joseph L. Casale]

> Have you tried using '=' ?
> 
> Also if you declare the columns as COLLATE NOCASE in your table definition,
> then using '=' will definitely work the way you want it to.  An example would 
> be
> 
> CREATE TABLE myTable (myName TEXT COLLATE NOCASE)
> 
> Simon.

I did and it excluded the comparisons whose case only differed, I only defined
COLLATE NOCASE in the index so I guess it wasn't being used.

I just changed the table defs to use this and am reloading the data.

Thanks,
jlc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query problems

[Keith Medcalf]

> I am using LIKE as the columns are indexed NOCASE and I need the
> comparison case insensitive. I suspect this is where is breaks down
> but I don't know enough sql to really appreciate the ways I could
> approach this better.

LIKE is used when comparing strings with wildcards.  For example, val LIKE 
'abra%' (which will match 'abraCaDAbra' and 'abrakadee'.  

If there are no wildcards you should be using =, not LIKE.  LIKE will/should 
always indicate that a table or index scan is required, perhaps of the whole 
table/index if the like expression is not a constant (there is no other choice 
since the wildcarded expression could evaluate to '%d%' which would return 
every row with a 'd' anywhere in the value.  This means that the query planner 
must assume that this join will require a full table/index scan for each 
inner-loop and may return all rows because no other plan assumption would be 
valid.  This will result in really crappy performance.

Are the columns declared as COLLATE NOCASE, or just the index?  If just the 
index, why?

If there is some (really strange) reason why the table column is not declared 
with COLLATE NOCASE, then you can always override the collation of the column 
in the expression itself:

CollateBinaryColumn COLLATE NOCASE = SomeOtherColumnCollationDoesNotMatter





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query problems

[Joseph L. Casale]

> Hi,
> Can you do "DESCRIBE QUERY PLAN " and post results here?
> 
> Also, what do you mean by "unbearable at scale"? Did you measure it? What
> is the result?
> 
> Thank you.

It doesn't finish with maybe 4 or 5 hours run time.

Sorry, do you mean "explain query plan ..."?
0   0   1   SCAN TABLE d_table_b AS da (~10 rows)
0   1   3   SEARCH TABLE d_table_a AS d USING INTEGER PRIMARY KEY 
(rowid=?) (~1 rows)
0   2   0   SEARCH TABLE s_table_b AS sa USING AUTOMATIC COVERING 
INDEX (key=?) (~7 rows)
0   3   2   SEARCH TABLE s_table_a AS s USING INTEGER PRIMARY KEY 
(rowid=?) (~1 rows)

Thanks,
jlc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query problems

[Simon Slavin]

On 2 Sep 2013, at 2:03am, Joseph L. Casale  wrote:

> I am using LIKE as the columns are indexed NOCASE and I need the
> comparison case insensitive.

Have you tried using '=' ?

Also if you declare the columns as COLLATE NOCASE in your table definition, 
then using '=' will definitely work the way you want it to.  An example would be

CREATE TABLE myTable (myName TEXT COLLATE NOCASE)

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query problems

[Igor Korot]

Hi,
Can you do "DESCRIBE QUERY PLAN " and post results here?

Also, what do you mean by "unbearable at scale"? Did you measure it? What
is the result?

Thank you.

On Sun, Sep 1, 2013 at 6:03 PM, Joseph L. Casale
wrote:

> I have a query that is unbearable at scale, for example when
> s_table_a and s_table_b have 70k and 1.25M rows.
>
> SELECT s.id AS s_id
>,s.lid AS s_lid
>,sa.val AS s_sid
>,d.id AS d_id
>,d.lid AS d_lid
>   FROM s_table_b sa
>   JOIN d_table_b da ON
>(
>  da.key=sa.key
>  AND da.key='unique_string'
>  AND da.val LIKE sa.val
>)
>   JOIN s_table_a s ON
>s.id=sa.id
>   JOIN d_table_a d ON
>(
>  d.id=da.id
>  AND NOT d.lid LIKE s.lid
>)
>
> I am using LIKE as the columns are indexed NOCASE and I need the
> comparison case insensitive. I suspect this is where is breaks down
> but I don't know enough sql to really appreciate the ways I could
> approach this better.
>
> Both {s|d}_table_a have 2 columns, id, lid where id is PK.
> Both {s|d}_table_b have 4 columns, seqno, id, key, val where seqno is PK,
> id is a FK ref to {s|d}_table_a.id, and several key/val pairs are
> inserted to correspond
> to the associated PK id from {s|d}_table_a.
>
> I'd be grateful for any suggestions or hints to improve this.
> Thanks,
> jlc
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query problems

[Joseph L. Casale]

I have a query that is unbearable at scale, for example when
s_table_a and s_table_b have 70k and 1.25M rows.

SELECT s.id AS s_id
   ,s.lid AS s_lid
   ,sa.val AS s_sid
   ,d.id AS d_id
   ,d.lid AS d_lid
  FROM s_table_b sa
  JOIN d_table_b da ON
   (
 da.key=sa.key
 AND da.key='unique_string'
 AND da.val LIKE sa.val
   )
  JOIN s_table_a s ON
   s.id=sa.id
  JOIN d_table_a d ON
   (
 d.id=da.id
 AND NOT d.lid LIKE s.lid
   )

I am using LIKE as the columns are indexed NOCASE and I need the
comparison case insensitive. I suspect this is where is breaks down
but I don't know enough sql to really appreciate the ways I could
approach this better.

Both {s|d}_table_a have 2 columns, id, lid where id is PK.
Both {s|d}_table_b have 4 columns, seqno, id, key, val where seqno is PK,
id is a FK ref to {s|d}_table_a.id, and several key/val pairs are inserted to 
correspond
to the associated PK id from {s|d}_table_a.

I'd be grateful for any suggestions or hints to improve this.
Thanks,
jlc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help needed for major SQLite problem

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

He needs to give the exception traceback which will show what is happening
at the time.

A common mistake with newish Python programmers is to catch all
exceptions, and then keep going which also hides the exception tracebacks.

On 01/09/13 15:12, Richard Hipp wrote:
> Does Python have an interface to the error and warning log mechanism
> of SQLite?  (http://www.sqlite.org/errlog.html)  Can you turn that on?
> It will probably give more details about what it happening.

pysqlite is the standard Python sqlite3 module and doesn't expose that
functionality.  (It also hasn't had a new release in over two years.)  It
tries to make SQLite look like the standard Python DBAPI and maps the
SQLite errors into those fewer DBAPI exceptions.

APSW (disclosure: I am the author) does provide the SQLite errors in a 1:1
mapping to exceptions.  The error log could be used in theory, but is
impractical because it has to be setup before any other SQLite call.
There doesn't appear to be any reason why it can't be changed after the
library is initialised.

I'll see if I can add something more useful to the next APSW release.

Note that the APSW API is similar to pysqlite, but not the same.  It
behaves the SQLite way rather than the DBAPI way.

  http://apidoc.apsw.googlecode.com/hg/pysqlite.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlIj0vYACgkQmOOfHg372QSYrwCcCuJyMqsUiS5SYVFpeYll72d+
Z0oAoJSoWZNanNAxoBMUzjXHMfyEFmj1
=eyCs
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help needed for major SQLite problem

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/09/13 14:34, C M wrote:
> Do you know how I can do that with Python?  For example, I tried this:
> 
> status = cursor.execute("some SQL statement here") print "The status
> is: ", status
> 
> But it prints the cursor object:
> 
>> The status is 

cursor.execute returns an iterator of the results which is the cursor itself.

What you need to provide is the traceback which will show what calls were
active.

The exception is generally caused by opening a file that isn't a database,
but can be caused by other rare sequences of api calls.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlIj0AIACgkQmOOfHg372QTU5QCfeGcle8r3m8nDs3dDgI7zb6BK
qWQAoLWkeKarpubX4S1SzcBxaT5LT1yr
=sjZY
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help needed for major SQLite problem

[Simon Slavin]

On 1 Sep 2013, at 10:34pm, C M  wrote:

> Do you know how I can do that with Python?  For example, I tried this:
> 
> status = cursor.execute("some SQL statement here")
> print "The status is: ", status
> 
> But it prints the cursor object:
> 
>> The status is 

Does the cursor object have properties ?  Can you either look through them in a 
debugger or find documentation somewhere that tells you if one of them is 
something like 'last error' ?

There should be a way to do it: the ability to read the code returned by API 
calls is essential to using SQLite properly.

With regard to your other posts about this problem, my guess is that you have 
some underlying error causing this that isn't part of SQLite.  The expected 
cause of this error is if someone deletes your database file (or a journal) 
while you have the database open.  There are other errors which can make the 
file handle invalid.  But I suspect that just as you wrote about your RAM, you 
actually have a hardware or OS problem which is the real culprit.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help needed for major SQLite problem

[Richard Hipp]

Does Python have an interface to the error and warning log mechanism of
SQLite?  (http://www.sqlite.org/errlog.html)  Can you turn that on?  It
will probably give more details about what it happening.


On Sun, Sep 1, 2013 at 5:32 PM, C M  wrote:

> On Sun, Sep 1, 2013 at 1:38 PM, Richard Hipp  wrote:
>
> > On Sun, Sep 1, 2013 at 1:12 PM, C M  wrote:
> >
> > >
> > > I have a Python application that uses SQLite, and I randomly get this
> > > error:
> > >
> > > "SQL logic error or missing database"
> > >
> > > I have no idea how to figure out what's wrong,
> > >
> >
> > A good starting place might be to tell us what the program is doing when
> > the error comes back.
> >
>
> I'm not 100% sure, because there are several SQLite calls that it could be
> tripping up on at that part of the program's use.  But all of them are
> basic SQL, and at the point in the code where I see the error, it is either
> SELECT or INSERT INTO statements.  (If it becomes necessary, I can try to
> put in a way to know right where it happens, though I'm not sure how to do
> that...see my response to Simon Slavin).
>
> Importantly, the exact same part of the code will work on one occasion, and
> on another occasion with the same "state" of the application, will not
> work.  That is, I can run my application, try something basic from a fresh
> start, this error will occur, close the application, try the same exact
> thing again, and that time it will work fine.  It occurs rather
> infrequently, but the point is I want to make sure it never occurs.
>
> The only thing I've noticed is that *maybe* it is more likely to happen
> when my laptop's RAM is pretty filled up with other processes (such as when
> Firefox runs high RAM and lots of other applications are open).  Could that
> put us on the trail of what could be going wrong?
>
> Thanks!
> Che
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] help needed for major SQLite problem

[C M]

On Sun, Sep 1, 2013 at 4:28 PM, jose isaias cabrera
wrote:

>
> "C M" wrote...
>
>
>  Keeping it simple:
>>
>> I have a Python application that uses SQLite, and I randomly get this
>> error:
>>
>> "SQL logic error or missing database"
>>
>>  Is the database in network drive or not in the same machine that is
> running the app?
>

The database is a file on the same hard drive that the app is on.  It's all
on a one laptop.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help needed for major SQLite problem

[C M]

On Sun, Sep 1, 2013 at 2:23 PM, Simon Slavin  wrote:

>
> On 1 Sep 2013, at 6:38pm, Richard Hipp  wrote:
>
> > A good starting place might be to tell us what the program is doing when
> > the error comes back.
>
> As well as telling us the call that trieggers the error, please put logic
> into your program so that it not only checks the result code of the call
> that generates the error but also checks to see that all earlier SQLite
> calls return SQLITE_OK when you expect them to.  Often the call that
> returns the error is after the one that caused the problem.
>

Do you know how I can do that with Python?  For example, I tried this:

status = cursor.execute("some SQL statement here")
print "The status is: ", status

But it prints the cursor object:

> The status is 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help needed for major SQLite problem

[C M]

On Sun, Sep 1, 2013 at 1:38 PM, Richard Hipp  wrote:

> On Sun, Sep 1, 2013 at 1:12 PM, C M  wrote:
>
> >
> > I have a Python application that uses SQLite, and I randomly get this
> > error:
> >
> > "SQL logic error or missing database"
> >
> > I have no idea how to figure out what's wrong,
> >
>
> A good starting place might be to tell us what the program is doing when
> the error comes back.
>

I'm not 100% sure, because there are several SQLite calls that it could be
tripping up on at that part of the program's use.  But all of them are
basic SQL, and at the point in the code where I see the error, it is either
SELECT or INSERT INTO statements.  (If it becomes necessary, I can try to
put in a way to know right where it happens, though I'm not sure how to do
that...see my response to Simon Slavin).

Importantly, the exact same part of the code will work on one occasion, and
on another occasion with the same "state" of the application, will not
work.  That is, I can run my application, try something basic from a fresh
start, this error will occur, close the application, try the same exact
thing again, and that time it will work fine.  It occurs rather
infrequently, but the point is I want to make sure it never occurs.

The only thing I've noticed is that *maybe* it is more likely to happen
when my laptop's RAM is pretty filled up with other processes (such as when
Firefox runs high RAM and lots of other applications are open).  Could that
put us on the trail of what could be going wrong?

Thanks!
Che
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help needed for major SQLite problem

[jose isaias cabrera]

"C M" wrote...


Keeping it simple:

I have a Python application that uses SQLite, and I randomly get this 
error:


"SQL logic error or missing database"

Is the database in network drive or not in the same machine that is running 
the app? 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help needed for major SQLite problem

[Simon Slavin]

On 1 Sep 2013, at 6:38pm, Richard Hipp  wrote:

> A good starting place might be to tell us what the program is doing when
> the error comes back.

As well as telling us the call that trieggers the error, please put logic into 
your program so that it not only checks the result code of the call that 
generates the error but also checks to see that all earlier SQLite calls return 
SQLITE_OK when you expect them to.  Often the call that returns the error is 
after the one that caused the problem.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] to encrypt sqlite db

[Ulrich Telle]

Am 01.09.2013 18:40, schrieb Etienne:

wxSQLite is free, while SEE is definitively not.


The original poster searched for a free encryption extension, of which 
there exist several: System.Data.SQLite (RC4), wxSQLite3 (AES-128 or 
AES-256), SQLCipher (AES-256 with nonce) to name a few.


Depending on the security requirements one of the free solutions may be 
good enough for the OP.



wxSQLite means "pseudo" encryption (as formerly discussed), while SEE is real 
encryption.


This statement is nonsense. Certainly SEE provides stronger encryption 
than wxSQLite3, however, the encryption used by wxSQLite3 is also "real" 
AES encryption (not "pseudo", which implies "easy to  break").


Regards,

Ulrich


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help needed for major SQLite problem

[Richard Hipp]

On Sun, Sep 1, 2013 at 1:12 PM, C M  wrote:

>
> I have a Python application that uses SQLite, and I randomly get this
> error:
>
> "SQL logic error or missing database"
>
> I have no idea how to figure out what's wrong,
>

A good starting place might be to tell us what the program is doing when
the error comes back.


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

[sqlite] help needed for major SQLite problem

[C M]

Keeping it simple:

I have a Python application that uses SQLite, and I randomly get this error:

"SQL logic error or missing database"

I have no idea how to figure out what's wrong, and if I can't figure it
out, it leaves a huge deal-breaking bug in my application, such that I'd
have to abandon SQlite and use another RDMS.  I'd hate that, as I love
SQLite and it is integrated deeply with my application.  But I can't have a
database application that occasionally just doesn't work, and I've put
years of work into this project and really need it to be robust.

Any help on this could potentially make a huge positive difference in my
efforts.  Thank you!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] to encrypt sqlite db

[Etienne]

wxSQLite (relevant part) and SEE are extensions to SQLite. 

wxSQLite is free, while SEE is definitively not.

wxSQLite means "pseudo" encryption (as formerly discussed), while SEE is real 
encryption.

What is weird???

Regards,
Etienne


- Original message -
From: Paolo Bolzoni 
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] to encrypt sqlite db
Date: Sun, 1 Sep 2013 18:24:13 +0200

Another weird sentence in the mailing list
of probably most used DB that is really free.

On Sun, Sep 1, 2013 at 6:10 PM, Etienne  wrote:
> On Sun, Sep 1, 2013, at 17:50, Clemens Ladisch wrote:
>> Ulrich Telle wrote:
>> > Am 31.08.2013 22:01, schrieb Etienne:
>> >> I simply wanted to warn the OP that wxSQLite, while free, does NOT use
>> >> salts:
>> >
>> > Well, that's not completely true. The encryption extension coming with
>> > wxSQLite3 uses a different IV (initial vector) for each database page.
>> > True is that the IVs are not random, but deduced from the page number.
>> > However, I don't see much difference between generating an IV
>> > algorithmic or using a random nonce which is stored at the end of each
>> > database page
>>
>>  says:
>> | Randomization is crucial for encryption schemes to achieve semantic
>> | security, a property whereby repeated usage of the scheme under the
>> | same key does not allow an attacker to infer relationships between
>> | segments of the encrypted message.
>>
>> Without a random IV/nonce, every page is guaranteed to encrypt to the
>> same data if the contents and the key have not changed.  Thus, wxSQLite3
>> gives an attacker the ability to determine whether any particular page
>> has changed, by comparing the old and new versions.  With SEE, rewriting
>> a page will encrypt to a different value because the IV changes even for
>> otherwise unchanged pages.
>>
>> > The weak point of probably all SQLite encryption methods is that the
>> > unencrypted content of the first 16 bytes of a SQLite database file is
>> > well known.
>>
>> Many file formats have fixed parts.  However, this is not a problem with
>> properly implemented encryption algorithms.
>>
>> Regards,
>> Clemens
>
> Amen.
>
> In this particular case, you get what you pay for.
>
> Regards,
> Etienne
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] to encrypt sqlite db

[Paolo Bolzoni]

Another weird sentence in the mailing list
of probably most used DB that is really free.

On Sun, Sep 1, 2013 at 6:10 PM, Etienne  wrote:
> On Sun, Sep 1, 2013, at 17:50, Clemens Ladisch wrote:
>> Ulrich Telle wrote:
>> > Am 31.08.2013 22:01, schrieb Etienne:
>> >> I simply wanted to warn the OP that wxSQLite, while free, does NOT use
>> >> salts:
>> >
>> > Well, that's not completely true. The encryption extension coming with
>> > wxSQLite3 uses a different IV (initial vector) for each database page.
>> > True is that the IVs are not random, but deduced from the page number.
>> > However, I don't see much difference between generating an IV
>> > algorithmic or using a random nonce which is stored at the end of each
>> > database page
>>
>>  says:
>> | Randomization is crucial for encryption schemes to achieve semantic
>> | security, a property whereby repeated usage of the scheme under the
>> | same key does not allow an attacker to infer relationships between
>> | segments of the encrypted message.
>>
>> Without a random IV/nonce, every page is guaranteed to encrypt to the
>> same data if the contents and the key have not changed.  Thus, wxSQLite3
>> gives an attacker the ability to determine whether any particular page
>> has changed, by comparing the old and new versions.  With SEE, rewriting
>> a page will encrypt to a different value because the IV changes even for
>> otherwise unchanged pages.
>>
>> > The weak point of probably all SQLite encryption methods is that the
>> > unencrypted content of the first 16 bytes of a SQLite database file is
>> > well known.
>>
>> Many file formats have fixed parts.  However, this is not a problem with
>> properly implemented encryption algorithms.
>>
>> Regards,
>> Clemens
>
> Amen.
>
> In this particular case, you get what you pay for.
>
> Regards,
> Etienne
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] to encrypt sqlite db

[Etienne]

On Sun, Sep 1, 2013, at 17:50, Clemens Ladisch wrote:
> Ulrich Telle wrote:
> > Am 31.08.2013 22:01, schrieb Etienne:
> >> I simply wanted to warn the OP that wxSQLite, while free, does NOT use
> >> salts:
> >
> > Well, that's not completely true. The encryption extension coming with
> > wxSQLite3 uses a different IV (initial vector) for each database page.
> > True is that the IVs are not random, but deduced from the page number.
> > However, I don't see much difference between generating an IV
> > algorithmic or using a random nonce which is stored at the end of each
> > database page
> 
>  says:
> | Randomization is crucial for encryption schemes to achieve semantic
> | security, a property whereby repeated usage of the scheme under the
> | same key does not allow an attacker to infer relationships between
> | segments of the encrypted message.
> 
> Without a random IV/nonce, every page is guaranteed to encrypt to the
> same data if the contents and the key have not changed.  Thus, wxSQLite3
> gives an attacker the ability to determine whether any particular page
> has changed, by comparing the old and new versions.  With SEE, rewriting
> a page will encrypt to a different value because the IV changes even for
> otherwise unchanged pages.
> 
> > The weak point of probably all SQLite encryption methods is that the
> > unencrypted content of the first 16 bytes of a SQLite database file is
> > well known.
> 
> Many file formats have fixed parts.  However, this is not a problem with
> properly implemented encryption algorithms.
> 
> Regards,
> Clemens

Amen.

In this particular case, you get what you pay for.

Regards,
Etienne

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] to encrypt sqlite db

[Clemens Ladisch]

Ulrich Telle wrote:
> Am 31.08.2013 22:01, schrieb Etienne:
>> I simply wanted to warn the OP that wxSQLite, while free, does NOT use
>> salts:
>
> Well, that's not completely true. The encryption extension coming with
> wxSQLite3 uses a different IV (initial vector) for each database page.
> True is that the IVs are not random, but deduced from the page number.
> However, I don't see much difference between generating an IV
> algorithmic or using a random nonce which is stored at the end of each
> database page

 says:
| Randomization is crucial for encryption schemes to achieve semantic
| security, a property whereby repeated usage of the scheme under the
| same key does not allow an attacker to infer relationships between
| segments of the encrypted message.

Without a random IV/nonce, every page is guaranteed to encrypt to the
same data if the contents and the key have not changed.  Thus, wxSQLite3
gives an attacker the ability to determine whether any particular page
has changed, by comparing the old and new versions.  With SEE, rewriting
a page will encrypt to a different value because the IV changes even for
otherwise unchanged pages.

> The weak point of probably all SQLite encryption methods is that the
> unencrypted content of the first 16 bytes of a SQLite database file is
> well known.

Many file formats have fixed parts.  However, this is not a problem with
properly implemented encryption algorithms.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] to encrypt sqlite db

[Mohit Sindhwani]

On 31/8/2013 9:52 PM, dd wrote:

Thank you for your quick response.

I am looking for freeware. If freeware not available, I have to implement
encryption support for sqlite on winrt.

What is the procedure to implement encryption support on winrt?

Thanks,
dd


Many others have replied with the couple of other known alternatives.  
Frankly, I find this insistence on "freeware" a bit flawed unless you 
plan to open source the final solution and want something that offers 
you a license to do so.  Assuming you get paid at least US$500 per 
month, I think you'll find that the solutions will be cheaper than 
actually implementing it yourself.  I am confident that you'll spend at 
least 4 man months to convince management, search other alternatives, 
figure out how encryption works, design a solution that works within 
SQLite, create tools for building the encrypted database, create code 
for querying the encrypted database, test the solution works well for 
you and then document it for other devs to use.  Instead of that, a 
US$2000 solution that is royalty free and can be used on any number of 
projects (as long as they are built at the same site IIRC), is a "steal".


On the other hand, maybe, your requirements are dead simple.  What do 
you want as "encryption support"?  Something that encrypts the full 
file, the content in some of the fields/ tables, or something that also 
supports querying such a database?  I don't think that the last one is 
quite that straightforward.  I would recommend you sit down someone from 
management and explain to them that what the cost tradeoffs are.


Best Regards,
Mohit.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] to encrypt sqlite db

[Ulrich Telle]

Am 31.08.2013 22:01, schrieb Etienne:

  On Sat, 31 Aug 2013 17:17:23 +0200
  Etienne  wrote:

  > > On the other hand removing patterns definitely cannot hurt.
  >
  > Precisely.
  >
  > The very first bytes of SQLite files are, AFAIK, well known.

  That's what salt is for, no?


"nonce", "IV", "salt" - call it whatever you want.  Yes.


I simply wanted to warn the OP that wxSQLite, while free, does NOT use
salts:


Well, that's not completely true. The encryption extension coming with 
wxSQLite3 uses a different IV (initial vector) for each database page. 
True is that the IVs are not random, but deduced from the page number. 
However, I don't see much difference between generating an IV 
algorithmic or using a random nonce which is stored at the end of each 
database page as SEE does according to the documentation to be found 
here: http://www.sqlite.org/see/doc/trunk/www/readme.wiki


In both cases you know the IV - at least if you have access to the code 
generating it (which is the case for wxSQLite3 as it is open source, but 
not for SEE as it is closed source).


The weak point of probably all SQLite encryption methods is that the 
unencrypted content of the first 16 bytes of a SQLite database file is 
well known. To get a better encryption maybe the first 16 bytes should 
always be kept unencrypted to not give a clue to a known pattern.



2 SQLite DBs built exactly the same way at different times are
byte-to-byte identical.


That's true. However, in real life it probably matters only for 
read-only databases. As soon as different people start to work with such 
an encrypted database, the database files will certainly deviate from 
each other considerably as it is very unlikely that all users perform 
their actions in exactly the same order.


BTW, you only get identical database files if you use the same 
passphrase. If the passphrase is hardcoded into the software then 
certainly this may impose a problem. However, for an application with 
high security demands you certainly shouldn't hardcode the passphrase 
into the software.


Regards,

Ulrich


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is SQLite a DBMS?

[Kees Nuyt]

tOn Sun, 1 Sep 2013 12:34:02 +0200, Paolo Bolzoni
 wrote:

>
> Wait a second, this is a mailing list where you need
> to register to write. Isn't it?

Yes, but he appears to have posted from nabble.
I guess nabble has a subscription.

> It means the OP actually registered 

Probably not

> but he did not
> try to seek for wikipedia sqlite in google?

Yes, that probably means he is young and has not learned how to do
research yet.

> It is just me or it is quite weird?

Weirdness is common on the internet.

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is SQLite a DBMS?

[Paolo Bolzoni]

Wait a second, this is a mailing list where you need
to register to write. Isn't it?

It means the OP actually registered but he did not
try to seek for wikipedia sqlite in google?

It is just me or it is quite weird?

On Sun, Sep 1, 2013 at 12:23 PM, Simon Slavin  wrote:
>
> On 1 Sep 2013, at 5:39am, kimtiago  wrote:
>
>> I need to know if SQLite is a DBMS and why.
>
> That's okay.  Just have your teacher post here and we'll tell them directly.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is SQLite a DBMS?

[Simon Slavin]

On 1 Sep 2013, at 5:39am, kimtiago  wrote:

> I need to know if SQLite is a DBMS and why.

That's okay.  Just have your teacher post here and we'll tell them directly.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is SQLite a DBMS?

[Stefan Keller]

Hi,

Wikipedia answers with yes and why (= because it's ACID and SQL compliant)
within the first three sentences!
http://en.wikipedia.org/wiki/SQLite

Yours, S.


2013/9/1 kimtiago 

> Hi,
>
> I need to know if SQLite is a DBMS and why.
>
> Please its urgent.
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/Is-SQLite-a-DBMS-tp70868.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is SQLite a DBMS?

[kimtiago]

Hi,

I need to know if SQLite is a DBMS and why.

Please its urgent.



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Is-SQLite-a-DBMS-tp70868.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users