1

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?

Rafiq
  • 1,380
  • 4
  • 16
  • 31

2 Answers2

2

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.

Amadan
  • 191,408
  • 23
  • 240
  • 301
  • Uh! I was sure that the `$TEST_PASSWORD` variable won't be evaluated if I use single quotes around it like `'TEST_PASSWORD'`, but I was wrong. Just checked with `echo psql -U postgres -c "create user testuser with encrypted password '$TEST_PASSWORD';"`. Thank you! – Rafiq Apr 14 '23 at 04:36
  • The single quotes are themselves quoted (by the double quotes), so they lose their quotiness - they're just another character in the string. The only characters with superpowers inside double quotes (that I can think of) are `$` and `!`. – Amadan Apr 14 '23 at 06:36
1

Use single quotes around the environment variable reference

psql -U postgres -c "create user testuser with encrypted password '$TEST_PASSWORD';"
user7247147
  • 1,045
  • 1
  • 10
  • 24