Re: Problems with pgcrypto and special characters

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