[ KB ] Encryption support in SQLite with Zeos DBC components

[mdaems]

Article Name: Encryption support in SQLite with Zeos DBC components
Author: mdaems
Description: Article by Eike Hoffmann (ehoffman)

Category: SQLite
Type:


>>Read Full Article

Article Name: Encryption support in SQLite with Zeos DBC components
Author: mdaems
Description: Article by Eike Hoffmann (ehoffman)

Category: SQLite
Type:


>>Read Full Article

[mdaems]

Article Name: Encryption support in SQLite with Zeos DBC components
Author: mdaems
Description: Article by Eike Hoffmann (ehoffman)

Category: SQLite
Type:


>>Read Full Article

[Phisatho]

Encryption does not work for an attached database when the main database is of type :memory:
Encryption on attached database works when the main database is file based - even if one of the attached databases is memory resident.

I am using System.Data.SQLite DLL.
I was not able to make wxSQLite3 work at all.

It would have been great if encryption along with static linking of sqlite3 was available on Zeos. (May be I am asking too much. I understand that Zeos development is currently stalled)

[mdaems]

Phisatho,
Zeoslib development is more or less stalled, yes. Because there's not a lot of developers who are willing to invest time in it.
I just changed the SVN repository back to sourceforge to make it easier for me to grant update rights to new volunteers. Just let me know when you're ready.
I remember there was a proposal for adding static linking. But I can't remember what the problem (or objection) was.

Mark

[seawolf]

If you need something like this

ZConnection1.Database := ':memory:';
ZConnection1.Protocol := 'sqlite-3';
ZConnection1.Password := 'test';
ZConnection1.Properties.Text := 'encrypted=true';
ZConnection1.Connect;

At the moment this gives you an error

But changing on ZdbcSqlite.pas

procedure TZSQLiteConnection.Open;

ErrorMessage := '';

to

ErrorMessage := nil;

All works as expected

THe problem is related to CheckSQLiteError which needs a nil ErrorMessage

[Phisatho]

Seawolf,
Thanks for the help. That removed the irritating error that pops up when ever I run the code.
However, Zeos still creates the attached database un-encrypted when main database is :memory:

Code: Select all

con.Database:= ':memory:';
Con.Connect;
Con.ExecuteDirect('ATTACH DATABASE "File.db3" AS Savefile;');
Con.ExecuteDirect('CREATE TABLE IF NOT EXISTS SaveFile.Points(' +
		 'ID INTEGER PRIMARY KEY,' +
		 'Name TEXT, Tdb REAL, Twb REAL, RH REAL, Phi REAL, W REAL, H REAL, V REAL, Tdp REAL);');
QR.SQL.Text := 'CREATE TABLE IF NOT EXISTS MYTABLE (num INTEGER, text1 TEXT, TEXT2 TEXT);';
QR.SQL.Add('INSERT INTO MYTABLE (num, text1, text2) VALUES (10, "mytext1", "mytext2");');
QR.ExecSQL;

mdaems,
I am unable to offer help as I am not a programmer. No formal education in programming.
Just trying to put together some simple applications (Well, not so simple to my standards).

[seawolf]

Which dll are you using? sqllite3.dll does not support encryption
and System.Data.SQLite DLL too seems not support it

[Phisatho]

I am using System.Data.SQLite DLL.
It encrypts Ok as long as the main Database is a file.
The only trouble is when the main database is :memory:, any attached database will be silently created without any encryption - and it does not spitout any error messages.
The workaround I am using is to create a dummy database as the connection's main database and attach the memory and other file based databases to the connection. (In my situation, I cannot have my file based database as the connection's main database)

btw, I could not find a sqlite database browser with encryption support. So I slightly tinkered the 'embedded' example that comes with Zeos to make one (sort of).
If anybody is interested,please let me know.

Regards

Shibu

[seawolf]

Hi, can you provide an example?

I mean I don't understand this sentence

The only trouble is when the main database is :memory:, any attached database will be silently created without any encryption - and it does not spitout any error messages.

Can you post the internet site where you found System.Data.SQLite DLL, and the package name? I found 2 sites and several packages

[Phisatho]

The site mentioned in the tutorial
http://sourceforge.net/projects/sqlite- ... p/download
This site has a few different versions of the DLL. Only one of them works with Zeos. The correct one is 904,704 bytes long.

See the code I quoted above. If I do
Connection.database := 'mainfile.db3';
Connection.ExecuteDirect('ATTACH DATABASE "File.db3" AS SecondFile;');
Encryption and decryption works as expected on both mainfile.db3 and file.db3.

However, if I do
Connection.database := ':memory:';
Connection.ExecuteDirect('ATTACH DATABASE "File.db3" AS SecondFile;');
File.db3 is created without encryption. (What it does with memory database is irrelevant)

Connection.database := 'file.db3';
Connection.ExecuteDirect('ATTACH DATABASE ":memory:" AS memoryFile;');
works fine

My problem is that I can create file.db3 only much later in the code. (File.db3 is used to archive when the user decides to save his work.)
The connection cannot be used to attach a database unless a database is assigned to the connection.

My workaround is:
Connection.database := 'dummy.db3';
Connection.ExecuteDirect('ATTACH DATABASE ":memory:" AS memoryFile;');
and later in the code:
Connection.ExecuteDirect('ATTACH DATABASE "file.db3" AS secondFile;');

Here dummy.db3 just lies in the folder without any useful purpose.

Hope I explained the issue properly.

[Phisatho]

Update:
Wxsqlite is working - latest update (2.1.3 - 16/08/2011).
It encrypts even when main database is :memory:
http://sourceforge.net/projects/wxcode/ ... wxSQLite3/