Direct access to the plain DB driver over ZConnection

[ehoffman]

Hi,

is it possible to directly acces the functions of the plain dbc driver over the ZConnection component?

I have a ZConnection connected to a SQLite3 database and I need to call a function of the plain TZSQLitePlainDriver. Is this possible and how can I do it?

I'm using BDS 2006.

Thank you very much for your help.

Best regards, Eike

[mdaems]

You could try to use the TZConnection.DbcConnection.GetPlainDriver function that should return an IZSQLitePlainDriver.
If that works, please tell us (and demystify what you're trying to do).
(BTW : I didn't really try it so it's a guess based on the code)

Mark

[ehoffman]

Hi Mark,

at first: Thank you for your reply!

TZConnection.DbcConnection.GetPlainDriver is a protected (or private) function and I can't access it without modifying the source code of ZeosLib.

What I want to do is simple: I want to use SQLite3 as backend for my application to store some private user data. The problem is that the database needs to be encrypted (not everyone should be able to open it by simply doing sqlite test.db).

SQLite3 has such hooks to encrypt a database file in background (there is a commercial SQLite3 extension available from sqlite.org). The functions for this are:

- sqlite_key,
- sqlite_reky and
- sqlite_openencrypted.

Here http://sqlite.phxsoftware.com/ you can download a free modified version of SQLite3 (for MS .NET) - with implemented encryption hooks. You can use the System.Data.SQLite3.dll as replacement for sqlite3.dll - simply be renaming it (it works as usual).

Then I modified some Zeos files and added the three functions to the plain drivers. The last two functions where already there, only deactivated.

So now I try to:

1. Establish a connection with TZConnection to a SQLite3 function (works)
2. Call "PlainDriver.sqlite_key("MyPassword")

Then the database file should be transparently encrypted. The next problem will be to check if this also works when opening an already encrypted file or if I need to use "OpenEncrypted" for this (thus again modifying the source of Zeos).

The problem is: I'm programming with the C++ personality of BDS2006. I can read Delphi-Code and do some simple modifications, but I'm not very good in Delphi...

Best regards, Eike

[ehoffman]

Hi again,

so, TZConnection->DbcConnection is of Type "_di_IZConnection". How can I cast/convert this in C++ to "TZSQLiteConnection" (this has GetPlainDriver)? The usual ways "dynamic_cast", "static_cast", ... do not work.

Thank you!
Best regards, Eike

[mdaems]

Sorry Eike...
I'll not be able to answer your questions. So it will be up to you or maybe some other people to solve this. I'm not a sqlite user.

About the GetPlainDriver function, that I can handle by making the method public. But I'll only do that if that solves a real problem (so you'll first have to fix the other problems)

About the 'special features' I propose you try to implement it using the TZConnection Properties. That could be implemented in the zeoslib code as a SQLite specific thing, provided you can
- get it working
- catch errors when using these functionality on sqlite versions not using these functions

Have a look at TZMySQLConnection.Open (dbc/Zdbcmysql.pas). There we use it to set mysql specific flags.

Patches can be send here or the user patches forum. Maybe the last one is more safe to avoid that the patch gets lost in time.

Mark

[ehoffman]

Hi Mark,

the encryption in SQLite is transparent, that means there are hooks in the library which need to be implemented for working encrpytion.

If the hooks are implemented it's works (almost) without any required user actions. That means the SQLite library behaves as if there is no encryption. But when you call: sqlite_key, sqlite_rekey or open_encrypted the file is encrypted (while reading and writing the file). There are no visibile changes for the user in the way the api calls work.

If there is no encryption in the library the three functions are simply "NULL" pointer, so a call to them does do nothing - no errrors - the file is just not encrypted.

What I could do is:

- Extend ZConnection with a param "Encrypted=YES" (or something like that) - as you suggested

But then we also need the functions "sqlite_key and sqlite_rekey" to be exposed in ZConnection, because they are used to reset a password.

Will it be ok, if I do that? For the other databases enginges these calls would do (currently) nothing, but maybe this is changed in the future?

Best regards, Eike

[mdaems]

I agree on the first part. Add an property encrypt_key=... and if that's not empty open using the encryption statement. If I understand this well this should be sufficient to access an existing encrypted database.

For the second part : NO... but there's no problem adding it to the IZSQLiteConnection interface and TZSQLiteConnection class.
The reason : we can't allow the connection component to grow wild with methods that can only be implemented for one specific database server.

I'm sure the interface or class mentionned above must be useable from your program one way or another. Try this : (ZConnection.DbcConnection as IZSqliteConnection).GetPlaindriver (or instead of GetPlaindriver you could use a method you added to the sqlite specific connection interface.

Mark

[ehoffman]

Hi Mark,

I have it. The problem was my bad knowledge of Interface-Programming. I can call the GetPlainDriver like this:

Code: Select all

    ZConnection1->Connected = true;
    IZSQLiteConnection *C;              

    ZConnection1->DbcConnection->QueryInterface(
        Sysutils::StringToGUID("{A4B797A9-7CF7-4DE9-A5BB-693DD32D07D2}"),
        (void **)&C
    );

    C->GetPlainDriver()->ReKey(C->GetConnectionHandle(),
        ZConnection1->Password.c_str(), strlen(ZConnection1->Password.c_str()));

I will post a patch for the encryption to the seconed forum you mentioned above. Thank you againf for your help

best regards, Eike

[ehoffman]

Hi Mark,

I've created a patch and a description how it works, see here: http://zeos.firmos.at/viewtopic.php?t=1674

I think the patch can be integrated in the main version, because it does not break any functionality and it works with and without encryption support in SQLite.

But you should have a look at the code, as I said, I'm not a Delphi programmer...

Best regards, Eike

[krassonkel]

Just stumbled upon this because I wanted to ReKey an existing database with Zeos.

For the protocol (and maybe to be included into that knowledge base article), this is how it works in Delphi:

Code: Select all

var Conn: IZSQLiteConnection;
    Pass: string;
begin
  Pass := 'secret';
  Conn := (ZConnection1.DbcConnection as IZSQLiteConnection);
  Conn.GetPlainDriver.ReKey(Conn.GetConnectionHandle, PChar(Pass), StrLen(PChar(Pass)));
end;

[mdaems]

krassonkel,
Thanks. I added you remark to the knowledge base article.

Mark