Precompiled sqlite3.dll with sqlite3_user_authenticate
(1) By Anse (ansgar) on 2021-01-05 16:28:12 [source]
I was asked for supporting password protected SQLite databases in HeidiSQL. So I read about the
SQLITE_USER_AUTHENTICATION compile time option and the
sqlite3_user_authenticate() interface here: https://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth.txt .
As I'm a bit stuck here, I'd like to ask whether there are precompiled dlls out there which already include this extension?
HeidiSQL itself is developed in Delphi, so I cannot link the C++ library code statically.
I think, with "password protected" you mean encrypted database files. There are several projects dealing with this, e.g. https://github.com/utelle/SQLite3MultipleCiphers or https://github.com/sqlcipher/sqlcipher
The first one also has precompiled binaries. The second one has to be compiled yourself.
I'm afraid it's user authentication, not encryption. I'll paste a part of the above doc here:
Activate the user authentication logic by including the ext/userauth/userauth.c source code file in the build and adding the -DSQLITE_USER_AUTHENTICATION compile-time option. The ext/userauth/sqlite3userauth.h header file is available to applications to define the interface.
When using the SQLite amalgamation, it is sufficient to append the ext/userauth/userauth.c source file onto the end of the amalgamation.
The following new APIs are available when user authentication is activated:
With this extension, a database can be marked as requiring authentication. By default a database does not require authentication.
The sqlite3_open(), sqlite3_open16(), and sqlite3_open_v2() interfaces work as before: they open a new database connection. However, if the database being opened requires authentication, then attempts to read or write from the database will fail with an SQLITE_AUTH error until after sqlite3_user_authenticate() has been called successfully. The sqlite3_user_authenticate() call will return SQLITE_OK if the authentication credentials are accepted and SQLITE_ERROR if not.
The precompiled binaries of the project SQLite3 Multiple Ciphers include also the user authentication extension you are referring to. Yes, the project offers database encryption, too, but if you don't need encryption, just don't use it.
Thanks for your thoughts. Yes, sqlite3 does not yet support authentication for an SQLite database. Adding support for login will require changes to HeidiSQL itself, and probably a different sqlite3.dll than the one shipped with Heidi.
I just read about authentication on sqlite.org and ran some tests here, but it seems sqlite3.dll does not provide the required sqlite3_user_authenticate interface.
Feel free to shed some light on SQLite authentication here.