How to convert escaped text column - force E prefix

Hi

st 6. 1. 2021 v 8:55 odesílatel Durumdara <durumdara@gmail.com> napsal:

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?

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''' || $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://commitfest.postgresql.org/31/2613/

Regards

Pavel

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

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

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

čt 7. 1. 2021 v 12:13 odesílatel Durumdara <durumdara@gmail.com> napsal:

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?

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

Regards

Pavel

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.

čt 7. 1. 2021 v 15:50 odesílatel David G. Johnston <
david.g.johnston@gmail.com> napsal:

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?

yes. It is a very different usage of security definer functions, but it can
work.

Regards

Pavel