utelle / sqlite3multipleciphers

SQLite3 encryption extension with support for multiple ciphers

SQLite3MultipleCiphers

The project SQLite3 Multiple Ciphers implements an encryption extension for SQLite with support for multiple ciphers. In the past the encryption extension was bundled with the project wxSQLite3, which provides a thin SQLite3 database wrapper for wxWidgets.

In the course of time several developers had asked for a stand-alone version of the wxSQLite3 encryption extension. Originally it was planned to undertake the separation process already in 2019, but due to personal matters it had to be postponed for several months. However, maybe that wasn't that bad after all, because there were changes to the public SQLite code on Feb 7, 2020: “Simplify the code by removing the unsupported and undocumented SQLITE_HAS_CODEC compile-time option”. These changes took effect with the release of SQLite version 3.32.0 on May 22, 2020. As a consequence, all SQLite encryption extensions out there will not be able to easily support SQLite version 3.32.0 and later.

In late February 2020 work started on a new implementation of a SQLite encryption extension that will be able to support SQLite 3.32.0 and later. The new approach is based on SQLite's VFS feature. This approach has its pros and cons. On the one hand, the code is less closely coupled with SQLite itself; on the other hand, access to SQLite's internal data structures is more complex.

The code was mainly developed under Windows, but was tested under Linux as well. At the moment no major issues are known.

Version information

• 1.8.7 - August 2024
  • Based on SQLite version 3.46.1

For further version information please consult the CHANGELOG.

How to participate

Help in testing and discussing further development will be highly appreciated. Please use the issue tracker to give feedback, report problems, or to discuss ideas.

Documentation

Documentation of the currently supported cipher schemes and the C and SQL interfaces is provided on the SQLite3 Multiple Ciphers website.

Documentation on how to build the extension can be found on the page SQLite3 Multiple Ciphers Installation.

sqlite3multipleciphers's Issues

Compile on qnx-700 platform

Hi, I want to compile sqlite3mc's amalgamation source on qnx-700, with different build configurations include aarch64, armv7a.

The toolchain of qnx-700 runs on a x86_64 Linux host.

If I run it with the provided C compiler:

sqlite3mc_amalgamation.c:250248:20: fatal error: endian.h: No such file or directory
 #include <endian.h>

Although there is: /opt/qnx700/target/qnx7/usr/include/io-pkt/machine/endian.h, I'm not sure whether it's the needed one.
In the end I just commented this #include <endian.h> line.

Then there is error about platform-specific entropy function.

The cross-compiler defines feature macro __QNX__, if I run it with command "/opt/qnx700/host/linux/x86_64/usr/bin/qcc", it has also __unix__ defined. If I run it with the command /opt/qnx700/host/linux/x86_64/usr/bin/aarch64-unknown-nto-qnx7.0.0-gcc-5.4.0, no __unix__ is defined.

So I copied the following two functions from __unix__ section:

static size_t read_urandom(void* buf, size_t n)
static size_t entropy(void* buf, size_t n)

And put them to a new __QNX__ conditional compiling section, the compilation and linking of a simplistic main() function seemed OK, and the output program could be run on target board and call sqlite3_libversion(), version string is printed.

My question is:

is this entropy() function the only platform specific stuff to deal with?

If I don't care about the encryption strength, can I just call C standard rand() and fill the data in the buffer for entropy?

Some doc on the qnx-700:
https://www.qnx.com/developers/docs/7.0.0/#com.qnx.doc.neutrino.lib_ref/topic/manifests.html

Though the toolchain seems not freely available.

Usage of the VFS shim as a standalone VFS extension

@utelle, myself and my colleague have been digging through the project and evaluating it as a possible alternative to SQLCipher and the SQLite's authors' SEE extension. We are searching for a standalone loadable encryption-enabled VFS extension that can be used for both a Swift-based iOS project and a Kotlin/C/C++ Android project.
There are a few questions that we couldn't answer ourselves as we are very far from SQLite programming experts:

• If we forget about compatibility and interoperability between different SQLite encryption libraries, are there any stoppers to omit all the patches applied in script/patchsqlite3.sh amalgamation file and compile the library's sources against an official untouched sqlite.c file?
• Client processes on iOS would already have an initialized SQLite instance, probably loaded as a dynamic library. Can sqlite3mc_initialize() be called manually ?
• What modifications are required to be able to build the VFS as a standalone dynamically linked extension for SQLite?

Thank you

Faild to compile with VS2010

VS2010 is not providing inttypes.h
1>ClCompile: 1> sqlite3mc.c 1>d:\sqlite3multipleciphers\src\sqlite3mc_vfs.c(15): fatal error C1083: Cannot open include file: 'inttypes.h': No such file or directory 1> 1>Build FAILED.

Question about versions

Hi,

Just a simple question, wouldn't it be confusing for developers to have a different version number between SQLite3MC and the official SQLite3 library ?
For example, how do I know that 1.0.0 correspond effectively to version 3.33.0 of SQLite3 ?

I perfectly understand why SQLite3MC has a different version number, due to the fact some internal modification have been made to SQLite3.

I don't really know what the best option is, maybe double tag each release (one tag for SQLite3MC and one to keep a reference to SQLite3) ?

Applied to the current release, we would have:
tag 1 (SQLite3MC): 1.0.0
tag 2 (SQLite3) : SQLite3 - 3.33.0

Any suggestions or thoughts ?

Crash on removing encryption from a database

When removing encryption from an encrypted database with function sqlite3_rekey a null pointer exception occurs. This is a consequence of the fix to issue #18. It is necessary to check whether the codec pointer is valid, before resetting the error state.

Can multiple passwords coexist?😂

Login with different passwords can successfully see the data😁🤞

How to configure ciphers?

In addition to a C interface the former wxSQLite3 encryption extension allowed to configure the supported ciphers via user-defined functions. For example:

-- Get cipher used for the next key or rekey operation
SELECT wxsqlite3_config('cipher');

In principle, the new SQLite VFS based approach would allow to implement specialized PRAGMA statements. However, these PRAGMA statements have a serious restriction: they work only on file-based databases and not on databases held in memory, that is, a database opened with filename :memory:. In the latter case such PRAGMA statements would be silently ignored.

One could argue that for databases held in memory this doesn't impose a real problem, because they aren't encrypted anyway. However, if one attaches a file-based database to such a memory database connection, it would be necessary to use the schema prefix of the attached database, so that PRAGMA statements take effect.

The question is:

Should the new implementation offer specialized PRAGMA statements for cipher configuration in addition to the C interface, user-defined functions in SELECT statements, and URI parameters? Or would that add too much confusion?

Please share your opinions! Thanks.

[Node.js] Unable to build using the amalgamation

I'm trying to build better-sqlite3 with the latest amalgamation but always ends up with this error no matter what version of sqlitemc I use.

sqlite3.c(112699): error C2039: 'nTableLock': is not a member of 'Parse'
sqlite3.c(19096): note: see declaration of 'Parse'

Checked the source. nTableLock is defined. Error can be reproduced using:

npm install https://github.com/m4heshd/better-sqlite3-multiple-ciphers-test

I've been using sqleet amalgamation till now and it works perfectly fine (better-sqleet). But I'm trying to upgrade to the latest release of SQLite3. You can also test the successful build with following command. It uses the same version of better-sqlite3.

npm install https://github.com/m4heshd/better-sqlite3-multiple-ciphers-test#sqleet

Also tried merging all the compile-time options but no difference. What's going on here?. Sqlitemc amalgamation should be a drop-in replacement to the vanilla amalgamation right? So in theory shouldn't this work? 🤔

Segmentation Fault when rekeying the database

As before, I was playing around with the SQLiteShell (linux 64bit). I'm facing "Segmentation Fault" error when trying to rekey the database file.

Below the steps I used:

SQLite version 3.32.0 2020-04-29 01:09:46
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open file.db
sqlite> pragma key='rr';
ok
sqlite> CREATE TABLE contacts (contact_id INTEGER PRIMARY KEY,first_name TEXT NOT NULL,last_name TEXT NOT NULL,email TEXT NOT NULL UNIQUE, phone TEXT NOT NULL UNIQUE);
sqlite> select 1 from sqlite_master;
sqlite> pragma rekey='rrr';
Erreur de segmentation

Broken WAL mode in 1.3.0|1

I was actually trying to replace sqlite3mc 1.1.1 with 1.3.1 using WAL mode and some old database, but stumbled upon some issues. After narrowing down I found that WAL mode in general seems to be broken in release 1.3.1 (or 1.3.0).
Run this to create a new database (in the sqlite3mc shell provided in the binary download for win64):

.open newtest-wal1.db
pragma cipher=chacha20;
pragma key='123';
pragma journal_mode=wal;
create table test (x int);
begin immediate;
insert into test (x) values (1);
commit;
select * from test;

and you still get a result, but after re-opening the database again:

.open
.open newtest-wal1.db
pragma cipher=chacha20;
pragma key='123';
select * from test;

we're lost with a broken db:
Error: unsupported file format

My original attempt was to model our application code and switch back out of WAL mode before closing the db:

.open
.open newtest-wal2.db
pragma cipher=chacha20;
pragma key='123';
pragma journal_mode=wal;
create table test (x int);
begin immediate;
insert into test (x) values (1);
commit;
select * from test;
pragma journal_mode=delete;

which also fails, but with a different error message:
Error: database disk image is malformed

SQLITE_SOURCE_ID mismatch

Hi,

I downloaded this "amalgamation" source package: sqlite3mc-1.3.4-sqlite-3.36.0-amalgamation.zip

In file sqlite3.h:

#define SQLITE_VERSION        "3.36.0"
#define SQLITE_VERSION_NUMBER 3036000
#define SQLITE_SOURCE_ID      "2021-06-18 18:36:39 5c9a6c06871cb9fe42814af9c039eb6da5427a6ec28f187af7ebfb62eafa66e5"

In file sqlite3mc_amalgamation.c, at line 1300:

#define SQLITE_VERSION        "3.36.0"
#define SQLITE_VERSION_NUMBER 3036000
#define SQLITE_SOURCE_ID      "2021-06-18 18:36:39 5c9a6c06871cb9fe42814af9c039eb6da5427a6ec28f187af7ebfb62eafa66e5"

In file sqlite3mc_amalgamation.c, at line 235620:

/************** End of stmt.c ************************************************/
#if __LINE__!=235511
#undef SQLITE_SOURCE_ID
#define SQLITE_SOURCE_ID      "2021-06-18 18:36:39 5c9a6c06871cb9fe42814af9c039eb6da5427a6ec28f187af7ebfb62eafaalt2"
#endif
/* Return the source-id for this library */
SQLITE_API const char *sqlite3_sourceid(void){ return SQLITE_SOURCE_ID; }
/************************** End of sqlite3.c ******************************/
/*** End of #include "sqlite3patched.c" ***/

Note that the last 4 chars are changed from "66e5" to "alt2".

So if I #include <sqlite3.h> and call sqlite3_sourceid(), I got a const char* string that is different from the macro SQLITE_SOURCE_ID defined in <sqlite3.h>.

Is this intentional?

If this is the feature of sqlite3mc, Can I employ this discrepancy to check in runtime that whether I'm calling the original SQLite library or this SQLite Multiple Ciphers library?

use sqlite3_key create a sqlite database file, then can't open with navicat for sqlite.

I created a sqlite file using the following code
sqlite3* db;
sqlite3_open("test_pwd.db", &db);
const char* pwd = "123456";
sqlite3_key(db, pwd, strlen(pwd));
char* pStr = nullptr;
sqlite3_exec(db, createSQL, nullptr, nullptr, &pStr);
sqlite3_close(db);

Then I opened the file with Navicat and used the password '123456', but it was prompted that this is not a sqlite database file!How can I open it by navicat?

To patch or not to patch SQLite?

Up to now the former wxSQLite3 encryption extension did not require to patch the original SQLite code, except for using certain preprocessor symbols (like SQLITE_HAS_CODEC) on compiling the code.

In principal, I would prefer to keep it that way, even after the removal of the SQLITE_HAS_CODEC compile time option in the upcoming SQLite version 3.32.0.

However, there are 2 use cases where this would/could have consequences:

1. The PRAGMA KEY= and PRAGMA REKEY= statements
   After the removal of the SQLITE_HAS_CODEC option both PRAGMA statements are no longer available in SQLite itself.
2. The (undocumented) KEY keyword in ATTACH statements
   The KEY keyword is still parsed by SQLite (although I have no idea whether this will be the case in future SQLite versions). However, a given key value is silently ignored.

Use case 1 is most likely a non-problem, because those PRAGMA statements can be implemented a specialized PRAGMA statements in the VFS (as is already done in the preliminary implementation).

A workaround for use case 2 is to specify the key parameter as a URI parameter of the database file name in the ATTACH statement. However, this breaks existing code. To keep the KEY keyword operational would require to patch the SQLite code accordingly.

How important would it be to keep the former ATTACH statement syntax operational?

Please share your opinions! Thanks.

Whether the iOS system is supported

cross compile with clang in mac for IOS,sqlite3_prepare_v2 return error

make install does not copy header files

The header files are not installed correctly after following the installation instructions of the document.

Is it recommended to increase the compilation method of CMake

Is it recommended to increase the compilation method of CMake

Non-legacy `SQLCipher` runs really slow on `armv7`

I've recently started testing SQLite3MC on armv7 for better-sqlite3-multiple-ciphers. At first the tests failed with SQLCipher because it was so slow that it ran past the timeout of 5 seconds.

You can take a look at this run and see that it took almost 12 seconds for each encryption and decryption task. I've tested this multiple times to check if the results were consistent. They were. Sqleet runs flawlessly and both ciphers are fine on arm64.

So what I wanted to know is if this was the expected outcome because I've seen that SQLCipher had this issue in the past.

FYI, these tests are running on Qemu containers by the way. That could've also affected the results. I never got to test on a physical device like a Raspberry Pi.

Minor memory leak without sqlite3_shutdown

Hello, I was seeing some memory leaks on app exit and traced it to sqlite3mc_amalgamation.c(23761). Specifically, three blocks of size 8, 48, and 208 bytes. One of these being wsdAutoext.aExt which is freed in sqlite3_reset_auto_extension but only if I call sqlite3_shutdown. Not sure if I'm just doing something wrong, but the call to shutdown seems to be necessary for me (building the default sqlite3mc_amalgamation.c on VS2019).

Not that it's a significant amount of memory or anything, but the "Detected memory leaks!" warning might make someone think twice about using the library. Anyway, it might be nice to mention this somewhere in the docs for others who might run into the same leaks.

Build issues with GCC and Linux

Hi @utelle !

I've been trying to build the project today, but it wasn't successful. I've updated the premake5.lua to support linux build, in hope that it will work like before.

The premake5.lua file I used is accessible here: https://gist.github.com/Willena/c970c5575dab9b83689ef93a69b09f9a

I have also ran the patchsqlite3.sh script and the rekeyvacum.sh script before building.

Bellow the GCC log if it can help. At the end you have my GCC version.

Thanks

guillaume@master:~/SQLite3MultipleCiphers$ ../premake5 gmake
Building configurations...
Running action 'gmake'...
Generated build/Makefile...
Generated build/sqlite3lib.make...
Generated build/sqlite3so.make...
Generated build/sqlite3shell.make...
Done (103ms).
guillaume@master:~/SQLite3MultipleCiphers$ cd build/
guillaume@master:~/SQLite3MultipleCiphers/build$ make
==== Building sqlite3lib (debug_linux32) ====
Creating obj-gcc/Linux32/Debug/sqlite3lib
sqlite3mc.c
In file included from ../src/sqlite3mc.c:113:0:
../src/cipher_common.c:32:1: error: static declaration of ‘sqlite3mcGetCipherParameter’ follows non-static declaration
 sqlite3mcGetCipherParameter(CipherParams* cipherParams, const char* paramName)
 ^~~~~~~~~~~~~~~~~~~~~~~~~~~
In file included from ../src/codec_algos.c:10:0,
                 from ../src/sqlite3mc.c:106:
../src/cipher_common.h:75:12: note: previous declaration of ‘sqlite3mcGetCipherParameter’ was here
 extern int sqlite3mcGetCipherParameter(CipherParams* cipherParams, const char* paramName);
            ^~~~~~~~~~~~~~~~~~~~~~~~~~~
In file included from ../src/cipher_config.c:11:0,
                 from ../src/sqlite3mc.c:114:
../src/cipher_config.h:25:20: error: static declaration of ‘sqlite3mcFileControlPragma’ follows non-static declaration
 SQLITE_PRIVATE int sqlite3mcFileControlPragma(sqlite3* db, const char* zDbName, int op, void* pArg);
                    ^~~~~~~~~~~~~~~~~~~~~~~~~~
In file included from ../src/sqlite3mc.c:65:0:
../src/sqlite3patched.c:124791:14: note: previous declaration of ‘sqlite3mcFileControlPragma’ was here
   extern int sqlite3mcFileControlPragma(sqlite3*, const char*, int, void*);
              ^~~~~~~~~~~~~~~~~~~~~~~~~~
In file included from ../src/cipher_config.c:11:0,
                 from ../src/sqlite3mc.c:114:
../src/cipher_config.h:26:20: error: static declaration of ‘sqlite3mcHandleAttachKey’ follows non-static declaration
 SQLITE_PRIVATE int sqlite3mcHandleAttachKey(sqlite3* db, const char* zName, const char* zPath, sqlite3_value* pKey, char** zErrDyn);
                    ^~~~~~~~~~~~~~~~~~~~~~~~
In file included from ../src/sqlite3mc.c:65:0:
../src/sqlite3patched.c:108405:16: note: previous declaration of ‘sqlite3mcHandleAttachKey’ was here
     extern int sqlite3mcHandleAttachKey(sqlite3*, const char*, const char*, sqlite3_value*, char**);
                ^~~~~~~~~~~~~~~~~~~~~~~~
In file included from ../src/sqlite3mc.c:116:0:
../src/codecext.c:13:20: error: static declaration of ‘sqlite3mcCodecAttach’ follows non-static declaration
 SQLITE_PRIVATE int sqlite3mcCodecAttach(sqlite3* db, int nDb, const void* zKey, int nKey);
                    ^~~~~~~~~~~~~~~~~~~~
In file included from ../src/sqlite3mc.c:114:0:
../src/cipher_config.c:900:14: note: previous declaration of ‘sqlite3mcCodecAttach’ was here
   extern int sqlite3mcCodecAttach(sqlite3*, int, const void*, int);
              ^~~~~~~~~~~~~~~~~~~~
In file included from ../src/sqlite3mc.c:116:0:
../src/codecext.c:14:21: error: static declaration of ‘sqlite3mcCodecGetKey’ follows non-static declaration
 SQLITE_PRIVATE void sqlite3mcCodecGetKey(sqlite3* db, int nDb, void** zKey, int* nKey);
                     ^~~~~~~~~~~~~~~~~~~~
In file included from ../src/sqlite3mc.c:114:0:
../src/cipher_config.c:901:15: note: previous declaration of ‘sqlite3mcCodecGetKey’ was here
   extern void sqlite3mcCodecGetKey(sqlite3*, int, void**, int*);
               ^~~~~~~~~~~~~~~~~~~~
sqlite3lib.make:186 : la recette pour la cible « obj-gcc/Linux32/Debug/sqlite3lib/sqlite3mc.o » a échouée
make[1]: *** [obj-gcc/Linux32/Debug/sqlite3lib/sqlite3mc.o] Erreur 1
Makefile:40 : la recette pour la cible « sqlite3lib » a échouée
make: *** [sqlite3lib] Erreur 2
guillaume@master:~/SQLite3MultipleCiphers/build$ gcc -v
Using built-in specs.
COLLECT_GCC=gcc
COLLECT_LTO_WRAPPER=/usr/lib/gcc/x86_64-linux-gnu/6/lto-wrapper
Target: x86_64-linux-gnu
Configured with: ../src/configure -v --with-pkgversion='Debian 6.3.0-18+deb9u1' --with-bugurl=file:///usr/share/doc/gcc-6/README.Bugs --enable-languages=c,ada,c++,java,go,d,fortran,objc,obj-c++ --prefix=/usr --program-suffix=-6 --program-prefix=x86_64-linux-gnu- --enable-shared --enable-linker-build-id --libexecdir=/usr/lib --without-included-gettext --enable-threads=posix --libdir=/usr/lib --enable-nls --with-sysroot=/ --enable-clocale=gnu --enable-libstdcxx-debug --enable-libstdcxx-time=yes --with-default-libstdcxx-abi=new --enable-gnu-unique-object --disable-vtable-verify --enable-libmpx --enable-plugin --enable-default-pie --with-system-zlib --disable-browser-plugin --enable-java-awt=gtk --enable-gtk-cairo --with-java-home=/usr/lib/jvm/java-1.5.0-gcj-6-amd64/jre --enable-java-home --with-jvm-root-dir=/usr/lib/jvm/java-1.5.0-gcj-6-amd64 --with-jvm-jar-dir=/usr/lib/jvm-exports/java-1.5.0-gcj-6-amd64 --with-arch-directory=amd64 --with-ecj-jar=/usr/share/java/eclipse-ecj.jar --with-target-system-zlib --enable-objc-gc=auto --enable-multiarch --with-arch-32=i686 --with-abi=m64 --with-multilib-list=m32,m64,mx32 --enable-multilib --with-tune=generic --enable-checking=release --build=x86_64-linux-gnu --host=x86_64-linux-gnu --target=x86_64-linux-gnu
Thread model: posix
gcc version 6.3.0 20170516 (Debian 6.3.0-18+deb9u1)

Segfault when setting key for chacha20

As in #50, I'm only explicitly compiling in support for chacha20, however when I set the key via PRAGMA key = 'foobarbaz', I get a segfault. Here's the backtrace:

0x00007ffff41dccfd in sqlite3mcGetCipherParameter (cipherParams=0x7ffff41dd14c <sqlite3mcFreeCodecParameterTable>, paramName=0x7ffff41f8f5f "legacy") at ../deps/sqlite3/sqlite3mc_amalgamation.c:256475
256475	  for (; strlen(cipherParams->m_name) > 0; ++cipherParams)
(gdb) bt
#0  0x00007ffff41dccfd in sqlite3mcGetCipherParameter (cipherParams=0x7ffff41dd14c <sqlite3mcFreeCodecParameterTable>, paramName=0x7ffff41f8f5f "legacy") at ../deps/sqlite3/sqlite3mc_amalgamation.c:256475
#1  0x00007ffff41dc01f in AllocateChaCha20Cipher (db=0x4a22050) at ../deps/sqlite3/sqlite3mc_amalgamation.c:255434
#2  0x00007ffff41dd5d3 in sqlite3mcCodecSetup (codec=0x7fffe0008950, cipherType=3, userPassword=0x4a35470 "foobarbaz", passwordLength=9) at ../deps/sqlite3/sqlite3mc_amalgamation.c:256716
#3  0x00007ffff41e13ee in sqlite3mcCodecAttach (db=0x4a22050, nDb=0, zPath=0x4a22eac "/tmp/encrypted.db", zKey=0x4a35470, nKey=9) at ../deps/sqlite3/sqlite3mc_amalgamation.c:258683
#4  0x00007ffff41e1654 in sqlite3_key_v2 (db=0x4a22050, zDbName=0x0, zKey=0x4a35470, nKey=9) at ../deps/sqlite3/sqlite3mc_amalgamation.c:258758
#5  0x00007ffff41dfdba in sqlite3mcFileControlPragma (db=0x4a22050, zDbName=0x0, op=14, pArg=0x7fffe7ffddd0) at ../deps/sqlite3/sqlite3mc_amalgamation.c:257939
#6  0x00007ffff413af73 in sqlite3Pragma (pParse=0x7fffe7ffea90, pId1=0x7fffe7ffe0d8, pId2=0x7fffe7ffe0f0, pValue=0x7fffe7ffe120, minusFlag=0) at ../deps/sqlite3/sqlite3mc_amalgamation.c:129007
#7  0x00007ffff4176a94 in yy_reduce (yypParser=0x7fffe7ffe090, yyruleno=247, yyLookahead=1, yyLookaheadToken=..., pParse=0x7fffe7ffea90) at ../deps/sqlite3/sqlite3mc_amalgamation.c:162666
#8  0x00007ffff4177fd5 in sqlite3Parser (yyp=0x7fffe7ffe090, yymajor=1, yyminor=...) at ../deps/sqlite3/sqlite3mc_amalgamation.c:163298
#9  0x00007ffff4179227 in sqlite3RunParser (pParse=0x7fffe7ffea90, zSql=0x4a35588 "", pzErrMsg=0x7fffe7ffea68) at ../deps/sqlite3/sqlite3mc_amalgamation.c:164594
#10 0x00007ffff4140ff2 in sqlite3Prepare (db=0x4a22050, zSql=0x4a35718 "PRAGMA key = 'foobarbaz'", nBytes=24, prepFlags=128, pReprepare=0x0, ppStmt=0x7fffe7ffed38, pzTail=0x7fffe7ffed40) at ../deps/sqlite3/sqlite3mc_amalgamation.c:131874
#11 0x00007ffff41412dc in sqlite3LockAndPrepare (db=0x4a22050, zSql=0x4a35718 "PRAGMA key = 'foobarbaz'", nBytes=24, prepFlags=128, pOld=0x0, ppStmt=0x7fffe7ffed38, pzTail=0x7fffe7ffed40) at ../deps/sqlite3/sqlite3mc_amalgamation.c:131956
#12 0x00007ffff4141532 in sqlite3_prepare_v3 (db=0x4a22050, zSql=0x4a35718 "PRAGMA key = 'foobarbaz'", nBytes=24, prepFlags=0, ppStmt=0x7fffe7ffed38, pzTail=0x7fffe7ffed40) at ../deps/sqlite3/sqlite3mc_amalgamation.c:132062
(gdb) print *cipherParams
$1 = {m_name = 0x10ec8348e5894855 <error: Cannot access memory at address 0x10ec8348e5894855>, m_value = -125990584, m_default = -129660088, m_minValue = 272665416, m_maxValue = -389576376}

If I compile in all codecs/ciphers, then it works fine, so I suspect there is code assuming the size of globalCodecParameterTable or something similar.

Memory leak after VACUUM

From my preliminary tests, performing a VACUUM makes a memory leak of the codec instance (65xxx bytes). With no VACUUM (i.e. regular INSERT/UPDATE/SELECT), no memory leak.
I guess there is a missing sqlite3mcCodecFree() call somewhere...
Perhaps the ATTACH statement executed during VACCUM doesn't work as expected.

Segfault when only compiling in the chacha20 codec

If I compile with HAVE_CIPHER_CHACHA20=1 and have the rest of the ciphers/codecs explicitly disabled, there is a segfault when I explicitly set the cipher (PRAGMA cipher = 'chacha20') which I believe is caused by a cipher/codec index mismatch.

Specifically, while codecDescriptorTable has dummy descriptors to make sure that the indices remain the same no matter what ciphers/codecs are compiled in, globalCodecParameterTable does not have such dummy entries to account for non-existent ciphers/codecs.

Additionally, if I set chacha20 as the default cipher at build time (although it's currently already the build-time default) and don't specify the cipher via the pragma, then the issue does not occur.

Incompatible SQLCipher 4 database (SQLite3MultiplerCiphers vs DB Browser for SQLite)

I created a database using Microsoft.Data.Sqlite with SQLitePCLRaw.bundle_e_sqlcipher. I'm a newbie to these libraries. I didn't specify any special options. So I believe the database is formatted with the defaults of SQLCipher Version 4.

I could browse the contents with DB Browser for SQLite (SQLCipher). The default options for SQLCipher 4 were applied to open it.

I'm a maintainer of TkSQLite-Cipher. So, I wanted to open the database with SQLite3MultipleCiphers too. But it couldn't decrypt the database.

This is SQL version of what TkSQLite-Cipher does. I used sqlite3mc_shell_x64.exe to run this SQL.

PRAGMA cipher='sqlcipher';
--> sqlcipher
PRAGMA key='pass';
--> ok
SELECT * FROM accounts;
--> Error: file is not a database

I also checked if the database created by SQLite3MultipleCiphers can be opened with DB Browser.

PRAGMA cipher='sqlcipher';
PRAGMA rekey='pass';
CREATE TABLE accounts (ID INTEGER PRIMARY KEY, User TEXT, Password TEXT, Privilege INTEGER DEFAULT NULL);
INSERT INTO accounts (User, Password, Privilege) VALUES ('yusuke', 'pass1', 63);
SELECT * FROM accounts;
--> 1|yusuke|pass1|63

The database created by SQLite3MultipleCiphers is actually encrypted. It can be opened with sqlite3mc_shell_x64.exe and TkSQLite-Cipher.

I'm not sure which of SQLite3MultipleCiphers or Microsoft.Data.Sqlite is correct in that it follows the standard configuration of SQLCipher 4. I wish you could give me any advice.

Opening an unencrypted database while using `PRAGMA key`

I've been setting up and testing better-sqlite3-multiple-ciphers with my existing projects and have hit a slight bump on the road. This could be the intended behavior but very problematic in my use case.

As I've mentioned in #42, my project previously used sqleet and this application is designed to read both encrypted and unencrypted databases depending on the scenario but using the same codebase. So PRAGMA key='xxxxx' statement is used in the source regardless of encryption status. Worked perfectly fine on both types of DBs with sqleet. But when using sqlitemc an attempt to open an unencrypted DB while using PRAGMA key results in the following error.

SqliteError: unsupported file format

However it doesn't throw this error if the DB is empty (0kb). Is there any way to change this behavior to my expectation? If not, is there any specific cipher I can use other than chacha20 to make it work? I'm trying to avoid a massive amount of refactoring to my code.

EDIT:
Just saw #7. So what I'm thinking now is, "Should PRAGMA key be encrypting the DB at any point?". In my experience, only PRAGMA rekey should be able to encrypt the DB (as sqleet does) because it makes sense. Using PRAGMA key statement for both tasks on different scenarios generates a bit of confusion and lead to possible unintended circumstantial bugs in applications. But that's just my take. No idea if you intended for it to behave that way to match some compatibility problem. Does SEE work that way? Documentation is not very clear on that.

Assertion failure when compiled with `SQLITE_DEBUG`

What I wanted to know is if this is the intended behavior for Sqlitemc.

I'm compiling Sqlitemc with SQLITE_DEBUG set just for the tests. The assertion failure happens if you try to decrypt the DB with an incorrect passphrase (Sqleet) using PRAGMA key which exactly is the scenario I'm trying to test with Mocha. But since JS doesn't catch C++ runtime errors, the whole test process fails because of it.

// Line 53647 in amalgamated sqlite3.c

case PAGER_READER:
  assert( pPager->errCode==SQLITE_OK ); //⬅ this one
  assert( p->eLock!=UNKNOWN_LOCK );
  assert( p->eLock>=SHARED_LOCK );
  break;

Was this assert() supposed fail on the above mentioned scenario? 🤔

sqlite3_rekey to re-encrypt a db

Hi,

I have an pure Win32 API C application currently using sqleet.
While trying to move to this library (testing), I noticed the following:

Calling sqlite3_rekey(db, "", 0), then sqlite3_rekey(db, newPW, strlen(newPW)) in sequence gives SQLITE_OK for both, but I loose control of the database, meaning the new password cannot decrypt the db anymore.
If I insert the sequence of closing and reopening the db, then it is fine.

So :

{
sqlite3_rekey(db, "", 0) <- SQLITE_OK // decrypt db
...
sqlite3_rekey(db, newPW, strlen(newPW)) <- SQLITE_OK // reencrypt db
}

-> ok with sqleet, but not with SQLite3MultipleCiphers, in spite of both SQLITE_OK.
No access to db with the new password.

But :

{
sqlite3_rekey(db, "", 0) <- SQLITE_OK
sqlite3_close(db); <- NEW
sqlite3_open(DbName, &db); <- NEW
sqlite3_rekey(db, newPW, strlen(newPW)) <- SQLITE_OK
}

-> ok now with with SQLite3MultipleCiphers

Is the closing and reopening between 2 calls to sqlite3_rekey mandatory , or it this a bug ?

Anyway thanks for the impressive good job !

My config :

IDE : CodeBlocks
Compiler : GCC 10.2 64bit from winlibs.com
Libraries : most recent of Sqleet and SQLite3MultipleCiphers as of 11/8/2020

Extension functions vs SQLite Math Functions

The upcoming version 3.35.0 of SQLite will provide a new official extension implementing many built-in mathematical SQL functions.

Unfortunately, this new SQLite extension is not equivalent to the Extension Functions extension which is included in SQLite3 Multiple Ciphers.

This issue lists all provided SQL functions and the discrepancies compared to the official SQLite.

Mathematical functions

Function	Description	SQLite Math Extension (SME)	Extension Functions (EF)	Remarks
acos(X)	arccosine of X	✔️	✔️
acosh(X)	hyperbolic arccosine of X	✔️	✔️
asin(X)	arcsine of X	✔️	✔️
asinh(X)	hyperbolic arcsine of X	✔️	✔️
atan(X)	arctangent of X	✔️	✔️
atanh(X)	hyperbolic arctangent of X	✔️	✔️
atan2(X,Y)	arctangent of Y/X	✔️	✔️
atn2(X,Y)	alias for atan2	❌	✔️
ceil(X)	next larger integer value above X	✔️	✔️
ceiling(X)	alias for ceil	✔️	✔️
cos(X)	cosine of X	✔️	✔️
cosh(X)	hyperbolic cosine of X	✔️	✔️
cot(X)	cotangent of X	❌	✔️
coth(X)	hyperbolic cotangent of X	❌	✔️
degrees(X)	convert X from radians to degrees	✔️	✔️
exp(X)	e raised to the power X	✔️	✔️
floor(X)	next integer value less than X	✔️	✔️
ln(X)	natural logarithm of X	✔️	✔️
log(B,X)	base-B logarithm of X	✔️	❌
log(X)	base-10 logarithm of X	✔️	❗	EF calculates natural logarithm.
log10(X)	base-10 logarithm of X	✔️	✔️
log2(X)	base-2 logarithm of X	✔️	❌
mod(X,Y)	remainder after dividing X by Y	✔️	❌
pi()	approximation for π	✔️	✔️
pow(X,Y)	X raised to Y-th power	✔️	❌
power(X,Y)	alias for pow	✔️	✔️
radians(X)	convert X from degrees into radians	✔️	✔️
sign(X)	sign of X	✔️	✔️
sin(X)	sine of X	✔️	✔️
sinh(X)	hyperbolic sine of X	✔️	✔️
sqrt(X)	square root of X	✔️	✔️
square(X)	square of X	❌	✔️
tan(X)	tangent of X	✔️	✔️
tanh(X)	hyperbolic tangent of X	✔️	✔️
trunc(X)	truncate X to an integer value	✔️	❌

The Extension Functions extension already provides most functions included in the upcoming SQLite Math Extension.

Options to handle math functions in the future:

1. Activate the new SQLite Math Extension, and remove duplicates from the Extension Functions extension.
2. Implement missing functions in the Extension Functions extension, and do NOT activate the new SQLite Math Extension.

🛑 At the moment I prefer the first variant, but there may be criteria to prefer the second variant over the first. Please give feedback, if you are aware of any such criteria.

Unfortunately, there is one hard conflict regarding the log function. While the Extension Functions extension calculates the natural logarithm, the new SQLite Math Extension calculates the base-10 logarithm. The conflict cannot be resolved without breaking compatibility.

🛑 At the moment I intend to switch the implementation in the Extension Functions extension from natural logarithm to base-10 logarithm for the sake of future compatibility with SQLite. Please let me know, if there are reasons to keep the current definition.

String functions

Function	Description	SQLite	Extension Functions (EF)
charindex(S1,S2)	find position of S1 in S2	❌	✔️
charindex(S1,S2,N)	find position of S1 in S2 starting at position N	❌	✔️
leftstr(S,N)	N leftmost characters of S	❌	✔️
padc(S,N)	center pad S to length N	❌	✔️
padl(S,N)	left pad S to length N	❌	✔️
padr(S,N)	right pad S to length N	❌	✔️
proper(S)	capitalize all words in S	❌	✔️
replicate(S,N)	replicate S N times	❌	✔️
reverse(S)	reverse S	❌	✔️
rightstr(S,N)	N rightmost characters of S	❌	✔️
strfilter(S1,S2)	remove from S1 all characters not in S2	❌	✔️

No string function of the Extension Functions extension is currently in conflict with SQLite.

Aggregate functions

Function	Description	SQLite	Extension Functions (EF)
lower_quartile(X)	25% quartile of X	❌	✔️
median(X)	median of X	❌	✔️
mode(X)	most frequent value of X	❌	✔️
stdev(X)	standard deviation of X	❌	✔️
upper_quartile(X)	75% quartile of X	❌	✔️
variance(X)	variance of X	❌	✔️

No aggregate function of the Extension Functions extension is currently in conflict with SQLite.

Why the library compiled is so big about 9MB?

As Title shown, THANK YOU!

URI parameter *key* does not work for main database

If the encryption key for the main database is given as a URI parameter key= it is currently ignored. That is, a newly created database will not be encrypted and an existing encrypted database can't be opened.

Workaround: use SQL statement PRAGMA key=.

How to encrypt an existing unencrypted database using sqlitemc shell?

Hi, this is not an issue report but rather a newbie question - what are the exact command sequences to encrypt an existing non-encrypted database using sqlitemc shell ? I tried quite a few combinations of pragma statements but no success, It is greatly appreciated if you can provide with an example. Thank you very much in advance!

Failed access to database concurrently opened in System.Data.SQLite 1.0.112 (sqlite3 <3.32)

I'm not sure if this qualifies as a bug or if it is a fundamental incompatibility due to the different way encryption is implemented, but would like to check which is the case:

Background: we have two separate applications where one (A) creates a SQLite database and the other (B) reads it. In some cases B could open the database while A is still updating it. Historically, both used System.Data.SQLite with RC4 encryption. Current versions use SQLite3Multiciphers. For compatibility, current versions of B need to be able to open databases created by old versions of A.

Problem: ,

• A creates db with System.Data.SQLite 1.0.112 (based on sqlite3 3.31), using WAL mode and RC4 encryption, db connection is still open
• Trying to open a connection to the same db with B fails with "Error: file is not a database", using SQLite3MultiCiphers lib or shell (any version, tested with 1.1.1 and 1.2.4)

In contrast, concurrently opening another connection with System.Data.SQLite <=1.0.112 works.
Also, after A closes the connection (i.e. shm and wal files disappear), opening the db with SQLite3MultiCiphers works.

Estimated date for a first release ?

Hi @utelle,

Can we consider the codebase is stable ?
Will you release a version in the next weeks or months ?
Are there any known blocking issues ?

Thanks

Adding a separator in the CSV extension

Hello everyone, is it possible to add a CSV separator extension ? Thanks for your feedback.

Failed to make Windows 32bit Tcl extension with v1.1.4

The previous version I could successfully make was v1.0.0 with Tcl 8.6.10.
This time, I tried to make v1.1.4 with Tcl 8.6.11 but it failed with error.

Google implies it is related SSE. I'm not familiar with the code related to CPU...

config.log
make.log

Platform: Windows 10 Pro 20H2 64bit MSYS2 32bit

$ gcc --version
gcc.exe (Rev6, Built by MSYS2 project) 10.2.0
Copyright (C) 2020 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

Segment fault when custom build with gcc.

I am trying to use this project with emcc, got segment fault when compiling with following command:

gcc
-O0
-DSQLITE_OMIT_LOAD_EXTENSION
-DSQLITE_DISABLE_LFS
-DSQLITE_ENABLE_FTS3
-DSQLITE_ENABLE_FTS3_PARENTHESIS
-DSQLITE_THREADSAFE=0
-DSQLITE_USE_URI=1
-DHAVE_CIPHER_AES_128_CBC=0
-DHAVE_CIPHER_AES_256_CBC=0
-DHAVE_CIPHER_RC4=0
-DSQLITE_ENABLE_EXTFUNC
-DSQLITE_ENABLE_SERIES
-DSQLITE_ENABLE_NORMALIZE
sqlite-src/sqlite3mc-1.1.4/sqlite3mc_amalgamation.c
sqlite-src/sqlite3mc-1.1.4/shell.c
-o out/sqlite3.o
-maes
-lm

Debugging with gdb --args ./out/sqlite3.o 'file:./test.db?cipher=chacha20&key=test', I got:

Program received signal SIGSEGV, Segmentation fault.
0x0000000000000000 in ?? ()
(gdb) bt
#0  0x0000000000000000 in ?? ()
#1  0x0000000008107657 in sqlite3mcCodecSetup (codec=0x858c468, cipherType=1, userPassword=0x857b65a "test", passwordLength=4) at sqlite-src/sqlite3mc-1.1.4/sqlite3mc_amalgamation.c:252720
#2  0x000000000810b4bb in sqlite3mcCodecAttach (db=0x857b0f8, nDb=0, zPath=0x857bf44 "/mnt/e/Workspaces/github/javascript/sql.js/test.db", zKey=0x857b65a, nKey=4) at sqlite-src/sqlite3mc-1.1.4/sqlite3mc_amalgamation.c:254646
#3  0x000000000810b70e in sqlite3_key_v2 (db=0x857b0f8, zDbName=0x8139312 "main", zKey=0x857b65a, nKey=4) at sqlite-src/sqlite3mc-1.1.4/sqlite3mc_amalgamation.c:254720
#4  0x000000000810a3ce in sqlite3mcCodecQueryParameters (db=0x857b0f8, zDb=0x8139312 "main", zUri=0x857b63c "./test.db") at sqlite-src/sqlite3mc-1.1.4/sqlite3mc_amalgamation.c:254011
#5  0x000000000810a5d2 in sqlite3mcHandleMainKey (db=0x857b0f8, zPath=0x857b63c "./test.db") at sqlite-src/sqlite3mc-1.1.4/sqlite3mc_amalgamation.c:254071
#6  0x00000000080cc8a3 in openDatabase (zFilename=0x7ffffffedd38 "file:./test.db?cipher=chacha20&key=test", ppDb=0x7ffffffec7b0, flags=70, zVfs=0x0) at sqlite-src/sqlite3mc-1.1.4/sqlite3mc_amalgamation.c:165298
#7  0x00000000080cc92d in sqlite3_open_v2 (filename=0x7ffffffedd38 "file:./test.db?cipher=chacha20&key=test", ppDb=0x7ffffffec7b0, flags=6, zVfs=0x0) at sqlite-src/sqlite3mc-1.1.4/sqlite3mc_amalgamation.c:165321
#8  0x0000000008128792 in open_db (p=0x7ffffffec7b0, openFlags=0) at sqlite-src/sqlite3mc-1.1.4/shell.c:14155
#9  0x00000000081354ad in runOneSqlLine (p=0x7ffffffec7b0, zSql=0x857b060 "select * from sqlite_master;", in=0x0, startline=1) at sqlite-src/sqlite3mc-1.1.4/shell.c:19922
#10 0x0000000008135a2a in process_input (p=0x7ffffffec7b0) at sqlite-src/sqlite3mc-1.1.4/shell.c:20026
#11 0x000000000813751f in main (argc=2, argv=0x7ffffffeda98) at sqlite-src/sqlite3mc-1.1.4/shell.c:20808

The Makefile from sql-js/sql.js works great with sqleet, but not sqlite3mc.

make install fails for version 1.3.7

The just released version 1.3.7 fails at make install with a will not overwrite just-created error about src/sqlite3.h.

$ make install -j1
make[1]: Entering directory '/R/winlibs64-11.2.0ucrt/SQLite3MultipleCiphers-1.3.7'
 /usr/bin/mkdir -p '/R/winlibs64-11.2.0ucrt/inst_sqlite3multipleciphers-1.3.7/lib'
 /bin/sh ./libtool   --mode=install /usr/bin/install -c   libsqlite3mc.la '/R/winlibs64-11.2.0ucrt/inst_sqlite3multipleciphers-1.3.7/lib'
libtool: install: /usr/bin/install -c .libs/libsqlite3mc.dll.a /R/winlibs64-11.2.0ucrt/inst_sqlite3multipleciphers-1.3.7/lib/libsqlite3mc.dll.a
libtool: install: base_file=`basename libsqlite3mc.la`
libtool: install:  dlpath=`/bin/sh 2>&1 -c '. .libs/'libsqlite3mc.la'i; echo libsqlite3mc-0.dll'`
libtool: install:  dldir=/R/winlibs64-11.2.0ucrt/inst_sqlite3multipleciphers-1.3.7/lib/`dirname ../bin/libsqlite3mc-0.dll`
libtool: install:  test -d /R/winlibs64-11.2.0ucrt/inst_sqlite3multipleciphers-1.3.7/lib/../bin || mkdir -p /R/winlibs64-11.2.0ucrt/inst_sqlite3multipleciphers-1.3.7/lib/../bin
libtool: install:  /usr/bin/install -c .libs/libsqlite3mc-0.dll /R/winlibs64-11.2.0ucrt/inst_sqlite3multipleciphers-1.3.7/lib/../bin/libsqlite3mc-0.dll
libtool: install:  chmod a+x /R/winlibs64-11.2.0ucrt/inst_sqlite3multipleciphers-1.3.7/lib/../bin/libsqlite3mc-0.dll
libtool: install:  if test -n '' && test -n 'strip --strip-unneeded'; then eval
'strip --strip-unneeded /R/winlibs64-11.2.0ucrt/inst_sqlite3multipleciphers-1.3.7/lib/../bin/libsqlite3mc-0.dll' || exit 0; fi
libtool: install: /usr/bin/install -c .libs/libsqlite3mc.lai /R/winlibs64-11.2.0ucrt/inst_sqlite3multipleciphers-1.3.7/lib/libsqlite3mc.la
libtool: install: /usr/bin/install -c .libs/libsqlite3mc.a /R/winlibs64-11.2.0ucrt/inst_sqlite3multipleciphers-1.3.7/lib/libsqlite3mc.a
libtool: install: chmod 644 /R/winlibs64-11.2.0ucrt/inst_sqlite3multipleciphers-1.3.7/lib/libsqlite3mc.a
libtool: install: ranlib /R/winlibs64-11.2.0ucrt/inst_sqlite3multipleciphers-1.3.7/lib/libsqlite3mc.a
 /usr/bin/mkdir -p '/R/winlibs64-11.2.0ucrt/inst_sqlite3multipleciphers-1.3.7/include/sqlite3mc'
 /usr/bin/install -c -m 644 src/sqlite3.h src/sqlite3userauth.h src/sqlite3ext.h src/sqlite3mc_version.h src/sqlite3mc_vfs.h src/sqlite3.h src/sqlite3mc.h '/R/winlibs64-11.2.0ucrt/inst_sqlite3multipleciphers-1.3.7/include/sqlite3mc'
/usr/bin/install: will not overwrite just-created '/R/winlibs64-11.2.0ucrt/inst_sqlite3multipleciphers-1.3.7/include/sqlite3mc/sqlite3.h' with 'src/sqlite3.h'
make[1]: *** [Makefile:694: install-includemcHEADERS] Error 1
make[1]: Leaving directory '/R/winlibs64-11.2.0ucrt/SQLite3MultipleCiphers-1.3.7'
make: *** [Makefile:952: install-am] Error 2

My attempt to build was on Windows 11 with MinGW-w64 GCC 11.2.0.

SQLCipher v4 incompatibility

Hi !

While troubleshooting an issue (Willena/sqlite-jdbc-crypt#25) on my repo, I came to the conclusion that there is something wrong when using SQLCipher and SQLiteMC together. SQLiteMC cannot read databases created with SQLCipher and SQLCipher cannot read database created by SQLiteMC.

I did some tests myself with a fresh build of SQLCipher and using 1.0.1 binaries of SQLiteMC.
Here is the scenario I used:

1. I created a new db named fromCipher.db using SQLCipher:

SQLite version 3.33.0 2020-08-14 13:23:32 (SQLCipher 4.4.2 community)
sqlite> .open fromCipher.db 
sqlite> pragma cipher_default_settings;
PRAGMA cipher_default_kdf_iter = 256000;
PRAGMA cipher_default_page_size = 4096;
PRAGMA cipher_default_use_hmac = 1;
PRAGMA cipher_default_plaintext_header_size = 0;
PRAGMA cipher_default_hmac_algorithm = HMAC_SHA512;
PRAGMA cipher_default_kdf_algorithm = PBKDF2_HMAC_SHA512;
sqlite> pragma key='abc';
ok
sqlite> CREATE TABLE "T1" (
	"Field1"	INTEGER,
	"Field2"	INTEGER,
	"Field3"	INTEGER,
	"Field4"	INTEGER
);

2. I created a "SQLCipher" database named fromMC.db with SQLiteMC

SQLite version 3.33.0 2020-08-14 13:23:32
sqlite> .open fromMC.db
sqlite> pragma cipher = 'sqlcipher';
sqlcipher
sqlite> pragma kdf_iter = 256000;
256000
sqlite> pragma fast_kdf_iter = 2;
2
sqlite> pragma hmac_use = 1;
1
sqlite> pragma hmac_pgno = 1;
1
sqlite> pragma hmac_salt_mask = 0x3a;
58
sqlite> pragma legacy = 4;
4
sqlite> pragma legacy_page_size = 4096;
4096
sqlite> pragma kdf_algorithm = 2;
2
sqlite> pragma hmac_algorithm = 2;
2
sqlite> pragma plaintext_header_size = 0;
0
sqlite> pragma key = 'abc';
ok
sqlite> CREATE TABLE "T1" (
   ...> "Field1"INTEGER,
   ...> "Field2"INTEGER,
   ...> "Field3"INTEGER,
   ...> "Field4"INTEGER
   ...> );

3. Try to open fromMC.db with SQLCipher (it fails)

SQLite version 3.33.0 2020-08-14 13:23:32 (SQLCipher 4.4.2 community)
sqlite> .open fromMC.db
sqlite> pragma cipher_default_settings;
PRAGMA cipher_default_kdf_iter = 256000;
PRAGMA cipher_default_page_size = 4096;
PRAGMA cipher_default_use_hmac = 1;
PRAGMA cipher_default_plaintext_header_size = 0;
PRAGMA cipher_default_hmac_algorithm = HMAC_SHA512;
PRAGMA cipher_default_kdf_algorithm = PBKDF2_HMAC_SHA512;
sqlite> pragma key='abc';
ok
sqlite> select 1 from sqlite_master;
Error: file is not a database

4. Try to open fromCipher.db with SQLiteMC (it fails)

SQLite version 3.33.0 2020-08-14 13:23:32 (SQLCipher 4.4.2 community)
sqlite> .open fromCipher.db
sqlite> pragma cipher = 'sqlcipher';
sqlcipher
sqlite> pragma kdf_iter = 256000;
256000
sqlite> pragma fast_kdf_iter = 2;
2
sqlite> pragma hmac_use = 1;
1
sqlite> pragma hmac_pgno = 1;
1
sqlite> pragma hmac_salt_mask = 0x3a;
58
sqlite> pragma legacy = 4;
4
sqlite> pragma legacy_page_size = 4096;
4096
sqlite> pragma kdf_algorithm = 2;
2
sqlite> pragma hmac_algorithm = 2;
2
sqlite> pragma plaintext_header_size = 0;
0
sqlite> pragma key = 'abc';
ok
sqlite> select 1 from sqlite_master;
Error: file is not a database

Both test databases files are available here test_databases.zip

Any thoughts or ideas ?

Note : SQLite Version is 3.33.0, SQLiteMC version is 1.0.1, SQLCipher is 4.4.2
Note : SQLiteMC can write and read to it own created database as well as SQLCipher.
Note : Bug is also present in latest version (SQLiteMC 1.1.0)
This issue prevent users coming from SQLite tools like DB Browser (which support SQLCipher) to use their database with their java code (in the use case of my JDBC connector).

The first query after "pragma key" returns error

#include <stdio.h>
#include <stdlib.h>
#include "sqlite3.h"

int main() {
	sqlite3* db;
	sqlite3_open_v2("D:/crypt_a.sqlite", &db, SQLITE_OPEN_READWRITE, NULL); // db with key = a

	sqlite3_exec(db, "select * from sqlite_master", 0, 0, 0);
	sqlite3_exec(db, "pragma cipher=sqlcipher", 0, 0, 0);
	sqlite3_exec(db, "pragma legacy=4", 0, 0, 0);
	sqlite3_exec(db, "pragma key=b", 0, 0, 0);
	printf("With the incorrect key: %i\n", SQLITE_OK == sqlite3_exec(db, "select * from sqlite_master", 0, 0, 0)); // 0
	sqlite3_exec(db, "pragma key=a", 0, 0, 0);
	printf("With the correct key: %i\n", SQLITE_OK == sqlite3_exec(db, "select * from sqlite_master", 0, 0, 0)); // 0
	printf("With the correct key: %i\n", SQLITE_OK == sqlite3_exec(db, "select * from sqlite_master", 0, 0, 0)); // 1

	sqlite3_close_v2(db);
	return 0;
}

MinGW32, Win7x64, Release page dll.

Backup/Restore not working

The backup/restore function is not working. It restarts from the beginning in an endless loop.

The reason is a bug in the implementation of a partial page read.

Interoperability issues between releases 1.1.1 and 1.3.x with hot WAL journal

The following scenario may seem somewhat strange or constructed, but it depicts a real situation given the sqlite3mc release 1.1.1 we have in the field (and due to #39 I'm even somewhat happy we didn't switch).

Overall scenario: one application A writes to a new database in WAL mode, data inserted in a transaction is already commited, then the application crashes or is killed, leaving a hot WAL journal behind (The fact that SQLite is normally able to incorporate that journal into the database when the database is opened the next time meets a desirable design goal in our case, i.e. providing fail-over safety). Another application B should then still be able to open the database with all the commited data being available. Eventually, it will then switch back to DELETE journal mode.
This scenario worked well with System.Data.SQLite (used before we switched to sqlite3mc). Note that RC4 encryption here is only a legacy thing, current databases use chacha20 but we need to provide compatibility.
Now, depending on the sqlite3mc versions in use for A and B we see different errors as described below.

I recreated the issue just using the appropriate versions of the sqlite3mc shell (binaries as provided in the github releases) using the scripts below. "master" means shell binary built from sources downloaded after commit 43bd436.

Database creation (using 1.1.1):

.open newtest.db
pragma cipher=rc4;
pragma rekey='123';
create table test (x int);
pragma journal_mode=wal;
begin immediate;
insert into test (x) values (1);
commit;
*** kill shell here ***

Reading database with hot WAL journal:

.open newtest.db
pragma cipher=rc4;
pragma key='123';
pragma journal_mode;

1.1.1 and 1.3.1: wal
master: delete
select * from test;
1.1.1 and 1.3.1: no data
master: Error: no such table: test
.open (close db)
1.1.1 and 1.3.1: wal journal discarded, data lost
master: wal journal still exists, but:

.open newtest.db
.dbinfo

master: unable to read database header

It's unclear to me if the database created with 1.1.1 is broken (but can be read with 1.1.1 with no problems) or 1.3.x stumbles or maybe both.

SQL logic error when using SQLite Shell and applying a key

While doing some tests with the sqlite shell (a fresh build on linux from the repository sources) I encounter an error when applying the key.

Any ideas ? Is it a bug ?

Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open test.db
sqlite> CREATE TABLE IF NOT EXISTS warehouses ( id integer PRIMARY KEY, name text NOT NULL, capacity real);
sqlite> pragma key='toto';
Error: SQL logic error
sqlite>

Backup / Restore API Status

Hi Utelle,

Are the information displayed in the old readme for the backup / restore api (https://github.com/utelle/wxsqlite3/tree/0b3cad24b38406927568b9997302f6fe5642cc9e/sqlite3secure#backupapi) still applicable to this "new and reworked" version of the library ? Are there any major changes ?

Sporadically corrupted HMAC in multi-threaded environment

When running in a multi-threaded environment one can observe sporadically that the HMAC calculation fails, resulting in a SQLITE_CORRUPT error.

It seems that simply the wrong decryption key is used. The database file itself seems to be intact.

Multiple calls to sqlite3_key

Hi,

My application using sqleet is opening a db, repeatedly calling sqlite3_key(db, pw, strlen(pw)) until the correct password is given and only closing the db on application exit.

Here is what happens with SQLite3MultipleCiphers:

• Opendb

• First call to sqlite3_key with a wrong password :
  sqlite3_key <- SQLITE_OK
  checking if the key is correct with "SELECT * FROM sqlite_master" gives -> "File is not a database"

• All following calls to sqlite3_key with a wrong password give :
  sqlite3_key <- SQLITE_OK
  checking if the key is correct with "SELECT * FROM sqlite_master" gives -> "database disk image is malformed"

• Closedb

After the 2nd call to sqlite3_key, the correct password is also not accepted anymore.

Your documentation only says :
sqlite3_key() ... should usually be called immediately after sqlite3_open().

So :

• Are multiple calls to sqlite3_key allowed without closing end reopening the db ?
• If not, would it be possible that sqlite3_key return an error on the second and the following calls ?

Thanks !

Integration in System.Data.SQLite (.NET)

Can you give a hint on how to use this project in the System.Data.SQLite assemblies/distribution so the ciphers can be used in .NET development?

[NOT A ISSUE]small notice for anyone build this ext under MacOS

if you meet error like this when execute "../configure":

config.status: error: in/Users/qindj/sdk/SQLite3MultipleCiphers/build-gtk':
config.status: error: Something went wrong bootstrapping makefile fragments
for automatic dependency tracking. If GNU make was not used, consider
re-running the configure script with MAKE="gmake" (or whatever is
necessary). You can also try re-running configure with the
'--disable-dependency-tracking' option to at least be able to build
the package (albeit without support for automatic dependency tracking).
`

there should be something like : "../admin/build-aux/install-sh permission denied" in the config.log file

try using "sudo chmod +x ../admin/build-aux/install-sh" to fix it

my env:
MacOS Catalina 10.15.6

Download contains wrong version

The archive https://github.com/utelle/SQLite3MultipleCiphers/releases/download/v1.3.3/sqlite3mc-1.3.3-sqlite-3.36.0-win64.zip contains a shell binary which identifies as

SQLite version 3.35.5 2021-04-19 18:32:05 (SQLite3 Multiple Ciphers 1.3.2)

The other archives of v1.3.3 are ok.

Request: move version #defines to amalgamated header file instead of source file

While SQLITE_VERSION, SQLITE_VERSION_NUMBER, and SQLITE_SOURCE_ID are all included in the amalgamated header file, the various SQLite3MultipleCiphers-specific version #defines are not. It would be great to have those moved the header file as well so embedders can more easily get at this data.

No access to CHACHA20 encrypted db after update to 1.3.5

Hi,

I made a quick test with the new version 1.3.5 and I noticed that I could not access the databases encrypted with previous versions (CHACHA20).

• If I decrypt (with the old version) and encrypt again (with the new one), then it's fine.
• But now a tool like SQLite Studio 3.3.3 cannot read the newly encrypted files, which was fine before.

Is the compatibility broken with new version ?

Thanks for your comment

Request add user io readwrite

Hi,
I want to open the database in memory
I want to open the database in android assets

I Googled and found that vfs is needed, but I don’t know how to implement it. Can you add related interfaces? thanks

Sorry, I don’t speak English, this is the content translated by Google