Обсуждение: Problems with pgcrypto and special characters

Hello!

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:

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

The cluster was initialized with locale de_DE.UTF-8, pg_controldata confirms:
LC_COLLATE:                           de_DE.UTF-8
LC_CTYPE:                             de_DE.UTF-8

Database version is PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2

I think I'm missing something very obvious here, so please give me a hint: How can I use pgcrypto to encrypt and
decrypttext which contains UTF-8 special characters like german umlauts? I think that this simple bytea2text-function
probablyneeds a replacement, but I haven't got the faintest clue about how to actually retrieve the original input
afterencryption. Any help would be tremendously appreciated :) 

Thanks in advance!

Kind regards

   Markus

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

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
functionnor any type of extra WITHOUT FUNCTION casts - just decode and encode, alas in exactly the opposite order than
Ioriginally 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