How can I include single quotes surrounding a value coming from an environment variable into a PostgreSQL statement

Question

This line of command I picked from one of my bash scripts I am trying to use to automate some initial setup of a PostgreSQL database in an Amazon Linux machine.

I have the environment variable named TEST_PASSWORD contains the value TestSecret. I need to pass this value in the SQL statement with single quotes like 'TestSecret'.

So, as expected, it works in in Method-1, but not in Method-2 or Method-3 as showing below:

Method-1:

[root@host ~]# psql -U postgres -c "create user testuser with encrypted password 'TestSecret';"
CREATE ROLE

Method-2:

[root@host ~]# psql -U postgres -c "create user testuser with encrypted password $TEST_PASSWORD;"
ERROR:  syntax error at or near "TestSecret"
LINE 1: create user testuser with encrypted password TestSecret;
                                                     ^

Method-3:

[root@host ~]# psql -U postgres -c "create user testuser with encrypted password \'$TEST_PASSWORD\';"
ERROR:  syntax error at or near "\"
LINE 1: create user testuser with encrypted password \'TestSecret\';
                                                     ^

How can I overcome this?

Answer 1

The trivial answer is: just replace the password itself, and keep everything else as you had it in your working command:

psql -U postgres -c "create user testuser with encrypted password '$TEST_PASSWORD';"

HOWEVER, this leaves you vulnerable to SQL injection. There is no good way to fight against it in the command line. You can homebrew your own solution like in this answer, or you can delegate the job to another language, one that supports parametrised queries.