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

st 6. 1. 2021 v 10:54 odesílatel Gavan Schneider <list(dot)pg(dot)gavan(at)pendari(dot)org>
napsal:

> On 6 Jan 2021, at 19:43, Pavel Stehule wrote:
>
> Currently there are not any functions that you need. You need to write
> your
> own.
>
> CREATE OR REPLACE FUNCTION public.unistr(text)
> RETURNS text
> LANGUAGE plpgsql
> IMMUTABLE STRICT
> AS $function$
> declare r text;
> begin
> execute 'select e''' || quote_literal($1) || '''' into r;
> return r;
> end;
> $function$;
>
> Attention: This is ugly and possible sql injection vulnerable!!! But there
> is not another way. The fix is in queue
>
> https://www.postgresql.org/docs/current/functions-string.html
> quote_literal ( text ) → text
> Returns the given string suitably quoted to be used as a string literal in an SQL statement string.
> Embedded single-quotes and backslashes are properly doubled.
> Note that quote_literal returns null on null input; if the argument might be null, quote_nullable is often more suitable.
> See also Example 42.1. quote_literal(E'O\'Reilly') → 'O''Reilly'
>
> It is even more ugly but would it at least help with the SQL injection
> risk?
>

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
> ——
> Gavan Schneider, Sodwalls, NSW, Australia
> Explanations exist; they have existed for all time; there is always a
> well-known solution to every human problem — neat, plausible, and wrong.
> — H. L. Mencken, 1920
>