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

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

st 6. 1. 2021 v 10:54 odesílatel Gavan Schneider <list.pg.gavan@xxxxxxxxxxx> 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