TL;DR: sqlite3.Connection.enable_load_extension enables loading third party shared libraries. Should there be an audit event for this?
If Python is configured with --enable-loadable-sqlite-extensions, it is possible to load third party SQLite extensions (shared libraries/DLL’s) via the sqlite3 extension module. This is probably not a very much used feature, as it is disabled by default. When enabled, the sqlite3.Connection.enable_load_extension() class method will enable the loading of third party extensions via SQL queries, using the SQL function load_extension(). (It also enables loading extension via C, using the sqlite3.Connection.load_extension() class method.) Quoting from the SQLite docs:
" It is recommended that extension loading be enabled using the SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION method rather than this interface, so the load_extension() SQL function remains disabled. This will prevent SQL injections from giving attackers access to extension loading capabilities."
SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION is an SQLite option that must be set before opening a database connection. Using this option, one can choose to only enable loading extensions via the C API, and to keep the SQL function disabled.
I know that PEP 578 don’t try to sandbox Python, but I still think it would be nice to add an audit hook for the sqlite3.Connection.enable_load_extension method.
We should also migrate to using the SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION interface iso. sqlite3_enable_load_extension, but that will require altering the behaviour of the current API. I’ll file that as a separate issue. It would be nice to get this in before the 3.10 beta.
Can I just clarify this latter point? Will it still be possible to load extensions dynamically? Specifically, will the Python APIs load_extension and enable_load_extension remain?
Disclaimer: I’ve not used these APIs “for real” in the past, but I’m doing some work where I was considering using them. The loss of the APIs wouldn’t be a disaster for me, but I would need to rethink a few things.
FTR, I’m leaving this proposal. It’s nice to try to minimise foot-shooting, however, imposing this restriction now is bound to break some people’s code for little benefit. Let’s just leave this API as it is.
I just re-discovered this discussion, having come back around to the investigations that prompted my original question, and I was frustrated to note the following in the docs (which I’d seen previously, but assumed was macOS/Unix only)
The sqlite3 module is not built with loadable extension support by default, because some platforms (notably macOS) have SQLite libraries which are compiled without this feature. To get loadable extension support, you must pass the --enable-loadable-sqlite-extensions option to configure.
Apparently, the standard Windows build doesn’t enable extensions. Given that (a) we ship our own sqlite DLL, and (b) compiling your own Python is a lot less practical for most Windows users, is there a good reason for not enabling extensions by default on Windows? It’s a relatively minor point because there seem to be very few sqlite extensions published for Windows, and in most cases implementing a custom function in Python is sufficient, but it does make it harder to write SQL that’s transportable between the SQLite shell and Python (which is the use case I have).
I don’t know. Historically, maybe for consistency across platforms? I’m fine with enabling it by default on Windows. I can create an issue for that on the tracker, unless you beat me to it.
Also, going back to my idea from earlier in this thread:
Now, that proposal is doomed (IMO), since someone is going to protest about the SQL API being denied. However, could expose the SQLite C API sqlite3_db_config in sqlite3 (together with SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION) so folks on macOS can enable extension loading the safe way. That would work on all platforms, so, as a side-effect, your app would be more portable.
Alternatively, we could tweak the sqlite3.Connection.enable_load_extension API to use sqlite3_db_config if sqlite3_enable_load_extension is not available.
I’m honestly not bothered how the ability to load extensions is enabled - I’m fine with the setup being different in Python and the sqlite client. I don’t know enough about the sqlite C API to have an opinion (in particular, the benefit of using SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION).