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