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

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

CodePudding user response：

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"  }'

CodePudding user response：

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.