Simon Willison’s Weblog

Weeknotes: New releases across nine different projects

12th June 2021

A new release and security patch for Datasette, plus releases of sqlite-utils, datasette-auth-passwords, django-sql-dashboard, datasette-upload-csvs, xml-analyser, datasette-placekey, datasette-mask-columns and db-to-sqlite.

Datasette 0.57 with a security fix

I started the week by wrapping up work on Datasette 0.57, which I previewed here last week.

While upgrading the ?_trace=1 debug mechanism to work with streaming CSV files (in order to fix a nasty CSV performance issue) I spotted a severe XSS security hole. I fixed this in 0.57 and also pushed out a 0.56.1 release with the same fix, to cover users who weren’t ready to test and upgrade the larger changes in the minor point release.

GitHub have a neat mechanism for documenting security vulnerabilities which helps alert other repositories that are relying on a piece of software. They also offer a tool for requesting a CVE—I used this, and a couple of days later GitHub’s security team reviewed and issued CVE-2021-32670 (and in NIST)—Datasette’s first entry in the CVE database!

I don’t believe anyone found this hole before me, so it’s unlikely to have been exploited in the wild in the past, but if you use Datasette with an authentication plugin sach as datasette-auth-passwords or deploy on a domain that also hosts other authenticated web applications you should upgrade urgently.

Security aside, my favourite features in the 0.57 release are:

• If you try to execute a SQL query containing a syntax error, Datasette will re-display your query rather than expecting you to click the “back” button—demo here. This is a huge usability improvement that’s been far too long coming.
• You can now use ?_col= and ?_nocol= parameters to select which columns are displayed for a table, either by URL hacking or using new “Hide this column” / “Show all columns” cog menu options. These selections also affect JSON and CSV output.
• Facet results now have a ... more link which links to the ?_facet_size=max version of the page, showing up to 1,000 results—so you can finally facet across al U.S states or all world countries without being truncated at 30. Here’s a demo.

I also released a 0.57.1 bug fix and a 0.58a0 alpha previewing a small improvement to the menu_links(), table_actions() and database_actions() plugin hooks. I’ve decided to start releasing plugin hook changes as alphas as early as possible, since it makes it easier for people (including myself) to try them out in plugins.

sqlite-utils 3.8 and 3.9

Two minor releases each with two new features.

sqlite-utils 3.8 introduces a sqlite-utils indexes my.db command-line tool for viewing the indexes in a database, and a new Python library table.xindexes introspection property which returns more details than the existing table.indexes—I decided to implement a new property rather than break backwards compatibility for existing code using table.indexes.

Similarly, sqlite-utils 3.9 introduces a sqlite-utils schema my.db command and accompanying db.schema property that exposes the SQL schema of the entire database—as opposed to table.schema which just returns the schema for one specific table.

django-sql-dashboard 0.16

The first new feature contributed to this package by another developer: Atul Varma upgraded the description on saved dashboards to render as Markdown, which makes sense since Markdown is packaged with Django SQL Dashboard already—as seen in this delightfully gnarly query which dynamically constructs a Markdown summary of all of the groups and permissions configured for a Django application.

db-to-sqlite 1.4

db-to-sqlite is my command-line tool for importing data from any SQLAlchemy-supported relational database into a SQLite database. It’s a quick and easy way to load data into a format that can be handled by Datasette.

The last release was nearly a year ago. I dropped into the project to wrap up a pull request from six months ago and ended up migrating CI from Travis to GitHub Actions and fixing a few other long-standing bugs and feature requests too.

Thanks to Jeremy Dormitzer for implementing the new --postgres-schema feature, and sorry it took so long to land in a release!

And the others

• datasette-auth-passwords simply bumps the Datasette dependency to 0.56.1 or later, to ensure it isn’t run against an older insecure Datasette version.
• datasette-upload-csvs fixes a bug where this plugin broke against Datasette versions newer than 0.54.
• xml-analyser 1.1 added support for reading from standard input, e.g. cat x.xml | xml-analyzer -—I wanted this for some XML exploration I was doing, then I added some sorting improvements in this and in a 1.2 follow-up release.
• datasette-placekey is a tiny new plugin which adds SQL functions for working with placekeys—a way of encoding addresses which looks like this: 222-222@63s-spp-nbk. The plugin provides SQL functions including geo_to_placekey(latitude, longitude) and placekey_to_geo() and placekey_to_h3().
• datasette-mask-columns 0.2.1 simply removes a stale pinned version of Datasette.

TIL this week

• Building a Markdown summary of Django group permissions
• Exporting Amplitude events to SQLite
• Running a MySQL server using Homebrew