Encrypt database

array81 · 2011-07-14 07:49:50

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.

ab · 2011-07-14 09:52:57

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.

array81 · 2011-07-14 10:41:49

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?

ab · 2011-07-14 11:41:56

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.

array81 · 2011-07-14 22:06:10

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...

ab · 2011-07-15 08:44:55

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.

array81 · 2011-07-15 09:21:33

These are 2 bad news...

array81 · 2011-07-15 21:04:03

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

ab · 2011-07-16 05:56:02

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.

array81 · 2011-07-16 09:56:21

There is nothing wrong

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.

ab · 2011-07-22 09:41:25

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

array81 · 2011-07-27 07:52:50

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.

ab · 2011-07-27 09:23:06

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.

array81 · 2011-07-27 09:35:25

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)

array81 · 2011-08-24 21:30:47

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

ab · 2011-08-25 10:10:42

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.

array81 · 2011-08-25 11:44:45

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.

ab · 2011-08-25 12:41:39

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.

array81 · 2011-08-25 13:21:19

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

ab · 2011-08-26 10:11:30

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?

coblongpamor · 2011-08-26 10:52:14

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.

array81 · 2011-08-26 11:54:37

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.

ab · 2011-08-26 13:57:02

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.

array81 · 2011-08-26 14:23:21

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)

ab · 2011-08-26 15:38:33

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.

array81 · 2011-08-26 17:10:54

Good What about SQL features? Works again?

ab · 2011-08-26 17:27:04

??

An encrypted database will work just as usual.
Encryption is made at the hard disk file level.

array81 · 2011-08-26 19:21:28

Perfect, so you don't use the TSQLRawBlob but a "transparent encrypt". I will try it tomorrow. Thanks.
I will reply with my feedback.

array81 · 2011-08-27 23:13:03

I have not had time to test the new version.

Some question:
1) TSQLDatabase, TSQLRestClientDB and TSQLRestServerDB constructors now accept an optional Password parameter, but there is a way to decrypt the database?
2) Is there a way to know if a file is or not encrypted?
3) This is a question about another topic. I'd like add to many of my table 2 new field "create" and "modification", on "create" I'd like add the date of new record, on "modification" I'd like add the date of last modification of record. Can the framework automatically handles this kind of fields? Or I have to manually manage the fields (with 2 simple TDatetime).

Thanks

ab · 2011-08-28 16:22:36

1a) The password is needed to access the database, just as usual - encryption is made on the fly.
1b) The whole database file has the same format as with the "global" encryption scheme. You can use the same procedure to change the password for a database file - password = '' equals no encryption

2) No direct way, because the 1024 first bytes are not encrypted

3) In revision 1.15 there is a new TModTime published property / sftModTime SQL field - see http://synopse.info/fossil/info/19607da0cf
I didn't think of a "TCreateTime" property, but it may be useful in some apps - I'll add it tomorrow.

array81 · 2011-08-31 13:27:53

Hi,

I'm try to understand how I can edit my application to add the encrypt support, I have some question about it:
1) I can encrypt and/or decrypt (with password = "") only I open a database, right? I cannot encrypt and/or decrypt it after open it, right?
2) Can I change the password of encrypted database? or I need decrypt and then re-encrypt it with a different password?
3) If I need only set password="" to decrypt, without use the old password, the database is not secure, I misunderstood?

Thanks

ab · 2011-08-31 14:12:38

1) The password has to be specified when the database is opened.
You can not change the password on an opened database.

2) To change the password, you'll need to call the ChangeSQLEncryptTablePassWord procedure on a closed DB file.

3) ChangeSQLEncryptTablePassWord() with NewPassword='' will change the DB file to be a standard SQlite3 file, not secure any more.

array81 · 2011-09-01 20:27:36

Hi,

today I have try to use this code to open a database:

Database := TSQLRestClientDB.Create(Model, nil, DBFileName, TSQLRestServerDB, False, edtPassword.Text);

The database is open but to encrypt...

Is there a bug on the source code or an error on my code?

Thanks

ab · 2011-09-02 05:17:14

Did you get the latest version from http://synopse.info/fossil ?

array81 · 2011-09-02 09:40:56

Yes, I use the last version.

After open the database with a test password "pippo" (italian name of one of Walt Disney characters ), and close it I can open the database without password.

Besides if I try to open the dabase with an SQLite editor I see all data. The database is clear.

array81 · 2011-09-05 09:59:38

Today I updated my version of the framework. I thought I could be wrong and not have the latest version.
But the result is the same, the database is not encrypt.

array81 · 2011-09-14 16:44:45

Do you have test it?

Phisatho · 2011-09-24 19:30:47

Synopse with static linking and encryption fits my bill perfectly.
However, ORM is beyond me. Can you please provide a simple CRUD example with SQL?

Array81, I suspect that the fifth parameter (aHandleUserAuthentication) need to be true. ab, any comments?

ab · 2011-09-25 06:26:15

About the aHandleUeserAuthentication parameter, this is about URI-level authentication, not DB encryption.
So it is not the issue in this case, I guess.

About CRUD and SQLite3 with static binding, see the new SynSQLite3 unit and the associated SynDBSQLite3 unit.
With this last one, you can write easily SQL statements using SQLite3, able to change with any other DB engine (direct Oracle or any other via OleDB) just by changing a class.
About SQL itself, there are plenty of information on the Net, including http://sqlite3.org

Phisatho · 2011-09-27 15:54:43

Thanks ab for the help. I was able to create a simple example using SynDBSQLite3.

However, the problem array81 reported is confirmed. My code below:

procedure TForm1.Button1Click(Sender: TObject);
var
   VarRec: array of TVarRec;
begin
   SQLite3DB := TSQLDBSQLite3ConnectionProperties.Create('Test.db3', '', '', '123');
   SQLite3DB.ExecuteNoResult('create table if not exists mytable (a integer, b integer);', VarRec);
   SQLite3DB.ExecuteNoResult('INSERT INTO mytable VALUES (1, 2);', VarRec);
end;

The database is successfully created, but I can open the file in a sqlite browser without any password.
ab, can you please look into it?

Phisatho · 2011-09-28 15:48:39

I tried CreateSQLEncryptTable.
The data is encrypted. However, the table structure is still visible from sqlite browser. Is that the way it is supposed to be or am I missing something?

procedure TForm1.Button1Click(Sender: TObject);
var
   VarRec: array of TVarRec;
begin
   CreateSQLEncryptTable('123');
 //  ChangeSQLEncryptTablePassWord('test.db3','123',''); 
   SQLite3DB := TSQLDBSQLite3ConnectionProperties.Create('Test.db3', '', '', '');
   SQLite3DB.ExecuteNoResult('create table if not exists mytable (a integer, b integer);', VarRec);
   SQLite3DB.ExecuteNoResult('INSERT INTO mytable VALUES (1, 2);', VarRec);
   SQLite3DB.ExecuteNoResult('INSERT INTO mytable VALUES (3, 4);', VarRec);
   SQLite3DB.ExecuteNoResult('INSERT INTO mytable VALUES (5, 62);', VarRec);
   SQLite3DB.ExecuteNoResult('INSERT INTO mytable VALUES (7, 8);', VarRec);
   
end;

I dont understand what that TVarRec array is doing in a no-result-query
I am wondering why TSQLDBSQLite3ConnectionPropertiesis named so and not TSQLDBSQLite3Connection.

Last edited by Phisatho (2011-09-30 19:42:17)

ab · 2011-09-29 05:23:42

When you use CreateSQLEncryptTable, the first file page is not encrypted, as stated by the documentation.
Since this first page is mostly 0 bytes, it may reduce a lot the security, due to the standard quick&dirty encryption mechanism used by default.
So the table structure will be visible in sqlite browser, but you won't be able to read it .

About ExecuteNoResult(), I don't get your point here.
This is defined as

function ExecuteNoResult(const aSQL: RawUTF8; const Params: array of const): integer;

so Params is a standard array of constants, just like in

function Execute(const aSQL: RawUTF8; const Params: array of const; RowsVariant: PVariant=nil): ISQLDBRows;

By default, you put [] here and [ParamVarOne,ParamVarTwo,...] if you use some ? parameters to the SQL statement.

About TSQLConnection / TSQLConnectionProperties purpose, see the documentation and the comments in the code.
In short:
- TSQLConnectionProperties classes maintain the connection parameters;
- TSQLConnection instanciate an actuall connection to the DB - you may have multiple TSQLConnection instances (e.g. one per thread) for the same TSQLConnectionProperties settings.

Thanks for your interest

Phisatho · 2011-09-30 19:37:27

Hi ab,
Thanks for the help.
However, I am facing a new problem now. I am not able to attach a :memory: database.
The code:

DBsUser.ExecuteNoResult('ATTACH DATABASE ":memory:" AS Memfile;', [VarRec]);

I am getting the error:
"SQL logic error or missing database"

ab · 2011-10-01 09:35:54

Of course, you need to open an existing database before running this command.
You need something to attach your :memory: database to.

Did you try with single quotes ' ?

attach database ':memory:' as MemFile

And you can get rid of this unneeded [VarRec] and replace it with [].

Phisatho · 2011-10-01 14:09:30

I circumvented the issue bu making :memory: as the main database which involved extensive changes to the code. I will do further tests later and let you know.
Single quotes within single quotes is not allowed in Delphi.

ab · 2011-10-01 19:02:22

Phisatho wrote:

Single quotes within single quotes is not allowed in Delphi.

It is, you have to put '' instead.

array81 · 2011-10-05 19:29:00

Is there some hope to see the single database encrypt working?

Phisatho · 2011-10-08 17:04:21

If you are in a hurry, ZeosDBO is a good solution.
It provides sqlite3 encryption when used sqlite3.dll from WxSqlite or System.Data.SQLite. My experience is that WxSqlite is superior to the later.
See http://zeos.firmos.at/viewtopic.php?t=1 … ecb3efde22
Development is more or less stalled at the moment, however, the library is very stable (IMHO).
The only drawback I experienced is that static linking of sqlite is not available.

array81 · 2011-10-08 17:54:07

All my software is based on SQLite Framework, so I'd like use it for encryption.

ab · 2011-10-09 17:42:05

I do not have good experience with ZeosDB.

In particular, the Oracle implementation was in fact not usable about performance.

I tried to use Zeos as the primary DB layer of mORMot, then rewrite another set of classes, because Zeos just did not make it.
In particular, there was too many methods and too many datatypes handled.

About SQLite3 encryption, the http://www.hwaci.com/sw/sqlite/see.html (the official external encryption ready dll) is a paid library.
And http://sqlcipher.net/ is open source but do not provide any dll to be used directly.

About System.Data.SQLite, you can use it with our SynDB classes without any problem.
But it will IMHO slower than our direct SQLite3 access (via SynDBSQLite3).

I'll take a look a the database-level encryption.
In all case, the global unit-wide encryption already implemented in our SynSQLite3 unit works well.

mORMot Open Source

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

Encrypt database

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

Re: Encrypt database

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

Re: Encrypt database

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

Re: Encrypt database

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

Re: Encrypt database

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

Re: Encrypt database

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

Re: Encrypt database

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

Re: Encrypt database

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

Re: Encrypt database

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

Re: Encrypt database

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

Re: Encrypt database

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

Re: Encrypt database

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

Re: Encrypt database

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

Re: Encrypt database

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

Re: Encrypt database

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

Re: Encrypt database

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

Re: Encrypt database

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

Re: Encrypt database

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

Re: Encrypt database

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

Re: Encrypt database

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

Re: Encrypt database

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

Re: Encrypt database

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

Re: Encrypt database

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

Re: Encrypt database

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

Re: Encrypt database

#26 2011-08-26 17:10:54

Re: Encrypt database

#27 2011-08-26 17:27:04

Re: Encrypt database

#28 2011-08-26 19:21:28

Re: Encrypt database

#29 2011-08-27 23:13:03

Re: Encrypt database

#30 2011-08-28 16:22:36

Re: Encrypt database

#31 2011-08-31 13:27:53

Re: Encrypt database

#32 2011-08-31 14:12:38

Re: Encrypt database

#33 2011-09-01 20:27:36

Re: Encrypt database

#34 2011-09-02 05:17:14

Re: Encrypt database

#35 2011-09-02 09:40:56

Re: Encrypt database

#36 2011-09-05 09:59:38

Re: Encrypt database

#37 2011-09-14 16:44:45

Re: Encrypt database

#38 2011-09-24 19:30:47

Re: Encrypt database

#39 2011-09-25 06:26:15

Re: Encrypt database

#40 2011-09-27 15:54:43