DBMS_CRYPTO.ENCRYPT returning different results in different oracle DB versions

Question

I got an annoying issue I'm out of ideas with. When using the DBMS_CRYPTO.ENCRYPT function of the oracle dbms_crypto package I get a different result in oracle 11C and oracle 19. I first noticed this issue when migrating a database from 11c to 19c and running into issue decrypting stored values.

The following sql query illustrates this issue:

select rawtohex(DBMS_CRYPTO.ENCRYPT(src => UTL_I18N.STRING_TO_RAW ('TOENCRYPT', 'AL32UTF8'),typ => 5128 ,key => UTL_I18N.STRING_TO_RAW ('e24WwDYbk25wqe5pevJ3g3VJgyjXr6HX', 'AL32UTF8'))) from dual;

In oracle 11c this query returns 9A18D619A269A5AF9716F2869A8A4F5F while in oracle 19c it returns 049AFACC8EC7AE239EC496E5B4534048.

I have been trying to figure out what could cause this difference. I checked the sub-parts of the query and I isolated the first difference to the output of the encrypt function.

I also checked with different databases of 11g and the query always gave the same result.

Did someone else encounter this issue before and knows how to solve this? Or can someone give me some pointers as to what would influence the functions from the query?

wolφi · Accepted Answer · 2020-05-26 23:16:42Z

1

@TenG's hint was spot on.

typ = 5128 = 0x1408 
             0x1000 PAD_PKCS5
             0x0400 CHAIN_OFB
             0x0008 ENCRYPT_AES256

11.2         9A18D619A269A5AF9716F2869A8A4F5F
12.1         049AFACC8EC7AE239EC496E5B4534048

According to Oracle Support Doc ID 2014909.1, OFB mode was not properly implemented in 11.2 and was in fact doing the ECB chaining.

             0x1000 PAD_PKCS5
             0x0300 CHAIN_ECB
             0x0008 ENCRYPT_AES256
typ = 4872 = 0x1308 

11.2         9A18D619A269A5AF9716F2869A8A4F5F
12.1         9A18D619A269A5AF9716F2869A8A4F5F

In other words, in 11.2, if you asked for OFB, you got ECB instead. If you ask for ECB, you get the same value in both versions.

answered May 26, 2020 at 23:16

wolφi

8,3814 gold badges42 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Asis Kumar Panda Nov 5 at 19:32

typ parameter is calculated based on encryption algorithm, block cipher mode and padding mode. typ= Encryption Algo + Cipher Mode + Padding Mode (Algo , Block Cipher Mode, Padding Mode are const macros). It could be a bug that certain combination of Algo/Cipher Mode/Padding Mode is not working as expected.

Collectives™ on Stack Overflow

DBMS_CRYPTO.ENCRYPT returning different results in different oracle DB versions

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest