[sqlite] hard links and SQLite
On 1/11/2016 11:52 PM, Scott Doctor wrote: > TOCTTOU? What is that? A friendly local search engine suggests it means "time of check to time of use" ( https://en.wikipedia.org/wiki/Time_of_check_to_time_of_use ) -- Igor Tandetnik
[sqlite] hard links and SQLite
> On 12 January 2016 at 13:26, Scott Hess wrote:
>
> > On Mon, Jan 11, 2016 at 9:12 PM, Felipe Gasper
> > wrote:
> >
> > > On 11 Jan 2016 9:06 PM, Rowan Worth wrote:
> > >
> > >> * if it returns SQLITE_OK and zero rows, the schema hasn't been
> created
> > >> yet
> > >>
> > >
> > > Sure; however, by the time you do the next action it?s possible that
> > > something else will be creating the schema. This is the condition that
> > I?m trying to avoid.
Then just do:
set timeout to something
BEGIN IMMEDIATE
PRAGMA user_version;
if returned value is *not* the user/schema version you want make it so IN THIS
TRANSACTION
{
... create/update schema ...
PRAGMA user_version=x;
}
COMMIT;
This is race condition free and will even work with an existing database in WAL
mode ...
There is no need for all the complication -- it is really quite simple.
Rather than rely on the user_version, you could check anything you like. The
secret sauce is to do all the checking and updating INSIDE the single IMMEDIATE
transaction.
[sqlite] hard links and SQLite
On 2016/01/11 9:37 PM, Warren Young wrote: > The OP was vague about that, but I think the point of his current > gymnastics is to prevent the other process from creating a rogue > schema, or to insert compromising data into a correct schema. To make > it concrete, you could probably write a black hat process that could > get in between the creation of a new Fossil DB file and the initial > schema setup, then add an all-powerful Admin user with a known > password. When the fossil binary regains control from the OS, it will > see that the ?users? table already exists, so it won?t install its > own, leaving the DB file backdoored. That?s why you have to use > something like mkstemp() to create the DB file somewhere only your > process can see it, and with permissions that allow only your user to > open or modify it. There is nothing preventing a rogue anything from adding data to your file, even after your Schema exists. Why would it need this condition to "help" it? Also, if you save passwords without encryption with proper SALTs etc, then worrying about the above is the least of your concerns. I think the OP really means simply other processes from their own system(s) doing a bit of jiggery-pokery when finding an un-made schema - but I could be wrong.
[sqlite] hard links and SQLite
So, if I understand correct, you create a file and then start to add a schema etc. Before the schema is committed (or before the exclusive transaction is started) you assume that other threads/processes /might/ be opening the file and trying to write some data to it (because reading would be harmless), and you further assume, because you have now found corruptions, that this might be the root of such corruptions? I don't see a reason to believe it definitely is, but you could, easily eliminate the options. If an SQLite DB file gets opened (for reading) by another sqlite engine, the result is harmless and should end in errors (Zero possibility of corruption). If such a file gets opened for writing to a schema that does not exist, again, only errors should be thrown, no corruption should be happening. If such a file gets opened, and found to NOT have a schema, then try to create the schema (all within the period between opening the file and adding a schema by the first connection), then the first connection should find the file to be locked, or at least, transaction-locked, and throw the error. I cannot believe there is a plausible and possible way of encountering the described race condition, much less it causing corruption. This doesn't mean it isn't possible (just that I find it hard to believe), but it's very easy to prove programmatically. Have you instrumented the code? Have you checked EVERY return code from every SQLite call? Are you certain the file system isn't of the networking variety? Can you construct a small program that even causes the race condition and causes wrong data to end up in the DB without throwing an error of any sort? (Never-mind corruption, even just proving the wrong data can end up there is enough to make me believe it's possible). This is the first time in forever that anyone has described such a race condition to be, not only possible, but also file-damaging on this forum. If this is possible it should be of great concern to us all, so we want to know the detail very much. The best way to avoid it, is to create the file as temp or just a different name, then rename it after the schema is added - but this advice is superfluous in my opinion - I don't think the race condition is what is going on - which means something else might be wrong that you are unaware of yet, so best is to make sure first that the hypothesis is true. Cheers, Ryan
[sqlite] hard links and SQLite
On Mon, Jan 11, 2016 at 9:12 PM, Felipe Gasper wrote: > On 11 Jan 2016 9:06 PM, Rowan Worth wrote: > >> * if it returns SQLITE_OK and zero rows, the schema hasn't been created >> yet >> > > Sure; however, by the time you do the next action it?s possible that > something else will be creating the schema. This is the condition that I?m > trying to avoid. If something else created the schema between check and use, what's the problem? You can avoid this by doing: - query sqlite_master. - if the query returns rows, the schema exists, exit; else - open a transaction. - query sqlite_master. - if the query returns rows, the schema exists, end transaction and exit; else - create the schema. - commit transaction. Or, just use CREATE IF NOT EXISTS style when creating the schema, and you will successfully not create it the second time. Or just pay attention to your error codes in the create code. -scott
[sqlite] hard links and SQLite
Felipe Gasper wrote: > To prevent race conditions where a 2nd process accesses a newly-created > SQLite file before the creator process can set up the schema, ... And what happens when the 2nd process tries to access the database just before the first creates it? > I?m seeing some corruption in our SQLite files now but have had a hard > time tracking down the cause. Could it be because of the above logic? All changes done (through SQLite) to a database are properly locked. If you see corruption, that's either an effect of some program trying to be clever with hard links, or incorrect locking due to a buggy network file system or virtual machine. Regards, Clemens
[sqlite] hard links and SQLite
On 01/11/2016 18:06, Rowan Worth wrote: > On 12 January 2016 at 03:00, Felipe Gasper wrote: > >> On 11 Jan 2016 1:45 PM, Scott Hess wrote: >> >>> As far as preventing the other process from using it before the schema >>> exists, do "SELECT count(*) FROM sqlite_master", and if the result is 0, >>> the schema does not exist. If you create the schema as a transaction, >>> that >>> will be atomic. >>> >> But in order for that SELECT to avert TOCTTOU errors, we?d have to do >> BEGIN EXCLUSIVE LOCK at the beginning of every single DB handle creation. >> > No, only the connection which is creating the schema needs BEGIN EXCLUSIVE. > The other connections can determine the schema state based on a normal > "SELECT count(*) FROM sqlite_master": > > * if it returns SQLITE_OK and at least one row, the schema has been created > and it can proceed > * if it returns SQLITE_OK and zero rows, the schema hasn't been created yet > * if it returns SQLITE_BUSY, the schema is in the process of being created > (or there's some other EXCLUSIVE transaction in progress, or a transaction > is being committed at this very moment, or an in-progress write transaction > has spilled sqlite's memory cache) > > > As Scott also hinted at, hard linking DB files is dangerous because > connections against each of the links will use different -journal files. In > the event that your schema creation process (or the machine its running on) > crashes halfway through COMMIT, connecting to the permanent database will > cause corruption to be observed (because it's not aware of the other > journal file and thus can't rollback the partial transaction). > > This may also be possible if another process simply connects to the > permanent DB at just the wrong time (ie. halfway through the schema > creation COMMIT)? Or maybe not, in my experience POSIX locks are maintained > across hardlinks but I haven't checked if this is specified by the standard > or file-system dependent. > > > Do your different connections actually run different code? Or are you > potentially in the situation where two threads are trying to create the > same DB at the same time (each via their own .tmp file)? > > -Rowan > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users TOCTTOU? What is that? -- - Scott Doctor scott at scottdoctor.com
[sqlite] hard links and SQLite
On Mon, Jan 11, 2016 at 7:57 PM, Stephan Beal wrote: > On Mon, Jan 11, 2016 at 7:55 PM, Warren Young wrote: > >> On POSIX systems, you can securely create a temp file that only your user >> can see via the mkstemp(3) C library call. SQLite will happily open the >> resulting 0-byte file, allowing you to create your schema inside it. Then >> when the file is set up, you can move it into the desired location and >> change its file modes so that the other processes can open it. >> >> There must be an equivalent of mkstemp() on Windows, doubtless taking 3 >> times as many parameters and with a function name 4 times as long. :) >> > > sqlite exposes the functionality of fetching a temp file name using its > mechanism, but i don't recall at the moment how it's done. A quick google > isn't revealing it but i recall using it but finding out that it doesn't > work with the :memory: VFS. > https://www.sqlite.org/c3ref/file_control.html http://sqlite-users.sqlite.narkive.com/oehps0E9/proper-use-of-sqlite3-file-control -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
[sqlite] hard links and SQLite
On Mon, Jan 11, 2016 at 7:55 PM, Warren Young wrote: > On POSIX systems, you can securely create a temp file that only your user > can see via the mkstemp(3) C library call. SQLite will happily open the > resulting 0-byte file, allowing you to create your schema inside it. Then > when the file is set up, you can move it into the desired location and > change its file modes so that the other processes can open it. > > There must be an equivalent of mkstemp() on Windows, doubtless taking 3 > times as many parameters and with a function name 4 times as long. :) > sqlite exposes the functionality of fetching a temp file name using its mechanism, but i don't recall at the moment how it's done. A quick google isn't revealing it but i recall using it but finding out that it doesn't work with the :memory: VFS. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
[sqlite] hard links and SQLite
On 11 Jan 2016, at 7:06pm, Warren Young wrote: > On Jan 11, 2016, at 11:57 AM, Simon Slavin wrote: > >> Just do BEGIN IMMEDIATE immediately after you open the database. > > Doesn?t that set up a race condition? What prevents the other process from > opening the DB and running its own SQL if the OS?s scheduler happens to take > the CPU away from the legitimate process between sqlite3_open_v2() and > sqlite3_exec(?, ?BEGIN IMMEDIATE?, ?) ? Okay, so your concern is that the other process intrudes between the _open() and the BEGIN. If that's the case then I suggest that you create your new database (including schema and any initial data) with a different filename. Then once this is complete, close it (as far as SQLite is concerned) then use your OS calls to rename the file to whatever your database is normally called. My question is what the other process does. Suppose your program opens the database and spots that there's no schema there (however you do it). What does it do then ? Is there only one process which can create the schema or should it close the file again and back off for a while ? Simon.
[sqlite] hard links and SQLite
On 11 Jan 2016, at 6:25pm, Felipe Gasper wrote: > - create SQLite file as a .tmp beside permanent location I think you can omit everything to do with the link. Just do BEGIN IMMEDIATE immediately after you open the database. This should lock other processes out until you have finished creating your schema and INSERTing initial data. There's another way to do it but that depends on whether the schema is the same for every installation. If it is, then include a copy of your 'starter database' with the application, and when you want to create a 'blank' database just copy this file to wherever you store your working database. Simon.
[sqlite] hard links and SQLite
> What have folks here done to avoid the race condition originally described? > I?ve seen some documentation of SQLite and hard links but Your message appears to be truncated. What if you move the hard link creation part to right after the commit?
[sqlite] hard links and SQLite
On 11 Jan 2016 1:45 PM, Scott Hess wrote: > > As far as preventing the other process from using it before the schema > exists, do "SELECT count(*) FROM sqlite_master", and if the result is 0, > the schema does not exist. If you create the schema as a transaction, that > will be atomic. But in order for that SELECT to avert TOCTTOU errors, we?d have to do BEGIN EXCLUSIVE LOCK at the beginning of every single DB handle creation. That would seem to get expensive? -FG
[sqlite] hard links and SQLite
On 11 Jan 2016 1:30 PM, Bernardo Sulzbach wrote: >> What have folks here done to avoid the race condition originally described? >> I?ve seen some documentation of SQLite and hard links but > > Your message appears to be truncated. Oops! Nothing earth-shattering ? I meant to put ?but didn?t think the avisos about links quite sounded like my situation?. > > What if you move the hard link creation part to right after the commit? The problem is that we didn?t actually see the corruption in internal testing and have never recreated it ourselves ? so, I have no way of knowing now whether anything really fixes the issue. We actually did implement out solution like that originally; the problem was that we were doing data import also prior to the hard-link creation, so for DBs with lots of data we were crashing servers. I didn?t consider, though, that schema creation might be more error-prone than importing data? Interesting. -FG
[sqlite] hard links and SQLite
Hi all, To prevent race conditions where a 2nd process accesses a newly-created SQLite file before the creator process can set up the schema, I?ve implemented logic like this: - create SQLite file as a .tmp beside permanent location - BEGIN EXCLUSIVE LOCK - hard-link the temp file to the permanent location - create schema - COMMIT - unlink temp file This prevents the problem because the permanent file never exists unlocked prior to the schema existence. The 2nd process would either fail to open the DB because it?s locked or open it and see the schema. I?m seeing some corruption in our SQLite files now but have had a hard time tracking down the cause. Could it be because of the above logic? What have folks here done to avoid the race condition originally described? I?ve seen some documentation of SQLite and hard links but -FG
[sqlite] hard links and SQLite
On Jan 11, 2016, at 12:27 PM, Simon Slavin wrote: > > On 11 Jan 2016, at 7:06pm, Warren Young wrote: > >> On Jan 11, 2016, at 11:57 AM, Simon Slavin wrote: >> >>> Just do BEGIN IMMEDIATE immediately after you open the database. >> >> Doesn?t that set up a race condition? What prevents the other process from >> opening the DB and running its own SQL if the OS?s scheduler happens to take >> the CPU away from the legitimate process between sqlite3_open_v2() and >> sqlite3_exec(?, ?BEGIN IMMEDIATE?, ?) ? > > Okay, so your concern is that the other process intrudes between the _open() > and the BEGIN. > > If that's the case then I suggest that you create your new database > (including schema and any initial data) with a different filename. Then once > this is complete, close it (as far as SQLite is concerned) then use your OS > calls to rename the file to whatever your database is normally called. Which is what I suggested with my mkstemp(3) solution. I neglected to say that you should close the DB file before moving it, though, so I guess it?s good to have it restated. Another previously unstated detail is that you can leave the mkstemp-created file permissions alone until after your process has moved and re-opened the database. That lets your process do further modifications to the DB before calling chmod on it to allow other users? processes access to it. > My question is what the other process does. The OP was vague about that, but I think the point of his current gymnastics is to prevent the other process from creating a rogue schema, or to insert compromising data into a correct schema. To make it concrete, you could probably write a black hat process that could get in between the creation of a new Fossil DB file and the initial schema setup, then add an all-powerful Admin user with a known password. When the fossil binary regains control from the OS, it will see that the ?users? table already exists, so it won?t install its own, leaving the DB file backdoored. That?s why you have to use something like mkstemp() to create the DB file somewhere only your process can see it, and with permissions that allow only your user to open or modify it.
[sqlite] hard links and SQLite
On Jan 11, 2016, at 11:57 AM, Simon Slavin wrote: > > Just do BEGIN IMMEDIATE immediately after you open the database. Doesn?t that set up a race condition? What prevents the other process from opening the DB and running its own SQL if the OS?s scheduler happens to take the CPU away from the legitimate process between sqlite3_open_v2() and sqlite3_exec(?, ?BEGIN IMMEDIATE?, ?) ?
[sqlite] hard links and SQLite
On Mon, Jan 11, 2016 at 11:00 AM, Felipe Gasper wrote: > On 11 Jan 2016 1:45 PM, Scott Hess wrote: > >> As far as preventing the other process from using it before the schema >> exists, do "SELECT count(*) FROM sqlite_master", and if the result is 0, >> the schema does not exist. If you create the schema as a transaction, >> that >> will be atomic. >> > > But in order for that SELECT to avert TOCTTOU errors, we?d have to do > BEGIN EXCLUSIVE LOCK at the beginning of every single DB handle creation. > That would seem to get expensive? The solution really depends on what you're doing. You only need to verify that the schema exists once, if you check and find that it doesn't exist, you shouldn't be using it, if you check and find that it does exist, then it exists until you take action to change that situation. Presumably right now you have some sort of startup loop which looks like "Does the database exist? If not, sleep and check again." I'd just change that to "Does the schema exist? If not, sleep and check again." Note that the sqlite_master table is held in the same page as the SQLite header, unless it grows large enough to split into multiple pages. Also note that any use of the database tables will require reading the schema page. So querying the sqlite_master table at startup will not add any additional costs. Another option would be to use PRAGMA user_version, to signal from the schema-creation process to the schema-using process. I don't think it's really any better than querying the schema, though. Another option would be to just prepare a statement and if it fails with SQLITE_ERROR, that's probably because the relevant schema doesn't exist. So sleep and try again later. -scott
[sqlite] hard links and SQLite
On Jan 11, 2016, at 11:57 AM, Stephan Beal wrote: > > On Mon, Jan 11, 2016 at 7:55 PM, Warren Young wrote: > >> There must be an equivalent of mkstemp() on Windows, doubtless taking 3 >> times as many parameters and with a function name 4 times as long. :) > > sqlite exposes the functionality of fetching a temp file name using its > mechanism, but i don't recall at the moment how it's done. I see sqlite3_temp_directory(), but it isn?t documented here: https://www.sqlite.org/c3ref/funclist.html so I don?t think you can count on it to remain available. SQLite doesn?t use mkstemp(3), so it must be reinventing that particular wheel.
[sqlite] hard links and SQLite
On Jan 11, 2016, at 11:25 AM, Felipe Gasper wrote: > > To prevent race conditions where a 2nd process accesses a newly-created > SQLite file before the creator process can set up the schema On POSIX systems, you can securely create a temp file that only your user can see via the mkstemp(3) C library call. SQLite will happily open the resulting 0-byte file, allowing you to create your schema inside it. Then when the file is set up, you can move it into the desired location and change its file modes so that the other processes can open it. There must be an equivalent of mkstemp() on Windows, doubtless taking 3 times as many parameters and with a function name 4 times as long. :)
[sqlite] hard links and SQLite
Since this doesn't provide a -journal file, certain kinds of crashes cannot be recovered correctly. Why you you hard-link before the commit? The schema doesn't exist until the commit is successful, so there's no advantage to anyone else reading the file before then. As far as preventing the other process from using it before the schema exists, do "SELECT count(*) FROM sqlite_master", and if the result is 0, the schema does not exist. If you create the schema as a transaction, that will be atomic. -scott On Mon, Jan 11, 2016 at 10:25 AM, Felipe Gasper wrote: > Hi all, > > To prevent race conditions where a 2nd process accesses a > newly-created SQLite file before the creator process can set up the schema, > I?ve implemented logic like this: > > - create SQLite file as a .tmp beside permanent location > > - BEGIN EXCLUSIVE LOCK > > - hard-link the temp file to the permanent location > > - create schema > > - COMMIT > > - unlink temp file > > This prevents the problem because the permanent file never exists > unlocked prior to the schema existence. The 2nd process would either fail > to open the DB because it?s locked or open it and see the schema. > > I?m seeing some corruption in our SQLite files now but have had a > hard time tracking down the cause. Could it be because of the above logic? > What have folks here done to avoid the race condition originally described? > I?ve seen some documentation of SQLite and hard links but > > -FG > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Can SQLite know from the statement string if it is row producing or not?
Thanks, will look at that one and see how it compares to using sqlite3_column_count. RBS On Mon, Jan 11, 2016 at 7:17 AM, Hick Gunter wrote: > You may also like to consider sqlite3_stmt_readonly() which will return > TRUE for all statments that do not (directly) modify the db contents > > -Urspr?ngliche Nachricht- > Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto: > sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Bart > Smissaert > Gesendet: Freitag, 08. J?nner 2016 21:28 > An: General Discussion of SQLite Database > Betreff: [sqlite] Can SQLite know from the statement string if it is row > producing or not? > > I am interested to know from the statement string if the statement is > invalid, row producing (could produce rows) or non row producing. I know > sqlite3_prepare16_v2 can see if the statement is valid or not but how > about the other 2? > I can do this in code no problem, but it may not always be 100% reliable > for example there could be a new pragma or maybe some new SQL keyword. > > RBS > ___ > 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-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Can SQLite know from the statement string if it is row producing or not?
You may also like to consider sqlite3_stmt_readonly() which will return TRUE for all statments that do not (directly) modify the db contents -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Bart Smissaert Gesendet: Freitag, 08. J?nner 2016 21:28 An: General Discussion of SQLite Database Betreff: [sqlite] Can SQLite know from the statement string if it is row producing or not? I am interested to know from the statement string if the statement is invalid, row producing (could produce rows) or non row producing. I know sqlite3_prepare16_v2 can see if the statement is valid or not but how about the other 2? I can do this in code no problem, but it may not always be 100% reliable for example there could be a new pragma or maybe some new SQL keyword. RBS ___ 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.
