Re: How to convert escaped text column - force E prefix

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

čt 7. 1. 2021 v 12:13 odesílatel Durumdara <durumdara@xxxxxxxxx> napsal:

Dear Members!

Pavel Stehule <pavel.stehule@xxxxxxxxx> ezt írta (időpont: 2021. jan. 6., Sze, 12:03):

it cannot  work, because \ will be  replaced by \\

postgres=# CREATE OR REPLACE FUNCTION public.unistr(text)
 RETURNS text
 LANGUAGE plpgsql
 IMMUTABLE STRICT
AS $function$
declare r text;
begin
  execute 'select ' || quote_literal($1) into r;
  return r;
end;
$function$
;
CREATE FUNCTION
postgres=# select unistr('Az ad\u00f3kulcsonk\u00e9nti');
┌──────────────────────────────┐
│            unistr            │
╞══════════════════════════════╡
│ Az ad\u00f3kulcsonk\u00e9nti │
└──────────────────────────────┘
(1 row)

Gavan Schneider

Thank you for the answer!

We will try your solution.

Only one question about it:

Could we use PG's JSON interpreter somehow. I don't know it, but pseudo.

select 

  GET_JSON_FIELD_VALUE(

    'name',
    FROM_JSON_TEXT(   '{name:' || chr(39) || thistable.thisfield || chr(39) || '}' )

  ) from thistable

or use FORMAT instead of CONCAT.

Is this possible to work? What do you think about the vulnerability?

The vulnerability is almost the same although it is a little bit harder to create attack strings.

Regards

Pavel

Thank you!

dd