Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Escape quotes on psql to avoid SQLInjections

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

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

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading