[sqlite] Prevent database file from being overwritten by other processes
On 04/08/2015 10:52 PM, R.Smith wrote: > > > On 2015-04-08 05:38 PM, Dan Kennedy wrote: >> On 04/08/2015 09:51 PM, R.Smith wrote: >>> >>> >>> On 2015-04-08 04:18 PM, Fabian Pr?bstl wrote: Hi there! Currently, we are using SQLite as our application file format for a Windows 7/C#/System.Data.SQLite based desktop application. We only allow one instance to open the file by running "set locking_mode=EXCLUSIVE;BEGIN EXCLUSIVE;COMMIT" when connecting to the database. >>> >>> BEGIN EXCLUSIVE - Locks the database from other SQLite3 database >>> connections for the time being. >>> COMMIT; - Unlocks it again - so calling all this in one go is >>> pointless. >> >> Almost always correct. But the "PRAGMA locking_mode=EXCLUSIVE" thing >> changes the behaviour: >> >> https://www.sqlite.org/pragma.html#pragma_locking_mode > > But you need an actual SELECT to get a shared lock and an actual write > operation to lock it exclusively, just starting the transaction and > ending it does nothing to that effect? Or is my understanding wrong? That's the usual case. But "BEGIN EXCLUSIVE" actually does take an exclusive lock: https://www.sqlite.org/lang_transaction.html
[sqlite] Prevent database file from being overwritten by other processes
On 04/08/2015 09:51 PM, R.Smith wrote: > > > On 2015-04-08 04:18 PM, Fabian Pr?bstl wrote: >> Hi there! >> >> Currently, we are using SQLite as our application file format for a >> Windows 7/C#/System.Data.SQLite based desktop application. We only >> allow one instance to open the file by running "set >> locking_mode=EXCLUSIVE;BEGIN EXCLUSIVE;COMMIT" when connecting to the >> database. > > BEGIN EXCLUSIVE - Locks the database from other SQLite3 database > connections for the time being. > COMMIT; - Unlocks it again - so calling all this in one go is pointless. Almost always correct. But the "PRAGMA locking_mode=EXCLUSIVE" thing changes the behaviour: https://www.sqlite.org/pragma.html#pragma_locking_mode > > That said, database locking serves only to protect from other database > changes... There is no way to prevent a user from intentional messing > with any file if they have the privileges to do so. Best practice is > to keep the file in your program's assigned /programdata folder or the > user folders (/AppData/Roaming/yourApp/ is the usual) - the typical > user won't go mess there or even know to look there. Other than that, > the entire point of an operating system is to serve its user, not your > program - as it should, so you cannot unfortunately protect users > against themselves. > > If this is to do with your own security being a concern (i.e. you are > not trying to safeguard the user) then I would strongly suggest an > encryption module or using a DB with user-level locking. (Even then > you still won't be able to protect against a willful user deleting, > moving, overwriting or otherwise accessing a file). > > At a tangent: > How would you feel if your operating system disallowed you those > privileges because some program you installed asked it to? I would > change operating systems immediately - Viruses are a big enough > problem as it is - imagine being unable to get rid of them... > > Good luck! > Ryan > > >> >> This all works fine, however a user can still open Windows Explorer >> and copy paste a file with the same name but different content (e.g. >> an empty file) over an existing, exclusively locked database. From >> what I found out with the OpenedFilesView tool, SQLite seems to open >> the file with SHARED_WRITE, which explains why *any* process can >> overwrite the contents. >> >> Is there an easy way of configuring / changing this so that >> SHARED_WRITE is not acquired? Will SQLite even function? Is it just >> easier to create a hidden copy and work on that? >> >> Thanks for the advice >> Fabian >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Prevent database file from being overwritten by other processes
On 2015-04-08 06:00 PM, Dan Kennedy wrote: > On 04/08/2015 10:52 PM, R.Smith wrote: >> >> >> On 2015-04-08 05:38 PM, Dan Kennedy wrote: >>> On 04/08/2015 09:51 PM, R.Smith wrote: On 2015-04-08 04:18 PM, Fabian Pr?bstl wrote: > Hi there! > > Currently, we are using SQLite as our application file format for > a Windows 7/C#/System.Data.SQLite based desktop application. We > only allow one instance to open the file by running "set > locking_mode=EXCLUSIVE;BEGIN EXCLUSIVE;COMMIT" when connecting to > the database. BEGIN EXCLUSIVE - Locks the database from other SQLite3 database connections for the time being. COMMIT; - Unlocks it again - so calling all this in one go is pointless. >>> >>> Almost always correct. But the "PRAGMA locking_mode=EXCLUSIVE" thing >>> changes the behaviour: >>> >>> https://www.sqlite.org/pragma.html#pragma_locking_mode >> >> But you need an actual SELECT to get a shared lock and an actual >> write operation to lock it exclusively, just starting the transaction >> and ending it does nothing to that effect? Or is my understanding wrong? > > That's the usual case. But "BEGIN EXCLUSIVE" actually does take an > exclusive lock: > > https://www.sqlite.org/lang_transaction.html Goodness... Where was my mind?! Thank you Dan and apologies for the round trip, and for misleading the OP! BEGIN EXCLUSIVE will of course instill the lock immediately.
[sqlite] Prevent database file from being overwritten by other processes
I have the same problem but in order to prevent overriding I actually make a hidden copy of the file in the local app directory when opening it. This allows me to recover it if the application breaks and to implement save, save As etc. and if it is no longer there I can still save where it was. just an idea Regards, Maarten, > "Fabian Pr?bstl" | > Hi there! > > Currently, we are using SQLite as our application file format for a Windows > 7/C#/System.Data.SQLite based desktop application. We only allow one instance > to > open the file by running "set locking_mode=EXCLUSIVE;BEGIN EXCLUSIVE;COMMIT" > when > connecting to the database. > > This all works fine, however a user can still open Windows Explorer and copy > paste > a file with the same name but different content (e.g. an empty file) over an > existing, exclusively locked database. From what I found out with the > OpenedFilesView tool, SQLite seems to open the file with SHARED_WRITE, which > explains why *any* process can overwrite the contents. > > Is there an easy way of configuring / changing this so that SHARED_WRITE is > not > acquired? Will SQLite even function? Is it just easier to create a hidden > copy and > work on that? > > Thanks for the advice > Fabian > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Prevent database file from being overwritten by other processes
On 2015-04-08 05:38 PM, Dan Kennedy wrote: > On 04/08/2015 09:51 PM, R.Smith wrote: >> >> >> On 2015-04-08 04:18 PM, Fabian Pr?bstl wrote: >>> Hi there! >>> >>> Currently, we are using SQLite as our application file format for a >>> Windows 7/C#/System.Data.SQLite based desktop application. We only >>> allow one instance to open the file by running "set >>> locking_mode=EXCLUSIVE;BEGIN EXCLUSIVE;COMMIT" when connecting to >>> the database. >> >> BEGIN EXCLUSIVE - Locks the database from other SQLite3 database >> connections for the time being. >> COMMIT; - Unlocks it again - so calling all this in one go is pointless. > > Almost always correct. But the "PRAGMA locking_mode=EXCLUSIVE" thing > changes the behaviour: > > https://www.sqlite.org/pragma.html#pragma_locking_mode But you need an actual SELECT to get a shared lock and an actual write operation to lock it exclusively, just starting the transaction and ending it does nothing to that effect? Or is my understanding wrong?
[sqlite] Prevent database file from being overwritten by other processes
In the Amalgamation Source search for the line (around 37836): dwShareMode = FILE_SHARE_READ | FILE_SHARE_WRITE; If you change this to: dwShareMode = 0 then use this version of sqlite3.c in your application. This will open the file for "exclusive" access and not shared access. This means that the file will not be able to be read/written/deleted while your application has the file open. "Locking Mode" is not the same as "ShareMode", and the standard library does not have the capability to open a database for exclusive (non-shared) access. It should be noted that even if you open the file in exclusive (non-shared) mode, it can still be deleted or renamed while in use -- but the contents cannot be changed (or read) by another process while it is open. --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-Original Message- >From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- >bounces at mailinglists.sqlite.org] On Behalf Of Fabian Pr?bstl >Sent: Wednesday, 8 April, 2015 08:18 >To: sqlite-users at mailinglists.sqlite.org >Subject: [sqlite] Prevent database file from being overwritten by other >processes > >Hi there! > >Currently, we are using SQLite as our application file format for a >Windows 7/C#/System.Data.SQLite based desktop application. We only allow >one instance to open the file by running "set >locking_mode=EXCLUSIVE;BEGIN EXCLUSIVE;COMMIT" when connecting to the >database. > >This all works fine, however a user can still open Windows Explorer and >copy paste a file with the same name but different content (e.g. an empty >file) over an existing, exclusively locked database. From what I found >out with the OpenedFilesView tool, SQLite seems to open the file with >SHARED_WRITE, which explains why *any* process can overwrite the >contents. > >Is there an easy way of configuring / changing this so that SHARED_WRITE >is not acquired? Will SQLite even function? Is it just easier to create a >hidden copy and work on that? > >Thanks for the advice >Fabian >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Prevent database file from being overwritten by other processes
On 2015-04-08 04:18 PM, Fabian Pr?bstl wrote: > Hi there! > > Currently, we are using SQLite as our application file format for a Windows > 7/C#/System.Data.SQLite based desktop application. We only allow one instance > to open the file by running "set locking_mode=EXCLUSIVE;BEGIN > EXCLUSIVE;COMMIT" when connecting to the database. BEGIN EXCLUSIVE - Locks the database from other SQLite3 database connections for the time being. COMMIT; - Unlocks it again - so calling all this in one go is pointless. That said, database locking serves only to protect from other database changes... There is no way to prevent a user from intentional messing with any file if they have the privileges to do so. Best practice is to keep the file in your program's assigned /programdata folder or the user folders (/AppData/Roaming/yourApp/ is the usual) - the typical user won't go mess there or even know to look there. Other than that, the entire point of an operating system is to serve its user, not your program - as it should, so you cannot unfortunately protect users against themselves. If this is to do with your own security being a concern (i.e. you are not trying to safeguard the user) then I would strongly suggest an encryption module or using a DB with user-level locking. (Even then you still won't be able to protect against a willful user deleting, moving, overwriting or otherwise accessing a file). At a tangent: How would you feel if your operating system disallowed you those privileges because some program you installed asked it to? I would change operating systems immediately - Viruses are a big enough problem as it is - imagine being unable to get rid of them... Good luck! Ryan > > This all works fine, however a user can still open Windows Explorer and copy > paste a file with the same name but different content (e.g. an empty file) > over an existing, exclusively locked database. From what I found out with the > OpenedFilesView tool, SQLite seems to open the file with SHARED_WRITE, which > explains why *any* process can overwrite the contents. > > Is there an easy way of configuring / changing this so that SHARED_WRITE is > not acquired? Will SQLite even function? Is it just easier to create a hidden > copy and work on that? > > Thanks for the advice > Fabian > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Prevent database file from being overwritten by other processes
On 8 Apr 2015, at 3:51pm, R.Smith wrote: > BEGIN EXCLUSIVE - Locks the database from other SQLite3 database connections > for the time being. > COMMIT; - Unlocks it again - so calling all this in one go is pointless. > > That said, database locking serves only to protect from other database > changes... There is no way to prevent a user from intentional messing with > any file if they have the privileges to do so. Correct. Part of my testing for setup security is as follows: Open a sqlite database file in a text editor (NOTEPAD.EXE, TextEdit, whatever). Type some gibberish characters at a few places in the file. Wait until an app is using it via SQLite calls. Hit 'Save'. You can't do anything to prevent it. But your business procedure (whatever software you run, whatever corruption testing you do, whatever corruption testing your software does) needs to be able to detect the problem and raise an alarm. Simon.
[sqlite] Prevent database file from being overwritten by other processes
Even if there were a simple way to protect an SQLite db file from being casually (or even maliciously) overwritten by a user (which there isn't), it is quite impossible to prevent a user with "root privileges" from accessing/altering/deleting/moving/renaming ANY file on any operating system (that is, after all, what root privileges are designed to do...). SQLite is designed to work with multiple processes/threads on the same system accessing the same file on local storage. If you need to have a special version that only allows one thread of one process to open the file you can change the appropriate lines in the source code und build your own image. I would strongly recommend statically linking your "special" copy of SQLite with your application. -Urspr?ngliche Nachricht- Von: Fabian Pr?bstl [mailto:Fabian.Proebstl at nanotemper.de] Gesendet: Mittwoch, 08. April 2015 16:18 An: sqlite-users at mailinglists.sqlite.org Betreff: [sqlite] Prevent database file from being overwritten by other processes Hi there! Currently, we are using SQLite as our application file format for a Windows 7/C#/System.Data.SQLite based desktop application. We only allow one instance to open the file by running "set locking_mode=EXCLUSIVE;BEGIN EXCLUSIVE;COMMIT" when connecting to the database. This all works fine, however a user can still open Windows Explorer and copy paste a file with the same name but different content (e.g. an empty file) over an existing, exclusively locked database. From what I found out with the OpenedFilesView tool, SQLite seems to open the file with SHARED_WRITE, which explains why *any* process can overwrite the contents. Is there an easy way of configuring / changing this so that SHARED_WRITE is not acquired? Will SQLite even function? Is it just easier to create a hidden copy and work on that? Thanks for the advice Fabian ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] Prevent database file from being overwritten by other processes
Hi there! Currently, we are using SQLite as our application file format for a Windows 7/C#/System.Data.SQLite based desktop application. We only allow one instance to open the file by running "set locking_mode=EXCLUSIVE;BEGIN EXCLUSIVE;COMMIT" when connecting to the database. This all works fine, however a user can still open Windows Explorer and copy paste a file with the same name but different content (e.g. an empty file) over an existing, exclusively locked database. From what I found out with the OpenedFilesView tool, SQLite seems to open the file with SHARED_WRITE, which explains why *any* process can overwrite the contents. Is there an easy way of configuring / changing this so that SHARED_WRITE is not acquired? Will SQLite even function? Is it just easier to create a hidden copy and work on that? Thanks for the advice Fabian
[sqlite] Prevent database file from being overwritten by other processes
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/08/2015 07:18 AM, Fabian Pr?bstl wrote: > Is there an easy way of configuring / changing this so that > SHARED_WRITE is not acquired? Will SQLite even function? Is it just > easier to create a hidden copy and work on that? For something this critical, an alternate approach is to restructure it so you have a service which does the database and similar work, and a separate gui that talks to the service. (Note I mean service in the Windows sense, much like a daemon on Unix.) You can run the service as a different user, which means the gui application user can't even access the file directly, nor cause mayhem. That separate structuring also has other benefits such as allowing for a separate command line client, makes testing easier, and you can put the service and gui on different machines. Multiple instances of the gui can run too which may be very useful. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlUlVvkACgkQmOOfHg372QTvbgCg2cy3bsDgRj8TiOLbsvWbr8cQ 1VoAn2ZAajXnlQwlsd9mzkf3R7k/racZ =kyDP -END PGP SIGNATURE-
