Page 1 of 1
[ KB ] Encryption support in SQLite with Zeos DBC components
Posted: 08.08.2008, 22:53
by 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
[ KB ] Encryption support in SQLite with Zeos DBC components
Posted: 09.07.2009, 23:14
by 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
Posted: 30.08.2011, 20:05
by 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)
Posted: 30.08.2011, 20:45
by 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
Posted: 30.08.2011, 21:19
by 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
Posted: 31.08.2011, 17:26
by 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).
Posted: 01.09.2011, 18:00
by seawolf
Which dll are you using? sqllite3.dll does not support encryption
and System.Data.SQLite DLL too seems not support it
Posted: 01.09.2011, 19:03
by 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
Posted: 01.09.2011, 22:01
by 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
Posted: 02.09.2011, 09:18
by 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.
Posted: 03.09.2011, 19:01
by 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/