Encrypt database

array81 · 2011-10-09 20:09:48

Do you think there will be hope to implement encryption of individual database? If yes, is there a ETA? (I don't know your roadmap).

Phisatho · 2011-10-13 15:53:41

ab,
Is it possible to statically link with WxSqlite and use the encryption implemented be Wxsqlite? - just an idea.

ab · 2011-10-13 17:16:25

WxSQlite is just a wrapper around the official SQLite3 library.
It includes only encryption for the non free SQLite Encryption Extension (SEE) module - http://www.hwaci.com/sw/sqlite/see.html

The link http://wxforum.shadonet.com/viewtopic.php?t=27217 did not work for me.

Phisatho · 2011-10-13 18:53:29

It is not a wrapper over hwaci SEE but over standard SQlite with added encryption. My understanding is it is a standalone implementation of encryption. However it is compatible with Hwaci implement.
I have used the dll with ZeosDB successfully.

http://zeos.firmos.at/kb.php?mode=article&k=14

Phisatho · 2011-10-13 18:55:36

http://wxcode.sourceforge.net/components/wxsqlite3/

ab · 2011-10-14 04:59:15

Phisatho wrote:

It is not a wrapper over hwaci SEE but over standard SQlite with added encryption. My understanding is it is a standalone implementation of encryption. However it is compatible with Hwaci implement.

You are right: I did only check the official wxsqlite source code repository, whereas I should have checked in wxCode project.
I'll see what can be done with this code.

ab · 2011-10-17 14:41:08

I've commited some test about per-database encryption.
See TTestExternalDatabase.CryptedDatabase in SynSelfTests.pas.

According to these tests, the per-database process is working as expected.
As expected, the testpass.db3-wal file is not encrypted, but the main testpass.db3 file is (after the first 1024 bytes).

In the tests, I add more than 11,000 rows of data to a file, close it.
testpass.db3 is encrypted as expected.
Then I open testpass.db3, and read the rows and check their content.
Then a row is added.
The whole testpass.db3 content is retrieved again, and checked again.
Then the testpass.db3 is uncrypted using ChangeSQLEncryptTablePassWord.
And the whole .db3 content is retrieved again, and checked again.

Current implementation sounds fine to me.
Encrypted file can be sometimes opened in SQLite3 standard tools, but the content is not available.

See http://synopse.info/fossil/info/21747e8b91

array81 · 2011-10-17 15:40:25

Sorry but this is not correct for me.

After use the single file encrypt, close the file and open it with a SQLite editor (I use this free tool http://sqliteadmin.orbmu2k.de/) I can see ALL the data: both database structure and record text, it's all clear. In this moment I'm not at home, this evining I will try again.

Can you write a small piece of code about an essential "single file" encrypt? (I think the code inside the test file is complex).

Last edited by array81 (2011-10-17 15:51:40)

ab · 2011-10-17 16:03:10

How did you open / create the file?

The supplied test file is not complex.

You can easily extract the main logic.
The only difference is to set a "password" parameter.

Phisatho · 2011-10-17 17:52:08

It works for me.
This time even though testpass.db3 opens in sqlite3 browser, even the database structure is not visible.
Array, have you removed the old synopse directories from delphi library options?

Phisatho · 2011-10-17 18:13:08

Is it available to TSQLDBSQLite3ConnectionProperties.Create?

Phisatho · 2011-10-17 19:33:00

I was wrong.
even though sqlite browser does not list data, sqlite console shows the table and data - except that PC's bell is ringing throughout the listing.

Phisatho · 2011-10-17 19:49:36

Near the end of the test, the encryption is removed.
Try adding this line to the end of TTestExternalDatabase.CryptedDatabase; :
ChangeSQLEncryptTablePassWord('testpass.db3', '', password);

ab · 2011-10-17 20:01:40

Phisatho wrote:

Is it available to TSQLDBSQLite3ConnectionProperties.Create?

No, it is not yet.
And is was never documented as such, in fact the creator description tells it explicitely: "other parameters (DataBaseName, UserID, Password) are ignored".

I was not sure it was all working as expected.
So I did not include it in SynDBSQLite3 unit yet.

Perhaps in release 1.16.
If it is found stable enough.

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

Phisatho wrote:

Near the end of the test, the encryption is removed.

You are perfectly right: as I wrote above, it is part of the testing method.
It is written with encryption, then the database file is globaly uncyphered with ChangeSQLEncryptTablePassWord.

So the remaining file on disk after test is plain and not encrypted.
But it you do not execute ChangeSQLEncryptTablePassWord, the database file will stay encrypted. You may be able to see some content (e.g. the table layout), but the content itself will be definitively encrypted.

array81 · 2011-10-17 21:08:48

OK, so it possible I don't have understand how it works.

In my case I just have an database file (this is an clear file, NO encrypth) and I want encryph it. This is my code:

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

    finally
      Database.Free;
    end;
  except
     // error message
 end;

DBFileName is the name of the database, edtPassword.Text is the password (I use a TEdit to write the password).

After this code the database is clear.

Are there error on my code?

Last edited by array81 (2011-10-17 21:09:37)

Phisatho · 2011-10-18 06:02:30

You should use ChangeSQLEncryptTablePassWord before you connect with the database

ChangeSQLEncryptTablePassWord(DBFilename, '', edtPassword.Text);

ab · 2011-10-18 06:21:00

Phisatho wrote:

You should use ChangeSQLEncryptTablePassWord before you connect with the database
ChangeSQLEncryptTablePassWord(DBFilename, '', edtPassword.Text);

Indeed.
As stated by the documentation, and as shown in the test method supplied, it will encrypt the whole file at once, just as you need.

array81 · 2011-10-18 10:23:15

OK, I think now it works but I have a problem. I need know if I database is or not encryph (before open it). In fact if it encryph my application must ask the password.

I use this code IsSQLite3File(Filename), however this fuction return TRUE also with encryph database.

Is there a way to know if a database is encrypt or not?

ab · 2011-10-18 11:25:13

The first page (i.e. first 1024 bytes) of the database are uncrypted, but other content is.

array81 wrote:

Is there a way to know if a database is encrypt or not?

There was not.
I've added this function in SynSQLite3.pas:

/// check if sounds like an encrypted SQLite3 file
// - this will check the 2nd file page beginning to be a valid B-TREE page
// - in some cases, may return false negatives (depending on the password used)
function IsSQLite3FileEncrypted(const FileName: TFileName): boolean;

See http://synopse.info/fossil/info/97a90607ee

array81 · 2011-10-18 12:38:28

Thanks, I will try it.

If I understand I can use IsSQLite3File function to know if the file is a SQLite file (both encryph and decrypt) and IsSQLite3FileEncrypted to know if the database is encryph (used after IsSQLite3File).

On your comment I see "in some cases, may return false negatives (depending on the password used)", what does it mean? When the password is a problem?

ab · 2011-10-18 12:58:04

array81 wrote:

On your comment I see "in some cases, may return false negatives (depending on the password used)", what does it mean? When the password is a problem?

Only one byte is checked to be either 5, 10 or 13.
There may be some password content that may return such a value.
So it is not 100% sure.

array81 · 2011-10-18 17:16:48

I have a question, I think a stupid question but I cannot solve it.

I use this code:

try
  Database := TSQLRestClientDB.Create(Model, nil, Filename, TSQLRestServerDB, False, decrypt);
except   
  MessageDlg('ERROR',  mtInformation, [mbOK], 0);
  Exit;
end;

to open an encrypt file. I use try...except because I want capture the bad password error.
However, if I use the right password all works without problem, if I use a bad password I have only an exception inside Delphi (line 4001 of SynSQLite3 of last framework relaese) but outsite Delphi nothing.

I don't understand why my error message is not show.

ab · 2011-10-18 17:23:11

array81 wrote:

I don't understand why my error message is not show.

In fact, the database opening probably only use the first page of the file, i.e. the uncrypted one.
So opening may always be correct, even with a wrong password.

Just call a simple SELECT on a table and it will fail in case of invalid password.

Any other method will probably encourage very easy "brute force" password cracking.

Phisatho · 2011-11-11 06:04:19

Are you implementing the new encryption on to TSQLDBSQLite3ConnectionProperties.Create?

Any plans to link with Wxsqlite?

ab · 2011-11-11 06:06:30

Phisatho wrote:

Are you implementing the new encryption on to TSQLDBSQLite3ConnectionProperties.Create?

It is already the case.
See http://synopse.info/fossil/fdiff?v1=294 … ef20d53de0

Phisatho wrote:

Any plans to link with Wxsqlite?

If the current version works, there is no need to use the wxsqlite implementation, which is more complex to integrate.

Phisatho · 2011-11-11 19:22:53

It is already the case.

Thanks, tested - OK.

If the current version works, there is no need to use the wxsqlite implementation, which is more complex to integrate.

The advantage I see is that the resultant database will be compatible with other implementations, and that it camouflages the fact that the file is a sqlite database.

If I decrypt an encrypted database, the file seems to be not fully sqlite compatible. sqlite browser cannot open the file. sqlite console will read the file, but will keep the PC bell ringing.

w5m · 2014-11-13 14:55:18

I'm using the static SQLite3 wrapper (i.e. away from mORMot) and just wanted to check I'm using encryption correctly in the following code snippet...

procedure TForm1.connectButtonClick(Sender: TObject);
var
  conn: TSQLDBSQLite3ConnectionProperties;
  dbFilename: String;
  rows: Variant;
begin
  dbFilename := 'C:\Temp\bds';

  ChangeSQLEncryptTablePassword(dbFilename, '123', '');  // unencrypt tables

  conn := TSQLDBSQLite3ConnectionProperties.Create(dbFilename, '', '', '');
  try
    with conn.Execute('SELECT * FROM TableName', [], @rows) do
    begin
      while Step do
        ShowMessage(rows.Id + ': ' + rows.Description);
    end;
  finally
    FreeAndNil(conn);
  end;
 
  ChangeSQLEncryptTablePassword(dbFilename, '', '123');  // re-encrypt tables
end;

...or is there a better way?

By the way, it seems that CreateSQLEncryptTable isn't available as a public method in SynSQLite3Static.pas.

Any advice would be much appreciated.

Last edited by w5m (2014-11-13 14:55:48)

ab · 2014-11-13 15:52:36

What do you want to do?
Here you are decrypting then encrypting the whole file before use.
IMHO this is not what encryption is for.

To use encryption, just specify the password to TSQLDBSQLite3ConnectionProperties.Create() !
Then the file will stay encrypted on disk, as expected.

Note that you are using a TSQLDBSQLite3ConnectionProperties instance locally, together with an Execute() method which will return an hidden interface instance.
This is not correct, regarding how Delphi works.

w5m · 2014-11-13 20:13:23

Thanks for the quick response Arnaud.

What I'm trying to do is protect a dataset such that it cannot be browsed. I thought I had to unencrypt the database in order to query it, then re-encrypt it so that it remains unbrowsable by others.

Are you saying that I can throw away my calls to ChangeSQLEncryptTablePassword and instead pass the password as the 4th parameter of TSQLDBSQLite3ConnectionProperties.Create()? If so, does that mean the database needs to have been previously encrypted? If so, how?

You mentioned I'm not using the correct approach by using a TSQLDBSQLite3ConnectionProperties instance locally and the Execute method. What is the correct approach?

Apologies if I'm asking basic questions, but I'm very new to both SQLite and your units.

Any advice would be much appreciated.

ab · 2014-11-13 21:24:55

Yes, the parameter is enough.

ChangeSQLEncryptTablePassword() is enough to encrypt an existing file the first time.

w5m · 2014-11-14 15:13:17

Excellent - thanks for confirming Arnaud. I've got the encryption sorted now.

I found another of your posts which gave an example of how to structure the rest of the code correctly...
http://synopse.info/forum/viewtopic.php?pid=9397#p9397

Many thanks!

ab · 2014-11-14 15:30:55

Yes, the best is perhaps to maintain a TSQLConnectionProperties instance for the whole application.
It will feature some nice thinks like a faster re-use of an existing connection, statement cache, and per-thread connection, if needed.

mORMot Open Source

#51 2011-10-09 20:09:48

Re: Encrypt database

#52 2011-10-13 15:53:41

Re: Encrypt database

#53 2011-10-13 17:16:25

Re: Encrypt database

#54 2011-10-13 18:53:29

Re: Encrypt database

#55 2011-10-13 18:55:36

Re: Encrypt database

#56 2011-10-14 04:59:15

Re: Encrypt database

#57 2011-10-17 14:41:08

Re: Encrypt database

#58 2011-10-17 15:40:25

Re: Encrypt database

#59 2011-10-17 16:03:10

Re: Encrypt database

#60 2011-10-17 17:52:08

Re: Encrypt database

#61 2011-10-17 18:13:08

Re: Encrypt database

#62 2011-10-17 19:33:00

Re: Encrypt database

#63 2011-10-17 19:49:36

Re: Encrypt database

#64 2011-10-17 20:01:40

Re: Encrypt database

#65 2011-10-17 20:05:09

Re: Encrypt database

#66 2011-10-17 21:08:48

Re: Encrypt database

#67 2011-10-18 06:02:30

Re: Encrypt database

#68 2011-10-18 06:21:00

Re: Encrypt database

#69 2011-10-18 10:23:15

Re: Encrypt database

#70 2011-10-18 11:25:13

Re: Encrypt database

#71 2011-10-18 12:38:28

Re: Encrypt database

#72 2011-10-18 12:58:04

Re: Encrypt database

#73 2011-10-18 17:16:48

Re: Encrypt database

#74 2011-10-18 17:23:11

Re: Encrypt database

#75 2011-11-11 06:04:19

Re: Encrypt database

#76 2011-11-11 06:06:30

Re: Encrypt database

#77 2011-11-11 19:22:53

Re: Encrypt database

#78 2014-11-13 14:55:18

Re: Encrypt database

#79 2014-11-13 15:52:36

Re: Encrypt database

#80 2014-11-13 20:13:23

Re: Encrypt database

#81 2014-11-13 21:24:55

Re: Encrypt database

#82 2014-11-14 15:13:17

Re: Encrypt database

#83 2014-11-14 15:30:55

Re: Encrypt database

Board footer