simonw/datasette 1.0a33

on GitHub

Stored queries can now be edited and deleted through the web interface, and the JSON API ?_extra= mechanism has been extended to cover row and query pages in addition to tables. This release also fixes two security issues: a SQL injection vulnerability involving table and column names that contain ], and an open redirect.

Editing and deleting stored queries

The stored query page gained a "Query actions" menu with Edit this query and Delete this querylinks for actors with the necessary permissions. The owner of a query can always edit or delete it; for queries that are not private, any actor with the update-query or delete-query permission can do so too. Private queries remain editable and deletable only by their owner. See Stored queries for details. (#2735)

?_extra= support for row and query pages

Row and query JSON pages now support the same ?_extra= mechanism as table pages. Row pages can request extras such as foreign_key_tables, query, metadata and database_color; arbitrary SQL and stored query pages can request extras such as columns, query, metadata and private. The implementation was refactored into a registry of extra classes shared by all three page types.

New generated reference documentation describes every ?_extra= parameter available on table, row and query JSON pages, with example output captured from a live Datasette instance at documentation build time. See Expanding JSON responses for the full list.

Other improvements and fixes to the extras mechanism:

• ?_extra= values can be separated by commas as well as repeated, e.g. ?_extra=count,next_url. Previously a comma-separated value that included columns failed to include the columns key in the response.
• The ?_extra=private extra on arbitrary SQL query pages now correctly reflects whether the SQL execution permission is private to the current actor - it previously always returned false.
• The ?_extra=query extra on query pages now reports the named parameters that were actually bound when the query executed, including parameters declared in a stored query's params list. Magic _-prefixed parameters are no longer echoed back with unbound values taken from the querystring.
• Extras that exist to serve the HTML interface (filters, actions, display_rows) are no longer advertised or reachable through the JSON API, where requesting them previously returned a 500 serialization error.
• The pre-1.0 ?_extras= (plural) parameter on row pages has been removed - use ?_extra=foreign_key_tables instead.

Security fixes

• Fixed a SQL injection vulnerability in datasette.utils.escape_sqlite(). Identifiers were wrapped in [brackets] without escaping any ] characters they contained - SQLite has no mechanism for escaping ] inside bracket quoting, so a table or column name containing ] could break out of the identifier and inject arbitrary SQL. Identifiers containing ] are now quoted using double quotes instead. (#2677)
• Fixed an open redirect vulnerability. Requesting a path such as /\example.com/ produced a redirect with a Location: /\example.com header - browsers normalize backslashes to forward slashes, turning that into the protocol-relative URL //example.com and redirecting the user off-site. Any run of leading slashes and backslashes in a redirect path is now collapsed to a single slash. (#2680)

Bug fixes

• can_render() callbacks registered by the register_output_renderer() plugin hook now receive the result rows and columns for stored queries. Previously renderers that inspect the available columns - such as datasette-atom and datasette-ics - never appeared as export options on stored query pages. (#2711)
• Fixed a 500 error from the /-/check permission debugging endpoint when checking query actions such as view-query, update-query and delete-query. (#2756)
• Write queries that use a named parameter called :sql no longer fail with an error. (#2761)
• db.execute_isolated_fn() now works against immutable databases, using a read-only connection that bypasses the write thread. It previously always attempted to open a writable connection, which would fail - breaking features built on top of it, such as the SQL analysis step used when storing a query. An exception raised while opening the connection for an isolated function no longer crashes the write thread. (#2768)
• Facet counts are now displayed on the same line as the facet value instead of wrapping onto a second line. (#2754)
• Datasette's pytest plugin no longer imports the rest of Datasette at pytest startup time. This means plugin test suites using pytest-cov now correctly record coverage of code that runs when datasette modules are first imported.