Support for run-time loadable SQLite extensions

[somelinguist]

• Platform: macOS 13.5
• SQLiteFlow: 6.0.3

Would it be possible to support run-time loadable SQLite extensions, at least on Mac?

There are some pretty interesting ones that would be helpful to try out:
https://github.com/asg017/sqlite-ecosystem

[SQLiteFlow]

Interesting.

[SQLiteFlow]

I did some researches about this feature recent days.

Looks like the loading arbitrary *.dylib file behavior is not implementable by a normal sandboxed app, because there're signing identities issue.

And the behavior will definitely violate the App Store Review Guidelines even if it is implementable, and the App that has this behavior will be rejected by App Store.

And here's also a relative link that talk something similar about this:
https://developer.apple.com/forums/thread/683914?answerId=680499022#680499022

It is very unfortunate that the feature can't be shipped since I'm kind like this feature come true.

[somelinguist]

Thanks for looking into this. Unfortunately, it's not that surprising. :) It looks like it can be difficult even if it weren't a sandboxed app. For example, see https://til.simonwillison.net/sqlite/trying-macos-extensions and sqlitebrowser/sqlitebrowser#2882

Thanks again.

[SQLiteFlow]

Thank you for pointing out the links.

From the links, I didn't aware in the past that there's a official supported way to disable library verification https://developer.apple.com/forums/thread/129977?answerId=409591022#409591022.

So apparently my words "loading arbitrary *.dylib file behavior is not implementable by a normal sandboxed app" is wrong. It is implementable!

And my another words "it violate the App Store Review Guidelines" is based on the link I mentioned and App Store Review Guidelines 2.4.5 (iv)
They may not download or install standalone apps, kexts, additional code, or resources to add functionality or significantly change the app from what we see during the review process.

So now for me, there's a official supported way to let the app implement the feature, but also there's App Store Review Guidelines that looks like they don't want you do that.

So maybe the next step is to check if it is really allowed to ship the feature to the Mac App Store.

[SQLiteFlow]

Well, here's a surprise. Looks like this feature has been approved by App Store Review, and it's on App Store now.

When I testing the feature, I compile the example extensions listed in https://www.sqlite.org/loadext.html, and I download some release builds from sqlean release page https://github.com/nalgeon/sqlean/releases, they all work like a charm.

But when I use some release builds from https://github.com/asg017/sqlite-ecosystem, https://github.com/asg017/sqlite-http/releases, they all lead to app crash when app is loading it, I'm not sure why but it is out of my support scope.

Here're some notes that you can also see in the app:

• This feature is not supported by the macOS embedded SQLite library.
• If you load an extension from an untrusted source, the extension may fail to load, or may bring up system alert dialogs, and a lot of "OK" you need to press both in the dialogs and in the macOS System Settings - Privacy & Security.
• Load an extension from an untrusted source has potential security risks, so you better avoid to do that. It is recommend that you build the extension from source.
• Unwell coded or unwell complied extensions may crash the app.
• Too may extensions loaded may show down the running speed of the app.

And here're two screenshots about its settings and its using place.

[somelinguist]

That's great to hear that it was accepted. I've got the update and will try some extensions. When I tried out some of the extensions from https://github.com/asg017/sqlite-ecosystem with DB Browser for SQLite the other day, they crashed as well. I as able to get them working in that app by following the directions in one of the issues above about re-signing the extensions, which wasn't ideal. That didn't seem to work at least so far with SQLiteFlow. When I have time, I'll try building some of the extension from there myself and see if anything changes.

Thanks again for adding such a useful feature!

[SQLiteFlow]

The resign trick you mentioned did work for me though. The command is codesign --force -s - path/to/*.dylib.

The working screenshots attached.

Something not working (yet) is if you click a blob field cell in a table view, and if the blob contains html content and it exec javascript, then it may lead app crash because of sandbox permission issue. I will fix this issue in the next release by try to parse the html content as plain text instead of webpage live content. The third screenshot is a demo of the fixed version.

[somelinguist]

Hi again. Thanks for the update. I re-signed an extension again, and this time it worked. Thanks again for this feature!

[SQLiteFlow]

You're welcome.
And the fixed version (SQLiteFlow 6.0.5) I mentioned before has been released by the way.