Re: [sqlite] 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Encryption

[Richard Andersen]

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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] Encryption

[Richard Andersen]

I'm using the ADO.NET version (System.Data.SQlite).

I've created an RSA encrypted database using SQLite2009 and that is
working fine, but I can't find any tools for editing the table in
SQLite2009 once it's been created. Is it possible at all?

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?

thanks,
Richard

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

Re: [sqlite] Updating multiple records in C#

[Richard Andersen]

Wednesday, December 7, 2016, 3:12:05 AM, you wrote:


>> On Dec 6, 2016, at 5:40 PM, Richard Andersen <r...@taosoft.dk> wrote:
>> 
>> cmd.CommandText = @"UPDATE pdata SET FileName = @fileName WHERE FileName = 
>> 'filename.zip'";

> You can add other columns to set by adding more “name = value”
> expressions in between SET and WHERE.
> Here’s the documentation of the UPDATE command:
> http://www.sqlite.org/lang_update.html
> <http://www.sqlite.org/lang_update.html>

Ah - have already looked at these pages but ignored the diagrams as I
thought they were refering to some internal technical stuff, but now I
realize they're explaining the syntax (never seen it done that way
before). 

Thanks!
Richard

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

[sqlite] Updating multiple records in C#

[Richard Andersen]

I have a table with several thousand records each with multiple
fields. One field contains a filename, this is the only unique field
so I use it to identify the different records.

What I need is a way to update all fields that have changed in any
record, at once. There's no need to check IF a record has changed,
that's already been done; I have all the data for the changed records
in a jagged array and just need to update the corresponding table
records, using the file name as record identifier. I know how to do
this for one record and one field, in this case the file name field:

using (SQLiteConnection con = new SQLiteConnection(_connectionString))
{
SQLiteCommand cmd = new SQLiteCommand();

cmd.CommandText = @"UPDATE pdata SET FileName = @fileName 
WHERE FileName = 'filename.zip'";

cmd.Connection = con;
cmd.Parameters.Add(new SQLiteParameter("@fileName", "newfilename.zip"));
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}

But how do I do this for multiple records and mutiple fields? I
imagine I need to use SQLiteTransaction here but I'm not sure about
the syntax.


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

Re: [sqlite] Updating multiple records in C#

[Richard Andersen]

Wednesday, December 7, 2016, 9:15:57 AM, you wrote:

Thanks Ryan, this looks very useful, think I've got what I need now to
make it work!

Richard

>> But how do I do this for multiple records and mutiple fields? I
>> imagine I need to use SQLiteTransaction here but I'm not sure about
>> the syntax.

> It depends what kind of updates they are. If you want the same fields to
> be updated in the same way, you can simply add more update fields and 
> expand the WHERE clause to include all the afflicted records, like this:

> UPDATE pdata
> SET FileName = @filename, FileNumber = @filneo, SomeOtherField = @ 
> someotherfld
>   WHERE FileName IN ('example1.zip', 'example2.zip', 'example3.zip');

> If however the updates that should be done are different for each row,
> you have to do them one by one matching the correct update values 
> against the correct WHERE clause filter so that only the rows matching
> that specific filter are affected.  You CAN do this in an sql script /
> list normally, starting a transaction and adding all the different 
> UPDATE commands to the list (separated by semi-colons) and running it 
> all at once - although I think your data interface might not allow that,
> and you cannot bind into a list, so you have to populate the strings first.

> If executing a list /is/ allowed, you can do this for example:

> Do a BEGIN TRANSACTION first, if it succeeds, compile the following kind
> of list:
> 
> UPDATE pdata SET FileName = 'NewFilename1.zip', OtherFIeld = '1'  WHERE
> FileName = 'example1.zip';
> UPDATE pdata SET FileName = 'NewFilename2.zip', OtherFIeld = '2' WHERE
> FileName = 'example2.zip';
> UPDATE pdata SET FileName = 'NewFilename3.zip', OtherFIeld = '3' WHERE
> FileName = 'example3.zip';
> 

> Execute the list, and if it succeeds, do a COMMIT; - else do a ROLLBACK;
> and report the error somewhere.

> Good luck.
> Ryan


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

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