How do you send multiple commands, including an SQL query, via an SSH-Tunnel with Plink?

Question

I am trying to create a Batch file from a Python script which executes Plink to send an SQL-Query to an external Database via SSH. The script would have to activate a batch file with multiple command lines to be sent to the server. Researching on the internet I have found, that a solution akin to the code snipped below should work.

(
    echo command 1
    echo command 2
    ...
) | plink.exe user@hostname -i sshkey.ppk

Entering my commands would yield the following:

(
    echo mysql -u admin -pPassword Database
    echo INSERT INTO Table VALUES(DEFAULT, (SELECT ID FROM Another_Table WHERE Another_ID = 'foo'), 'bar', 'foobar', 0, 'date', 1);
) | plink.exe user@hostname -i sshkey.ppk

The problem I have is that I am getting the following error: 'bar' can't be processed syntactically at this point. (I am sorry if the translation might be off here, english is not my first language). I have checked if some special characters have to be escaped, but have not found any conclusive answers. Note, that the first command is correct and works as intended on its own; only the second command seems to be faulty. Would anybody be willing to provide me a solution?

Answer 1

So the answer here is that you need to escape the closing parenthesis TWICE, not only once, and thus have to use three "^" characters. This is because the command inside the brackets is parsed twice and the second "^" needs to be escaped for the first parsing, thus requiring a third character.

See here for details: Escaping parentheses within parentheses for batch file

The code would therefore look like this:

(
    echo mysql -u admin -pPassword Database
    echo INSERT INTO Table VALUES(DEFAULT, (SELECT ID FROM Another_Table WHERE Another_ID = 'foo'^^^), 'bar', 'foobar', 0, 'date', 1^^^);
) | plink.exe user@hostname -i sshkey.ppk