[sql] SQLCipher Encryption

[tunecino]

Hi and thank you for everything you are doing. I just started using Tauri (+ this plugin) for a side Angular project and results were amazing so far.

One thing left to figure out, my app will be downloadable for free, with the condition to only use it in a single machine. My logical thinking for it is to encrypt the database using some per-device unique ID. So I was wondering if is it possible to use SQLCipher. Digging a bit into sqlx, I found this thread linking a great sample repo: https://github.com/launchbadge/sqlx/issues/2009

It seems possible by overriding libsqlite3-sys dependencies to use bundled SQLCipher:

# optional = false - force to build the package with sqlcipher feature enabled
libsqlite3-sys = { version = "0.24.1", optional = false, default-features = false, features = [
     "bundled-sqlcipher"
] }

But I know nothing about RUST or SQLCipher and may be totally wrong. Is it possible to use SQLCipher through this library? can it be initialized with a key from the main.rs file?

Any help on choosing the right direction will be appreciated. Thank you.

[FabianLars]

I think we need to expose the .pragma() method for this to work 🤔

[philipphoberg]

Exposing .pragma() would also be lovely, to enable Foreign-Key support for sqlite. Or am I missing a way of doing this already.

Thanks in advance.

[bryancusatis]

Would also like this feature. A password protected embedded database would be useful.

[eleroy]

I've implemented a simple fork with a simple key encryption (no advanced pragma stuff).

https://github.com/eleroy/plugins-workspace/tree/v2/plugins/sql

If anyone needs it. It adds a key entry to the plugin conf that you can use to define you database key. It's only implemented in the v2 branch

[Sparkenstein]

@eleroy will you be opening PR to merge this? otherwise it'll be a good idea to mention this in somewhere like awesome-tauri

[eleroy]

I opened a PR,

I changed the way it works, it is now similar to the way migrations are handled and I have added support for other pragmas.

[luis-cicada]

Hi @eleroy first, crack 👍 !!

Is this working for v2?

I'm trying to implemented but I'm getting file is not a database error

[eleroy]

Hi,

Yes it is only implemented for v2.

It was not working anymore but I synched the fork and now it should work (try cargo update in src-tauri).

Don't forget to update your cargo.toml to include sqlcipher as sqlx feature as described in the pull request.

Edouard.

[luis-cicada]

@eleroy let me test it again!! THANK YOU!!

[luis-cicada]

Hi @eleroy I'm still having the same issue: file is not a database

I have done the following:

I did run cargo update on tauri-src

Cargo.toml

[dependencies]
serde_json = "1.0"
serde = { version = "1.0", features = ["derive"] }
tauri = { version = "2.0.0-beta", features = [] }
tauri-plugin-fs = "2.0.0-beta.4"
tauri-plugin-dialog = "2.0.0-beta.4"
tauri-plugin-notification = "2.0.0-beta.3"
tauri-plugin-os = "2.0.0-beta.3"
tauri-plugin-store = "2.0.0-beta.4"
tauri-plugin-shell = "2.0.0-beta.3"
libsqlite3-sys = { version = "*", features = ["bundled-sqlcipher"] }

[dependencies.tauri-plugin-sql]
features = ["sqlite"]
version = "2.0.0-beta"
git = "https://github.com/eleroy/plugins-workspace"
branch = "v2"

main.rs

tauri::Builder::default()
.plugin(
            tauri_plugin_sql::Builder::default()
                .add_migrations(db_url, migrations)
                .add_sqlite_options(
                    "sqlite:app.db",
                    SqliteConfig {
                        key: "my_database_key",
                        journal_mode: "DELETE",
                        foreign_keys: true,
                        read_only: false,
                        ..Default::default()
                    },
                )
                .build(),
        )
        .run(tauri::generate_context!())

And then on Tauri

import Database from '@tauri-apps/plugin-sql'

const db = await Database.load("sqlite:app.db")

I'm I missing somthing??

[eleroy]

Which os are you using ? Windows ?

I'd try cargo clean and deleting cargo.lock before running tauri dev

Here is a bare project example : https://github.com/eleroy/test-sql-cipher I actually also had to add libcrypto.dll to the src-tauri folder and to the resources entry in tauri conf file to make sure the project would work, not doing this result in error 3221225781.

Good luck

[luis-cicada]

@eleroy I'm using macOS Sonoma 14.4.1 . Is this only working on windows?

[eleroy]

@luis-cicada I did not try on Mac OS, probably you'll need to adjust the features of libsqlite3-sys you could try: libsqlite3-sys = { version = "", features = ["sqlcipher"] } libsqlite3-sys = { version = "", features = ["bundled-sqlcipher-vendored-openssl"] }

using bundled-sqlcipher, make also sure you have openssl installed as specified in https://docs.rs/openssl/latest/openssl/ brew install openssl@3

if you use the sqlcipher feature, I think you need to install sqlcipher brew install sqlcipher

It should work on Mac, maybe you can find additionnal information here : https://docs.rs/crate/libsqlite3-sys/latest

[luis-cicada]

@eleroy interest findings.. So I was trying to open the db with dbeaver but since it is encrypted then duh.. it wont open.. I was able to open the db and my migrations and all where there..

Are you able to query and write to the encripted db like:

db.execute("INSERT INTO users (name) VALUES (?)", [name])

[luis-cicada]

@eleroy new update, I download your demo-repo and the project is unable to write to the db as well.. did you tried just no to encrypt the db but also to write into it?

[eleroy]

Hello, I just checked and execute and select are working fine (I've updated my example so it is possible to add names to the database using the form in the app).

try using $1 instead of ? : db.execute("INSERT INTO users (name) VALUES ($1)", [name])

Which libsqlite3-sys did you use in the end ? maybe it is a good idea that I add a note in the readme for macos

[skorphil]

@eleroy your solution expect encryption_key to be statically defined before build?

I think it might be useful to have this on client-side. So user can provide its own key for db

[HuakunShen]

I submitted a PR https://github.com/tauri-apps/plugins-workspace/pull/2553 to implement sqlite cipher encryption.

pragma key can be introduced in runtime dynamically, not during initialization, thus keys can be user input or a random value generated on user's device.