Allow-list pragma_table_info(tablename) and similar

[simonw]

It would be great if pragma_table_info(tablename) was allowed to be used in queries. See also https://github.com/simonw/til/blob/master/sqlite/list-all-columns-in-a-database.md

select * from pragma_table_info(tablename); is currently disallowed for user-provided queries via a regex restriction - but could help here too.

datasette/datasette/utils/__init__.py

Line 174 in d349d57

disallawed_sql_res = [(re.compile("pragma"), "Statement may not contain PRAGMA")]

Originally posted by @simonw in #760 (comment)

[simonw]

I could allow-list some other useful pragma_x tables too.

SQLite calls these "pragma functions" - documented here: https://www.sqlite.org/pragma.html#pragfunc

They sound safe:

Table-valued functions exist only for PRAGMAs that return results and that have no side-effects.

[simonw]

The rationale for blocking pragma entirely from statements is that it can be used to change the state of the SQLite database, e.g. from https://www.sqlite.org/pragma.html :

PRAGMA schema.application_id;
PRAGMA schema.application_id = integer ;

That second line is unsafe. I don't think it's possible to use the pragma_table_x variants to make writes in this way.

[simonw]

Maybe use a negative lookahead assertion? https://docs.python.org/3/library/re.html#index-20

(?!...)

Matches if ... doesn’t match next. This is a negative lookahead assertion. For example, Isaac (?!Asimov) will match 'Isaac ' only if it’s not followed by 'Asimov'.

[simonw]

For the moment I'll allow-list the following:

• pragma_database_list
• pragma_foreign_key_list
• pragma_function_list
• pragma_index_info
• pragma_index_list
• pragma_index_xinfo
• pragma_page_count
• pragma_max_page_count
• pragma_page_size
• pragma_schema_version
• pragma_table_info
• pragma_table_xinfo

[simonw]

Some demos:

• https://latest.datasette.io/fixtures?sql=select+*+from+pragma_database_list%28%29
• https://latest.datasette.io/fixtures?sql=select+*+from+pragma_foreign_key_list%28%27complex_foreign_keys%27%29
• https://latest.datasette.io/fixtures?sql=select+*+from+pragma_function_list%28%29
• https://latest.datasette.io/fixtures?sql=select+*+from+pragma_index_info%28%27idx_compound_three_primary_keys_content%27%29
• https://latest.datasette.io/fixtures?sql=select+*+from+pragma_index_list%28%27compound_three_primary_keys%27%29
• https://latest.datasette.io/fixtures?sql=select+*+from+pragma_index_xinfo%28%27idx_compound_three_primary_keys_content%27%29
• https://latest.datasette.io/fixtures?sql=select+*+from+pragma_page_count%28%29
• https://latest.datasette.io/fixtures?sql=select+*+from+pragma_max_page_count%28%29
• https://latest.datasette.io/fixtures?sql=select+*+from+pragma_page_size%28%29
• https://latest.datasette.io/fixtures?sql=select+*+from+pragma_schema_version%28%29
• https://latest.datasette.io/fixtures?sql=select+*+from+pragma_table_info%28%27complex_foreign_keys%27%29
• https://latest.datasette.io/fixtures?sql=select+*+from+pragma_table_xinfo%28%27complex_foreign_keys%27%29

[simonw]

Interestingly https://latest.datasette.io/fixtures?sql=select+*+from+pragma_function_list() doesn't work, when it DOES work on my laptop.

latest.datasette.io currently runs SQLite 3.27.2 while my laptop runs 3.31.1

https://www.sqlite.org/changes.html#version_3_30_0 says that as-of 3.30.0:

The PRAGMA function_list, PRAGMA module_list, and PRAGMA pragma_list commands are now enabled in all builds by default. Disable them using -DSQLITE_OMIT_INTROSPECTION_PRAGMAS.

[simonw]

More demos here: https://github.com/simonw/til/blob/master/sqlite/list-all-columns-in-a-database.md

[simonw]

Here's another demo I built using this feature: https://observablehq.com/@simonw/datasette-table-diagram

Running on this query:

select
  sqlite_master.name as table_from,
  fk_info.[from] as column_from,
  fk_info.[table] as table_to,
  fk_info.[to] as column_to
from
  sqlite_master
  join pragma_foreign_key_list(sqlite_master.name) as fk_info
order by
  sqlite_master.name

See https://github-to-sqlite.dogsheep.net/github?sql=select%0D%0A++sqlite_master.name+as+table_from%2C%0D%0A++fk_info.%5Bfrom%5D+as+column_from%2C%0D%0A++fk_info.%5Btable%5D+as+table_to%2C%0D%0A++fk_info.%5Bto%5D+as+column_to%0D%0Afrom%0D%0A++sqlite_master%0D%0A++join+pragma_foreign_key_list%28sqlite_master.name%29+as+fk_info%0D%0Aorder+by%0D%0A++sqlite_master.name