Cannot Create Legacy SQLCipher Encrypted DB

[petef19]

Following along the info stated in the readme file to a create a legacy SQLCipher encrypted DB that can be opened in SQLIte Browser.

Running this code:

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.close();

creates a valid sqlite3 db but that db is NOT encrypted, SQLIte Browser does NOT ask a for a password or key, it directly opens the empty db. Navicat does the same thing.

What am I doing wrong?

Win 10 x64 Node 16.13.2 better-sqlite3-multiple-ciphers: 9.4.1

Also, is there a list of operating systems that are known to work or not work with this package?

Thanks!

[m4heshd]

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

[petef19]

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

embarrassed to say I don't know how. But, it is literally just the few lines of code I posted above, nothing else.

[m4heshd]

@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.

[petef19]

@m4heshd I do, but didn't include that originally in my post. I updated the code snippet, please see above.

[m4heshd]

@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]

Thanks for the detailed examples! both approaches unfortunately do NOT produce the desired result the created encrypted db does return data for the SELECT query but now the db does NOT open in latest DB Browser for SQLite, see here:

[m4heshd]

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]

Make sure to delete the previous test.db before you run that block of code.

I do delete the test.db file after every test run so that a new file is created every time. Which OS are you on and which version of DB Browser for SQLite? I tested this in two (2) different Node repos, 1x default Node and 1x Electron. Both exhibit the exact same behavior.

[m4heshd]

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.

[petef19]

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

🤣 yup, you were absolutely right! I never realized there is a separate DB4S exe for the SQLCipher version, holy cow!

okay, it works now - THANK YOU! 😄

so, can you comment on if there is any major concerns or differences for these encryption algos: (1) SQLCipher legacy - works in DB4S (2) AES 128-bit - works in Navicat (3) SQLeet ChaCha - default for bs3 - works in SQLite Studio

I'm not a fan of SQLite Studio which is why I'm asking if (1) or (2) raise any concern? Security is obviously key, but workability during development is a major concern as well!

Thanks!

[m4heshd]

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.

[petef19]

nice, thanks!

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.

did you publish the JB addon? we're using JB IDE as well...

it tends to be much faster

did a quick & dirty performance test between three (3) bs3 algos in Node that included these tasks: (1) create a new encrypted db (2) create table and insert 200 rows (3) then read the 200 rows

results:

AES 128-bit:                960.136ms
SQLeet cha-cha (default):   987.61ms
SQLCipher legacy:           969.46ms

from that, they all look equal. My concern is that if ever (god forbid) this great bs3 package gets EOL'd, then which node package can we use to open our ChaCha20-Poly1305 encrypted database in our app? W/ SQLCipher legacy we could fall back to node-sqlite3 package, not great but at least a fallback option.... Are there other node packages that support ChaCha20-Poly1305 ?

Thanks!

[m4heshd]

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.

[petef19]

your timing... is impeccable 😆 I literally just figured the JB IDE part out and wanted to post here and now see your post.

In case anybody else reads this here, the one thing that held me up was that one has to also specify the slf4j-api-1.7.36.jar file (in addition to the jdbc file) when creating a custom User driver! I tested this with ChaCha20, SQLCipher and AES 128 CBC using a custom URL to open the encrypted db file - all working. not sure if I prefer the DBMS over Navicat, but for a quick overview of the current data structure this will do nicely.

Thanks regarding your input regarding performance!

[m4heshd]

@petef19 That's great. Keeping this issue open for the moment because I'd like to add this process to the documentation at some point.

[GitMurf]

...For reference, take a look at these latest test timings.

@m4heshd is there any way you could post these test timings and have them persisted? It says the logs have all expired so cannot view them. Thanks!

[m4heshd]

is there any way you could post these test timings and have them persisted?

You can always take a look at the log of the latest test runs from the test workflow.

[GitMurf]

Sorry for the dumb question but is there a specific workflow action I should look at in there for the benchmarking of the different encryption options? I was looking for the timings you referred to above recommending chacha as the fastest (have to show my team haha) :) thanks!

[m4heshd]

@GitMurf It's always this portion of any test.