How to integrate SQLite3MultipleCiphers with RSQLite ?

[JeremyPasco]

Hello,
I'm trying to integrate SQLite3MultipleCiphers into the R package RSQLite (package issue: r-dbi/RSQLite#484) but I think I miss some part of the picture.
As I understand, RSQLite use the amalgation source code of SQLite right now. I managed to replace it with the amalgation source code of SQLite3MultipleCiphers and to recompile the package without error.

The idea behind was that, maybe, the current R <--> C binding of the RSQLite package with SQLite would be enough to handle SQLite3MultipleCiphers.

But then, PRAGMA statements don't trigger encryption, DB is still in plain text. Am I missing something? Is there some bindings or other dev I should do to make it work?

[utelle]

I'm trying to integrate SQLite3MultipleCiphers into the R package RSQLite (package issue: r-dbi/RSQLite#484) but I think I miss some part of the picture. As I understand, RSQLite use the amalgation source code of SQLite right now. I managed to replace it with the amalgation source code of SQLite3MultipleCiphers and to recompile the package without error.

In principle, the SQLite3 Multiple Ciphers amalgamation source code can be used as a drop-in replacement for the official SQLite amalgamation source code. However, usually you need to specify some extra preprocessor symbols to make it work as you expect.

Without knowing which C preprocessor symbols you defined for compiling the source code, it is hard to tell whether the resulting library works as expected or not.

The idea behind was that, maybe, the current R <--> C binding of the RSQLite package with SQLite would be enough to handle SQLite3MultipleCiphers.

Again, in principle, it should not require any special handling, except for specifying the relevant URI parameters or executing the corresponding PRAGMA statements.

But then, PRAGMA statements don't trigger encryption, DB is still in plain text. Am I missing something? Is there some bindings or other dev I should do to make it work?

Well, your description is too vague for analyzing the problem, unfortunately.

1. If you want to create a new encrypted database, you will have to execute a PRAGMA key='passphrase' statement before accessing (reading or writing) any data in the database.
2. If you try to open an existing non-empty plain database, and execute a PRAGMA key='passphrase' statement, you will get errors like "file is not a database" when accessing the database file.
3. If you want to encrypt an existing non-empty database, you will have to execute PRAGMA rekey='passphrase' to actually encrypt the database file.
4. If you want to use a non-default cipher scheme, you have to set the cipher scheme parameters before executing PRAGMA key='passphrase'.

In principle, all encryption parameters can be set via the database URI. However, I don't know how the R SQLite interface handles URI parameters. If they are passed unchanged to the SQLite open function, then SQLite3 Multiple Ciphers will handle them properly. However, if the R SQLite interface performs other activities before SQLite3 Multiple Ciphers gets a chance to handle the URI parameters, this may explain your problems.

[JeremyPasco]

Thank you for your quick reply. As I am not a C developper, I feared that I could have missed some low level integration detail.
RSQLite already had C preprocessor working well with SQLite but doesn't handle URI parameters.

I just missed a compilation error with AES needing some compilation flag. Once fixed, PRAGMA statements work as intended through RSQLite + SQLite3MultiCiphers.
Thanks a lot for your amazing work!

[utelle]

As I am not a C developper, I feared that I could have missed some low level integration detail. RSQLite already had C preprocessor working well with SQLite but doesn't handle URI parameters.

Enabling URI parameters in SQLite makes it usually a lot easier to configure non-default ciphers for SQLite3 Multiple Ciphers, because you don't need to explicitly perform separate PRAGMA statements in a specific order.

I just missed a compilation error with AES needing some compilation flag. Once fixed, PRAGMA statements work as intended through RSQLite + SQLite3MultiCiphers.

Glad to hear that.