Escape quotes on psql to avoid SQLInjections-CodePudding

I'm doing a function to compare JSONs and then insert on a table (trigger) only the differences. The function works perfectly when none of the registers come with a quote. So I would to know how to escape these quotes that may come:

CREATE OR REPLACE FUNCTION public.fnc_compare_jsonb(old_reg jsonb, new_reg jsonb)
 RETURNS jsonb
 LANGUAGE plpgsql
 IMMUTABLE STRICT
AS $function$
declare
    keys record;
    jsonb_return jsonb = '{}'::jsonb;
begin
    for keys in
        select *
        from jsonb_object_keys($1)
    loop
        if $1 -> keys.jsonb_object_keys <> $2 -> keys.jsonb_object_keys then
            jsonb_return = jsonb_return || format('{"%s": "%s"}', keys.jsonb_object_keys, $2 ->> keys.jsonb_object_keys)::jsonb;
        end if;
    end loop;
    return jsonb_return;
end
$function$
;

The error is happening on line:

jsonb_return = jsonb_return || format('{"%s": "%s"}', keys.jsonb_object_keys, $2 ->> keys.jsonb_object_keys)::jsonb;

SQL Error [22P02]: ERROR: invalid input syntax for type json
  Detail: Token "@" is invalid.
  Where: JSON data, line 1: {"email": "test2"@...
PL/pgSQL function ecidadao.fnc_compare_jsonb(jsonb,jsonb) line 11 at assignment

CodePudding user response：

Your format statement creates some text, not a valid json. Use a json-function to do so:

SELECT  json_build_object('foo'::text,'text with double quotes " "'::text);

Result: {"foo" : "text with double quote " ""}