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

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?

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