1

)

I'm deveoping a program using an SQLite database I acces via QSqlDatabase. I'd like to handle the (hopefully rare) case when some changes are done to the database which are not caused by the program while it's running (e. g. the user could remove write access, move or delete the file or modify it manually).

I tried to use a QFileSystemWatcher. I let it watch the database file, and in all functions wrtiting something to it, I blocked it's signals, so that only "external" changes would trigger the changed signal.

Problem is that the check of the QFileSystemWatcher and/or the actual writing to disk of QSqlDatabase::commit() seems not to happen in the exact moment I call commit(), so that actually, first the QFileSystemWatcher's signals are blocked, then I change some stuff, then I unblock them and then, it reports the file to be changed.

I then tried to set a bool variable (m_writeInProgress) to true each time a function requests a change. The "changed" slot then checks if a write action has be requested and if so, sets m_writeInProgress to false again and exits. This way, it would only handle "external" changes.

Problem is still that if the change happens in the exact moment the actual writing is going on, it's not catched.

So possibly, using a QFileSystemWatcher is the wrong way to implement this.

How could this be done in a safe way?

Thanks for all help!

Edit:

I found a way to solve a part of the problem. Starting an exclusive lock on the database file prevents other connections from changing it. It's quite simple, I just have to execute

PRAGMA locking_mode = EXCLUSIVE
BEGIN EXCLUSIVE
COMMIT

and handle the error that emerges if another instance of my program trys to access the database.

What's left is to know if the user (accidentally) deleted the file during runtime ...

Tobias Leupold
  • 1,512
  • 1
  • 16
  • 41
  • The destructive changes done to the database file would be perfectly detectable by the next SQL query you'd attempt to run from your app: it would fail with some error which you'd need to process. As a part of processing you can check whether the database file still exists, it's writable etc. However, this won't detect malicious changes done properly i.e. if some data within some table cell got changed externally. To catch this you can e.g. set up [triggers](https://www.sqlitetutorial.net/sqlite-trigger/) catching tables updates and disable them for your own changes. – Dmitry May 30 '18 at 12:00
  • It is possible to execute [callbacks](https://www.sqlite.org/c3ref/update_hook.html) on triggered events in SQLite, however, I'm not sure whether Qt exposes this possibility within its QtSql API. – Dmitry May 30 '18 at 12:04
  • @Dmitry Those callbacks to nothing to modifications done from other than a given connection. They are specific to a given connection, and have a fairly niche uses. They are good for what they work for, but they are not a cure all. They have nothing to do with server-centric callbacks. – Kuba hasn't forgotten Monica Jun 01 '18 at 18:56

1 Answers1

0

First of all, there's no SQLITE support for this: SQLITE only supports monitoring changes created over a database connection within your direct control. Whatever happens in a separate process concurrently with your process, or when your process is not running, is by design completely out of your control.

The canonical solution to this problem is to encrypt the database with a key specific to your application (and perhaps user, etc.). Then, no third-party process can modify the database using SQLITE. Of course any process can corrupt your database, or get rid of it -- that's too bad. You can detect corruption trivially by using cryptographic signatures, perhaps even error correcting codes so as to be able to restore the data should a certain amount of corruption happen. You don't need notifications of someone moving or deleting the database file: you will know when you attempt to open the database and the "file not found" error is given back to you.

Of course all of the above requires a custom VFS implementation. That's very much par for the course.

Kuba hasn't forgotten Monica
  • 95,931
  • 16
  • 151
  • 313
  • Database encryption would be a bit too much I think. It's not about malicious changes, it's more about preventing PEBCAK breakage of the database (user opens the same file with another instance or deletes it while the program is running). See my edit! – Tobias Leupold Jun 01 '18 at 16:49
  • For deletion while running you have locking. And you're more than free to keep backups. – Kuba hasn't forgotten Monica Jun 01 '18 at 18:54