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-boun...@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-boun...@sqlite.org>
> > [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
