Encryption

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Encryption

Denis Burke
I am not sure about sqlite2009, but you edit DBs that are encrypted using
the encryption in system.data.sqlite.  I use SQLITE Expert. It allows you
to replace the standard library which does not support encryption to one
from the system.data.sqlite that does. It is the interop dll that you
replace. Then after entering the password, the encryption and decryption
becomes transparent to you.

Richard Andersen wrote:
In DB Browser for SQlite I can edit the table but I'm not sure if the
SQLCipher encryption used here can be made to work with
System.Data.SQlite, or how to do if it can. Does anyone know anything
about this?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Encryption

Richard Andersen
Sunday, November 13, 2016, 2:16:40 PM, you wrote:

Thanks. Well what I've done is to create an encrypted database with
SQLite2009 and then use that in my C# project. I just add the password to
the connection string in my app and then it works right away. As far as
I understand (I'm new to all this) you can also create a database from
within your app if it's based on system.data.sqlite.

I just can't edit the table columns or add new ones in SQLite2009 once
the database is saved or reopened, I can only edit the record data. I
just read that once created, you can't (or only very limited) edit the
columns of an SQLite database, you have to create a new database with
the desired structure and copy the data over. I think that's what DB
Browser for SQLite does since you actually can reopen and edit the
columns and their parameters etc. with it. It can also create
encrypted databases but the encryption scheme it uses is not supported
by system.data.sqlite it seems.

But all in all it works fine, I'm just a bit concerned with the RSA
encryption in system.data.lite as I've heard it's slow and easy to
crack, so I'd prefer something else. I haven't notice any speed
problems so far though, I can insert 5500+ records each with 14
columns in a second or so.

SQLITE Expert looks interesting but I wonder if it also uses the
built-in RSA encryption in system.data.sqlite?

> I am not sure about sqlite2009, but you edit DBs that are encrypted using
> the encryption in system.data.sqlite.  I use SQLITE Expert. It allows you
> to replace the standard library which does not support encryption to one
> from the system.data.sqlite that does. It is the interop dll that you
> replace. Then after entering the password, the encryption and decryption
> becomes transparent to you.

> Richard Andersen wrote:
> In DB Browser for SQlite I can edit the table but I'm not sure if the
> SQLCipher encryption used here can be made to work with
> System.Data.SQlite, or how to do if it can. Does anyone know anything
> about this?
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Encryption

Ulrich Telle
Richard,

> Well what I've done is to create an encrypted database with
> SQLite2009 and then use that in my C# project. I just add the password to
> the connection string in my app and then it works right away.

According to the information on the SQLite2009 website (http://sqlite2009pro.azurewebsites.net/) SQLite2009 supports 2 encryption methods:

** Encryption Method is now compatible with wxSQLite3 (AES-128 bits) and SQLite3 ADO.NET Provider (RSA-MS Crypt) **

I don't know SQLite2009 from own experience, but I assume that it allows you to choose which enryption method to use, when creating a new database. To be compatible with the ADO.NET provider System.Data.SQLite (http://system.data.sqlite.org) you obviously have to choose the corresponding encryption method in SQLite2009.

> As far as
> I understand (I'm new to all this) you can also create a database from
> within your app if it's based on system.data.sqlite.
> I just can't edit the table columns or add new ones in SQLite2009 once
> the database is saved or reopened, I can only edit the record data. I
> just read that once created, you can't (or only very limited) edit the
> columns of an SQLite database, you have to create a new database with
> the desired structure and copy the data over.

I have a bit the impression that you mixed up the terms 'database' and 'table'. A 'database' can contain several tables, and adding new tables or removing existing tables is simple (and should be supported by any SQLite administration tool). However, changing the structure of an existing table in SQLite is more complicated, since SQLite only supports a limited set of operations to modify a table definition. Therefore, if you want to add or remove columns from a table definition, you usually have to create a new table with a different name, copy the data from the previous table to the new one, remove the previous table, and rename the new table to the previous name.

> I think that's what DB Browser for SQLite does since you actually can
> reopen and edit the columns and their parameters etc. with it.

Under the hood DB Browser for SQLite performs the above mentioned steps for you.

> It can also create encrypted databases but the encryption scheme it uses
> is not supported by system.data.sqlite it seems.

Correct. DB Browser for SQLite supports SQLCipher (https://www.zetetic.net/sqlcipher/), an AES-256 encryption scheme.

> But all in all it works fine, I'm just a bit concerned with the RSA
> encryption in system.data.lite as I've heard it's slow and easy to
> crack, so I'd prefer something else.

The RSA encryption offered by System.Data.SQLite should not be used, if security is a concern for you. You should prefer an AES encryption scheme.

> SQLITE Expert looks interesting but I wonder if it also uses the
> built-in RSA encryption in system.data.sqlite?

According to the description on the website (http://www.sqliteexpert.com/features.html) SQLiteExpert "Supports password protected databases (requires third party SQLite library - not included)." That is, you have to provide a SQLite3 DLL supporting the encryption scheme of your choice. For example,

- SQLCipher (https://github.com/sqlcipher/sqlcipher), you have to build the DLL yourself
- wxSQLite3 (https://github.com/utelle/wxsqlite3/releases), Windows binaries are provided

Other SQLite3 management tools that support the wxSQLite3 encryption scheme are

- SQLite Maestro (https://www.sqlmaestro.com/en/products/sqlite/maestro/about/)
- wxSQLitePlus (https://github.com/guanlisheng/wxsqliteplus)

> > Richard Andersen wrote:
> > In DB Browser for SQlite I can edit the table but I'm not sure if the
> > SQLCipher encryption used here can be made to work with
> > System.Data.SQlite, or how to do if it can. Does anyone know anything
> > about this?

In principle, it should be possible to replace the SQLite encryption implementation in System.Data.SQLite by the SQLCipher or wxSQLite3 implementation, although it might not be trivial. The latter should be easier to accomplish, since the wxSQLite3 encryption implementation is self-contained, while SQLCipher requires the OpenSSL library as well.

Regards,

Ulrich
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Encryption

Chris Locke
Ulrich- a fantastically detailed post.

On Mon, Nov 14, 2016 at 3:23 PM, Ulrich Telle <[hidden email]> wrote:

> Richard,
>
> > Well what I've done is to create an encrypted database with
> > SQLite2009 and then use that in my C# project. I just add the password to
> > the connection string in my app and then it works right away.
>
> According to the information on the SQLite2009 website (
> http://sqlite2009pro.azurewebsites.net/) SQLite2009 supports 2 encryption
> methods:
>
> ** Encryption Method is now compatible with wxSQLite3 (AES-128 bits) and
> SQLite3 ADO.NET Provider (RSA-MS Crypt) **
>
> I don't know SQLite2009 from own experience, but I assume that it allows
> you to choose which enryption method to use, when creating a new database.
> To be compatible with the ADO.NET provider System.Data.SQLite (
> http://system.data.sqlite.org) you obviously have to choose the
> corresponding encryption method in SQLite2009.
>
> > As far as
> > I understand (I'm new to all this) you can also create a database from
> > within your app if it's based on system.data.sqlite.
> > I just can't edit the table columns or add new ones in SQLite2009 once
> > the database is saved or reopened, I can only edit the record data. I
> > just read that once created, you can't (or only very limited) edit the
> > columns of an SQLite database, you have to create a new database with
> > the desired structure and copy the data over.
>
> I have a bit the impression that you mixed up the terms 'database' and
> 'table'. A 'database' can contain several tables, and adding new tables or
> removing existing tables is simple (and should be supported by any SQLite
> administration tool). However, changing the structure of an existing table
> in SQLite is more complicated, since SQLite only supports a limited set of
> operations to modify a table definition. Therefore, if you want to add or
> remove columns from a table definition, you usually have to create a new
> table with a different name, copy the data from the previous table to the
> new one, remove the previous table, and rename the new table to the
> previous name.
>
> > I think that's what DB Browser for SQLite does since you actually can
> > reopen and edit the columns and their parameters etc. with it.
>
> Under the hood DB Browser for SQLite performs the above mentioned steps
> for you.
>
> > It can also create encrypted databases but the encryption scheme it uses
> > is not supported by system.data.sqlite it seems.
>
> Correct. DB Browser for SQLite supports SQLCipher (
> https://www.zetetic.net/sqlcipher/), an AES-256 encryption scheme.
>
> > But all in all it works fine, I'm just a bit concerned with the RSA
> > encryption in system.data.lite as I've heard it's slow and easy to
> > crack, so I'd prefer something else.
>
> The RSA encryption offered by System.Data.SQLite should not be used, if
> security is a concern for you. You should prefer an AES encryption scheme.
>
> > SQLITE Expert looks interesting but I wonder if it also uses the
> > built-in RSA encryption in system.data.sqlite?
>
> According to the description on the website (http://www.sqliteexpert.com/
> features.html) SQLiteExpert "Supports password protected databases
> (requires third party SQLite library - not included)." That is, you have to
> provide a SQLite3 DLL supporting the encryption scheme of your choice. For
> example,
>
> - SQLCipher (https://github.com/sqlcipher/sqlcipher), you have to build
> the DLL yourself
> - wxSQLite3 (https://github.com/utelle/wxsqlite3/releases), Windows
> binaries are provided
>
> Other SQLite3 management tools that support the wxSQLite3 encryption
> scheme are
>
> - SQLite Maestro (https://www.sqlmaestro.com/en/products/sqlite/maestro/
> about/)
> - wxSQLitePlus (https://github.com/guanlisheng/wxsqliteplus)
>
> > > Richard Andersen wrote:
> > > In DB Browser for SQlite I can edit the table but I'm not sure if the
> > > SQLCipher encryption used here can be made to work with
> > > System.Data.SQlite, or how to do if it can. Does anyone know anything
> > > about this?
>
> In principle, it should be possible to replace the SQLite encryption
> implementation in System.Data.SQLite by the SQLCipher or wxSQLite3
> implementation, although it might not be trivial. The latter should be
> easier to accomplish, since the wxSQLite3 encryption implementation is
> self-contained, while SQLCipher requires the OpenSSL library as well.
>
> Regards,
>
> Ulrich
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Encryption

Jens Alfke-2
In reply to this post by Ulrich Telle

> On Nov 14, 2016, at 7:23 AM, Ulrich Telle <[hidden email]> wrote:
>
> The RSA encryption offered by System.Data.SQLite should not be used, if security is a concern for you. You should prefer an AES encryption scheme.

I’m surprised anything encrypts databases with RSA, as that algorithm is pretty unsuitable for file encryption. It’s an asymmetric (public-key) cipher: you encrypt data with an RSA public key, which can then only be decrypted by the owner of the matching private key. (This is used by email encryption, for example.) Or you can sign data with the private key so that it can be verified by anyone who has the public key. Neither of these modes is useful for securing a file that you are going to use yourself. Moreover, RSA is extremely slow.

File encryption uses a symmetric-key cipher, of which AES is the most common up-to-date one. Both SQLite’s own encryption engine and the 3rd party SQLCipher use AES.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Encryption

Ulrich Telle
> > On Nov 14, 2016, at 7:23 AM, Ulrich Telle wrote:
> >
> > The RSA encryption offered by System.Data.SQLite should not be used,
> > if security is a concern for you. You should prefer an AES encryption scheme.
>
> I’m surprised anything encrypts databases with RSA, as that algorithm
> is pretty unsuitable for file encryption.

Ouch ... sorry for not resolving the confusion ... I've been taken again by the *wrong* citation of RSA encryption.

In fact, System.Data.SQLite acquires an RSA provider, but doesn't use key exchange anywhere. Only the supported symmetric encryption algorithm is used. And that algorithm is *RC4*.

> It’s an asymmetric (public-key) cipher: you encrypt data with an RSA
> public key, which can then only be decrypted by the owner of the
> matching private key. (This is used by email encryption, for example.)
> Or you can sign data with the private key so that it can be verified
> by anyone who has the public key. Neither of these modes is useful for
> securing a file that you are going to use yourself. Moreover, RSA is
> extremely slow.

True. However, RC4 is fast, but also weak and should therefore be avoided.

> File encryption uses a symmetric-key cipher, of which AES is the most
> common up-to-date one. Both SQLite’s own encryption engine and the
> 3rd party SQLCipher use AES.

The wxSQLite3 encryption extension also uses AES, either AES-128 or AES-256.

Regards,

Ulrich
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Encryption

Richard Andersen
In reply to this post by Ulrich Telle
Monday, November 14, 2016, 4:23:49 PM, you wrote:

Thanks a million Ulrich for all the useful information, I think I got
what I need now to make things work the way I want! :)

Richard

> Richard,

>> Well what I've done is to create an encrypted database with
>> SQLite2009 and then use that in my C# project. I just add the password to
>> the connection string in my app and then it works right away.

> According to the information on the SQLite2009 website
> (http://sqlite2009pro.azurewebsites.net/) SQLite2009 supports 2 encryption methods:

> ** Encryption Method is now compatible with wxSQLite3 (AES-128
> bits) and SQLite3 ADO.NET Provider (RSA-MS Crypt) **

> I don't know SQLite2009 from own experience, but I assume that it
> allows you to choose which enryption method to use, when creating a
> new database. To be compatible with the ADO.NET provider
> System.Data.SQLite (http://system.data.sqlite.org) you obviously
> have to choose the corresponding encryption method in SQLite2009.

>> As far as
>> I understand (I'm new to all this) you can also create a database from
>> within your app if it's based on system.data.sqlite.
>> I just can't edit the table columns or add new ones in SQLite2009 once
>> the database is saved or reopened, I can only edit the record data. I
>> just read that once created, you can't (or only very limited) edit the
>> columns of an SQLite database, you have to create a new database with
>> the desired structure and copy the data over.

> I have a bit the impression that you mixed up the terms 'database'
> and 'table'. A 'database' can contain several tables, and adding new
> tables or removing existing tables is simple (and should be
> supported by any SQLite administration tool). However, changing the
> structure of an existing table in SQLite is more complicated, since
> SQLite only supports a limited set of operations to modify a table
> definition. Therefore, if you want to add or remove columns from a
> table definition, you usually have to create a new table with a
> different name, copy the data from the previous table to the new
> one, remove the previous table, and rename the new table to the previous name.

>> I think that's what DB Browser for SQLite does since you actually can
>> reopen and edit the columns and their parameters etc. with it.

> Under the hood DB Browser for SQLite performs the above mentioned steps for you.

>> It can also create encrypted databases but the encryption scheme it uses
>> is not supported by system.data.sqlite it seems.

> Correct. DB Browser for SQLite supports SQLCipher
> (https://www.zetetic.net/sqlcipher/), an AES-256 encryption scheme.

>> But all in all it works fine, I'm just a bit concerned with the RSA
>> encryption in system.data.lite as I've heard it's slow and easy to
>> crack, so I'd prefer something else.

> The RSA encryption offered by System.Data.SQLite should not be
> used, if security is a concern for you. You should prefer an AES encryption scheme.

>> SQLITE Expert looks interesting but I wonder if it also uses the
>> built-in RSA encryption in system.data.sqlite?

> According to the description on the website
> (http://www.sqliteexpert.com/features.html) SQLiteExpert "Supports
> password protected databases (requires third party SQLite library -
> not included)." That is, you have to provide a SQLite3 DLL
> supporting the encryption scheme of your choice. For example,

> - SQLCipher (https://github.com/sqlcipher/sqlcipher), you have to build the DLL yourself
> - wxSQLite3 (https://github.com/utelle/wxsqlite3/releases), Windows binaries are provided

> Other SQLite3 management tools that support the wxSQLite3 encryption scheme are

> - SQLite Maestro
> (https://www.sqlmaestro.com/en/products/sqlite/maestro/about/)
> - wxSQLitePlus (https://github.com/guanlisheng/wxsqliteplus)

>> > Richard Andersen wrote:
>> > In DB Browser for SQlite I can edit the table but I'm not sure if the
>> > SQLCipher encryption used here can be made to work with
>> > System.Data.SQlite, or how to do if it can. Does anyone know anything
>> > about this?

> In principle, it should be possible to replace the SQLite
> encryption implementation in System.Data.SQLite by the SQLCipher or
> wxSQLite3 implementation, although it might not be trivial. The
> latter should be easier to accomplish, since the wxSQLite3
> encryption implementation is self-contained, while SQLCipher
> requires the OpenSSL library as well.

> Regards,

> Ulrich
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users