We ran into a similar issue while working on our own (semi-custom) SQLite encryption mechanisms. In my case, it was caused by the SQLite page cache reading the "File Change Counter" (Header page, offset 24) directly from the ciphertext *before* decrypting the page. (Presumably, this is done for efficiency: No point in decrypting the page if nothing has changed.)
In my case, I had organized my ciphertext so that there happened to be some unencrypted housekeeping data at that location, such that the File Change Counter did not appear to change. (It did change in the *decrypted* page; just not in the raw, encrypted format.) This caused exactly the issues you report: Changes from one client were not seen by another, quickly leading to database corruption. The solution, in my case, was to ensure that encrypted header bytes 24-27 always changed when the page was re-written, to ensure that other clients knew there had been a change. Not sure if that's what's happening here with System.Data.SQLite, but it does sound very similar to what I encountered. ~Eric On Wed, Sep 4, 2013 at 8:32 AM, Brzozowski, Christoph < christoph.brzozow...@siemens.com> wrote: > Hello, > > Our application uses the System.Data.SQLite .NET Wrapper ( version > 1.0.66.0 ) in a multi user scenario, where multiple users on different > machines access the same database, which lies on a network share, or in a > single user scenario, where the database lies locally but is accessed > simultaneously by multiple processes on the same machine. > > Initially we used a database without password protection and some of our > synchronization mechanisms which periodically polled data from the database > worked as intended and our application performed as expected. > > When we switched the database to an encrypted one, by adding a password to > the connection string passed to the ADO.NET Sqlite provider, the > synchronization mechanisms ceased to work. > > Before this modification every time one process wrote some data to the > database by issuing an UPDATE Sql statement, the modified data was > instantaneously available to other simultaneously running processes when > requerying it through a SELECT statement. > > After activating password encryption, the read/write behavior seemed to > change, as the modifications made by one process, were not visible to other > processes reading from the database at the same time. If you for example > add some rows to a table in one process, and then refresh the view of the > same table in another process by requerying and redisplaying it, you would > not see the additional rows when using an encrypted database. In contrary > when using an unencrypted database the new rows would get displayed as > expected. > > Even worse, hard-killing all processes accessing the encrypted database > with eventually running write operations would lead to a corrupt database. > At least I assume that the database got corrupted as performing subsequent > read operations using the DataReader classes of System.Data.SQLite to > obtain table row data led to an AccessViolation exception. This does not > happen when I repeat the same scenario with a database where encryption is > disabled. All data is intact, no exceptions when reading it. > > Is this behavior a bug caused by some caching introduced, when encryption > is enabled, or is this a known limitation of System.Data.SQLite when using > encrypted databases? I.e. encrypted databases should not be used in a > multi-user or multi-process scenario. > > Many thanks and kind regards, > Christoph Brzozowski > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
