Re: [sqlite] SQLite Encryption Extension Performance?

[Adolfo J . Millan]

>
>  Mensaje original 
> De: Simon Slavin <slav...@bigfraud.org>
> Para:  a...@zator.com,General Discussion of SQLite Database 
> <sqlite-users@sqlite.org>
> Fecha:  Thu, 28 Jun 2012 15:43:26 +0200
> Asunto:  Re: [sqlite] SQLite Encryption Extension Performance?
>
> 
>
>
>On 28 Jun 2012, at 12:12pm, ajm@zatorcom wrote:
>

>I hope Richard's answer tells you what you want to know.  He runs the
>team that makes SEE.
>

Of course I know, DRH is the pope in this matter :-)

>Just in case you're actually looking for this because you're used to it
>with other DBMSen, I wondered whether you might be asking not about
>encrypted storage on disk but about the communications between your app
>and the database server.  Having these communications encrypted is a
>requirement for some installations and I'm used to being asked this
>question.
>

No I,m using SQLite from some years ago, and mine is just a desktop app. Any 
way thanks for your advice.

I must think about the solution proposed in the DRH response.

--
Adolfo


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

Re: [sqlite] SQLite Encryption Extension Performance?

[Simon Slavin]

On 28 Jun 2012, at 12:12pm, a...@zator.com wrote:

> Assuming using SEE, do is there the ability to decide what I/O operations are 
> to be performed encrypted or with the plain (as is) content?

I hope Richard's answer tells you what you want to know.  He runs the team that 
makes SEE.

Just in case you're actually looking for this because you're used to it with 
other DBMSen, I wondered whether you might be asking not about encrypted 
storage on disk but about the communications between your app and the database 
server.  Having these communications encrypted is a requirement for some 
installations and I'm used to being asked this question.

SQLite does not have a client/server architecture or anything like it.  Unless 
you make special provision for it, all access to the data is done inside the 
process of your application which asks for the operation.  Any communication 
between your app and a database server or service takes place inside your CPU.  
The only server-related concern would be if you are accessing a database file 
using file-sharing over a network.  In that case, if you are using SEE and the 
database is encrypted, any data passing over your network will be encrypted at 
the page level, which makes it difficult even to identify database records and 
fields, let alone what was requested or changed by an operation.  It's as 
secure as AES-256 (or whatever you're using) can be.

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

Re: [sqlite] SQLite Encryption Extension Performance?

[Richard Hipp]

On Thu, Jun 28, 2012 at 7:12 AM, <a...@zator.com> wrote:

> Hi:
>
> Assuming using SEE, do is there the ability to decide what I/O operations
> are to be performed encrypted or with the plain (as is) content?
>

Encryption is turned on and off at the database level.  So either an entire
database file is completely encrypted or none of it is.  You cannot encrypt
some rows and leave others plaintext.

But you can use the ATTACH <http://www.sqlite.org/lang_attach.html> command
to connect two or more database files to your database connection at the
same time, so that they look to your application as if they are a single
file, and some subset of those files can be encrypted while another subset
is plaintext.  So if you can arrange your schema such that encrypted
content goes in one set of tables, and you put those tables in one database
file, and your plaintext data goes in a separate set of tables and those
tables go in a different database file, then you can have some content
encrypted and other content not encrypted.


>
> I'm planning an application with only selected data encripted (in each row
> of certain tables, there is a flag showing the encrypted/plain state).
>
> Thanks in advance.
>
> >
> >  Mensaje original 
> > De: Richard Hipp <d...@sqlite.org>
> > Para:  Paul Vercellotti <pverce...@yahoo.com>, General Discussion of
> SQLite Database <sqlite-users@sqlite.org>
> > Fecha:  Wed, 27 Jun 2012 21:28:30 +0200
> > Asunto:  Re: [sqlite] SQLite Encryption Extension Performance?
> >
> >
> >SEE is a drop-in replacement for public-domain SQLite.  In other words, it
> >will read and write ordinary unencrypted database files, and it will do so
> >with no speed penalty.
> >
> >For performance sensitive applications, what developers sometimes do is
> >break up their content into sensitive and non-sensitive, storing each in
> >separate databases, and only encrypt the sensitive content.  SEE is able
> to
> >open both databases at once (using the ATTACH command) and do joins on
> >tables between the two databases, even though only one of the two is
> >encrypted.
> >
>
>
> ___
> 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] SQLite Encryption Extension Performance?

[ajm]

Hi:

Assuming using SEE, do is there the ability to decide what I/O operations are 
to be performed encrypted or with the plain (as is) content?

I'm planning an application with only selected data encripted (in each row of 
certain tables, there is a flag showing the encrypted/plain state).

Thanks in advance.

>
>  Mensaje original 
> De: Richard Hipp <d...@sqlite.org>
> Para:  Paul Vercellotti <pverce...@yahoo.com>, General Discussion of SQLite 
> Database <sqlite-users@sqlite.org>
> Fecha:  Wed, 27 Jun 2012 21:28:30 +0200
> Asunto:  Re: [sqlite] SQLite Encryption Extension Performance?
>
>
>SEE is a drop-in replacement for public-domain SQLite.  In other words, it
>will read and write ordinary unencrypted database files, and it will do so
>with no speed penalty.
>
>For performance sensitive applications, what developers sometimes do is
>break up their content into sensitive and non-sensitive, storing each in
>separate databases, and only encrypt the sensitive content.  SEE is able to
>open both databases at once (using the ATTACH command) and do joins on
>tables between the two databases, even though only one of the two is
>encrypted.
>


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

Re: [sqlite] SQLite Encryption Extension Performance?

[Richard Hipp]

On Wed, Jun 27, 2012 at 2:55 PM, Paul Vercellotti wrote:

>
>
> Hi there,
>
> We are considering using the SQLite Encryption Extension in one of our
> products, and are wondering what the performance characteristics of it are?
>   Does the encryption algorithm affect performance?   Any stats on this you
> might have would be useful.
>

SEE is a drop-in replacement for public-domain SQLite.  In other words, it
will read and write ordinary unencrypted database files, and it will do so
with no speed penalty.  If you enable encryption, however, SQLite has to
run your chosen encryption algorithm whenever content is read from disk, or
written to disk.  Whether or not this effects performance, and by how much,
depends heavily on your application (how much it uses the database), which
encryption algorithm you choose, and on the relative speeds of CPU versus
I/O on your target platform.

Your worst-case performance hit is probably going to be about 50%.  In
other words, a query that used to take 100us would now take 150us with AES
128-bit encryption enabled, the extra fifty microseconds being time spend
running the encryption/decryption algorithms.  This worst case is for
queries that have to do actual disk I/O.  Queries out of SQLite's internal
cache use pre-decrypted content and do not slow down at all.  A typical
application will sometimes hit the cache and sometimes go to disk,
resulting in a performance hit somewhere in between.

For performance sensitive applications, what developers sometimes do is
break up their content into sensitive and non-sensitive, storing each in
separate databases, and only encrypt the sensitive content.  SEE is able to
open both databases at once (using the ATTACH command) and do joins on
tables between the two databases, even though only one of the two is
encrypted.



>
> Thanks!
>
> -Paul
> ___
> 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] SQLite Encryption Extension Performance?

[Etienne]

Hi Paul,

Are you speaking of CEROD?

FYI, I use to link sqlite3.c with the free wxSQLite3 encryption (AES128/256) 
module (http://wxcode.sourceforge.net/components/wxsqlite3).

I didn't notice any significant slowing down (3-4% max.), but of course there 
is no compression involved...

Hope this helps.

Regards,
Etienne


- Original message -
From: Paul Vercellotti 
To: General Discussion of SQLite Database 
Subject: [sqlite] SQLite Encryption Extension Performance?
Date: Wed, 27 Jun 2012 11:55:21 -0700 (PDT)

Hi there,

We are considering using the SQLite Encryption Extension in one of our 
products, and are wondering what the performance characteristics of it are?   
Does the encryption algorithm affect performance?   Any stats on this you might 
have would be useful.

Thanks!

-Paul
___
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