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.

Craig Ringer · Accepted Answer · 2012-11-13 12:42:28Z

4

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.

edited Nov 13, 2012 at 12:42

answered Nov 13, 2012 at 12:34

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Colin 't Hart · Accepted Answer · 2012-11-13 12:48:07Z

0

Seems to work for me by just doing psql -c "$SQLCOMMAND"

% export SQLCOMMAND='select "generate_series"(1, 5, 1);'
% echo $SQLCOMMAND
select "generate_series"(1, 5, 1);
% psql -c "$SQLCOMMAND"
 generate_series 
-----------------
               1
               2
               3
               4
               5
(5 rows)

or

% export SQLCOMMAND="SELECT 'aaa' FROM \"generate_series\"(1,2);"
% echo $SQLCOMMAND
SELECT 'aaa' FROM "generate_series"(1,2);
% psql -c "$SQLCOMMAND"
 ?column? 
----------
 aaa
 aaa
(2 rows)

Provided you can get the SQL statement into the variable correctly, double quoting the argument to psql's -c option seems to work.

edited Nov 13, 2012 at 12:48

answered Nov 13, 2012 at 10:33

Colin 't Hart

7,8084 gold badges34 silver badges53 bronze badges

5 Comments

Craig Ringer Over a year ago

Yeah, but that'll fall apart with "double quoted" identifiers.

Craig Ringer Over a year ago

SELECT 'aaa' FROM "generate_series"(1,2);

Craig Ringer Over a year ago

Yes, as edited... but the OP appears to want a way to do it without having to escape SQL manually. "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."

Colin 't Hart Over a year ago

I'm only escaping to get the SQL statement correctly into the environment variable. Once there, it's passed as is. Semantics. I'll leave it up to the OP to decide whether or not this is useful.

Craig Ringer Over a year ago

Fair enough. You'll need to double the escaping to pass it through ssh to psql -c then. ssh localhost /usr/pgsql-9.2/bin/psql -c "$SQLCOMMAND" will fail with bash: -c: line 0: syntax error near unexpected token '('. You'd need to use something like bash's printf '%q'

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related