Allow only SELECT statements in a read-write database (is EXPLAIN a good practice?)
(1.1) By Galicarnax (galicarnax) on 2020-08-31 12:18:57 edited from 1.0 [link] [source]
My application makes use of sqlite. The application has plugins, implemented by third-parties using an embedded language. The host application should have full read-write access to the database, and it also allows plugins to make queries from the database through dedicated API. All of that happens in a single thread.
For security reasons, I would like to restrict plugins' queries to
SELECT statements, so that plugins cannot modify the database even inadvertently. The simplest approach would be to examine the query from a plugin and reject it if it contains keywords like
UPDATE, ... (at least not within quotes). That's cearly dumb. Another approach would be to use EXPLAIN on that query, and reject it if it has
Clear anywhere in the opcodes
But as the linked page explains, applications should not use
EXPLAIN as the format of the output might be subject to change in future versions. Does this imply I cannot even count on the presence of
Or is there a better way to filter out read-only queries without using
EXPLAIN (and without opening the database in a separate thread)?
You want an authorizer:
(3.1) Originally by curmudgeon with edits by Richard Hipp (drh) on 2020-08-31 11:14:47 from 3.0 in reply to 1.0 [source]
Would this suffice
(5) By Galicarnax (galicarnax) on 2020-08-31 12:22:43 in reply to 3.1 [link] [source]
Seems this would suffice, thanks! (I wonder why google didn't come up with this, when I tried various combinations of search terms).
Others have already posted two widely-used solutions. However, a good response to your post depends on which parts of your software you trust.
You might like to have the host application open two different connections to the database: one normal one which it uses to to its own work, and another, read-only one, which it passes to the plugin. That way the plugin does not need to know where the database file is (no need to understand home folders or directory structures), and it cannot write to the database because it has only a read-only connection.