I just wanted to bring this one again up. Unfortunately there was no reaction from the system.data.sqlite nor from the SQLite maintainers.
I've patched the crypt.c module to ignore the change counter, so it will never be encrypted. But that's not the best solution. Is there at least a public documentation of the SQLite Encryption Extension API, to get informations if it's allowed to encrypt the first page or if it needs to be excluded? Kind Regards Fabrizio > -----Ursprüngliche Nachricht----- > Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] Im Auftrag von Fabrizio Steiner > Gesendet: Freitag, 16. Dezember 2011 15:49 > An: General Discussion of SQLite Database > Betreff: Re: [sqlite] SQLite reading old data > > 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-bounces@sqlite.o > > > rg > > > > > > > [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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
