[GENERAL] Problems with pgcrypto and special characters

[Markus Wollny]

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 decrypt text which contains UTF-8 special 
characters like german umlauts? I think that this simple bytea2text-function 
probably needs a replacement, but I haven't got the faintest clue about how to 
actually retrieve the original input after encryption. Any help would be 
tremendously appreciated :)

Thanks in advance!

Kind regards

   Markus

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Problems with pgcrypto and special characters

[Ragnar Hafstað]

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




---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Problems with pgcrypto and special characters

[Tom Lane]

Markus Wollny [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Problems with pgcrypto and special characters

[Markus Wollny]

Hi!

-Original Message-
From:   Ragnar Hafsta [mailto:[EMAIL PROTECTED]

are you sure your problem is with pg_crypto ?
what does this produce:
  select bytea2text('Tbingen'::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('Tbingen','escape') ,
  'mypassphrase'::bytea,
  'bf'::text
  ),
  'mypassphrase'::bytea,
  'bf'::text
  )
  ) as foo;

Yes, and that doesn't work either:

mypgdb=# select decode(encode('Tbingen'::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('Tbingen','escape'),'escape');
  encode
--
 Tbingen
(1 row)

Now using pgcrypto works, too:

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

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

Kind regards

   Markus


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Problems with pgcrypto and special characters

[Markus Wollny]

Hi!

-Original Message-
From:   Tom Lane [mailto:[EMAIL PROTECTED]
 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('Tbingen'::text,'escape');
 decode
-
 T\303\274bingen
(1 row)

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

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

Kind regards

   Markus


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match