I don’t think this is a bug with SQLCipher, but my app and also the Signal desktop are seeing a similar issue with some users getting SQLITE_CORRUPT errors for SQLCipher databases. See details here:
Maybe there are some app lifecycle/forking nuances with using SQLite/SQLCipher with Electron. I was just wondering if anybody has seen this or had any resources to point to about how to avoid SQLITE_CORRUPT in an Electron+SQLCipher environment.
Thanks!
–Ben
I just checked our Electron app’s code pretty carefully… Electron has a main process and a renderer process. We don’t touch the SQLCipher database in the main process at all, only the renderer process… so that rules out suspect 2.6 (carrying a connection across a fork) from the SQLite How To Corrupt page.
Hello @aguynamedben. I don’t think we’ve had any other reports of widespread SQLITE_CORRUPT errors with SQLCipher 3.4.x or 4.x. Of course that doesn’t rule out a problem in SQLCipher, but as a data point it’s not a problem we’re currently tracking.
A few questions:
PRAGMA schema_version. Is your application using schema_version too?Thanks for the quick reply.
PRAGMA schema_version. The only PRAGMA statements we have in our code are PRAGMA cipher_migrate (for the 3>-4 upgrade) and PRAGMA KEY = 'sekrit' (to set the key when opening the database.The only other suspicious thing I see is section 2.2.1 from How To Corrupt A SQLite Database File…
2.2.1 Multiple copies of SQLite linked into the same application
“…tracking down some infrequent database corruption issues they were seeing on Linux and Mac. The problem was eventually traced to the fact that the application was linking against two separate copies of SQLite. The solution was to change the application build procedures to link against just one copy of SQLite instead of two.”
Our app is an Electron app, and we custom build the node-sqlite3 module in order to use SQLCipher with node-sqlite3. Maybe it’s possible that we’re inadvertently building the native module with both SQLite (3.26.0) and SQLCipher (3.26.0), instead of dropping in SQLCipher in place of SQLite…?
Our native module ends up in node_modules/sqlite3/lib/binding/electron-v4.1-darwin-x64/node_sqlite3.node. I can run this command to dump symbols…
nm -an node_modules/sqlite3/lib/binding/electron-v4.1-darwin-x64/node_sqlite3.node | grep sqlcipher
Is there some way I can detect that the native module contains “only” SQLCipher and isn’t somehow double-bundling 2 versions of SQLite?
Alright, we’re going to implement a UI workaround for now (“database was cleared”), I’ll keep looking at this. For fun, our node-sqlite build steps are below…
In package.json we have…
{
"scripts": {
"postinstall": "yarn flow-typed && yarn build-dll && yarn limited-electron-rebuild && yarn rebuild-sqlite",
"limited-electron-rebuild": "electron-rebuild --only @sentry/electron,keytar,sequelize",
"rebuild-sqlite": "./sqlcipher/RebuildSQLite.sh",
}
}
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
custom-binding.gyp
# WORKS
{
"includes": [ "deps/common-sqlite.gypi" ],
"variables": {
},
"targets": [
{
"target_name": "<(module_name)",
"include_dirs": [
"<!(node -e \"require('nan')\")",
"/Users/ben/code/sqlcipher"
],
"libraries": [
"/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)"
}
]
}
]
}
Update: When /Users/ben/code/sqlcipher/.libs/libsqlcipher.a is built, it’s dynamically linked against macOS (--with-crypto-lib=commoncrypto).
Side note: There appears to be an interesting SQLite corruption bug under investigation that involves the new ALTER TABLE code (from 3.25.0) being used within a transaction, however I don’t think this is the cause of my corruption issue given the error logs we have from our users experiencing the corruption (i.e. we ran no ALTER TABLES around the time of the corruption happening).
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg114356.html
I looked at our custom-binding.gyp very carefully and it’s pretty strict about only statically linking SQLCipher. I also un-asar’d our Electron app and inspected the packaged node_modules/sqlite3/lib/binding/electron-v4.1-darwin-x64/node_sqlite3.node and don’t see any way we’re double linking 2 different versions of SQLite.
Ruling that out for now. I’m going to implement a UI workaround (“your database had an issue”) but I’m very curious if anybody else using Electron, node-sqlite3, SQLCipher, etc. sees database corruption.
Another mechanism to verify what SQLite/SQLCipher version your application is loading at runtime, please execute the following SQL command and check the resulting string value:
PRAGMA cipher_version;
SQLite will not return a value for that command, whereas SQLCipher will.
@aguynamedben A few more questions:
1. Are you using journal or WAL mode?
No, our code has no mention of WAL mode. We do use the Sequelize ORM which recently fixed the way the SQLite files are closed for WAL mode, but we don’t enable WAL mode in the first place when using Sequelize (relevant test to explicitly use WAL mode with Sequelize).
2. Are you making any adjustments to synchronous mode?
No… the only PRAGMAs in our code are PRAGMA key = 'sekrit'; and now PRAGMA integrity_check; (for UI workaround that clears database in rare case of corruption). I looked through the Sequelize code and don’t see it toying with synchronous mode either.
FWIW I’ve read through How To Corrupt at SQLite File and don’t think we’re doing much creative with SQLite/SQLCipher.
3. Are you using any SQLite extensions, virtual tables, etc?
Our configure command…
./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"
So I think we’re enabling FTS3, FTS5, and JSON but not actually using any of those. Our database is 4 tables and pretty simple…
4. Does your application open and maintain multiple simultaneous connections to the database?
So, I was suspicious about this due to our usage of Sequelize which, due to it’s cross-database nature, has connection pooling logic. But Sequelize does not use any connection pooling logic for SQLite.
Our Electron app launches 2 process: the main process and 1 renderer process. We are careful not to access the database in the main process. We have a check in place to error out if this is ever even attempted:
if (process.type !== 'renderer') {
throw new Error('trying to connect to database in main process');
}
Electron is basically Chrome, so each new “window” (i.e. a Chrome tab) is a separate process. Our app starts with the main process… and then launches a single Electron BrowserWindow (new process via fork()), and that is the only process that connects to SQLite.
5. Are connections accessed across threads?
I’m less sure about this, mainly because my grasp on how threading may work in Chrome/Electron is weak. But this is one similarity between my app and the Signal app… we’re both Electron apps. Signal doesn’t seem to use Sequelize, and I think there’s a low likelihood our use of Sequelize is the issue.
Dumb question #1:
What happens if the app hard crashes during a write? Or power goes out during a write? SQLite should be fine… right? That’s the whole point behind using a hardened database like SQLite/SQLCipher, right?
Dumb question #2:
Do we need to explicitly close the file before a process exits? I’m suspicious of 1.1. Continuing to use a file descriptor after it has been closed. Because I’m at a such a high level in the stack (JavaScript > Node.js > Electron/Chrome) I’m suspicious that somewhere the Node/Electron app quit/crash process could mishandle the file descriptor for the SQLite database. However… it seems that when the Node.js process dies… the file should simply be closed.
We’re still trying to gather data, but for one of the users that saw corruption the pattern was this…
The user may have force quit the app and then during restart something went wrong with file descriptors or soft/hard links in the file system?
Will post here if I get any more data.
I’ve just posted the same questions to the relevant GitHub Issue for the Signal app, but could you also share your observations to these questions:
We have 2 cases…
Case 1 - Electron 3.0.6
Case 2 - Electron 3.0.6
We’re installing Electron binaries from npm. Our package.json has
"electron": "3.0.6",
and our yarn.lock has
electron@3.0.6:
version "3.0.6"
resolved "https://registry.yarnpkg.com/electron/-/electron-3.0.6.tgz#2d7b4ed521e90c69d83ffe5696db173b0e7b2473"
integrity sha512-MqwvA6IM0IDvUgPo/zHasmLMn3eYhMJ2I0qTNfQtxwqdoo762UlFS+upmMgcnCXPcGMGDWi3wtZhNir9nEw1kA==
dependencies:
"@types/node" "^8.0.24"
electron-download "^4.1.0"
extract-zip "^1.0.3"
What we build
npm rebuild (i.e. node-gyp) to rebuild the native module for node-sqlite3 with support for SQLCipher (custom binding.gyp) and our Electron runtime.Here’s how we build node-sqlite3…
#!/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
This produces node_modules/sqlite3/lib/binding/electron-v3.0-darwin-x64/node_sqlite3.node, which is packaged along with our Electron app to manage SQLCipher databases on user’s computers.
Since the corruption cases have happened, we’ve deployed an upgrade to Electron 4.1.4, but we have not yet seen a case of corruption occurring with a user on Electron 4.1.4.
I’m not sure what kind of low-level testing you may want to do for issues like this, but I’m happy to email you a download link of a built version of our app (any version), a non-corrupt database, source code, etc.
Also, I realize this isn’t a commercial-level support forum. We’re happy to help debug if it helps you, but we don’t have expectations. We’re appreciative of the time you’ve already taken to think about this.
Versions of Electron can get confusing… here’s some Node.js/Chrome/V8 version info from a version of our app that saw corruption (i.e. on Electron 3.0.6)… using process.versions from within Electron. So SQLCipher is probably trusting built-in Node.js native modules for file opening, etc. I’m not sure if v8/Chrome matter…
[15:11:15.144] [info] Versions { http_parser: '2.8.0',
node: '10.2.0',
v8: '6.6.346.32',
uv: '1.20.3',
zlib: '1.2.11',
ares: '1.14.0',
modules: '64',
nghttp2: '1.29.0',
napi: '3',
openssl: '1.1.0h',
electron: '3.0.6',
chrome: '66.0.3359.181' }
this error log from Signal is also with Node.js 10.2.0… but I think any Electron 3.x version is going to use Node.js 10.2.0, so doesn’t implicate Node.js necessarily…
I’m trying to overlay what I’ve learned recently about SQLite corruption with recent changelog entries for Node.js, so this may be a bunch of off-base conjecture, but here goes…
Conjecture 1: process: properly close file descriptor on exit
Node 10.15.3 (2019-03-05)
Author: Ruben Bridgewater
Could a crashed/exited Electron/Node.js process not be closing the file descriptor to the SQLite database correctly? Or could another thread be closing the file while another thread is still writing? Looking at this diff, it looks like there an async Node.js function to close a file descriptor was being used without being awaited, so there’s a change the process could be stopped without closing the file descriptor.
May match known SQLite corruption causes:
“1.1. Continuing to use a file descriptor after it has been closed”
“2.2. Posix advisory locks canceled by a separate thread doing close()”
Conjecture 2: src: plug trace file file descriptor leak
Node 10.9.0 (2018-08-15)
Author: Anna Henningsen
Seems to fix some similar issues with cleaning up file descriptor leaks.
If we’re suspicous of Node.js file handling for SQLite/SQLCipher it might be worth pinging those authors so see if they’ve heard of such a thing… i.e. what prompted them to make these changes?
Hello @aguynamedben - thanks for these additional details. This is a difficult issue to pin down. I posted over to the Signal thread about a tool that will help us determine on the one corrupted database available whether the file has been manipulated externally, or if the data is inconsistent internally. It is our hope this will help narrow things down.
With respect to the possible causes, I don’t think we can completely rule out the issue of linking multiple versions of SQLite, because it appears that electron actually embeds a copy of SQLite itself. We can’t say for certain that this would cause a problem, since technically they should not be opening the same database files, but it’s a consideration.
It would be very helpful to have a stripped-down/minimal application that is setup with SQLCipher that we could run some experimentation with. I’m thinking something along the lines of a simple electron app that just has a button and a text output control, which, when pressed, can execute some SQL on a SQLCipher database. We could expand that on our end to run some various load testing, and/or experiment with killing the app in various states. Unfortunately we’re not particularly experienced with electron tooling and development, so preparing that wouldn’t be a quick and trivial effort for our team. Would you be able to put something like that together and share the code with us? If so, it might help move things along.
@sjlombardo Thanks again. Yes, I can do that soon-ish… in the next week or two (in the middle of a sprint right now). I’ll use https://github.com/electron-react-boilerplate/electron-react-boilerplate and add in SQLCipher as a native module. This will be pretty straightforward for me to do since I’ve been working very actively with a fork of that project for 1.5 years now.
I’ll keep posted here when I get time to throw that together. Thanks again.
@aguynamedben - the new release of SQLCipher 4.2.0 adds PRAGMA cipher_integrity_check. It performs an independent check of each page in a database using the stored HMAC, and produces a list of an errors found. It might be a good idea to collect some information when these errors occur. If no problems are found with the “envelope” of the database by cipher_integrity_check, yet the database it is being reported as SQLITE_CORRUPT during use, then it is likely that some problem corrupted the database internally (e.g. logic error, improper use of the database API, etc.).
Let us know when have a chance to put together that sample application.