"Statement may not contain PRAGMA" error is not strictly true

[simonw]

Consider https://latest.datasette.io/fixtures?sql=select+%27select%0D%0A%27+%7C%7C+group_concat%28%27++++case+when+%5B%27+%7C%7C+name+%7C%7C+%27%5D+is+not+null+then+%27+%7C%7C+quote%28name+%7C%7C+%27%2C+%27%29+%7C%7C+%27+else+%27%27%27%27+end%27%2C+%27+%7C%7C%0D%0A%27%29+%7C%7C+%27%0D%0A++as+columns%2C%0D%0A++count%28*%29+as+num_rows%0D%0Afrom%0D%0A++%5B%27+%7C%7C+%3Atable+%7C%7C+%27%5D%0D%0Agroup+by%0D%0A++columns%0D%0Aorder+by%0D%0A++num_rows+desc%27+as+query+from+pragma_ytable_info%28%3Atable%29&table=facetable

It says "Statement may not contain PRAGMA" - but that's not actually true. Datasette has an allow-list of PRAGMA that are OK - in this case there was a typo in pragma_ytable_info which caused the error, but pragma_table_info` would have been OK.

So the error message is misleading.

[simonw]

https://docs.datasette.io/en/stable/sql_queries.html?highlight=pragma#named-parameters documentation is out-of-date as well:

Datasette disallows custom SQL containing the string PRAGMA, as SQLite pragma statements can be used to change database settings at runtime. If you need to include the string "pragma" in a query you can do so safely using a named parameter.

[simonw]

That allow-list was added in #761 but is not currently documented. It's here in the code:

datasette/datasette/utils/__init__.py

Lines 173 to 186 in 8e8fc5c

	allowed_pragmas = (
	"database_list",
	"foreign_key_list",
	"function_list",
	"index_info",
	"index_list",
	"index_xinfo",
	"page_count",
	"max_page_count",
	"page_size",
	"schema_version",
	"table_info",
	"table_xinfo",
	)

[simonw]

I'm going to change the error message to list the allowed pragmas.