0

I am trying to use DBMS_CRYPTO encrypt, but no matter how I choose some of the parameters, I encounter values for which the code throws

ORA-01890: NLS error detected ORA-06512: at "SYS.UTL_I18N", line 72 ORA-06512: at "SYS.UTL_I18N", line 353 ORA-06512: at line 26

Here is an example

declare
-- v_value             VARCHAR2 (4000) := '9Ab2Ov1Bd4'; --  works
-- v_value             VARCHAR2 (4000) := '7Md4Mt7Gk0'; --  works
-- v_value             VARCHAR2 (4000) := '3Vf8Fi2Pa5'; --  works
-- v_value VARCHAR2(4000) := '5Vq2Dc4Cq9'; -- works as well
v_value VARCHAR2(4000) := '2Cq0Yh3Vb2'; --this does not work?
v_result            VARCHAR2 (4000);
v_raw_row           RAW (2000);                 -- stores ec binary text

v_enc_type          PLS_INTEGER
         :=                        
     DBMS_CRYPTO.ENCRYPT_AES256 +  
     DBMS_CRYPTO.CHAIN_CBC +
     DBMS_CRYPTO.PAD_ZERO;                   -- total encryption type

v_def_k             VARCHAR2 (32) := 'mMbSmSMr_!_uAlns9asG5_a_AfhS4_3a';
begin
 v_raw_row :=
            DBMS_CRYPTO.ENCRYPT (
               src   => UTL_I18N.STRING_TO_RAW (v_value, 'AL32UTF8'),
               typ   => v_enc_type,
               key   => UTL_RAW.CAST_TO_RAW (v_def_k));
v_result := UTL_I18N.RAW_TO_CHAR (v_raw_row, 'AL32UTF8');
dbms_output.put_line(v_result);
end;

Changing the encryption type and key "solves" the issue for the one value which does not work, but it I always encounter another value which will then throw this exact exception.

I tried this in Oracle 12.2.0.1.0 as well as in 19.0.0.0.0. Exactly the same behaviour.

I guess that something I am doing is completely wrong. Any help is appreciated.

Dantel35
  • 103
  • 1
  • 5
  • Encrypting the binary representation of a string will generally not result in a binary value which happens to be a printable string. Use `RAXTOHEX()` or `UTL_ENCODE.BASE64_ENCODE` or something if you want to print an arbitrary binary value (like the output of encryption). – kfinity Jun 28 '21 at 19:55
  • hi kfinity, thank you for your comment. I do not really want to print it but rather store it in a VARCHAR2 and decrypt it later. The example in the oracle documentation (https://docs.oracle.com/database/121/ARPLS/d_crypto.htm#ARPLS65690) suggests using UTL_I18N.RAW_TO_CHAR. Is your comment just about printing or a general suggestion to avoid this exception altogether? – Dantel35 Jun 29 '21 at 07:30

1 Answers1

1

Instead of AL32UTF8 as source character set, can you please try using WE8ISO8859P1 or null.

From Oracle doc:

UTL_I18N.RAW_TO_CHAR is a function that converts raw to char data type conversion can be implemented, but not different character sets can be converted. The second parameter should specify which characteret is passed by the raw data, ie the source character set.

Specify the character set that the RAW data was derived from. If src_charset is NULL, then the database character set is used.

so if using DB characterset or NULL, no error will be reported.

little_amb
  • 84
  • 5
  • Thank you for your answer. I was going to say this does not work, as the charset of my databases is indeed AL32UTF8, queried like this: `select * from nls_database_parameters where parameter='NLS_CHARACTERSET';`. But it still does seem to work setting the charset to NULL, I tried it with 1000 random Strings and the exception did not appear. But I do not understand why this is working and if this way is really safe or if I just did not hit the one String which will cause this to die again. Do you have any further insight? – Dantel35 Jun 29 '21 at 07:24
  • I am accepting this answer as it solved the problem, I tried with 300k random strings and it never failed. Although I am completely puzzled as to why this helps. – Dantel35 Jun 30 '21 at 08:04