Question 88989 in PostgreSQL Question posted in
The official documentation can be found here.

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

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?

Tags:

2

Answers


  1. 0

    Use single quotes around the environment variable reference

    psql -U postgres -c "create user testuser with encrypted password '$TEST_PASSWORD';"
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search