encode/decode trouble ('invalid input syntax for type bytea'); bug?

Поиск
Список
Период
Сортировка
От Tobias Herp
Тема encode/decode trouble ('invalid input syntax for type bytea'); bug?
Дата
Msg-id 11953.1144344623@www010.gmx.net
обсуждение исходный текст
Список pgsql-general
Hi,

I need to encrypt some fields in my database. The solution I came up with
is:

- create an additional field w/suffix "_encr" for every field which must be
encrypted;
- create update triggers which write the encrypted value into the new field
and null into the original field (not null constraints must be removed, of
course)
- change the views to use and decrypt the encrypted fields.
- add polymorpic encrypt(...) functions which are distinguished from their
argument types and take the key from a temporary table.

I'm pretty sure I need to use BYTEA fields to store the encrypted values;
and I need to convert TEXT <--> BYTEA, don't I?

So I created the following functions:

REATE OR REPLACE FUNCTION text2bytea(text)
  RETURNS bytea AS
$BODY$BEGIN
  RETURN decode($1, 'escape');
END;$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION bytea2text(bytea)
  RETURNS text AS
$BODY$BEGIN
  RETURN encode($1, 'escape');
END;$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE STRICT;
ALTER FUNCTION text2bytea(text) OWNER TO postgres;

Now, unfortunately there are backslashes in many varchar fields in the rows
wich must be encrypted; and for those strings, encode/decode seems to crash:

select decode('abc', 'escape');     -- yields 'abc'
select decode('ab\c', 'escape');    -- yields 'abc'
select encode('ab\c', 'escape');    -- yields 'abc'
select decode('ab\\c', 'escape');   -- ERROR: invalid input syntax for type
bytea
select encode('ab\\c', 'escape');   -- ERROR: invalid input syntax for type
bytea
select decode('ab\\\\c', 'escape'); -- yields 'ab\\c'
select encode('ab\\\\c', 'escape'); -- yields 'ab\\c'

This looks to me like a bug: the backslash should be escaped with another
backslash instead of raising an error.

Any ideas how to get around this?

--
TIA,

Tobias

В списке pgsql-general по дате отправления:

Предыдущее
От: SunWuKung
Дата:
Сообщение: php to import csv to postgres
Следующее
От: "samfisch@googlemail.com"
Дата:
Сообщение: recovering from a --clean dump