Problems with pgcrypto and special characters

On Mon, 2005-02-28 at 18:32 +0100, Markus Wollny wrote:

To get straight to the point, here's my problem:

mypgdb=# select bytea2text(decrypt(encrypt('T�bingen'::bytea, 'mypassphrase'::bytea,'bf'::text),'mypassphrase'::bytea,'bf'::text)) as foo;
foo
-----------------
T\303\274bingen
(1 row)

I have compiled and installed pg_crypto and I'am using the following function as workaround for a bytea-to-text-cast:

are you sure your problem is with pg_crypto ?
what does this produce:
select bytea2text('T�bingen'::bytea) as foo;
?

have you tried to use encode()/decode() instead ?
untested:
select
decode(
decrypt(
encrypt(
encode('T�bingen','escape') ,
'mypassphrase'::bytea,
'bf'::text
),
'mypassphrase'::bytea,
'bf'::text
)
) as foo;

(sorry for the obsessive indentation)

gnari

"Markus Wollny" <Markus.Wollny@computec.de> writes:

... I'am using the following function as workaround for a bytea-to-text-cast:

create or replace function bytea2text(bytea) returns text as '
begin
return $1;
end;
' language plpgsql;

That looks like your problem right there.

Possibly a binary cast (WITHOUT FUNCTION) would solve your problem,
though I doubt it will work well on bytea values containing \0.

regards, tom lane

Hi!

-----Original Message-----
From: Ragnar Hafstað [mailto:gnari@simnet.is]

are you sure your problem is with pg_crypto ?
what does this produce:
select bytea2text('Tübingen'::bytea) as foo;
?

Well I'm sure it's not WITH pgcrypto but with actually using pgcrypto in conjunction with UTF-8 encoded text. This function doesn't do anything but replace a bytea::text-cast.

have you tried to use encode()/decode() instead ?
untested:
select
decode(
decrypt(
encrypt(
encode('Tübingen','escape') ,
'mypassphrase'::bytea,
'bf'::text
),
'mypassphrase'::bytea,
'bf'::text
)
) as foo;

Yes, and that doesn't work either:

mypgdb=# select decode(encode('Tübingen'::text::bytea,'escape'),'escape');
decode
-----------------
T\303\274bingen
(1 row)

But I just found the bugger - we both confused encode and decode :)

mypgdb=# select encode(decode('Tübingen','escape'),'escape');
encode
----------
Tübingen
(1 row)

Now using pgcrypto works, too:

mypgdb=# select encode(decrypt(encrypt(decode('Tübingen'::text,'escape'),'mypassphrase','bf'),'mypassphrase','bf'),'escape');
encode
----------
Tübingen
(1 row)

Thanks nevertheless, this was exactly the push in the right direction that I needed!

Kind regards

Markus

Hi!

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]

Possibly a binary cast (WITHOUT FUNCTION) would solve your problem,
though I doubt it will work well on bytea values containing \0.

Thanks, I've been a bit thick here, but I just found the solution to my problem - and that doesn't need this awkward function nor any type of extra WITHOUT FUNCTION casts - just decode and encode, alas in exactly the opposite order than I originally expected.

mypgdb=# select decode('Tübingen'::text,'escape');
decode
-----------------
T\303\274bingen
(1 row)

mypgdbe=# select encode('T\303\274bingen','escape');
encode
----------
Tübingen
(1 row)

I think this should be safe for any kind of bytea value.

Kind regards

Markus