App Sandbox SQLite databases and other associated files

Targets macOS
1

Continues on from https://forum.xojo.com/29918-mac-app-store-entitlements/last

Preface: OS X 10.8.x was supposed to improve support for SQLlite databases in the App Sandbox, so that they don’t need to be moved around to be editable.
https://developer.apple.com/library/mac/documentation/Security/Conceptual/AppSandboxDesignGuide/AppSandboxInDepth/AppSandboxInDepth.html

[quote=248472:@Carlo Rubini]Yet, in a sandboxed app, after double-clicking the file or opening it via openDialog, if I don’t move/copy it into the container, calling file.update raises a database error 14: unable to open the file.
Really confusing.[/quote]
hmm… Then I can only presume that OS doesn’t know that your file is a SQLite database. I don’t do anything with databases, unless I’m contracted to do so; like I mentioned earlier, the last time I worked on a database application (made with Xojo), I was surprised that the SQLlite db just worked.

Two things I can think of that might affect this;
#1 The database plug-ins; maybe there’s something that the plugin should do to notify the OS???
#2 Make sure the UTI conforms to the SQLlite database UTI. I’d have to look up what the base UTI is.

2

@Sam Rowlands [quote]#2 Make sure the UTI conforms to the SQLlite database UTI. I’d have to look up what the base UTI is. [/quote]

Apple Docs
Apple docs speak of Identifier as public.database; with nothing for “Conform to”.
So I modified the filetype, but the sandboxed app still wont proceed, either error 14 for an existing file, or, if I try to create a new database file, it says that there is no table for such file.
I tried other ways, such as using sqllite as extension, and using public.sqllite.database and the like words in filetype.
Always the same problems.

3

Okay, so let me finish up the current stage of the project I’m working on. Then I’ll contact the client and see if they would mind me sharing information on the project. If they don’t mind, I’ll figure out what they’re doing that makes it just work.

4

Take your time. Thanks.

5

Been a long time I know; The problem is the journal file.

SQL Test(12625) deny file-write-create /Users/rowlands/Desktop/test.sqlite-journal

It seems that up until 10.8.3 even people using Apple’s CoreData database were having this issue. So the question is, what really changed with 10.8.3 and is there anything that can be done in Xojo to adopt the same mechanism as what Apple deems safe?

6

It appears that Xojo is not doing something that Core Data is doing; Here’s some proposals for the time being.

#1 Use a third party DB plugin.
#2 Disable journaling - Seriously don’t even consider this, runs the risk of the database becoming corrupted.
#3 Store the database file within a document package (a folder that acts like a document). If it’s your own file type, then this is fine.
#4 Store your databases in the application support folder and get the user to export them when they need to. Will annoy some users, plus if they ‘uninstall’ your app, all the data goes with it.
#5 Move the database on open to the application support folder and then when the window is closed, move it back. Runs the risk of phreaking the user out.

7

Yep, I’ve been using #5. Thanks.

8

Has there been any recent updates on this? My app has to open and operate on multiple sqlite databases (one at a time) in different locations determined by the user. The journal file still seems to be the problem with sandboxing. Is there a way in the app wrapper 3 to work around this?

9

None that I am aware of…
It seems with SQLite the biggest issue is if the journaling is WAL… since that wants to write the journaling files right beside the actual database (this is set by Xojo, since the SQLite protocol seems to allow these to be moved, but Xojo doesn’t seem to expose that).

My other is (not SQLite related) is Most Recently Used Files… I assume Security Scoped Bookmarks would solve that for macOS (if I could understand how they really worked :frowning: ).

10

When Dave and I were figuring this issue out I had filed a feedback for it: <https://xojo.com/issue/48386>
However, it hasn’t been looked at.

11

It HAS been looked at
By at least 2 of us

And it should be a feature request
Bug is “it doesn’t work as advertised”
Feature requests cover “but I wish it worked different”

12

In regards to the 5th point I made before; HDRTist NX was using a modifying version where it would move the users images to the app support folder, then it would create a hard link in the original location.

However this trick does not on High Sierra; often the hard link creation fails when the file is linked via a Security-Scoped Bodgemark :frowning:

So the safest way IMHO is to wrap the file in a document package and then use some of the other tricks so you don’t need any Security-Scoped Bodgemarks.

13

I see in other threads people are struggling with the journal file/WAL problem.

Can you elaborate more about the document package and the tricks needed? (sorry, I’m a noob.)

Also - can somebody recommend a third party plugin that embeds like sqlite and avoids this problem?

14

This is only an issue if you want to deploy via MAS… which is what I want to do with Tadpole… but until I can find a way to handle Sqlite databases located anywhere in the users computer… it will have to be deployed in other manners :frowning:

15

I’m wondering if @Christian Schmitz could build his plugin to behave in a sandbox friendly way.
(yes I know you don’t use plugins, Dave - but for those that do it would be a great interim workaround)

16

In simplicity a document package is a folder with an extension. You then add a file type to your application like the screenshot below. Make sure your “Conforms To” is correct. It can also take a couple of times for the OS to realize this and take effect.

What you then must do is change your mentality so that you think of this folder as your data file. When you save, you create a folder and in that folder you store the SQLiteDatabase. When a user selects a file, they’re actually selecting the folder. Your application then goes into the folder to read the db.

The next step is to utilize at least one trick described in my huge guide on Sandboxing for a xDev issue 13.2. That is to use the Apple Recent Items menu. Anything that’s stored in the Apple Recent Items menu, has access retained. I personally use my own Sandbox Kit.

Once your application is set-up to use your document packages; simply add OWRecentItems.addToRecentMenu( docPackageFile ) when a document is opened or saved and it will be added to the menu. Make sure that this is either the file dragged into the application or selected via the Open/Save dialog.

DO NOT USE SECURITY-SCOPED BODGEMARKS. You MUST request and release access to the files that these connect to, when using a SSB. If you do not correctly perform this operation it’s possible to leak kernel memory and crash a machine. SSBs are also incompatible with many of Apple’s API due to the Lazy loading.

The other technique, which also requires redesigning your application, is to use Apple’s “Resume” functionality. In principle this is easy, add a NSURL or NSDocument to a Window. However the application must be shut down in a very specific way and it relies on the user having it enabled in the OS.

And finally, Apple have added provision for databases to access their journal file without any modification, or so Apple say. My understanding is it only works for apps that are written in Apple’s tools and uses Apple’s own database API.

Yes; the App Sandbox is a nightmare for a lot of developers; it’s also the reason as to why the App Store is devoid of many professional applications. While I would recommend you still target the App Store, it’s not what it used to be and so don’t feel bad if Apple’s decisions don’t allow for your application to be there. The App Store used to account for 80% of our income, in the first 6 months of this year, it only accounted for 34% (the money coming in from the App Store has dropped).

17

@Sam
Thanks – this works and passes sandboxing tests. I would have one suggestion - please put this in your (excellent) App Wrapper 3 documentation - it would really help. Somehow, I just can’t follow the apple docs about sandboxing.

18

So, thanks to @Sam my app sandboxes nicely using document packages. All was well until one of my App beta testers moved a Windows sqlite file (created by my app Windows version) and tried to open it on a Mac version of my App. Strangely the file opened , then the sandbox app quietly crashed (it didn’t in debug). Console showed a sandboxing violation - presumably the journal file problem. As my app needs windows/mac interchangeable files, this is a real show stopper.

@Sam’s suggestion of moving the file into the sandbox on App open and moving it back on close seems to be the only way around this, but my beta testers really freak out when their data file disappears off the desktop.

Anybody have suggestions? Would creating a shortcut (inside the sandbox) to the sqlite file (outside the sandbox) create a sandbox violation? Does anybody have experience with disabling the journaling by a pragma - how dangerous is it?

19

There was another way, which was far more elegant IMHO. Which is to “move” the file into the application container and then create a Hard Link to the moved file, where it used to be. While this does appear to work quite well for image editing applications, it’s broken on High Sierra. Either they’ve blocked this trick or broken their own API for creating hard links.

20

So basically, it sounds like, while using the features and functions currently supplied via Xojo, one cannot

  • implement a “most recent used files” system, as per Sam (my understanding) requires “Security Scoped ‘Bodgemarks’” or jumping thru other even more complicated and “fragile” methods
  • Implement read/write on a SQLite Database (from an external source) since there is no control over where/how the journal files (especially WAL) are maintained, and this violates “sandbox” policy

And even if a Sqlite image were including in the app to supersede the building Xojo supplied one, the interface to that image complicates things, plus there is the loss of being able to encrypt/decrypt?

(and before plugins are mentioned… regardless my opinion of their use, there is the ROI factor… it would take 100’s of unit sales of the finished app to warrant paying for any more “tools”… yeah I know “CATCH-22”)