How to Use Correctly DBMS_CRYPTO.HASH With Clob and Raw Input? (Doc ID 760247.1)

Last updated on NOVEMBER 04, 2015

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.3 and later
Information in this document applies to any platform.
Information in this document applies to any platform.

Checked for relevance on 04-Jun-2013

Goal

When using the dbms_crypto.hash procedure, it is easy to overlook how this actually works and obtain the undesired results. Below is one example of such possible scenarios.

The scenario assumes that the database character set is not AL32UTF8 or UTF8 and that the character set is one Western European character set (assume WE8MSWIN1252), but the same can happen for any other characterset.

Trying to encrypt the same character (Ö), it appears that the procedure returns different results for the same character, when the character is over codepoint 127.

It is also good to mention that this procedure is deterministic, so it should return for the same input the same output.

For character 214:
SQL> begin
2 dbms_output.put_line(rawtohex(dbms_crypto.hash(src => hextoraw('d6'), typ => dbms_crypto.HASH_MD5)));
3 dbms_output.put_line(rawtohex(dbms_crypto.hash(src => to_clob(chr(214)), typ => dbms_crypto.HASH_MD5)));
4 end;
5 /
735E75A2CB5EF610F0AEB7495975377D
B3426522C7EC61900C76A863534F6A6A

while for 120;
SQL> begin
2 dbms_output.put_line(rawtohex(dbms_crypto.hash(src => hextoraw('78'), typ =>dbms_crypto.HASH_MD5)));
3 dbms_output.put_line(rawtohex(dbms_crypto.hash(src => to_clob(chr(120)) , typ =>dbms_crypto.HASH_MD5)));
4 end;
5 /
9DD4E461268C8034F5C8564E155C67A6
9DD4E461268C8034F5C8564E155C67A6

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms