Electron support

[Christilut]

Hi! Super awesome to see this project appear. So far SQLCipher has been a huge nightmare for me with Electron, it's just insanely difficult to get anything working at all and when it does work it is with giant workarounds.

Excited to give this a try, can you tell me a bit about the versions you are using that work reliably? Have you tested this on Electron? I'm on an older Electron since the SQLCipher has been blocking the upgrade so I'm hoping your project will allow me to get on a newer Electron version.

better-sqlite3 with built in cipher is exactly what I'm looking for, would be so awesome to get this working well.

[m4heshd]

Hello @Christilut,

I actually built this to be used with Electron since that was my personal goal. So I'm paying extra attention to the functionality with Electron. I've been using this library since Electron v11 and I'm currently on Electron v16. You won't have any incompatibility issues when upgrading either. I've tested this library to be extremely reliable on massive data sets used with Electron apps which I'm distributing commercially. So no worry there.

To start with, I recommend using latest stable Electron with BS3MC v7.4.5. You can upgrade later hassle-free.

Use following code to get SQLCipher running:

// encrypt new db
const db = require('better-sqlite3-multiple-ciphers')('foobar.db', { verbose: console.log });
db.pragma("cipher='sqlcipher'");
db.pragma("rekey='secret-key'");
db.close();

// decrypt db
const db = require('better-sqlite3-multiple-ciphers')('foobar.db', { verbose: console.log });
db.pragma("cipher='sqlcipher'");
db.pragma("key='secret-key'");
// run statements after this

Although if you have enough flexibility for a transition, I recommend using Sqleet cipher.

[Christilut]

Thanks, I'll start with Electron 16 and v7.4.5. Sadly I'm stuck using SQLCipher.

What Node are you using with this? Do you have to setup any webpack config, like externals?

And I'm using Electron Forge, not sure if that changes anything.

Sorry for all the questions, just so many variables that have an impact on this. Just so crazy sensitive to SQLCipher working.

[m4heshd]

like externals?

Yes. You'll need to setup externals.

All of my Electron projects use Vuelectro with the default configuration. You can take a look at the webpack configuration here.

And I'm using Electron Forge, not sure if that changes anything.

Just configure it not to package the BS3MC module into the output asar. That's pretty much it.

[Christilut]

Thanks, I managed to get it running on Electron 11, so far so good! Normal SQL works. But when I try to read an encrypted database, I get the "file is not a database" error when the key pragma runs.

I used your example code, nothing else:

    this.db = require('better-sqlite3-multiple-ciphers')(databasePath, { verbose: console.log })
    this.db.pragma(`cipher='sqlcipher'`)
    this.db.pragma(`key='123'`)

This is a SQLCipher 4 database. I used to use @journeyapps/sqlcipher where I could open it with. Any ideas?

[m4heshd]

That could be for multiple reasons. We have no idea if the database header was left unencrypted and what's the default header size for DBs created by that library. You could try one thing. This enables the legacy modes for defined algorithm but this isn't guaranteed to work in your case. Still worth giving a try.

this.db = require('better-sqlite3-multiple-ciphers')(databasePath, { verbose: console.log })
this.db.pragma(`legacy=1`)
this.db.pragma(`cipher='sqlcipher'`)
this.db.pragma(`key='123'`)

Trying to integrate with DBs created using different encryption extensions is always messy.

[Christilut]

Same problem. :( I don't think the database was created with @journeyapps/sqlcipher though, I only use it to read the file. Not sure what the db was created with, most likely a different library. DBBrowser for SQLite with cipher support reads it just fine too.

Anything I can try?

[m4heshd]

DBBrowser for SQLite with cipher support reads it just fine too.

If this is the case, it's definitely legacy. DB Browser for SQLite reads and creates DBs that has an encrypted header. Can you try the following code in exact order?

this.db = require('better-sqlite3-multiple-ciphers')(databasePath, { verbose: console.log })
this.db.pragma(`cipher='sqlcipher'`)
this.db.pragma(`legacy=4`)
this.db.pragma(`key='123'`)

[Christilut]

Awesome, that works! Thanks so much!

Can I just leave it on legacy=4? It's a third party database so I guess I don't have any choice.

Now I can finally get rid of some really ugly hacks to get the old cipher working :) Thanks!

[m4heshd]

Awesome, that works! Thanks so much!

You're welcome.

Can I just leave it on legacy=4? It's a third party database so I guess I don't have any choice.

Yep. You have to if you rely or third-party DBs. Legacy isn't bad or anything. It just encrypts the header which makes it impossible to read any metadata beforehand decryption. Hence the need to set metadata like page size manually in some use cases. If it's needed in any case, you can use the legacy_page_size pragma statement to do so.

[Christilut]

Cool, thanks for the info! :)

[jove0610]

@m4heshd

Just configure it not to package the BS3MC module into the output asar. That's pretty much it.

Can you please provide instructions for this? My electron-forge app won't package if I set the asar to true in package.json. It says file links out of the package. This is the complete message:

√ Checking your system
√ Preparing to Package Application for arch: x64
√ Preparing native dependencies: 3 / 3
/ Packaging Application
An unhandled rejection has occurred inside Forge:
Error: C:\Users\User\AppData\Local\Temp\electron-packager\win32-x64\Tarkie Desktop-win32-x64\resources\app\node_modules\better-sqlite3-multiple-ciphers\build\Release\obj\global_intermediate\sqlite3\sqlite3.c: file links out of the package

Electron Forge was terminated. Location:
{}
npm ERR! code ELIFECYCLE
npm ERR! errno 1
npm ERR! tarkie@1.3.0 package: `electron-forge package`
npm ERR! Exit status 1
npm ERR!
npm ERR! Failed at the tarkie@1.3.0 package script.
npm ERR! This is probably not a problem with npm. There is likely additional logging output above.

npm ERR! A complete log of this run can be found in:
npm ERR!     C:\Users\User\AppData\Roaming\npm-cache\_logs\2022-03-30T08_23_34_916Z-debug.log

Thank you very much.

[m4heshd]

@jove0610,

Haven't used electron-forge standalone since the initial release. But this issue you're having was introduced in the latest release of better-sqlite3, in an effort to get rid of the tar dependency. Build process creates symlinks for the source files. This obviously was a mistake and IMO should be refactored to a different approach. You can check https://github.com/JoshuaWise/better-sqlite3/issues/751 and https://github.com/JoshuaWise/better-sqlite3/issues/784.

For the time being, you can tell electron-forge not to pack anything except PROJECT_ROOT\node_modules\better-sqlite3-multiple-ciphers\build\Release\better_sqlite3.node from build\Release directory. That's the only binary you need for the library to work in production.

[jove0610]

I was able to make it work now. Not sure how to include only the .node file in the packaged app but I what I did is put in the ignores config the files that causes problems..

"packagerConfig": {
        "asar": true,
        "ignore": [
          "sqlite3.c",
          "sqlite3.h",
          "sqlite3ext.h"
        ]
      },

Thanks

[leopucci]

@m4heshd could you help me? I am not being able to rebuild the package on electron-rebuild. Tryed with vstudio 17 and 19 i get v8 errors on build. Any hints?

better_sqlite3.cpp
310 error c:\users\leo\.electron-gyp\20.0.2\include\node\v8-callbacks.h(221): error C2062: tipo 'int' inesperado [C:\pkt\electron-react-boilerplate_atualizado\release\app\node_modules\better-sqlite3-multiple-ciphers\build\better_sqlite3.vcxproj]
310 error c:\users\leo\.electron-gyp\20.0.2\include\node\v8-callbacks.h(335): error C2062: tipo 'int' inesperado [C:\pkt\electron-react-boilerplate_atualizado\release\app\node_modules\better-sqlite3-multiple-ciphers\build\better_sqlite3.vcxproj]
310 error c:\users\leo\.electron-gyp\20.0.2\include\node\v8-isolate.h(292): error C3646: 'legacy_oom_error_callback': especificador de substitui��o desconhecido [C:\pkt\electron-react-boilerplate_atualizado\release\app\node_modules\better-sqlite3-multiple-ciphers\build\better_sqlite3.vcxproj]
310 error c:\users\leo\.electron-gyp\20.0.2\include\node\v8-isolate.h(292): error C4430: faltando especificador de tipo - int assumido. Observa��o: C++ n�o suporta default-int [C:\pkt\electron-react-boilerplate_atualizado\release\app\node_modules\better-sqlite3-multiple-ciphers\build\better_sqlite3.vcxproj]
310 error c:\users\leo\.electron-gyp\20.0.2\include\node\v8-isolate.h(1482): error C2061: erro de sintaxe: identificador 'LegacyOOMErrorCallback' [C:\pkt\electron-react-boilerplate_atualizado\release\app\node_modules\better-sqlite3-multiple-ciphers\build\better_sqlite3.vcxproj]
310 error c:\users\leo\.electron-gyp\20.0.2\include\node\v8-isolate.h(1545): error C2061: erro de sintaxe: identificador 'WasmDynamicTieringEnabledCallback' [C:\pkt\electron-react-boilerplate_atualizado\release\app\node_modules\better-sqlite3-multiple-ciphers\build\better_sqlite3.vcxproj]
310 error c:\users\leo\.electron-gyp\20.0.2\include\node\v8-initialization.h(290): error C2061: erro de sintaxe: identificador 'LegacyOOMErrorCallback' [C:\pkt\electron-react-boilerplate_atualizado\release\app\node_modules\better-sqlite3-multiple-ciphers\build\better_sqlite3.vcxproj]
310 error c:\pkt\electron-react-boilerplate_atualizado\release\app\node_modules\better-sqlite3-multiple-ciphers\build\src\objects\database.lzz(453): warning C4309: 'static_cast': truncamento de valor constante [C:\pkt\electron-react-boilerplate_atualizado\release\app\node_modules\better-sqlite3-multiple-ciphers\build\better_sqlite3.vcxproj]
310 error c:\pkt\electron-react-boilerplate_atualizado\release\app\node_modules\better-sqlite3-multiple-ciphers\build\src\util\macros.lzz(157): error C2039: 'AccessorSignature': n�o � um membro de 'v8' [C:\pkt\electron-react-boilerplate_atualizado\release\app\node_modules\better-sqlite3-multiple-ciphers\build\better_sqlite3.vcxproj]
310 error   c:\users\leo\.electron-gyp\20.0.2\include\node\v8.h(71): note: consulte a declara��o de 'v8'
310 error c:\pkt\electron-react-boilerplate_atualizado\release\app\node_modules\better-sqlite3-multiple-ciphers\build\src\util\macros.lzz(157): error C3083: 'AccessorSignature': o s�mbolo � esquerda de um '::' deve ser um tipo [C:\pkt\electron-react-boilerplate_atualizado\release\app\node_modules\better-sqlite3-multiple-ciphers\build\better_sqlite3.vcxproj]
310 error c:\pkt\electron-react-boilerplate_atualizado\release\app\node_modules\better-sqlite3-multiple-ciphers\build\src\util\macros.lzz(157): error C2039: 'New': n�o � um membro de 'v8' [C:\pkt\electron-react-boilerplate_atualizado\release\app\node_modules\better-sqlite3-multiple-ciphers\build\better_sqlite3.vcxproj]
310 error   c:\users\leo\.electron-gyp\20.0.2\include\node\v8.h(71): note: consulte a declara��o de 'v8'
310 error c:\pkt\electron-react-boilerplate_atualizado\release\app\node_modules\better-sqlite3-multiple-ciphers\build\src\util\macros.lzz(157): error C3861: 'New': identificador n�o encontrado [C:\pkt\electron-react-boilerplate_atualizado\release\app\node_modules\better-sqlite3-multiple-ciphers\build\better_sqlite3.vcxproj]
310 error c:\pkt\electron-react-boilerplate_atualizado\release\app\node_modules\better-sqlite3-multiple-ciphers\build\src\util\binder.lzz(37): error C2039: 'CreationContext': n�o � um membro de 'v8::Object' [C:\pkt\electron-react-boilerplate_atualizado\release\app\node_modules\better-sqlite3-multiple-ciphers\build\better_sqlite3.vcxproj]
310 error   c:\users\leo\.electron-gyp\20.0.2\include\node\v8-value-serializer.h(21): note: consulte a declara��o de 'v8::Object'
310 error   test_extension.c

[m4heshd]

@leopucci What version of Electron are you using?

[leopucci]

@m4heshd "electron": "^20.0.2", 1 info using npm@8.15.0 2 info using node@v16.17.0 electron-builder version=23.3.3

[leopucci]

Found similar situation https://github.com/WiseLibs/better-sqlite3/issues/867#issue-1349631530

[m4heshd]

@leopucci That's the reason I asked. You can't get bs3 to compile against Electron v20 at the moment. I'm waiting for a fix on upstream.

[leopucci]

Thanks @m4heshd