Expose `sqlite3_db_config` and verbs (or equivalent)

[numist]

Feature or enhancement

Python's SQLite bindings should expose sqlite3_db_config and at least SQLITE_DBCONFIG_DEFENSIVE (or an idiomatic version of the same)

Pitch

The libsqlite3.dylib built into Darwin enables defensive mode by default for all connections in processes linked on or after macOS 11 Big Sur as a mitigation layer against the general class of security vulnerabilities that can be exploited with pure SQL, but it's still useful to be able to disable it when using certain tools (like sqlite-utils). Conversely, developers may find it useful to be able to enable defensive mode on other platforms when opening a database with an untrusted schema, or executing untrusted SQL statements from remote sources.

Previous discussion

This was prompted by a brief discussion on Mastodon with @erlend-aasland

Linked PRs

• gh-103489: Add get/set config methods to sqlite3.Connection #103506

[simonw]

Here's the particular problem I'm encountering at the moment.

SQLite has a documented mechanism for applying "dangerous" SQL schema changes - things like adding a new foreign key to an existing table. It's this process here: https://www.sqlite.org/lang_altertable.html#otheralter

1. Start a transaction.
2. Run PRAGMA schema_version to determine the current schema version number. This number will be needed for step 6 below.
3. Activate schema editing using PRAGMA writable_schema=ON.
4. Run an UPDATE statement to change the definition of table X in the sqlite_schema table: UPDATE sqlite_schema SET sql=... WHERE type='table' AND name='X'; [...]
5. If the change to table X also affects other tables or indexes or triggers are views within schema, then run UPDATE statements to modify those other tables indexes and views too. [...]
6. Increment the schema version number using PRAGMA schema_version=X where X is one more than the old schema version number found in step 2 above.
7. Disable schema editing using PRAGMA writable_schema=OFF.

I use this trick in my sqlite-utils Python library and CLI tool to power table.add_foreign_key() method, described here: https://sqlite-utils.datasette.io/en/stable/python-api.html#adding-foreign-key-constraints

Here's the problem: it turns out there it's increasingly common for Python environments to be linked against versions of SQLite that set the SQLITE_DBCONFIG_DEFENSIVE flag by default. This flag causes PRAGMA writable_schema=ON to silently be ignored... but then the attempt to update the sqlite_schema table fails with an error.

This breaks features in my software that use that feature... but there's no current way for me to provide a fix.

SQLite programs with the full C API can turn SQLITE_DBCONFIG_DEFENSIVE back off again - but since the Python sqlite3 module doesn't expose a mechanism for that I can't provide my users with a fix.

Related issue:

• Extract columns cannot create foreign key relation: sqlite3.OperationalError: table sqlite_master may not be modified simonw/sqlite-utils#235

[erlend-aasland]

Thoughts about the API? Currently, I've exposed only the boolean options, since those are the most interesting IMO. I think the lookaside and "main db name" options are special enough to warrant their own APIs, if we were to add them¹. However, we don't know what kind of options SQLite will add in the future; there's a risk that my proposed API won't be a good fit in the future.

Footnotes

1. IMO, those two are too low level to expose, unless there are real good use cases for them. ↩