Disable -wal option
SQLite version is 3.27. The journal_mode we set is truncate, but I think this is per connection. If someone connects to the same database by sqlite3.exe for example, the default connection is "delete". So here is the case, if someone drops a malicious -wal file and connect to the same sqlite db we use by setting jounal_mode=wal, which will make that wal file take effect and risk all the data in the db.
There are several things that I think could mitigate this issue, but it doesn't seem to be feasible after reading the documents. But I may miss something. - set the journal_mode at the db level and it cannot be modified - config the database to block specific PRAGMA statement such as PRAGMA journal_mode=wal; - have some mechanics to check whether the wal is valid/legitimate (e.g. created from the original database)
Would you recommend any best practices around it? So that no malicious wal file is executed. I use C# to create/connect the sqlite db.
So here is the case, if someone drops a malicious -wal file and connect to the same sqlite db we use by setting jounal_mode=wal, which will make that wal file take effect and risk all the data in the db.
If a malicious attacker can "connect to the database" and "set wal mode" and "drop a malicious wal file" then there is no need to do so as that attacker can simply obliterate your database directly without going to all the bother and convolution.
The predilection for this sort of construction is quite common where the pre-condition is "after obtaining root/system authority" the following X steps and Y vulnerabilities could be used to compromise the system. In actual fact, once the "obtain root/system authority" has been accomplished there is no further need for any vulnerabilities since you can simply do whatever dim sum (your little heart desires) without further ado.
In other words, the correct response for securing against the situation you have posited is to not allow unauthorized access to the database nor to the filesystem containing the database, and this action is quite outside SQLite3's purview.
In other words, the first defense against "anyone can come in the house and steal my computer and read everything that is on it" is not to hide the computer or encrypt the damn thing, or even to electrify the chassis, but rather to lock the doors!
And if you are worried that someone might break down the door after you have locked it (or pick the lock) then perhaps a motion detector alarm system would be a good next step. There are quite a lot of steps that you can take before running 100,000 Volts through the computer chassis (with a backup power supply) is a necessary defensive step.
Thanks for your reply! And I totally agree with you. The first step should be secure the file system. Actually I should have provided more details on the situation.
There are three levels of security we are trying to implement. Wouldn't it be nice if we don't just lock the door but also set some restrictions on the room/item access? So here is the plan. - file system level: as you mentioned, this is step one, and we are going to set the permissions. - database level: this is what's next and the original post is trying to mitigate this level, so we are seeking global settings such as blocking some PRAGMA commands. - table level: implement some integrity column or mechanics to prevent from unauthorized write.
Still working on the last two levels, and the database level is toughest part at this time. After reading the specs, it seems the there is not such way to block the -wal file. We are trying to make additional security actions besides the file system security, so feasible sanity operations would be nice.
You can build your own custom version of SQLite3 and OMIT all sorts of things including WAL support if you wish. I have no knowledge as to whether using the OMIT_WAL options is tested and works or not.
This will not, however, prevent someone with access to the database file from deleting it, overwriting or otherwise corrupting it, or using their own "program" (for any definition you like of program including but not limited to another copy of SQLite3 or the CLI or even an editor) from accessing and modifying the database file.
Thanks for the info, Keith. Is there any existing function that can achieve similar purpose, which may not exactly be omitting wal, but may be something else like verifying pragma with some security check? Whatever way to secure the db a little more on top of the file system security. The thing is we may not have the flexibility of building our own version as part of the reference process.
(8) By David Raymond (dvdraymond) on 2020-09-23 13:10:02 in reply to 6 [source]
When it comes down to it, SQLite on its own is just a fancy thing that edits a file. If you have rights to open something with a SQLite program, you can open it with any other program. If you compile your specific program with all sorts of fancy stuff removed, but don't trust the user, then it doesn't matter. They can just open up the file with their own version of SQLite compiled with all the bells and whistles still in it. Or open it and write to it with any other program.
It's like putting all sorts of fancy things into your own specific text editor. Someone can just shrug and say "eh, I'll just use notepad to edit it instead." Unless the only access to the data is via a trusted program, then you can't really enforce anything.
You can block pragma commands (and other commands) by the use of the authorizer callback. Also, one way to block the -wal file (or any other file) from within SQLite is a VFS shim.
(7) By Richard Damon (RichardDamon) on 2020-09-23 12:21:52 in reply to 3 [link] [source]
The key issue here is that SQLite runs as 'user' code, so if the user has access to the file, he can do whatever he wants to the file, and if the user doesn't have access to the file, he can't do anything. Yes, this has security implications, but it also has major efficiency implications.
To get the sort of protection you seem to be wanting, you need to put the database behind some security wall which users have no access to, and provide an access API that limits what they can do with the system. This can be done in SQLite, but now the user program no longer is directly using SQLite, but your API that crosses that security barrier, into the 'server' application that is running, perhaps, SQLite.
If that sort of thing is really part of your requirement, the simplest route may not be using SQLite, but some other database package inherently based on the more heavyweight client/server architecture, which can provide such protections.
Just to add to what Richard, David and others said:
There is no application on Earth that can provide the security you are looking for. It's a bit like asking: "How can I give people my car keys, but make it so they cannot adjust the seats, or rip them out completely. Please help me make stronger adjust-resistant seats...". It's not more absurd than the actual question about hardening a database file to which a user has inherent access - it's just that with the car analogy, the absurdity of the question is more clear.
Even with the big database engines, like MSSQL/MySQL/PostGres etc., if the user has access to the FILES on that machine, there is NOTHING, not a single thing that database Engine can do to protect against data loss by malicious attack.
The key for those systems lies mostly in the fact that the physical database files are hidden well-away from users' grabby little hands (hidden by file-system/OS/Server mechanics, not Database mechanics) and the only communication path to the engine is via a pipe or other secure tunnel.
With SQLite, the database ENGINE sits directly INSIDE the user-code operating on a file which already has user-access, and ANY code which understands SQLite can talk to that database if the file is accessible, and ANY code (no need to even understand SQLite) can hurt that file.
Hardening your own SQLite code against the user of your own program has Zero effect when they can simply open it with the next program, or their own thing, or even the freely downloadable SQLite CLI.
Either you CAN put the files where the user cannot reach it directly and use it in read-only mode or via different user (such as on /etc/) (not sure how one would do this in Windows, but there should be a way), or, give the user-program (and by inference, the user itself) write-access to the file, which means they can use anything to hurt it.
If your users are not malicious tech-savvy people, it's usually OK to just run their Queries through the SQLite Authorizer API in your application and avoid hurtful SQL. Any smart-protection on a user-editable file is folly and a complete waste of your time and resources. Just use PostGres or MariaDB then. It's equally free and can sit on a different machine which the User cannot reach. There are also Client-Server SQLite option out there, a bit of a google can find them, but by the time you have to do that, PostGres/MySQL/MariaDB is just better.
SQLite solves the "local-data-storage" problem, it can even be a player in the "data-security" playground in terms of using the SEE extension, but it is not, and never can be a player in the "data-hardening-against-attack" problem playground. That is up to File-systems, OSes and servers.
Thank you, David, Richard, Ryan and another user, for all your thoughtful and detailed input!
I agree that it is not a good idea to simply rely on solving this particular scenario because there are a lot of ways to harm the database (whether it is intentionally or not) once user has access to it, especially the write permission. The file system security is at the first place and should be most emphasized as we are also doing so. The original idea was to try to configure the db on top of the file system security if there are built-in mechanics, but it is not straightforward. Now that I am getting better idea of the way SQLite works, it is probably not worth going that way.