Hi there,

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.

Regards, Anse

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:

int sqlite3_user_authenticate(...);

int sqlite3_user_add(...);

int sqlite3_user_change(...);

int sqlite3_user_delete(...);

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.

Well, what shall I say: thanks a lot!

I was somehow confused about the downloads, lost somewhere on that github.io page and on the System.Data.SQLite pages.

The releases on their Github pages indeed have sqlite3-32/64.dll's with that sqlite3_user_authenticate() method.

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.