Setting up SQLCipher with node-sqlite3 and Electron

README.md

Gist notes

How I got SQLCipher working with Electron on macOS:

• First compile SQLCipher and dynamically link it to macOS' crypto libraries. Don't use OpenSSL. Zetetic (makers of SQLCipher) say that using macOS' crypto libraries is definitely going to be easiest on a Mac. Note that using SQLCipher libraries installed via Homebrew is going to seem like it works, and it will on your computer, but Homebrew's SQLCipher dynamcially links OpenSSL, so your app will crash when deployed to other computers (i.e. dynamic linking will fail at run timer because other computer wont have OpenSSL installed). So you want to dynamically link macOS crypto.
• Then you want to rebuild node-sqlite3 with a modified binding.gyp so that the SQLCipher library is statically linked into your SQLite binding. You should end up with a binary file ./node_modules/sqlite3/lib/binding/electron-v3.0-darwin-x64/node_sqlite3.node that has libsqlcipher.a statically linked within it. The statically linked libsqlcipher.a will in-turn dynamically link macOS' libcrypto.
• So in summary: dynamically link macOS' libcrypto when compiling SQLCipher, and statically link libsqlcipher when compiling (a.k.a. rebuilding) node-sqlite3 bindings.
• Also be sure you're using the Electron flags (check version!) for rebuilding. (see RebuildSQLite.sh below).
• The core things I didn't understand and that slowed me down:
  • Never having dealt with rebuilding native node modules. node-gyp and electron-rebuild are used to do this.
  • Not understanding dynamic and static linking well enough. This was especially challenging because crashes are just hard crashes with horrible error messages, and this setup requires multiple steps in linking (once when you compile SQLCipher and once when you rebuild node-sqlite3).

SQLCipher

1. Clone sqlcipher.

   cd ~/code git clone git@github.com:sqlcipher/sqlcipher.git cd sqlcipher git checkout -b v.3.4.2 tags/v3.4.2

2. Configure the build.

   make clean ./configure --enable-tempstore=yes --enable-load-extension --disable-tcl --with-crypto-lib=commoncrypto
   CFLAGS="-DSQLITE_HAS_CODEC -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS -DSQLITE_ENABLE_FTS5"
   LDFLAGS="-framework Security -framework Foundation"

(was -framework CoreServices instead of -framework Foundation at one point)

3. Run the build.

   make

This will produce the file ./.lib/libsqlcipher.a which will be referenced when we run npm rebuild. If you want to clean up the built files, run make clean.

Other notes on compiling SQLCipher

I've spent countless hours trying to get SQLCipher to work by statically linking OpenSSL's libcrypto.a. Building libsqlcipher.a seems to work, and so does npm rebuilding node-sqlite3, but when Command E is deployed everything seems to work, but the resulting database is just NOT encrypted. It seems there may be some problem or core thing I'm missing with how statically linking OpenSSL to SQLCipher works. ¯_(ツ)_/¯

4. Rebuild node-sqlite3 pointing to libsqlcipher.a

This is done in our script tesla/sqlcipher/RebuildSQLite.sh. You can just run yarn rebuild-sqlite.

echo "Copying custom binding.gyp (node-sqlite3)"
cp sqlcipher/custom-binding.gyp node_modules/sqlite3/binding.gyp

echo "Rebuilding node-sqlite3 bindings with statically linked SQLCipher (libsqlcipher.a)"
npm rebuild sqlite3 --build-from-source --static_linking_path=/Users/ben/code/sqlcipher/.libs/libsqlcipher.a --runtime=electron --target=3.0.6 --dist-url=https://atom.io/download/electron

libsqlcipher.a is pulled into the node-sqlite3 bindings and a new binary file is placed at ./node_modules/sqlite3/lib/binding/electron-v3.0-darwin-x64/node_sqlite3.node. This binary has SQLCipher bundled in it, which in-turn dynamically links Common Crypto. Running yarn package from here will build the app with the SQLCipher features enabled.

Debugging commands

otool -L /Users/ben/code/sqlcipher/.libs/libsqlcipher.dylib

strings .libs/libsqlcipher.a | grep -i RAND_add
strings .libs/libsqlcipher.a | grep -i RAND_add

What goes wrong

• Beware of accidental dynamic includes or configuration issues in binding.gyp.

custom-binding.gyp

# WORKS

{
  "includes": [ "deps/common-sqlite.gypi" ],
  "variables": {
  },
  "targets": [
    {
      "target_name": "<(module_name)",
      "include_dirs": [
        "<!(node -e \"require('nan')\")",
        # NOTE: This is hard-coded, you need to change accordingly.
        "/Users/ben/code/sqlcipher"
      ],
      "libraries": [
        # NOTE: This is hard-coded, you need to change accordingly.
        "/Users/ben/code/sqlcipher/.libs/libsqlcipher.a"
      ],
      "sources": [
        "src/database.cc",
        "src/node_sqlite3.cc",
        "src/statement.cc"
      ]
    },
    {
      "target_name": "action_after_build",
      "type": "none",
      "dependencies": [ "<(module_name)" ],
      "copies": [
          {
            "files": [ "<(PRODUCT_DIR)/<(module_name).node" ],
            "destination": "<(module_path)"
          }
      ]
    }
  ]
}

RebuildSQLite.sh

#!/bin/bash

set -e

echo "Copying custom binding.gyp (node-sqlite3)"
cp sqlcipher/custom-binding.gyp node_modules/sqlite3/binding.gyp

echo "Rebuilding node-sqlite3 bindings with SQLCipher"
npm rebuild sqlite3 --build-from-source --runtime=electron --target=3.0.6 --dist-url=https://atom.io/download/electron

# I don't know why building node-sqlite3 for Node.js isn't working... trying to
# use sqlite3 from Node.js (not Electron) fails with
#   Symbol not found: _kSecRandomDefault
# We don't actually need it to work, except for tests maybe...
#
# echo "Rebuilding node-sqlite3 bindings for Node.js with statically linked SQLCipher (libsqlcipher.a)"
# npm rebuild sqlite3 --build-from-source --runtime=node --target=10.13.0

Thanks @aguynamedben it worked :)

Rad!!!!!!