0

Pretty obscure situation and a couple hours of trying different things with no luck.

I have a bash script which restores a production database backup locally. Part of the script runs some SQL to set things in the database.

One of those items is setting the correct slack_token in a slack_details table. Here's how I run the bash script:

$ ./restore-prod-database.sh -t "xoxb-1234567-1234....."

The script has a line which triggers running a .sql file like this:

psql -h $DATABASE_HOST -d $DATABASE_NAME -a -v slack_token=$SLACK_TOKEN -f restore-populate-slack-details.sql

Finally, the SQL does something like this:

UPDATE slack_details
SET installation = jsonb_set(installation, '{bot,token}', ('"' || :slack_token ||'"')::jsonb)
WHERE id = 1;

No matter what I try, whether its concatenating the :slack_token with quotes, passing it in with quotes, using single quotes and " I get the following error:

UPDATE slack_details SET installation = jsonb_set(installation, '{bot,token}', ('"' || :slack_token ||'"')::jsonb) WHERE id = 1; psql:restore-populate-slack-details.sql:61: ERROR: column "xoxb" does not exist LINE 2: ... = jsonb_set(installation, '{bot,token}', ('"' || xoxb-12345...

Quick note, installation is a jsonb column and the following code works fine, moment I use the -v variable it doesn't work:

UPDATE slack_details
SET installation = jsonb_set(installation, '{bot,token}', ('"' || '123456' ||'"')::jsonb)
WHERE id = 1; --- works fine

1 Answer 1

2

To inject the :slack_token variable as a literal in the SQL query, the proper syntax to use is :'slack_token'.

From the psql documentation in "SQL interpolation":

When a value is to be used as an SQL literal or identifier, it is safest to arrange for it to be quoted. To quote the value of a variable as an SQL literal, write a colon followed by the variable name in single quotes. To quote the value as an SQL identifier, write a colon followed by the variable name in double quotes. These constructs deal correctly with quotes and other special characters embedded within the variable value

The query could be written as:

UPDATE slack_details
SET installation = jsonb_set(installation, '{bot,token}', ('"' || :'slack_token' ||'"')::jsonb)
WHERE id = 1;

Besides, as commented below by @jjanes, the construction of the jsonb value should be simplified and made safer by using to_jsonb, otherwise the lack of quoting inside the token might lead to invalid json strings with certain values of :slack_token:

UPDATE slack_details
SET installation = jsonb_set(
       installation,
       '{bot,token}',
       to_jsonb(:'slack_token'::text)
) WHERE id = 1;
3
  • Yes. But to improve it further, I think he would want to use to_jsonb() not concatenating literal double quotes to the thing. to_jsonb(:'slack_token'::text) That way it will work correctly if the token happens to contain a literal double quote.
    – jjanes
    Aug 15 at 15:28
  • @jjanes: good point, answer updated. Aug 16 at 14:32
  • amazing, thank you! Aug 16 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.