Yeah, I see the same two solutions. The first solution would also ensure currently encrypted databases will still be working. Whereas changing the encryption would make live a lot harder for currently deployed databases, which would need a conversion.
For testing purposes I just changed the encryption algorithm in System.Data.SQLite to exclude the change counter, so the change counter remains plaintext. My tests with the 3 threads did run fine and without any problems for several runs I've executed. It would be great if there's a statement from the SQLite developers what the requirements for the encryptor are. And what their opinion is regarding this issue. > -----Ursprüngliche Nachricht----- > Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] Im Auftrag von Michael Stephenson > Gesendet: Freitag, 16. Dezember 2011 15:27 > An: 'General Discussion of SQLite Database' > Betreff: Re: [sqlite] SQLite reading old data > > I think the only solutions would be: 1) SQLIte changes so that it does not > use > a direct file read/write for the change counter. 2) Have the page-level > encryptor specifically not encrypt certain parts of the database header, such > as the change counter, when saving page 1 to disk. > > I imagine that the direct reads/writes are to help ensure consistency and > recovery in the face of an application crash. > > You might try turning on WAL and see what happens. > > -----Original Message----- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Fabrizio Steiner > Sent: Thursday, December 15, 2011 3:00 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SQLite reading old data > > The answers to your questions : > 1)Yes I'm still using System.Data.SQLite but without any key. > 2)No I don't use WAL. > 3)Running only one thread didn't bring up the problem. > 4)Didn't have any time yet. > 5)Standard System.Data.SQLite with RC4 algorithm impleemnted there. > > I've taken a look into the the caching of SQLite during the afternoon because > I suspected some issues with caching. And I found a problem with the change > counter exactly as you thought. > > I took a look into the source code of the pager and the pager cache. I've > found the pager cache decides if the cache needs to be flushed whenever a > shared lock gets requested. In order to determine if a flush is needed or not, > the change counter of the db file is compared to the stored one in the pager > structure. The change counter is directly retrieved from the underlying OS > (sqlite3OsRead), so if the database is encrypted, the encrypted bytes will be > retrieved here (Sytem.Data.SQLite encrypts the entire database, including > page 1 and the SQLite header). > > So dbFileVers of the pager structure is always the encrypted value. I started > checking if there was a situation where it was used as the plaintext value. > > In the function pager_write_changecounter the change counter will get > incremented, but the encrypted value pPg->pPager->dbFileVer is used. > After incrementation the value will be put back into the page buffer. During > the write of the page 1, the is again encrypted (see, pager_write_pagelist). > After the page has been written the dbFileVers gets updated > (pager_write_pagelist Line 4049) with the encrypted value. > > So at least for incrementing the change counter the plaintext value should be > used. > > When RC4 with the full database encryption (as implemented in > System.Data.SQLite) is used the following happens. > RC4 basically XORs a plaintext byte with a pseudo random byte. Let's assume > the following change counter values, for simplicity consider only the last 4 > bits of it. As we've seen the encrypted change counter is incremented, > encrypted and stored back into the file. Let's consider the following > operations. > > DB Initial State > - Encrypted DB File counter: X Y Z > 0 (The bits X, Y, Z are unknown, but the last bit is 0.) > > First update of Database: > - Update DB, Increment counter: X Y Z > 1 (Adding one to the encrypted counter.) > - New encrypted Value in DB: (X XOR K1) (Y XOR K2) (Z > XOR K3) (1 XOR 1) = 0 (Assuming the LSB of the pseudo byte is 1, the > probability is 1/2 for this if its purely random.) > > Let's update the database again: > - Update DB, Increment counter: (X XOR K1) (Y XOR K2) (Z > XOR K3) 1 (Adding one to the encrypted counter.) > - New encrypted Value in DB: ((X XOR K1) XOR K1) ((Y XOR K2) XOR K2) ((Z > XOR K3) XOR K3) 0 (The same pseudo byte is again used for encryption.) > = X Y Z > 0 > > As a result after the second db update the encrypted change counter is the > same as before the updates occured. dbFileVers contains 12 more bytes but > these represent the "db size in pages", the "page number of the first free > page" and the "number of free pages". But these may be unchanged if no > new pages were needed and there was no free page. > > => Therefore theres a good chance that a db change is undetected > depending on the encryption algorithm. > > Is it allowed to encrypt the entire databse especially the header with the > pagesize, change counter and so on? > - If yes, then SQLite should make sure all data get's decrypted prior using > these values. > - If not, shouldn't SQLite make sure the crypt api never sees the header? > > I've found no documentation about implementing the crypting api. > > Kind Regards > Fabrizio > > > -----Ursprüngliche Nachricht----- > > Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > > boun...@sqlite.org] Im Auftrag von Michael Stephenson > > Gesendet: Donnerstag, 15. Dezember 2011 20:40 > > An: 'General Discussion of SQLite Database' > > Betreff: Re: [sqlite] SQLite reading old data > > > > A couple more questions: > > > > 1) Does "without encryption" mean still using System.Data.SQLite, > > just without using a key? > > 2) Are you using WAL? > > 3) Do you also see the problem with a single process with two > connections? > > 4) Any chance you could provide a small demo project that > > demonstrates the problem? > > 5) Are you using standard System.Data.SQLite, or the SqlCipher version? > > (http://sqlcipher.net) > > > > I'm pretty fuzzy on this, but here are some (possibly way off base) > > thoughts... > > > > The way I understand things, when you do an update inside of a > > transaction, the new data is written to the log until it is committed, > > and then a rollback involves discarding the log page with the changes. > > This means you expect to see the new data in your "session" but no one > > else should see the new data until you commit. > > > > The question of which version of the data a session sees is, I think, > > tied to some global counter-type metadata that tracks changes. In > > Oracle, this would be perhaps a system change number that would > > determine which undo blocks your session would see versus other > > sessions. I think SQLite has something similar, and I think this is > > stored in the database header on the first database page. > > > > I also recall that there are a few places in SQLite where it reads > > data for page > > 1 using system-level I/O rather than the pager (e.g., direct reads on > > the database file), and this causes issues for page-level encryptors > > because they don't get an opportunity to decrypt the encrypted page > > before SQLite reads that piece of data, so they typically have to > > hard-code some value at that data location or hope that SQLite ignores > > what would be an obviously invalid value. I think the change counter > > is > one of these items. > > > > Maybe folks with much more understanding could make sense of this, but > > my thought is that the equivalent of the "system change number" is > > being mucked up by the page-level encryptor, causing SQLite to get the > > old data because as far as it knows that's the data at the given > > (invalid) > change count. > > > > Well, something like that. Again, probably way off base, but I had a > > couple of minutes and thought I'd throw it out there... > > > > ~Mike > > > > -----Original Message----- > > From: > > sqlite-users-boun...@sqlite.org<mailto:sqlite-users-boun...@sqlite.org > > > > > [mailto:sqlite-users-boun...@sqlite.org]<mailto:[mailto:sqlite-users-b > > oun...@sqlite.org]> On Behalf Of Fabrizio Steiner > > Sent: Thursday, December 15, 2011 9:01 AM > > To: General Discussion of SQLite Database > > Subject: Re: [sqlite] SQLite reading old data > > > > I think the following observation, which I made in the meantime, may > > be very interesting. Which also gives a hint to a problem with SQLite > > in combination with the encryption of System.Data.SQLite. > > > > I tried the same test with an unencrypted database, and surprisingly > > the 3 threads do their work without any exception. I executed several > > test runs with the 3 threads and it wasn't reproducable anymore. > > Activating the encryption again it's reproducable and exceptions occur > > due to an old stamp that is being retrieved. > > > > Answers to your questions: > > - No connections are shared at all between threads. So every thread > > creates > > 2 private connections. > > - Updating always occurs in the second connection. > > - The after-update checks will be perfomed in the same transaction as > > the update itself. > > - No thread ever deletes a row. > > > > Regards > > Fabrizio > > > > > -----Ursprüngliche Nachricht----- > > > Von: > > > sqlite-users-boun...@sqlite.org<mailto:sqlite-users-bounces@sqlite.o > > > rg> [mailto:sqlite-users- > > > boun...@sqlite.org<mailto:boun...@sqlite.org>] Im Auftrag von Igor > > > Tandetnik > > > Gesendet: Donnerstag, 15. Dezember 2011 14:26 > > > An: sqlite-users@sqlite.org<mailto:sqlite-users@sqlite.org> > > > Betreff: Re: [sqlite] SQLite reading old data > > > > > > Fabrizio Steiner <f...@bbtsoftware.ch<mailto:f...@bbtsoftware.ch>> > wrote: > > > > I'm currently facing a problem with the System.Data.SQLite ADO.NET > > > > Provider. I'm using stamps (simple integer column) on the rows in > > > > order to detect if another user has altered the same datarow > > > > during the > > > time the first user started the edit and when he invoked the save. > > > Every update increases this stamp and the update statement is > > > constrained with the old stamp. If the update has > > > > no affected rows, there are different cases which are checked: > > > > - If the datarow has been deleted, the user is informed about this. > > > > - If the datarow still exists the current stamp in the database is > > retrieved. > > > > - If the db stamp is greater than the reference stamp, the user > > > > will be informed that another user changed the data in the meantime. > > > > - If the db stamp is smaller or equal to the reference stamp, an > > > > exception > > > is thrown because the stamp can never get smaller. > > > > > > > > I tried to simulate 3 users which are updating a datarow in the > > > > same table, every user changes it's own row. Basically I had 3 > > > > threads each of one first retrieving a row in one connection. > > > > Updating the data and > > > storing it back into the table with another connection. Every thread > > > made a loop with several edits and a sleep in between. > > > > > > Do you have two connections, each shared by three threads; or six > > > separate connections? Do you ever perform any writing on the > > > connection that reads the original version of the row? Do you > > > perform after-update checks on the same connection as the update > > > itself? Do you wrap the update and the checks together in a transaction? > > > > > > How is a particular "datarow" identified? Is it possible for one > > > thread to delete a row, and then for another to insert it back later > > > as new (presumably with the version stamp starting back from zero)? > > > -- > > > Igor Tandetnik > > > > > > _______________________________________________ > > > sqlite-users mailing list > > > sqlite-users@sqlite.org<mailto:sqlite-users@sqlite.org> > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org<mailto:sqlite-users@sqlite.org> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org<mailto: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 > > _______________________________________________ > 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
