skip to Main Content

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

3

Answers


  1. Chosen as BEST ANSWER

    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. 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. 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