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.