!/bin/sh
osascript -l JavaScript -e '
function run(argv) {
  // transpiled PHP code here
}' $(</dev/stdin)
          
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.
          
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.
          
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.
          
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!
          
@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.
          
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 .
Cannot use bundles that require PHP in macOS 12 (Format SQL)
Cannot use bundles that require PHP or Python in macOS 12 (Format SQL, Copy as Markdown, Toggle JSON...)
      Aug 16, 2022
          
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
          
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 ?
          
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)
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.
          '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 
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']
  nhsykym, huiwanggo, skirato, gabrielsky, TheOneDaveYoung, fidding, poooooom, amazingdudu, jhzhang09, chenshilong96, and 4 more reacted with thumbs up emoji
  gabrielsky, TheOneDaveYoung, poooooom, jhzhang09, chenshilong96, and gabriellupu reacted with hooray emoji
    All reactions
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)
          
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.
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!
          
For anyone still looking to resolve this, one option is using Automator.
Assuming you've used brew to install php...
In Sequel Ace, Bundles menu, Bundle Editor
Find the Format SQL bundle, copy all of the code
Paste into a script file under your home directory - in my case, ~/bin/formatsql.php
chmod a+rx the script
Open Automator.
Quick Action
search for action Run Shell Script
Settings: make sure to check Output replaces selected text.
zsh is fine, pass input to stdin
command to run: ~/bin/formatsql.php
Save, name the quick action FormatSQL
Now when you select the text in Sequel Ace (or anywhere else) right click, and you'll see your quick action listed under Services.
Profit!
This is working as of MacOS Venture 13.6 and Sequel-Ace 4.0.9
  samuelbradshaw, spaarti, gido, dahjelle, danstreeter, patvong, huyphamwork, and sonnykt reacted with thumbs up emoji
  ernstki reacted with rocket emoji
    All reactions
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
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."
          
For Automator, I recommend the better maintained node version of sql-formatter: https://github.com/sql-formatter-org/sql-formatter.
Use absolute paths to the node binary and the sql-formatter script. Also specify a config. The config is a simple json file.

See here for options: https://github.com/sql-formatter-org/sql-formatter/blob/master/docs/keywordCase.md.
~/.nvm/versions/node/v16.14.0/bin/node ~/.yarn/bin/sql-formatter --config ~/config/sql-formatter-config.json