issues: 1552368054

This data as json

id node_id number title user state locked assignee milestone comments created_at updated_at closed_at author_association pull_request body repo type active_lock_reason performed_via_github_app reactions draft state_reason
1552368054 I_kwDOBm6k_c5ch0G2 2000 rewrite_sql hook 193185 open 0     1 2023-01-23T01:02:52Z 2023-01-23T06:08:01Z   CONTRIBUTOR  

I'm not sold that this is a good idea, but thought it'd be worth writing up a ticket. Proposal: add a hook like

python def rewrite_sql(datasette, database, request, fn, sql, params)

It would be called from Database.execute, Database.execute_write, Database.execute_write_script, Database.execute_write_many before running the user's SQL. fn would indicate which method was being used, in case that's relevant for the SQL inspection -- for example execute only permits a single statement.

The hook could return a SQL statement to be executed instead, or an async function to be awaited on that returned the SQL to be executed.

Plugins that could be written with this hook:

  • https://github.com/cldellow/datasette-ersatz-table-valued-functions would use this to avoid monkey-patching
  • a plugin to inspect and reject unsafe Spatialite function calls (reported by Simon in Discord)
  • a plugin to do more general rewrites of queries to enforce table or row-level security, for example, based on the currently logged in actor's ID
  • a plugin to maintain audit tables when users write to a table
  • a plugin to cache expensive queries (eg the queries that drive facets) - these could allow stale reads if previously cached, then refresh them in an offline queue

Flaws with this idea:

execute_fn and execute_write_fn would not go through this hook, which limits the guarantees you can make about it for security purposes.

 107914493 issue     
{
    "url": "https://api.github.com/repos/simonw/datasette/issues/2000/reactions",
    "total_count": 0,
    "+1": 0,
    "-1": 0,
    "laugh": 0,
    "hooray": 0,
    "confused": 0,
    "heart": 0,
    "rocket": 0,
    "eyes": 0
}
    

Links from other tables