[ KB ] Encryption support in SQLite with Zeos DBC components

All about the Doxygen documentation for Zeoslib.
Links to the downloads and online version.

Moderators: gto, bravecobra

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

[ KB ] Encryption support in SQLite with Zeos DBC components

Postby mdaems » 08.08.2008, 22:53

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
Image

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

[ KB ] Encryption support in SQLite with Zeos DBC components

Postby mdaems » 09.07.2009, 23:14

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

Category: SQLite
Type:


>>Read Full Article
Image

Phisatho
Fresh Boarder
Fresh Boarder
Posts: 7
Joined: 29.07.2011, 10:53

Postby Phisatho » 30.08.2011, 20:05

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)

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 30.08.2011, 20:45

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
Image

seawolf
Zeos Dev Team *
Zeos Dev Team *
Posts: 385
Joined: 04.06.2008, 19:50
Contact:

Postby seawolf » 30.08.2011, 21:19

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
Fresh Boarder
Fresh Boarder
Posts: 7
Joined: 29.07.2011, 10:53

Postby Phisatho » 31.08.2011, 17:26

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
Zeos Dev Team *
Zeos Dev Team *
Posts: 385
Joined: 04.06.2008, 19:50
Contact:

Postby seawolf » 01.09.2011, 18:00

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

Phisatho
Fresh Boarder
Fresh Boarder
Posts: 7
Joined: 29.07.2011, 10:53

Postby Phisatho » 01.09.2011, 19:03

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
Zeos Dev Team *
Zeos Dev Team *
Posts: 385
Joined: 04.06.2008, 19:50
Contact:

Postby seawolf » 01.09.2011, 22:01

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
Fresh Boarder
Fresh Boarder
Posts: 7
Joined: 29.07.2011, 10:53

Postby Phisatho » 02.09.2011, 09:18

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
Fresh Boarder
Fresh Boarder
Posts: 7
Joined: 29.07.2011, 10:53

Postby Phisatho » 03.09.2011, 19:01

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/


Return to “Documentations”

Who is online

Users browsing this forum: No registered users and 1 guest