[ KB ] Encryption support in SQLite with Zeos DBC components
Moderators: gto, bravecobra
- mdaems
- Zeos Project Manager
- Posts: 2766
- Joined: 20.09.2005, 15:28
- Location: Brussels, Belgium
- Contact:
[ KB ] Encryption support in SQLite with Zeos DBC components
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
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
- Zeos Project Manager
- Posts: 2766
- Joined: 20.09.2005, 15:28
- Location: Brussels, Belgium
- Contact:
[ KB ] Encryption support in SQLite with Zeos DBC components
Article Name: Encryption support in SQLite with Zeos DBC components
Author: mdaems
Description: Article by Eike Hoffmann (ehoffman)
Category: SQLite
Type:
>>Read Full Article
Author: mdaems
Description: Article by Eike Hoffmann (ehoffman)
Category: SQLite
Type:
>>Read Full Article
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)
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
- Zeos Project Manager
- Posts: 2766
- Joined: 20.09.2005, 15:28
- Location: Brussels, Belgium
- Contact:
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
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
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
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
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:
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).
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;
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).
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
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
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
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
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.
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.
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/
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/