#1 2011-07-14 07:49:50

array81
Member
From: Italy
Registered: 2010-07-23
Posts: 334

Encrypt database

In this moment I use TSQLRawBlob and Cypher function to encrypt sensitive data. However I'd like to password protect / encrypt the rest of the database.
Is it possible (only if the user want it, in alternative no)?

Inside the documentation I have found CreateSQLEncryptTable and ChangeSQLEncryptTablePassWord but I don't have understand if I can use for my purpose and how.

Offline

#2 2011-07-14 09:52:57

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 5,668
Website

Re: Encrypt database

Encryption can be set globally for the whole process, for all databases.

Call  CreateSQLEncryptTable('password1') globally before using the DB. It will encrypt the whole content at lowest level (i.e. WinRead/WinWrite functions).

If the password is not correct, SQLite will return an error complaining that the database is corrupted.

Use ChangeSQLEncryptTablePassWord() to change the password, e.g. ChangeSQLEncryptTablePassWord('filename.db3','password1','') to convert the database file into its default uncrypted version.

Offline

#3 2011-07-14 10:41:49

array81
Member
From: Italy
Registered: 2010-07-23
Posts: 334

Re: Encrypt database

OK, two question:
1) Can I handle the error if the password is incorrect?
2) Can I use CreateSQLEncryptTable and ChangeSQLEncryptTablePassWord at any time? or only with a black database?

Offline

#4 2011-07-14 11:41:56

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 5,668
Website

Re: Encrypt database

1) You can try to open the database file, and catch the exception.

2) Use ChangeSQLEncryptTablePassWord() with '' for old password on an existing uncrypted database to crypt it.
ChangeSQLEncryptTablePassWord() expects the database to be closed: it change the file content on disk.

Offline

#5 2011-07-14 22:06:10

array81
Member
From: Italy
Registered: 2010-07-23
Posts: 334

Re: Encrypt database

1) Can I know if a database is crypted because try to open it?
2) Can I use 2 different database, one crypted and one uncrypted at the same time (on the same application)? CreateSQLEncryptTable is global...

Offline

#6 2011-07-15 08:44:55

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 5,668
Website

Re: Encrypt database

1) You can't know if it's crypted before opening it.

2) Encryption is global. You can't mix encryption (and even passwords) on the same application.

Offline

#7 2011-07-15 09:21:33

array81
Member
From: Italy
Registered: 2010-07-23
Posts: 334

Re: Encrypt database

These are 2 bad news... smile

Offline

#8 2011-07-15 21:04:03

array81
Member
From: Italy
Registered: 2010-07-23
Posts: 334

Re: Encrypt database

Is the point 2 a SQLite limit or a Framework limit?

Offline

#9 2011-07-16 05:56:02

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 5,668
Website

Re: Encrypt database

array81 wrote:

Is the point 2 a SQLite limit or a Framework limit?

The framework add encryption at the WinRead/WinWrite level, i.e. globally when accessing the disk.

There is some possibilities to add encryption at the SQLite level, via some APIs, but it was much difficult to implement. The SQLite author even sell an encryption solution, for $2000. See http://www.hwaci.com/sw/sqlite/see.html

So we provided the WinRead/WinWrite trick as an easy global encryption mechanism.

What is wrong with all data to be encrypted?
It won't be slower.

Offline

#10 2011-07-16 09:56:21

array81
Member
From: Italy
Registered: 2010-07-23
Posts: 334

Re: Encrypt database

There is nothing wrong smile

In my application I use 2 database: one database is an archive (there are general information like countries list, zip code list, ...), the second database is an user database (with personal user information), he can choose which database use (the user can create more database if he want).

I'd like add the ability to encrypt the user databases, but only the users databases. But I think this is not possible.

I knew the solution of the SQLite author, but I thought it was possible to implement a solution in the framework. I thought it was possible to add the encryption of data when the framework writing in the single database.

In alternative I can encrypt the file after use and decrypt it before use it (in this way the database is not protect when the application use it besides to decrypt the database I need of a lag time if database is big file).

I do not really like the solution but is better than nothing.

Thanks anyway for the wonderful work.

Offline

#11 2011-07-22 09:41:25

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 5,668
Website

Re: Encrypt database

For your information, I've extracted the SQLite3 engine wrapper from SQLite3.pas into the new shared SynSQLite3.pas unit.

It will allow direct access to SQLite3, without our mORMot overhead/features.
For instance, I will create a direct access class following our SynDB hierarchy.

I've also added the function IsSQLite3File() in this new SynSQLite3 unit, which could help you knowing if a file is a valid SQLite3 or not. If it is not, and should be a SQLite3 database file, then it's probably an encrypted version of the file.

See http://synopse.info/fossil/info/75b5b81a88

Offline

#12 2011-07-27 07:52:50

array81
Member
From: Italy
Registered: 2010-07-23
Posts: 334

Re: Encrypt database

Thanks I think I will use it.

I have a question, I think a stupid question but I like know what you think about it.

You could interpose a "secure layer" between the framework and the database (sqlite or other) to encrypt the information before writing and decrypt the information after reading. My application just works in this way for some database file using your SynCrypto class.

In this way each database can have a password, ok this system would decrease the speed but first of all the speed would decrease only when activated and then I do not think to greatly reduce (except on very large databases).

There may be some problem when the user enables / disables the encryption but I do not think it is impossible.

I'd like know what do you think about it.

Offline

#13 2011-07-27 09:23:06

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 5,668
Website

Re: Encrypt database

array81 wrote:

You could interpose a "secure layer" between the framework and the database (sqlite or other) to encrypt the information before writing and decrypt the information after reading. My application just works in this way for some database file using your SynCrypto class.

I also thought about this.

It should be available at the transport level, not at the application nor the database level, IMHO.
At the application level, there is already the RESTful authentication.
At the database level, it would imply that all data will be BLOB, so you'll loose all SQL features (indexes, advanced queries...). Not a good idea IMHO.

At the transport level, you may use HTTPS instead of HTTP.
This is a trusted way of remote access.

Other transports (GDI messages, named pipes, direct access) are local, so security won't be an issue.

May be HTTPS is a bit too slow or difficult to implement.
What could be possible is to add basic encryption at the HTTP classes levels, i.e. use custom encryption+compression functions, just like Deflate or SynLZ are already implemented.
Using SynLZ is already some kind of encryption, because it make JSON reading more difficult.

At database level, I may add a new field/column type, like sftBlobEncrypted, which could be some kind of sftBlob (RawByteString), but with encryption at the database level. But I'm not sure it could be worth it. Better encrypt the whole database with the existing methods, if you don't use a remote Client-Server approach, and want your data file to be secured.

Offline

#14 2011-07-27 09:35:25

array81
Member
From: Italy
Registered: 2010-07-23
Posts: 334

Re: Encrypt database

I never expected to HTTPS, in my case this is not a problem and it is also not easy to steal data, I'd like have only the database (the file) encrypt. I would also like to avoid having all BLOB, however for some type of field like string is possible save the encrypt string and not a clear string. In fact for other types of fields (boolean, float, ...) might be a problem ...

Last edited by array81 (2011-07-27 09:36:30)

Offline

#15 2011-08-24 21:30:47

array81
Member
From: Italy
Registered: 2010-07-23
Posts: 334

Re: Encrypt database

Today I have found this: http://sqlcipher.net/
Can it help you to add transparent AES support for single database?

Offline

#16 2011-08-25 10:10:42

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 5,668
Website

Re: Encrypt database

Yes, could be worth a try.

But I'm not sure if it will compile with Borland C++.
Or you'll have to load it as an external library, if it's possible.

Offline

#17 2011-08-25 11:44:45

array81
Member
From: Italy
Registered: 2010-07-23
Posts: 334

Re: Encrypt database

Load an external DLL will not a problem.

Do you think you will try it?

I ask it because if you think you work on it I will wait some news from you before try to add AES support to my application.

Offline

#18 2011-08-25 12:41:39

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 5,668
Website

Re: Encrypt database

I took a look at the sources.

It has some drawbacks:
- Can't be used with the amalgation file;
- Need to be linked within the main SQlite3 engine, so you can't have an external dll with it;
- Use the OpenSll library (which is a bit oversized for our purpose);
- The license won't easily allow to be linked with GPL or LGPL as far as I understood it.

But it's well written, and it could give me some sample code to base a pure pascal version on it.

There will be some low-level needed calls to the SQlite3 engine to access the internal paging and BTree mechanism.

I think it could be possible to create a pure Delphi version of the encryption.
With SynCrypto, there is everything we need for strong AES encryption at base level.

So it's a nice start, and worth taking a look at it.

Offline

#19 2011-08-25 13:21:19

array81
Member
From: Italy
Registered: 2010-07-23
Posts: 334

Re: Encrypt database

This sound good, so I will wait for some new. Thanks

Offline

#20 2011-08-26 10:11:30

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 5,668
Website

Re: Encrypt database

I just tried to start a pure pascal implementation of the encryption...

It I found out that it should not be made easy, because it does depend of a lot of low-level internal structures.
So in order to create an encryption module, we should better code it in plain C.

It will be more work than expected.
So perhaps not in this release....

What's wrong with the full database file encryption already included in the unit?

Offline

#21 2011-08-26 10:52:14

coblongpamor
Member
From: Bali-Indonesia
Registered: 2010-11-07
Posts: 130
Website

Re: Encrypt database

ab wrote:

What's wrong with the full database file encryption already included in the unit?

how to use full database file encryption/decryption? so, just my app can use(read/write) to the database.
i tried to search in this forum and the documentation, but i can't found it, or maybe i don't understand.
can u give some simple code?

thank you very much.

Offline

#22 2011-08-26 11:54:37

array81
Member
From: Italy
Registered: 2010-07-23
Posts: 334

Re: Encrypt database

ab wrote:

What's wrong with the full database file encryption already included in the unit?

Nothing but I like can works both with encryph and decrypt database at the same application.

It is not urgent for me, if you think you can add this feature in the future I believe that many users can be useful to many users.
Use all encrypted or decrypted database is not very elastic.

Do you think my link can help you to add this features? (in the future)

Iif you can't I will see to invent something different.

Offline

#23 2011-08-26 13:57:02

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 5,668
Website

Re: Encrypt database

Note that even the encryption detailed in your link is still for the whole database level.

There is no per-table encryption available in SQLite, even for http://www.hwaci.com/sw/sqlite/see.html

What I can do is perhaps easily is to make a per-database encryption, instead of a whole process encryption.
You'll be able to mix both encrypted and not-encrypted database in the same application, just as with SEE or sqlcipher.net.

Does it make sense to you?

An ever more flexible way could be to define a new TSQLRawBlobEncrypted published property type (sftBlobEncrypted).
But it's not difficult to encrypt/decrypt a blob content when you retrieve it from the ORM.

Offline

#24 2011-08-26 14:23:21

array81
Member
From: Italy
Registered: 2010-07-23
Posts: 334

Re: Encrypt database

ab wrote:

Note that even the encryption detailed in your link is still for the whole database level.
There is no per-table encryption available in SQLite, even for http://www.hwaci.com/sw/sqlite/see.html

Yes I know, in fact my problem is not it.

On my application I'd like can use both encrypted and decripted database at the same time.

If I understand this not possible with SQLite Framework at this time. If I need use 3 database on my application I need to use 3 encrypted database or 3 decrypted database (I cannot work with 1 encrypted database and 2 decrypted database), right?

If I undestand (it is possible I'm wrong) sqlcipher.net use a transparent crypt system so the user can:
1) works with encrypted and decrypted databases at the same time (on the same application), all dabase in fact are independent;
2) continue to works with SQL features;

If I use TSQLRawBlob I cannot use SQL features.

That's all.

What do you think?

Last edited by array81 (2011-08-26 14:24:43)

Offline

#25 2011-08-26 15:38:33

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 5,668
Website

Re: Encrypt database

Here is what I've just committed:
http://synopse.info/fossil/info/b4e0ee9a01
and
http://synopse.info/fossil/info/e52f4f90fd

TSQLDatabase, TSQLRestClientDB and TSQLRestServerDB constructors now accept an optional Password parameter, associated to the supplied file name, in order to use encryption at the database level - still experimental

It's an awful hack (but much easier than SEE modules) at the file level.
The previous implementation was using encryption for all database files (still available), whereas this version will allow a database-level encryption.
You may now be able to have 1 encrypted DB and 2 uncrypted DB in the same application.

It's still experimental - I've not made any unit tests yet.

Note that I also discovered than encryption will work only with database files with their page size fixed to the default 1024 bytes value. For non encrypted files, the pragma page_size command will work as expected.

Offline

Board footer

Powered by FluxBB