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
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 |