Re: dbms_obfuscation_toolkit issue

In article <3CF9B161.3070401_at_usa.net>, John says...
>
>Two databases - one 8.1.7.2.1 (production,) the other 8.1.7.0.0 (staging,) on
>different Win2K servers.
>
>Encryption of a string (using the obfuscation toolkit) using the same key
>returns different results on the two databases. Each database is using
>WE8ISO8859P1 character and NCHAR character sets.
>
>I'm "told" that prior to the upgrade to 8.1.7.2.1, the same string and the same
>key would return the same encrypted string on both systems.
>
>Can anyone cast light on this? Why are different encrypted strings being
>returned? Is there a more "portable" way to generate encrypted strings which
>can be migrated across databases?
>

The strings are encrypted the same -- the problem lies in the CLIENT calling the procedure.

it is probably best (based on experience) to never never never use varchars with encrypted data. Always use RAW (use utl_raw.cast_to_raw to cast the varchar2 into raw on the way in and utl_raw.cast_to_varchar2 on the way out to convert the raw back.

The problem is -- the encryption will undoubtably result in a string with some weird 8bit characters that either

1. don't fit into the character set normally
2. will be converted into something else (scrambled in effect) when a CLIENT what a different character set then the database fetches them out and they undergo character set conversion.

To see that they are the same -- have your routine log the encrypted values into a table. Use the DUMP function on that values in the table. You can confirm that they are the same as long as they do not leave the database. As soon as a client fetches the encrypted data back -- all bets are off if they character set of the client was different. The data is lost at that point. RAW will not suffer from this.

>Code follows.
>
>Thanks!
>
>---------------
>
>create or replace PACKAGE general IS
> Procedure EncryptPassword(input_string varchar2, key_string varchar2,
>pencrypted_string OUT varchar2);
> Procedure DecryptPassword(encrypted_string varchar2, key_string varchar2,
>decrypted_string OUT varchar2);
>
>END;
>
>create or replace PACKAGE BODY general IS
>
>
> Procedure EncryptPassword(input_string varchar2, key_string varchar2,
>pencrypted_string OUT varchar2)is
>
> encrypted_string VARCHAR2(2048);
> tyencrypted_string VARCHAR2(2048);
>
> begin
> dbms_obfuscation_toolkit.DESEncrypt(
> input_string => input_string,
> key_string => key_string,
> encrypted_string => encrypted_string );
>
> tyencrypted_string := rawtohex(UTL_RAW.CAST_TO_RAW(encrypted_string));
> pencrypted_string := tyencrypted_string;
>
>
> end;
>
> Procedure DecryptPassword(encrypted_string varchar2, key_string varchar2,
>decrypted_string OUT varchar2)
> is
> pencrypted_string VARCHAR2(2048);
> begin
> pencrypted_string := utl_raw.cast_to_varchar2(hextoraw(encrypted_string));
> dbms_obfuscation_toolkit.DESDecrypt(
> input_string => pencrypted_string,
> key_string => key_string,
> decrypted_string => decrypted_string );
>
> end;
>
>end;
>
>
>--
>John Bossert
>
>In what concerns you much, do not think that you
>have companions: know that you are alone in the world.
>
> -- Henry David Thoreau
>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp