Início / dba / Perguntas / 330317
Accepted
Luke Belbina
Asked: 2023-08-15 08:31:11 +0800 CST

Problemas usando uma variável psql -v com jsonb_set

  • 772

Situação bastante obscura e algumas horas tentando coisas diferentes sem sorte.

Eu tenho um script bash que restaura um backup de banco de dados de produção localmente. Parte do script executa algum SQL para definir coisas no banco de dados.

Um desses itens é definir o correto slack_tokenem uma slack_detailstabela. Aqui está como eu executo o script bash:

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

O script tem uma linha que aciona a execução de um arquivo .sql como este:

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

Finalmente, o SQL faz algo assim:

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

Não importa o que eu tente, seja concatenando o :slack_token com aspas, passando-o com aspas, usando aspas simples e "Recebo o seguinte erro:

ATUALIZAÇÃO slack_details SET instalação = jsonb_set(instalação, '{bot,token}', ('"' || :slack_token ||'"')::jsonb) WHERE id = 1; psql:restore-populate-slack-details.sql:61: ERRO: a coluna "xoxb" não existe LINHA 2: ... = jsonb_set(instalação, '{bot,token}', ('"' || xoxb- 12345...

Nota rápida, a instalação é uma coluna jsonb e o código a seguir funciona bem, no momento em que uso a variável -v não funciona:

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

1 respostas

  1. Best Answer

    Para injetar a :slack_tokenvariável como literal na consulta SQL, a sintaxe adequada a ser usada é :'slack_token'.

    Na documentação do psql em "interpolação SQL":

    Quando um valor deve ser usado como um literal ou identificador SQL, é mais seguro fazer com que ele seja colocado entre aspas. Para citar o valor de uma variável como um literal SQL, escreva dois pontos seguidos do nome da variável entre aspas simples. Para citar o valor como um identificador SQL, escreva dois pontos seguidos do nome da variável entre aspas duplas. Essas construções lidam corretamente com aspas e outros caracteres especiais embutidos no valor da variável

    A consulta poderia ser escrita como:

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

    Além disso, conforme comentado abaixo por @jjanes, a construção do valor jsonb deve ser simplificada e tornada mais segura usando to_jsonb, caso contrário, a falta de aspas dentro do token pode levar a strings json inválidas com determinados valores de :slack_token:

    UPDATE slack_details
SET installation = jsonb_set(
       installation,
       '{bot,token}',
       to_jsonb(:'slack_token'::text)
) WHERE id = 1;
    • 2

relate perguntas