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

Postgresql, How to escape single quote in dynamic json when it was concatenated into string?

Lets say I have function that triggers when table is being updated. Then it tries to send this data as json into remote database via dblink:

statement := 'INSERT INTO mytable(my_data) VALUES (''' || my_json || ''')';
PERFORM dblink('my connection data', statement);

my_json is formed by json_build_object method with some dynamic data. When some of this json fields values contains single quote, this function starts throw syntax errors.

I know that I need to use double single quotes, but I can’t because data is dynamic.

For example if my json is like this:

{ "a": "It's a test"  }

It throws:

Syntax error at s

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    I'm going to answer to my own question.

    The way I was able to solve this problem I have added some fixes before forming my statement:

    my_json := json_build_object(...);

my_json := REPLACE(payload, '''', '''''');

    Only after that I have formed my statement for my dblink:

    statement := 'INSERT INTO mytable(my_data) VALUES (''' || my_json || ''')';
PERFORM dblink('my connection data', statement);

  2. 0

    You can use quote_literal() to add the 2nd apostrophe:

    with src(a) as (select '{ "a": "It''s a test"  }') 
select a, quote_literal(a) from src;
            a            |       quote_literal
-------------------------+----------------------------
 { "a": "It's a test"  } | '{ "a": "It''s a test"  }'
    Login or Signup to reply.
  3. 0

    Use:

    statement := format('INSERT INTO mytable(my_data) VALUES (%L)', myjson);

    Based on your comments, I would point that:

    • there must not be single quotes around %L.
    • the fact that the contents represent a json value does not change anything to how it should be quoted. Any literal to inject into that statement would be treated the same.

    Another way using quote_literal() and not using format() would be:

    statement := 'INSERT INTO mytable(my_data) VALUES (' || quote_literal(myjson) || ')';

    Again there are no single quotes to add around quote_literal(myjson). It’s the responsibility of quote_literal to add these quotes.

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

Back To Top
Search