Cannot use bundles that require PHP or Python in macOS 12 (Format SQL, Copy as Markdown, Toggle JSON...)

[LetterXbox]

• Sequel Ace Version (including build number): Version 3.4.1 Build 3041
• Sequel Ace Source (App Store/GitHub/Homebrew): App Store
• macOS Version: macOS 12 beta 7
• Processor Type (Intel/Apple): Apple
• MySQL Version: 8.0.26
• macOS Localization: English

Is Issue Present in Latest Beta?
Yes

Description
Since macOS 12 doesn't come with php, some bundles including "Format SQL" doesn't work.
Later versions of macOS also dropped Python, breaking more bundles

Steps To Reproduce

1. Install macOS 12
2. Click "Format SQL"

Expected Behaviour
N/A

Related Issues
N/A

Additional Context
I have installed php with home-brew but it seems there isn't anyway to change default php.

[Jason-Morcos]

This will indeed be an issue when macOS 12 launches for real. It is a big issue. I have no idea how to solve it. With the sandbox, I doubt we can just look for php in a new location (brew location).

Maybe we need to provide users with a set of instructions to install php from brew and create a symlink from the old system location to the brew location? (ex. run sudo ln -s $(which php) after installing php from homebrew)

[Jason-Morcos]

A couple notes from experimenting:

• You can't symlink another version of php into /usr/bin. At least not through the shell even using sudo.
• Sequel Ace can't run the version of php installed by homebrew, even with full disk access or manual access given to the file. Looks like macOS completely blocks App Store apps from running executables that aren't either inside of the app bundle itself, or in /usr/bin

With that in mind, there are three possible solutions:

1. embed php into Sequel Ace (ship a version of php with the app)
2. Rewrite existing php bundles into another language that macOS still supports out of the box (ex. Ruby).
3. Create an XPC helper that allows non-sandboxed access and run all bundles through that (also would let us do jump server things and some other cool things).

None of these are that trivial, all could use help from the community. I'd lean towards rewriting the existing bundles as a short-term solution and exploring one of the others after as a longer term solution.

@Kaspik

[dnicolson]

Ruby will also be removed according to the 10.15 Release Notes.

PHP converts well into JavaScript, JXA could possibly be used as a replacement. It's not the most elegant solution, but something like this could work:

!/bin/sh

osascript -l JavaScript -e '
function run(argv) {
  // transpiled PHP code here
}' $(</dev/stdin)

[clayreimann]

For the same reasons that Apple wants apps to be sandboxed, I would be uncomfortable building an XPC that just runs random code. AFAIK there isn't any good way to do authentication between the XPC service and the app on the other side.

What I propose instead, and am working on a PR for, is that bundles have an optional sandboxed attribute that runs their contents in a local JavaScript context so that their execution can be entirely contained to Sequel Ace.

[Kaspik]

Just adding some links for reference once we start on this - https://github.com/tonyjohnston/swift-phpRunner#custom-php-binary

[Jason-Morcos]

This is a very high priority, but I for one haven't found a good way to fix it. My vote at this point would be to try to rewrite the format SQL bundle to not rely on PHP in the short-term - perhaps even rewrite it in pure bash - so that we can get this critical bundle back to functional. Alternatively, it may be time to build SQL formatting directly into Sequel Ace itself and write it in Swift as part of the core app (no longer a bundle). A PR implementing either of these approaches would be highly appreciated and gladly reviewed. If someone can figure out a way to let users select their own PHP/Python/etc executables within the confines of the sandbox, that would be the holy grail.

[buschjost]

While I have no generic solution for all bundles and script code execution, I want to show a maybe possible solution for the SQL formatting aspect.

I understood, that it would be possible to call other binaries that ship in the Sequel-Ace bundle.

There is a Rust library (sqlformat-rs) dual-licensed under MIT & Apache-2 license that is basically derived from the PHP lib used right now (sql-formatter). However, there was another NodeJS lib (sql-formatter-plus) in between that states it diverged quite a bit from the PHP original, so there might be some changes in formatting.

Using the rust lib to make a small CLI app to ship as part of Sequel-Ace is easy. Basically just some Meta packages config and this code for a really tiny Proof-Of-Concept that should behave similar to the current PHP code:

use std::io::*;
use sqlformat::*;

fn main() {
    let mut buffer = String::new();
    std::io::stdin().read_to_string(&mut buffer).unwrap_or_else(|error| {
                panic!("Problem reading stdin: {:?}", error);
            });

    let formatted = format(
        &buffer,
        &QueryParams::None,
        FormatOptions::default(),
    );

    println!("{}", formatted);
}

The resulting binaries are about 550-600KB in size (Arm build) and can easily be brought down to about 400kb if really needed.

In case this path to solve the missing Formatting issue is a viable solution for you, I could prepare a small project to publish the CLI as Github project or add the Rust code to build the CLI to your project.
It might also be possible to create a library from the rust code that could be loaded in Swift.

[Jason-Morcos]

This is a great idea @buschjost! I think a PR to Ace that attempted this approach to getting the formatter working would be very appreciated. @Sequel-Ace/all does anyone else have thoughts on this?

MIT license is the same as Ace already has so that would be no issue!

[buschjost]

@Jason-Morcos I think I have created the necessary building blocks, but as I have no experience with XCode I have some trouble to make the necessary changes to the project to get everything tied together. It would be great if somebody else could take over from here.

I have published the sqlformat project in https://github.com/buschjost/sqlformat .

You can fetch a specific binary version and verify the checksum during the build with this small shell-script:

#!/bin/bash

VERSION="0.0.1"
CHECKSUM="765c13b57d4156349d7c6563952774fcbce261c8862135a7bb73f40ad0c3693a"

echo "fetching sqlformat..."
curl -sJOL https://github.com/buschjost/sqlformat/releases/download/v${VERSION}/sqlformat_${VERSION}-darwin-universal
(echo "$CHECKSUM  sqlformat_${VERSION}-darwin-universal" | shasum -c) || (echo "sqlformat - Checksum verify failed"; exit 1)

mkdir -p ../Resources/sqlformat
mv sqlformat_0.0.1-darwin-universal ../Resources/sqlformat/sqlformat

The script is currently fetching a universal binary (855kb size), but I released dedicated x86_64 and arm64 versions as well (440 / 400kb size).

The Binary needs to be placed in the Contents/MacOS folder of the package bundle next to your other binaries. This is the only location that I could get working, as usual Bundle contents are moved to other folders where the execution is prevented by macOS.

And then the final glue code is this as the new command in the SharedSupport/Default Bundles/Format SQL.saBundle/command.plist Bundle:

"$SP_APP_RESOURCES_DIRECTORY/../MacOS/sqlformat" 

Another detail that I am not familiar with is code-signing for mac. The binaries I published in Github are basically as they come from the rust compiler. I think they would get signed during your build process while packing it together? When I manually installed this on different macs it worked fine when I once opened the binary with right-click open after downloading from github, before I moved it into the package bundle.

[Jason-Morcos]

Awesome, @buschjost! @Sequel-Ace/all is someone interested in taking a stab at wrapping up this implementation?

[gduh]

@Jason-Morcos I try to do the change.

[gduh]

@buschjost After downloading the universal version sqlformat_0.0.1-darwin-universal, shasumdoes not return the same value as specified in your post above 765c13b57d4156349d7c6563952774fcbce261c8862135a7bb73f40ad0c3693a?

[buschjost]

Hi, I guess you were manually verifying it with shasum sqlformat_0.0.1-darwin-universal - in this case the default algorithm is sha1 on mac and the value would be 884522c861c32f5f0c70f9eb3944058d034aa26f .

When you explicitly specify that you want to check it with SHA256: shasum sqlformat_0.0.1-darwin-universal -a 256 the result should be 765c13b57d4156349d7c6563952774fcbce261c8862135a7bb73f40ad0c3693a .

[Jason-Morcos]

An idea from #1534: What about bundling this into Ace and running the formatter as a build-in JS process? Thoughts, @Sequel-Ace/all? Would anyone be interested int taking a stab at this? https://github.com/TaoK/PoorMansTSqlFormatter

[Jason-Morcos]

Expanded this issue to cover python-based bundles too. This would be solvable with an XPC helper, likely, but we'd need someone to be willing to champion this and push it through to completion. #346

The note above is related to an alternative approach to SQL formatting. I think SQL formatting is the most critical bundle broken right now and likely should be the priority

[steffdimitrov]

Hello,
the issue still exist in version: Version 3.5.3 (Build 20035). Is there a chance someone to fix it soon? Please!
@Jason-Morcos @gduh @Kaspik ?

[sunnynature]

Hit the similar issue "python: command not found" for Sequel Pro when I use its Bundles "Toggle Json Format". It's also caused by latest MacOS not recognizing brew installed python.

My solution: download and install the python pkg form https://www.python.org/downloads/macos/. I didn't remove the original python, but just edit the bundle editor (Bundles -> Bundle Editor -> Input Fields -> Toggle Json Format), and change the python path from default to new installed /usr/local/bin/python3.

	DATA=$(echo "$DATA" | /usr/local/bin/python3 -mjson.tool)

[Jason-Morcos]

Hit the similar issue "python: command not found" for Sequel Pro when I use its Bundles "Toggle Json Format". It's also caused by latest MacOS not recognizing brew installed python.

My solution: download and install the python pkg form https://www.python.org/downloads/macos/. I didn't remove the original python, but just edit the bundle editor (Bundles -> Bundle Editor -> Input Fields -> Toggle Json Format), and change the python path from default to new installed /usr/local/bin/python3.

	DATA=$(echo "$DATA" | /usr/local/bin/python3 -mjson.tool)

This won't work in Sequel Ace because Sequel Ace is sandboxed to comply with macOS security standards.

[peacefulbyte]

the ruby code does temporarily worked.

#!/usr/bin/ruby
# encoding=utf-8

require "json"
require "open-uri"  
require 'net/https'

sql = $stdin.read()

params = {'sql': sql, 'reindent': 1,
          'keyword_case': 'upper', 'identifier_case': 'lower'}

url = 'https://sqlformat.org/api/v1/format'  

uri = URI(url)
http = Net::HTTP.new(uri.host, uri.port)
    if uri.scheme == "https"
        http.use_ssl = true
        http.verify_mode = OpenSSL::SSL::VERIFY_NONE 
    end
req = Net::HTTP::Post.new(uri.path)
req.set_form_data(params)
res = http.request(req)
json_body = JSON.parse res.body
puts json_body['result']

[samuelbradshaw]

On MacOS 12.6, there is a python binary located at /usr/bin/python3 - so is it just the case that we need to update the bundles to use that python binary?

From what I understand, /usr/bin/python3 is just a stub (not a full Python install) until a user installs the Xcode command line tools.

(For reference, macOS 12.3 is when macOS removed Python 2)

[jpmc]

Subscribing to this thread, but I wanted to mention I even changed my bundle to use #!/usr/bin/env php as per this comment suggestion sequelpro/Bundles#10 (comment)

But as evidenced, it doesn't matter if you point to any working item with Full Disk Access enabled due to the sandbox. Which sadly means my use case (Deserialize PHP) is no longer working.

I hope someone is able to find a workaround, either bundling in Ace or that non-sandbox XPC version in the other ticket. Figured I would check if anyone has had any workarounds or updates that hadn't made it here yet.

[Jason-Morcos]

I think the best approach at this point is to totally revamp bundles
I think we should adopt support for javascript-based bundles and switch SA to use core-level JS running to process things. While I'd love to get PHP and Python bundles working again, Apple really seems opposed to poking the needed holes to allow for these use cases

[hans2003]

#!/usr/bin/ruby
# encoding=utf-8

require "json"

sql = $stdin.read()

puts JSON.pretty_generate(JSON.parse(sql))

use this code，can format json in macos 12.4.

[Jason-Morcos]

```ruby
#!/usr/bin/ruby
# encoding=utf-8

require "json"

sql = $stdin.read()

puts JSON.pretty_generate(JSON.parse(sql))

use this code，can format json in macos 12.4.

If you want to submit a PR to improve the existing bundle, any kinds of improvements like this to existing bundles would be greatly appreciated!

[tcurtin]

For anyone still looking to resolve this, one option is using Automator.

1. Assuming you've used brew to install php...
2. In Sequel Ace, Bundles menu, Bundle Editor
3. Find the Format SQL bundle, copy all of the code
4. Paste into a script file under your home directory - in my case, ~/bin/formatsql.php
5. chmod a+rx the script
6. Open Automator.
7. New
8. Quick Action
9. search for action Run Shell Script
10. Settings: make sure to check Output replaces selected text.
11. zsh is fine, pass input to stdin
12. command to run: ~/bin/formatsql.php
13. Save, name the quick action FormatSQL
14. Now when you select the text in Sequel Ace (or anywhere else) right click, and you'll see your quick action listed under Services.
15. ?
16. Profit!

This is working as of MacOS Venture 13.6 and Sequel-Ace 4.0.9

[danstreeter]

For anyone still looking to resolve this, one option is using Automator.
...
This is working as of MacOS Venture 13.6 and Sequel-Ace 4.0.9

I can confirm this also works on MacOS Sonoma 14.3.1 on an M3 Max macbook.
I only had to change the interpreter line at the top to #!/opt/homebrew/bin/php and it works lovely!

Thanks

[chowtech]

For anyone still looking to resolve this, one option is using Automator.
...
This is working as of MacOS Venture 13.6 and Sequel-Ace 4.0.9

I can confirm this also works on MacOS Sonoma 14.3.1 on an M3 Max macbook. I only had to change the interpreter line at the top to #!/opt/homebrew/bin/php and it works lovely!

Thanks

But error for "/bin/bash: /opt/homebrew/bin/php: Operation not permitted."