Add database encryption/decryption

[2shady4u]

As requested by @MrSmite and many others, database encryption/decryption seems to be a much requested feature.

Work on this feature will be done on following branch: https://github.com/2shady4u/godot-sqlite/tree/godot-sqleet

Contained on this branch is a minimum (and rudimentary) viable product using SQLeet. Work on this feature will resume after other priority features (BLOBs, parameter bindings and database buffering) are completed.

Open questions/isues (mostly for myself):

• Does this work on android?
• Add proper compiler flags for SQLeet to expose the C API (= SQLITE_HAS_CODEC)
• Inclusion of SQLeet makes this project dependent on SQLeet keeping their SQLite up-to-date which might not be the best decision?

[MrSmite]

For people who may want to compile this branch, here is an important note from sqleet of a use case where opening the database will fail even if the password is correct. This feature can be disabled at compile time.

I broke up the quote so the bolded pieces would stand out better. Bold in a markdown quote isn't very bold.

https://github.com/resilar/sqleet#example

Note:

In sqleet, the contents of an encrypted database file are indistinguishable from random data (of the same length). This is a conscious design decision,

but as a drawback, database settings cannot be read from the database file. Therefore, it is the user's responsibility to properly initialize database settings before accessing the database.

The most common issue is that opening a database fails regardless of valid key because the page size of the database differs from the default 4096 and page_size has not been set to the correct value with PRAGMA or URI API.

The official SQLite Encryption Extension (SEE) leaves bytes 16..23 of the database header unencrypted so that page size and other settings can be directly read from encrypted databases, which obviously makes SEE-encrypted databases distinguishable from random data. In sqleet,

this behavior can be optionally enabled with -DSKIP_HEADER_BYTES=24 compile-time flag

(bytes 0..15 contain the KDF salt so only the bytes 16..23 are actually skipped and left unencrypted).

At run-time, the compile-time default can be overridden with URI parameter skip=n where n is the skip amount.

[2shady4u]

Added SQLeet as a submodule and did a proper definition of the SQLITE_HAS_CODEC flag.

[MrSmite]

Thanks for the update. I haven't had much chance to test it though but it looks promising.

[2shady4u]

Implemented compilation flag for skipping header bytes (SKIP_HEADER_BYTES=24) to avoid failure to open database due to wrong settings.

Also updated the source files to be compatible with latest master.

[hidemat]

Hi. Sorry to bother you, and please tell me if I'm out of line, but how goes merging of Sqleet and Fst5 to the main branch?

[2shady4u]

Hey @hidemat! Sorry about my inactivity regarding the encryption and virtual table implementation. I was burned-out on this project for several months and am slowly getting back into the vibe.

Currently I'm focusing development on porting the plugin to Godot 4.0 so that its ready whenever Godot 4.0 releases for real.

Regarding the implementation of encryption and virtual table implementation, I would have to get back up-to-date with what I did and what I was still planning to do.

There have been several issues that make/made this merge difficult:

• SQLite FTS5 adds shadow tables to the database which have to be removed whenever you export to JSON (an important feature for my dev purposes)
• The API that SQLeet gives is extremely tiny... there's basically only two functions that are exposed (opening the database with key & rekeying). There's no elegant way of checking if the database is actually encrypted + there's no clear feedback when you try to open a database with a wrong key (= it just returns a generic error)

I'm sorry for promising this features and then not delivering 🙏

[hidemat]

I understand, thanks for the update. I've been looking into forking the repo and getting the binaries from Github actions, but I have no clue how to run the workflows once I have forked the repositories and enabled-fts5. It makes me dizzy, I guess I'll keep trying to figure this out.

EDIT: OHHHH I think I figured it out. It runs on a "on: push" event. So I had to enable the workflows in the actions tab and THEN push to the repo. (Duh)

Looking forward to the Godot 4 port. Best of luck.

[xEstebanSalgadox]

:'c

[arabgamedev]

when you add encryption?

[2shady4u]

Hello everyone,

Due to lack of time, I won't be able to work on adding encryption in the near future. SQLeet is currently unmaintained (and might abandoned) due to changes in the latest versions of SQLite.

If you need encryption please check out the following fork of this project: https://github.com/abcjjy/godot-sqlcipher