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

From: "Tobias Herp" <tobias(dot)herp(at)gmx(dot)de>
To: "PostgreSQL" <pgsql-general(at)postgresql(dot)org>
Subject: encode/decode trouble ('invalid input syntax for type bytea'); bug?
Date: 2006-04-06 17:30:23
Message-ID: 11953.1144344623@www010.gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Browse pgsql-general by date

  From Date Subject
Next Message MargaretGillon 2006-04-06 18:02:38 Unique constraint or index, case insensitive, on multiple fields
Previous Message lmyho 2006-04-06 17:27:36 Debian package for freeradius_postgresql module