# Should we audit enabling loading of sqlite3 extensions (shared libraries)? **URL:** \ **Category:** Core Development\ **Created:** [April 7, 2021, 8:24am UTC](https://discuss.python.org/t/should-we-audit-enabling-loading-of-sqlite3-extensions-shared-libraries/8124 "2021-04-07T08:24:41Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![erlendaasland](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/erlendaasland/32/4378_2.png) [@erlendaasland](https://discuss.python.org/u/erlendaasland)\ **Post date:** [April 7, 2021, 8:24am UTC](https://discuss.python.org/t/should-we-audit-enabling-loading-of-sqlite3-extensions-shared-libraries/8124/1 "2021-04-07T08:24:41Z")
**TL;DR** : `sqlite3.Connection.enable_load_extension` enables loading third party shared libraries. Should there be an audit event for this? ## Background 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()](https://www.sqlite.org/lang_corefunc.html#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](https://www.sqlite.org/c3ref/c_dbconfig_defensive.html#sqlitedbconfigenableloadextension) method rather than this interface, so the [load\_extension()](https://www.sqlite.org/lang_corefunc.html#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. ## See also - [Run-Time Loadable Extensions](https://www.sqlite.org/loadext.html) - [sqlite3.Connection.enable\_load\_extension](https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.enable_load_extension) ---
**Author:** ![tiran](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/tiran/32/48_2.png) [@tiran](https://discuss.python.org/u/tiran)\ **Post date:** [April 7, 2021, 10:32am UTC](https://discuss.python.org/t/should-we-audit-enabling-loading-of-sqlite3-extensions-shared-libraries/8124/2 "2021-04-07T10:32:35Z")
SGTM! Thanks for the detailed explanation. Since the feature loads external code, it makes sense to add audit events for `enable_load_extension` and `load_extension`. ---
**Author:** ![erlendaasland](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/erlendaasland/32/4378_2.png) [@erlendaasland](https://discuss.python.org/u/erlendaasland)\ **Post date:** [April 7, 2021, 10:36am UTC](https://discuss.python.org/t/should-we-audit-enabling-loading-of-sqlite3-extensions-shared-libraries/8124/3 "2021-04-07T10:36:31Z")
> [@tiran](#): > > Since the feature loads external code, it makes sense to add audit events for `enable_load_extension` and `load_extension` . Would it make sense to use the same event name for these? I’d guess no, but keeping the event list as compact as possible also has a value. **EDIT** : Answering myself. It does not make sense to reuse the event name, IMO 🙂 I’ll create an issue / PR. ---
**Author:** ![erlendaasland](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/erlendaasland/32/4378_2.png) [@erlendaasland](https://discuss.python.org/u/erlendaasland)\ **Post date:** [April 7, 2021, 10:38am UTC](https://discuss.python.org/t/should-we-audit-enabling-loading-of-sqlite3-extensions-shared-libraries/8124/4 "2021-04-07T10:38:16Z")
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. ---
**Author:** ![pf\_moore](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/pf_moore/32/35_2.png) [@pf\_moore](https://discuss.python.org/u/pf_moore)\ **Post date:** [April 7, 2021, 2:13pm UTC](https://discuss.python.org/t/should-we-audit-enabling-loading-of-sqlite3-extensions-shared-libraries/8124/5 "2021-04-07T14:13:15Z")
> [@erlendaasland](#): > > We should also migrate to using the `SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION` 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. ---
**Author:** ![erlendaasland](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/erlendaasland/32/4378_2.png) [@erlendaasland](https://discuss.python.org/u/erlendaasland)\ **Post date:** [April 7, 2021, 2:36pm UTC](https://discuss.python.org/t/should-we-audit-enabling-loading-of-sqlite3-extensions-shared-libraries/8124/6 "2021-04-07T14:36:25Z")
Yes, the Python API will remain. What I’m proposing is to use `sqlite3_db_config` iso. `sqlite3_enable_load_extension`, which implies that the _SQL API_ only is disabled. ---
**Author:** ![erlendaasland](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/erlendaasland/32/4378_2.png) [@erlendaasland](https://discuss.python.org/u/erlendaasland)\ **Post date:** [July 17, 2022, 10:43am UTC](https://discuss.python.org/t/should-we-audit-enabling-loading-of-sqlite3-extensions-shared-libraries/8124/7 "2022-07-17T10:43:50Z")
> [@erlendaasland](#): > > What I’m proposing is to use `sqlite3_db_config` iso. `sqlite3_enable_load_extension`, which implies that the _SQL API_ only is disabled. 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. ---
**Author:** ![pf\_moore](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/pf_moore/32/35_2.png) [@pf\_moore](https://discuss.python.org/u/pf_moore)\ **Post date:** [August 4, 2022, 10:35am UTC](https://discuss.python.org/t/should-we-audit-enabling-loading-of-sqlite3-extensions-shared-libraries/8124/8 "2022-08-04T10:35:17Z")
> [@pf\_moore](#): > > 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? 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`](https://docs.python.org/3.11/using/configure.html#cmdoption-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). ---
**Author:** ![erlendaasland](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/erlendaasland/32/4378_2.png) [@erlendaasland](https://discuss.python.org/u/erlendaasland)\ **Post date:** [August 4, 2022, 1:35pm UTC](https://discuss.python.org/t/should-we-audit-enabling-loading-of-sqlite3-extensions-shared-libraries/8124/9 "2022-08-04T13:35:18Z")
> [@pf\_moore](#): > > is there a good reason for not enabling extensions by default on Windows? 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: > [@erlendaasland](#): > > What I’m proposing is to use `sqlite3_db_config` iso. `sqlite3_enable_load_extension`, which implies that the _SQL API_ only is disabled. 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. ---
**Author:** ![pf\_moore](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/pf_moore/32/35_2.png) [@pf\_moore](https://discuss.python.org/u/pf_moore)\ **Post date:** [August 4, 2022, 3:00pm UTC](https://discuss.python.org/t/should-we-audit-enabling-loading-of-sqlite3-extensions-shared-libraries/8124/10 "2022-08-04T15:00:35Z")
> [@erlendaasland](#): > > I can create an issue for that on the tracker, unless you beat me to it. > > > \# Feature or enhancement > > Enable SQLite extension loading in the Windows build… (in the sqlite DLL shipped with Python, and in the stdlib sqlite3 module). > > \# Pitch > > At the moment, sqlite extensions are disabled at compile time - see the note in \[the docs\](https://docs.python.org/3.11/library/sqlite3.html#sqlite3.Connection.enable\_load\_extension). On Windows, though: > > 1. We supply our own sqlite DLL, so we control the build options, and > 2. Users rarely build their own copy of Python. > > So the reasons for not enabling extension loading don't really apply there, and conversely, people who want to use extensions cannot do so without limiting themselves to custom Python builds. > > \# Previous discussion > > See https://discuss.python.org/t/should-we-audit-enabling-loading-of-sqlite3-extensions-shared-libraries/8124/9 > [@erlendaasland](#): > > 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. 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`). ---
**Author:** ![erlendaasland](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/erlendaasland/32/4378_2.png) [@erlendaasland](https://discuss.python.org/u/erlendaasland)\ **Post date:** [August 4, 2022, 4:26pm UTC](https://discuss.python.org/t/should-we-audit-enabling-loading-of-sqlite3-extensions-shared-libraries/8124/11 "2022-08-04T16:26:50Z")
Thanks for creating the issue! > [@pf\_moore](#): > > 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`). SQLite has two API’s for loading extensions: 1. The C API [sqlite3\_load\_extension()](https://www.sqlite.org/c3ref/load_extension.html). 2. The SQL API [load\_extension()](https://www.sqlite.org/lang_corefunc.html#load_extension). Quoting the SQLite docs: > For security reasons, extension loading is disabled by default […] Extension loading can be enabled using the following SQLite C APIs: - [sqlite3\_enable\_load\_extension()](https://www.sqlite.org/c3ref/enable_load_extension.html): this enables/disables _both_ of the APIs described above. - [sqlite3\_db\_config()](https://www.sqlite.org/c3ref/db_config.html) with [SQLITE\_DBCONFIG\_ENABLE\_LOAD\_EXTENSION](https://www.sqlite.org/c3ref/c_dbconfig_defensive.html#sqlitedbconfigenableloadextension): enable/disable _only_ the SQLite C API `sqlite3_load_extension`; the _SQL API_ remains disabled, to prevent SQL injection attacks.