How to have simple and double quotes in a scripted ssh command

I am writing a small bash script and want to execute the following command via ssh

sudo -i mysql -uroot -pPASSWORD --execute "select user, host, password_last_changed from mysql.user where password_last_changed <= '2016-9-00 11:00:00' order by password_last_changed ASC;"

Unfortunately this command contains both simple and double quotes so I can’t do

ssh user@host "command";

What would be the recommended way to solve this issue ?

Answer

Using a heredoc

You can just pass your exact code on the shell’s stdin:

ssh user@host bash -s <<'EOF'
sudo -i mysql -uroot -pPASSWORD --execute "select user, host, password_last_changed from mysql.user where password_last_changed <= '2016-9-00 11:00:00' order by password_last_changed ASC;"
EOF

Note that the above doesn’t perform any variable expansions — due to the use of <<'EOF' (vs <<EOF), it passes the code to the remote system exactly, so a variable expansion ("$foo") would be expanded on the remote side, using only variables available to the remote shell.

This also consumes stdin for the heredoc containing the script to be run — if you need stdin to be available for other purposes, that may not work as intended.

---

Generating an eval-safe command dynamically: Array Edition

You can also tell the shell itself to do the quoting for you. Assuming your local shell is bash or ksh:

#!/usr/bin/env bash
#              ^^^^ - NOT /bin/sh

# put your command into an array, honoring quoting and expansions
cmd=(
  sudo -i mysql -uroot -pPASSWORD
    --execute "select user, host, password_last_changed from mysql.user where password_last_changed <= '2016-9-00 11:00:00' order by password_last_changed ASC;"
)

# generate a string which evaluates to that array when parsed by the shell
printf -v cmd_str '%q ' "${cmd[@]}"

# pass that string to the remote host
ssh user@host "$cmd_str"

The caveat there is that if your string expands to a value containing non-printable characters, the nonportable $'' quoting form may be used in the output of printf '%q'. To work around that in a portable manner, you actually end up using a separate interpreter such as Python:

#!/bin/sh
# This works with any POSIX-compliant shell, either locally or remotely
# ...it *does* require Python (either 2.x or 3.x) on the local end.

quote_args() { python -c '
import pipes, shlex, sys
quote = shlex.quote if hasattr(shlex, "quote") else pipes.quote
sys.stdout.write(" ".join(quote(x) for x in sys.argv[1:]) + "n")
' "$@"; }

ssh user@host "$(quote_args sudo -i mysql -uroot -pPASSWORD sudo -i mysql -uroot -pPASSWORD)"

---

Generating an eval-safe command dynamically: Function Edition

You can also encapsulate your command in a function, and tell your shell to serialize that function.

remote_cmd() {
  sudo -i mysql -uroot -pPASSWORD --execute "select user, host, password_last_changed from mysql.user where password_last_changed <= '2016-9-00 11:00:00' order by password_last_changed ASC;"
}
ssh user@host bash -s <<<"$(declare -f remote_cmd); remote_cmd"

Using bash -s and passing code in a here-string or unquoted heredoc isn’t needed if you know with certainty that the remote shell is bash by default — if that were the case, you could pass the code on the command line (in place of the bash -s) instead.

If the remote command needs to be passed some variables, use declare -p to set them remotely in the same way the above uses using declare -f.