Datasette 0.64 restricts arbitrary SQL around SpatiaLite

A public SQL editor exposes the functions available on its database connection. That matters when the connection loads SpatiaLite. Its spatial operations are useful for publishing geographic data, but some functions can crash the process serving those queries. Datasette 0.64, released on January 9, adds a convenient way to disable arbitrary SQL and recommends doing so for untrusted users when SpatiaLite is enabled.

The practical question is which computations a visitor may request. A read-only database protects stored data against ordinary writes, but a function invoked by a query still executes code. Datasette addresses that distinction by allowing the publisher to select the SQL that visitors can run. The application can continue providing a geographic lookup without offering the entire extension as a public query interface.

I like SQLite because an application can keep SQL execution close to its data without operating a separate database server. That arrangement also makes the application responsible for deciding what its callers may execute. Datasette makes this responsibility particularly visible because exposing SQL is one of its useful capabilities.

What the new setting changes

The settings documentation in the 0.64 release defines default_allow_sql as the default permission for users to execute arbitrary SQL. Starting Datasette with the setting off makes checks for the execute-sql permission fail by default. For a database containing geographic data, the documented configuration can be expressed as follows.

datasette places.db --load-extension=spatialite --setting default_allow_sql off

This is a change to the default permission decision. It does not remove SpatiaLite from the connection or rewrite the extension. The extension remains available to SQL that the publisher chooses to expose through a canned query. That separation permits an application to retain spatial functionality while limiting the statements that an unknown visitor can submit.

The setting also makes an existing policy easier to express. Before choosing the command-line form, a publisher could use an allow_sql block in metadata. The release documentation presents the setting as a more convenient way to establish that default. The metadata equivalent for denying arbitrary SQL to everyone is small.

{
  "allow_sql": false
}

The convenience matters in a deployment command. A visible setting beside --load-extension records both the capability being enabled and the policy governing callers. It reduces the amount of separate configuration a publisher needs to find before understanding the intended access policy for a spatial database.

Read-only queries still invoke functions

The versioned SpatiaLite documentation explicitly warns that some additional SQL functions may crash the Datasette server. Its recommendation is to disable arbitrary SQL for untrusted users and define canned queries containing the functions that the application needs. This warning concerns availability of the serving process. It should not be expanded into an unsupported claim about remote code execution or database corruption.

A database statement can have consequences beyond changing a row. A spatial predicate runs extension code to interpret its arguments and evaluate geometry. Restricting writes does not establish that every possible invocation of that code is suitable for anonymous requests. The release therefore distinguishes permission to read published data from permission to author a computation against it.

This does impose a cost on users who want exploratory access. Arbitrary SQL lets a visitor express a query that the publisher did not anticipate. A canned endpoint offers only the computations that its author supplies. For a geographic API with a defined purpose, that restriction can be appropriate. A service intended for trusted analysts may choose a different permission policy.

My preference is to preserve SQLite's small operational footprint without treating embedded execution as an isolation mechanism. The relevant trust decision belongs to the application that exposes the connection. In this case, the public interface needs fewer operations than the installed extension provides.

Canned queries preserve a useful API

Datasette documents canned queries as named SQL statements in metadata. The 0.64 query documentation explains that these queries receive their own URLs and support named parameters. A visitor supplies parameter values while the publisher supplies the statement. Results can also be retrieved as JSON.

For a minimal non-spatial example, suppose places.db contains a places table with a name column. The following metadata offers a named lookup without requiring callers to write SQL.

{
  "allow_sql": false,
  "databases": {
    "places": {
      "queries": {
        "by_name": {
          "sql": "select name from places where name = :name"
        }
      }
    }
  }
}

Running Datasette with that metadata gives the query the path /places/by_name. The statement remains fixed as the name value changes. A spatial lookup follows the same arrangement, with the selected spatial functions written into the canned SQL. The release links a location-to-time-zone tutorial as a concrete application of this approach.

Named parameters have documented type behavior that matters for spatial APIs. Datasette treats their values as SQLite strings. A query that needs numeric comparisons should cast parameters to the appropriate numeric type. Choosing the statement therefore includes choosing how its inputs become values used by the spatial calculation. Merely moving a query into metadata does not supply that application-specific interpretation.

Table permissions and SQL permissions are separate

The 0.64 authentication documentation describes another consequence of exposing arbitrary SQL. Restricting access to a table through an allow block does not stop visitors from querying that table through custom SQL. The documentation tells publishers restricting individual tables to control allow_sql as well.

That behavior follows the difference between an application page and a database query. Permission to open a particular table page governs that route. Permission to submit SQL gives a caller another route to the data. A publisher cannot infer the second policy from the first. Datasette documents the two decisions separately because each enables a different operation.

More selective policies remain possible. An allow_sql block can permit only an actor with a particular identity, and a database-specific block can scope that permission to one database. Authentication plugins supply actor information, while permission rules decide what that actor may do. Disabling arbitrary SQL by default provides a starting policy for an installation that grants exceptions deliberately.

The SpatiaLite case gives that policy a concrete purpose. Published tables can remain browsable, and named queries can continue using selected spatial operations. The application decides which SQL reaches the extension, while the caller supplies only the inputs that those statements accept.