How to make encrypted sqlite3 database with nodejs in windows platform

Question

I want to make a small electron app that uses sqlite3 as database. I have installed sqlite3. npm install sqlite3 and rebuild it using node-gyp for using it with electron so far so good.

Now I want to encrypt the database how to do that particularly in windows platform

Answer 1

Yes, it's bit old question, though if somebody still wanted an answer, this might help them.

You can use @journeyapps/sqlcipher to enable encryption on your sqlite3 DB with electron app.

If you use any javascript ORM sequelize, you can configure that to use @journeyapps/sqlcipher as sqlite3 database engine as shown below

const sequelize = new Sequelize(null, null, 'your-encryption-key', { dialect: 'sqlite', dialectModulePath: '@journeyapps/sqlcipher', storage: 'path/to/db.sqlite' })

This works for me :)

Here is the example app which uses sqlite3 with sqlchiper wrapper.

Answer 2

Sqlite does not provide ways to encrypt the database file by default. It poses a lot of issues to attempt to do so. You'd have to encrypt the file with something like AES256, and then you'd have to decrypt the file when you wanted to access it, which implies that you'd have to keep a decrypted version somewhere. You could keep it in memory, but larger databases may not fit in memory, you'd also need to implement this in the SQLite library. You could create a temporary file, but this would mean that the decrypted version would be accessible any time the database was in use, and if your app crashes unexpectedly that file could fail to be cleaned up leaving your data exposed.

There are things like sqlitecrypt or sqlitecipher which encrypt your database, however they are replacements for sqlite. They implement the same API, however they are usually forks of sqlite. The node-sqlite3 module supports building for sqlitecipher, as illustrated here.

It's possible to encrypt data within the database. You'd need to generate a key with a passphrase, and then encrypt the data in each column. Set the passphrase on the key to the passphrase you'd like the user to use to unlock the data. This doesn't hide the structure of your database, and I'd only think this would be a pratical solution when you have a few columns of data that you'd like to encrypt, as you still need index fields to be able to query the data. This would be usefull for something like a password manager, where the username and password are encrypted fields, and there is a name associate to the user/pass pair which describes what its for. You'd be able to query by the credentials name, but the username and password would only be accessible by a password.

Answer 3

With extensive amounts of testing, I've found that better-sqlite3 is practically a flawless library for adding SQLite3 support for Node.js and Electron. So I've gone ahead and created an extended fork of that which supports multiple encryption algorithms such as SQLCipher and sqleet(ChaCha20-Poly1305) out of the box, by using SQLite3MultipleCiphers. It's well-tested, fast, and pretty much flawless like the original library.

Try it out here: https://www.npmjs.com/package/better-sqlite3-multiple-ciphers

Answer 4

I did using:

• https://github.com/journeyapps/node-sqlcipher

My personal app I have this code on "NODE JS" and ORM "SEQUELIZE":
more info https://sequelize.org/master/

const db_user = new Sequelize(
        database: "sdb",
        username: "",
        password: "mysecret",
        options: {
         dialect: "sqlite",
         dialectModulePath: '@journeyapps/sqlcipher',
         storage: "src/dbs/securedb.db",
       }
    );

    // SQLCipher config
    db_user.query("PRAGMA cipher_compatibility = 4");
    /*db_user.query("PRAGMA cipher_use_hmac = ON");
    db_user.query("PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA1");
    db_user.query("PRAGMA cipher_hmac_algorithm = HMAC_SHA1");
    db_user.query("PRAGMA cipher_page_size = 4096");
    db_user.query("PRAGMA cipher  = 'aes-256-cbc'");
    db_user.query("PRAGMA kdf_iter = 256000");
    db_user.query("PRAGMA cipher_plaintext_header_size = 0");*/
    db_user.query("PRAGMA key = 'mysecret'");

    async function connect(){
        try {
            await db_user.authenticate();
            return console.log('Connection has been established successfully.');
        } catch (error) {
            return console.error('Unable to connect to the database:', error);
        }
    }

    function loadModels(){
        var MdUser = db_user.define("user",{
            id: {
                type: DataTypes.INTEGER,
                autoIncrement: true,
                allowNull: false,
                unique: true,
                primaryKey: true
            },
            name:{
                type: DataTypes.STRING,
                allowNull: false
            }
        });

        db_user.sync(
            //{ force: true }
            { alter: true }
        );
     }

     connect()
        .then(loadModels())

Using DB-Browser-SQLite https://sqlitebrowser.org
you can open the new/definded SQL-chiper database.

1. Place the Password. (my case "mysecret")
2. default SQLCipher 4.
3. Ready to use.

You can Compare/verify the result with HexDump Viewer. using on Win10 https://www.saltybrine.com/hexdump32.htm

1. install this app.
2. open a encript database. (you will see a lot of wear chars but nothing human word to read)
3. Open and compare a NO encript database and you will see internal instructions/data words.