SQLite compilation options
(1) By anonymous on 2020-12-09 16:00:17 [link] [source]
Hi, I wonder if SQLite can be compiled only with some commands like CREATE TABLE and INSERT and no other DELETE or ALTER posibilities. I'm interested do use it in a blockchain system where changes are unwanted.
(2) By Warren Young (wyoung) on 2020-12-09 16:06:28 in reply to 1 [link] [source]
I don’t know about compile time, but you can prevent such things at run time on a given connection by registering an authorizer.
(3) By Ryan Smith (cuz) on 2020-12-09 19:36:00 in reply to 1 [source]
I believe it is relatively easy to fork SQLite into a tool that can be prevented to do many things... However, that does not solve your problem.
The very premise of a block chain system is that the records are checkpointed (and hashed - the "blocks" in the chain), unidirectional and immutably linked (the "chain"). This cannot be the case with an SQLite file. Even if you take every precaution and expend every effort to make your compiled app not change the DB, any other SQLite engine will be able to.
What you have to guarantee in a blockchain system is that the record (file) is:
- A: Unidirectional - i.e. one cannot in anyway remove or undo a record that is not the absolute last record, and even then, never back beyond a checkpointed transaction.
- B: Immutable, i.e. that I cannot change a single byte in the file to alter the meaning of it without also destroying the veracity of it,
- C: Safe so that A and B cannot be violated undetectably, even from outside the app/SQLite/engine that created the file. That makes it very safe for the file to be very public and distributed prolifically - which is indeed a requirement of a good blockchain and what makes it "Trusted by Mutually Suspicious Groups" in the words of the original proponent of such a system back in the 80's.
The power of the blockchain lies in the file format specification itself much more than in the engine/app that creates it. The algorithm is usually very simple and very public.
You can probably fork sqlite (or perhaps simply make a VFS or VTAB) to read or create any current blockchain format, but it would never be able to edit it, at least not to the point where one can fool the peer network. That problem/achievement is however due to peer agreement on the file format and progression, and not the inability of the programmer making the editor.
What I'm hoping to get to with all the above is: Looking for a way to make an SQLite compiled app without certain functions is a bit like being the boss of a big company while suffering from severe flatulence and then resorting to hiring only deaf people so the problem does not become known.
At some point, a real sqlite engine will touch the file, and then your game is over.
If all that is irrelevant and you would like to still make a blockchain reader/adder where you control functionality,
- see the VFS guide here: sqlite.org/vfs.html,
- see the Virtual Table guide here: sqlite.org/vtab.html, and
- how to block specific SQL functions (at least on a connection you control) here: sqlite.org/c3ref/set_authorizer.html.