How to run a SQL command from a bash variable via ssh and psql?

Question

I do want to run a SQL command which is stores in a bash variable via ssh postgres@hostname psql dbname -c SQL_COMMAND.

Considering that the SQL command has strings in it, what is the proper way of running this, so it is properly escaped?

Example: SQL_COMMAND="SELECT 'aaa'"

I am looking for a solution that takes care of the escaping, so I can easily run other SQL commands without having to escape them myself.

Answer 1

This is quite awkward to do in the shell. Given the choice I'd use a scripting language that was less sensitive about metacharacters. The shell is just painful. The only way I'm aware of to get reliable raw strings is with either (a) an input function like read; (b) a temporary file; or (c) with a quoted here-document.

Here's what I'd do, exploiting the way ssh passes stdin through:

$ ssh hostname psql <<"__END__"
SELECT 'aaa!*#${notavar}' FROM "generate_series"(1,2);
__END__

     ?column?     
------------------
 aaa!*#${notavar}
 aaa!*#${notavar}
(2 rows)

Unfortunately you can't easily wrap that in $( ... ) to store it in a shell variable. It'll seem to work, but certain meta-characters like ! will cause issues.