Canned queries with named parameters fail with error against SQLite 3.46.0 about datasette

[simonw]

Code at fault:

datasette/datasette/utils/__init__.py

Lines 1137 to 1150 in 7437d40

	@documented
	async def derive_named_parameters(db: "Database", sql: str) -> List[str]:
	"""
	Given a SQL statement, return a list of named parameters that are used in the statement
	e.g. for ``select * from foo where id=:id`` this would return ``["id"]``
	"""
	explain = "explain {}".format(sql.strip().rstrip(";"))
	possible_params = _re_named_parameter.findall(sql)
	try:
	results = await db.execute(explain, {p: None for p in possible_params})
	return [row["p4"].lstrip(":") for row in results if row["opcode"] == "Variable"]
	except sqlite3.DatabaseError:
	return possible_params

[simonw]

The key problem here is that opcodes are not a stable feature of SQLite, so it was always risk to attempt to use them in this way.

[simonw]

We need to ensure SQLite 3.46.0 is covered in the new tests here:

• #2352

[simonw]

Posted about this on the SQLite forum - https://sqlite.org/forum/forumpost/0adbc56447 - but since opcodes are an undocumented and unsupported interface I don't expect them to fix it.

[simonw]

This mechanism here was already supposed to catch things if SQLite broke the mechanism I was using:

datasette/datasette/utils/__init__.py

Lines 1149 to 1150 in 7437d40

	except sqlite3.DatabaseError:
	return possible_params

But it didn't think to catch AttributeError.

[simonw]

Here's the macOS SQLite 3.46.0 libsqlite3.0.dylib I was using: https://static.simonwillison.net/static/2024/libsqlite3.0.dylib

[simonw]

Here's the commit that removed this feature: https://sqlite.org/src/info/dd5977c9a8a418be

Via https://sqlite.org/forum/forumpost/1cafc721009cef7f

[simonw]

I manually tested the patch to 0.64.x like this:

DYLD_LIBRARY_PATH=sqlite-3.46.0 datasette fixtures.db -m issue-2353.yaml -p 8034

Then visited http://127.0.0.1:8034/fixtures/demo?table=facetable and it worked.

I confirmed that the page had an error before applying that cherry-pick.

[simonw]

Here's the full set of changes going out in 0.64.7: 0.64.6...0.64.7

[simonw]

Released: https://pypi.org/project/datasette/0.64.7/ / https://docs.datasette.io/en/stable/changelog.html#v0-64-7

[simonw]

Blogged about the release here: https://simonwillison.net/2024/Jun/12/datasette-0647/