SQLite Forum

Timeline
Login

5 forum posts by user galicarnax

2020-09-10
17:41 Reply: Exclusive write-only lock? (Allow only one process read-write, and multiple read-only) (artifact: 740ca69f72 user: galicarnax)

Thanks, all, for the answers. My fault, I didn't formulate the question clearly. Certainly, I know that I can write and read with the same database and use corresponding flags. What I meant is if it was possible to force any process that I may not be aware of (3rd party) to access the DB only in readonly mode when my app uses it. As I can see now, that's not possible. (I had a suspicion it could be possible when I saw there are distinct WAL locks used in the WAL-index file, e.g., WAL_WRITE_LOCK and WAL_READ_LOCK).

09:57 Post: Exclusive write-only lock? (Allow only one process read-write, and multiple read-only) (artifact: d48bb67efe user: galicarnax)

I've read about sqlite3 locks, and from what I understood it seems impossible to achieve what I want... Nevertheless, I'll ask here, just in case.

Is it possible to make it such that an sqlite database (either with rollback journal or in WAL-mode) might be opened only by one process that can modify it, but still allow other processes open it in read-only mode? If I set exclusive lock, once the writer process makes a change, it will block all other processess even from reading. I want a behavior where all processes, except a single one (say, the first one which opens the database), are not allowed to write but allowed to read.

2020-08-31
12:22 Reply: Allow only SELECT statements in a read-write database (is EXPLAIN a good practice?) (artifact: 12cc5acd97 user: galicarnax)

Seems this would suffice, thanks! (I wonder why google didn't come up with this, when I tried various combinations of search terms).

12:18 Edit: Allow only SELECT statements in a read-write database (is EXPLAIN a good practice?) (artifact: 7c97a4da4f user: galicarnax)

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 DELETE, 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 OpenWrite/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 OpenWrite/Clear opcode?

Or is there a better way to filter out read-only queries without using EXPLAIN (and without opening the database in a separate thread)?

10:00 Post: Allow only SELECT statements in a read-write database (is EXPLAIN a good practice?) (artifact: 474d0d5089 user: galicarnax)

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 DELETE, 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 ReadWrite/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 ReadWrite/Clear opcode?

Or is there a better way to filter out read-only queries without using EXPLAIN (and without opening the database in a separate thread)?