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