Re: [sqlite] Different read/write behaviour when using System.Data.SQLite .NET Wrapper and using database encryption through a password leads to data inconsistency
Brzozowski, Christoph wrote: Our application uses the System.Data.SQLite .NET Wrapper ( version 1.0.66.0 ) That version was released in April 2010 and is no longer officially supported. 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. Sharing a SQLite database file using a network share on Windows can be problematic, please refer to the following link for further information: https://www.sqlite.org/faq.html#q5 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. The CryptoAPI-based encryption included with System.Data.SQLite is a legacy feature, has known issues, and is officially unsupported. It is being retained only for the purpose of backward compatibility with legacy applications that make use of it. That all being said, you might wish to try using the latest released version of System.Data.SQLite. Alternatively, you might want to look into the commercial SEE extension, which does work with System.Data.SQLite and is fully supported for use with it. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Different read/write behaviour when using System.Data.SQLite .NET Wrapper and using database encryption through a password leads to data inconsistency
Thank you very much for the responses. That was helpful. With best regards, Christoph Brzozowski Siemens AG Industry Sector Industry Automation Division Industrial Automation Systems Process Automation I IA AS PA CIS RD 5 Karl-Legien-Str. 190 53117 Bonn, Germany Tel: +49 228 64805-215 mailto:christoph.brzozow...@siemens.com Siemens Aktiengesellschaft: Chairman of the Supervisory Board: Gerhard Cromme; Managing Board: Joe Kaeser, Chairman, President and Chief Executive Officer; Roland Busch, Brigitte Ederer, Klaus Helmrich, Barbara Kux, Hermann Requardt, Siegfried Russwurm, Peter Y. Solmssen, Michael Suess; Registered offices: Berlin and Munich, Germany; Commercial registries: Berlin Charlottenburg, HRB 12300, Munich, HRB 6684; WEEE-Reg.-No. DE 23691322 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Donnerstag, 5. September 2013 08:08 To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Different read/write behaviour when using System.Data.SQLite .NET Wrapper and using database encryption through a password leads to data inconsistency Brzozowski, Christoph wrote: Our application uses the System.Data.SQLite .NET Wrapper ( version 1.0.66.0 ) That version was released in April 2010 and is no longer officially supported. 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. Sharing a SQLite database file using a network share on Windows can be problematic, please refer to the following link for further information: https://www.sqlite.org/faq.html#q5 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. The CryptoAPI-based encryption included with System.Data.SQLite is a legacy feature, has known issues, and is officially unsupported. It is being retained only for the purpose of backward compatibility with legacy applications that make use of it. That all being said, you might wish to try using the latest released version of System.Data.SQLite. Alternatively, you might want to look into the commercial SEE extension, which does work with System.Data.SQLite and is fully supported for use with it. -- Joe Mistachkin ___ 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] Different read/write behaviour when using System.Data.SQLite .NET Wrapper and using database encryption through a password leads to data inconsistency
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
Re: [sqlite] Different read/write behaviour when using System.Data.SQLite .NET Wrapper and using database encryption through a password leads to data inconsistency
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
