Обсуждение: How to convert escaped text column - force E prefix

Dear Members!

A web developer stores JSON like strings in a text column.

With E prefix we can get the real text:

Select E'Az ad\u00f3kulcsonk\u00e9nti' 

Hungarian: "Az adókulcsonkénti" (ISO-8859-2)

How to get the same result from a table column?

select WhatAFunction( ATable.JSONLikeTextColumn) from ATable

What function do we need to use to "simulate" E prefix?

Thank you for the help!

Best regards

dd

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

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

Dear Members!

Pavel Stehule <pavel.stehule@gmail.com> 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?

Thank you!

dd

On Thursday, January 7, 2021, Pavel Stehule <pavel.stehule@gmail.com> wrote:

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

Would making the function run as “security definer” and setting up a minimal permissions user/owner help with mitigation?

David J.