better-sqlite3-multiple-ciphers: Cannot Create Legacy SQLCipher Encrypted DB

did you publish the JB addon?

~I can’t even remember how I set it up. Not using any of it anymore.~

Nevermind. Curiosity got the best of me. JetBrains IDE’s custom data source configuration is undocumented. Just spent 3.5 hours to set this up flawlessly. It took an insane amount of effort to tests and create these template URLs. 🥵

First, head over to this repo and download both the JDBC driver and slf4j-api-1.7.36.jar file and use them as a custom data source driver in your JB IDE. It should look like this:

Then comes the infuriating URL Template portion. I managed to create the following URL Templates to cover every cipher scheme and any scenario.

Multi-cipher:

jdbc:sqlite:!(:memory:){file::database.db}[\?<&,cipher={Cipher:variants:chacha20:sqleet=chacha20,SQLCipher=sqlcipher,Ascon\: Ascon-128 v1.2=ascon128,System.Data.SQLite\: RC4=rc4,wxSQLite3\: AES 128 Bit=aes128cbc,wxSQLite3\: AES 256 Bit=aes256cbc},legacy={Legacy version:param},key={Key:param}>]

This should be enough. The following are just optional.

Sqleet:

jdbc:sqlite:!(:memory:){file::database.db}[\?<&,cipher={Cipher:param:chacha20},key={Key:param}>]

SQLCipher:

jdbc:sqlite:!(:memory:){file::database.db}[\?<&,cipher={Cipher:param:sqlcipher},legacy={SQLCipher version:param:4},key={Key:param}>]

After this, you can simply create a Data Source using that custom driver. Following is a screenshot of the active data source of the very same example application I wrote for you yesterday. It’s using ChaCha20-Poly1305 now.

Please consider a donation for my time and effort if this library and my support served you well. 😌

from that, they all look equal.

Unfortunately, It’s not as simple as that. Also, you need much larger test scenarios to observe any actual difference. You can clearly see from the tests I linked, that AES operations are much slower. This also depends on hardware because AES cryptography is heavily hardware-accelerated unlike ChaCha20-Poly1305.

If you test further, you’ll find that opening even a smaller database encrypted using SQLCipher is a way more expensive task than opening a sqleet DB because of the resource-heavy iterations it performs for key derivation. That kind of performance matters when you develop scalable and adaptable software. This is why ChaCha20-Poly1305 is way more popular in embedded applications. You can read this wiki to get a much better understanding.

Are there other node packages that support ChaCha20-Poly1305 ?

Unfortunately, there’s only one, that was also published by me 😄, now deprecated. This is the reality of using third-party libraries. You just never know when you’ll run into trouble 🤷🏻. Been developing software practically my whole life and I can’t even count how many times I had to write my own libraries because of this very same reason.

I personally stick to ChaCha20-Poly1305 because, from the documentation I could find, it’s much more secure and reliable. Also judging by my own tests throughout the many years, it tends to be much faster than any other cipher scheme on every platform and architecture. For reference, take a look at these latest test timings.

But then you do run into the issue of not having a good compatible tool to manipulate your database visually. I used to write my own separate programs for that specific task but, ended up setting up my own custom driver for the IDE I use since JetBrains IDEs have a really advanced data source management plugin built-in.

You also could’ve missed one other tiny detail. DB4S has two versions. One’s compiled with plain SQLite3 and the other with SQLCipher.

You might be trying to open the DB in the plain version. 🤔

And when you’re trying to open it in the SQLCipher version, ensure the header values reflect the following.

That’s really odd because it all works perfectly fine for me. Make sure to delete the previous test.db before you run that block of code.

@petef19 Just realized that your new DB is empty. New databases in SQLite are 0-byte files which means any 0-byte file can be a database. So when you try to encrypt it, there’s no data to encrypt. All you have to do is create a schema or insert data into your DB.

Try this for an example:

import Database from 'better-sqlite3-multiple-ciphers';

const db = new Database('test.db', {verbose: console.log});

db.pragma(`cipher='sqlcipher'`);
db.pragma(`legacy=4`);
db.pragma(`key='secret-key'`);

db.prepare(
    `
        CREATE TABLE IF NOT EXISTS "person"
        (
            "id"   TEXT,
            "name" TEXT,
            "age"  REAL,
            PRIMARY KEY ("id")
        )
    `
).run();

db.prepare(
    `
        INSERT
        OR IGNORE INTO "person"
        VALUES (@id,
                @name,
                @age)
    `
).run({
    id: '1',
    name: 'Jane',
    age: '25'
});

console.log(
    db.prepare(`SELECT * FROM "person"`).all()
);

db.close();

This will create an encrypted DB in the first run and will fetch data in all of the following runs.

Also keep in mind, that simply setting an SQLite PRAGMA would also do the job since that makes the new file an actual database. Try the following as well.

import Database from 'better-sqlite3-multiple-ciphers';

const db = new Database('test.db', {verbose: console.log});

db.pragma(`cipher='sqlcipher'`);
db.pragma(`legacy=4`);
db.pragma(`key='secret-key'`);
db.pragma('journal_mode=WAL');

db.close();

@petef19 If it’s only those lines of code you tried, there’s an issue. You have to close the DB properly for the encryption to stick. You aren’t doing that on the snippet you posted.

Can you create a minimal reproducible repo so I can check it out?