Oracle m5 hash returns different string than do Postgresql and bash md5sum(1)

Question

Oracle 12c:

SQL> select UTL_RAW.CAST_TO_RAW('Tom') as hex_val, 
  2         dbms_crypto.hash(src=>UTL_RAW.CAST_TO_RAW('Tom'), typ=>2) as hex_hash
  3  from dual;
546F6D
D9FFACA46D5990EC39501BCDF22EE7A1

Postgresql 12.5:

sides=> select upper(encode('Tom', 'hex')), md5(upper(encode('Tom', 'hex')));
 upper  |               md5                
--------+----------------------------------
 546F6D | f679fe36c1c908fa2547e6915026b0af
(1 row)

md5sum with newline:

-bash-4.2$ echo "546F6D" | md5sum   
ef81d9f3f3e1305c92ce84efdecfd1bc  -

md5sum without newline:

-bash-4.2$ echo -n "546F6D" | md5sum
f679fe36c1c908fa2547e6915026b0af  -

As you can see, Postgres' MD5() function matches that of md5sum(1) without the newline. That's what I expected. However, the Oracle 12c doesn't match either, and nor does it match the md5 sum with newline?

What weirdness is Oracle doing (or am I making a PEBKAC mistake)?

(The ultimate purpose is to show the client that xml data successfully migrated from Oracle to Postgres. That's why weak hashing is acceptable.)

EDIT:

Using RAWTOHEX() returns the same value as CAST_TO_RAW().

SQL> select UTL_RAW.CAST_TO_RAW('Tom') as hex_val
  2       , dbms_crypto.hash(src=>UTL_RAW.CAST_TO_RAW('Tom'), typ=>2) as hex_hash
  3       , dbms_crypto.hash(src=>RAWTOHEX('Tom'), typ=>2) as raw_hash
  4  from dual;

HEX_VAL
--------------------------------------------------------------------------------
HEX_HASH
--------------------------------------------------------------------------------
RAW_HASH
--------------------------------------------------------------------------------
546F6D
D9FFACA46D5990EC39501BCDF22EE7A1
D9FFACA46D5990EC39501BCDF22EE7A1

SQL> select UTL_RAW.CAST_TO_RAW('Tom') as hex_val
  2       , dbms_crypto.hash(src=>UTL_RAW.CAST_TO_RAW('Tom'), typ=>2) as hex_hash
  3       , dbms_crypto.hash(src=>RAWTOHEX('Tom'), typ=>2) as raw_hash
  4       , standard_hash(rawtohex('Tom'), 'MD5') as std_hash
  5  from dual;

HEX_VAL
--------------------------------------------------------------------------------
HEX_HASH
--------------------------------------------------------------------------------
RAW_HASH
--------------------------------------------------------------------------------
STD_HASH
--------------------------------
546F6D
D9FFACA46D5990EC39501BCDF22EE7A1
D9FFACA46D5990EC39501BCDF22EE7A1
F679FE36C1C908FA2547E6915026B0AF

Jon Heller · Answer 1 · 2021-03-20T01:38:43.383

3

Your Oracle command is hashing the hexidecimal value, but your Postgres and bash commands are hashing the hexadecimal representation. To have Oracle hash the string of the hexadecimal value, use RAWTOHEX:

SQL> select standard_hash(rawtohex('Tom'), 'MD5') from dual;

STANDARD_HASH(RAWTOHEX('TOM'),'M
--------------------------------
F679FE36C1C908FA2547E6915026B0AF

DBMS_CRYPTO and STANDARD_HASH work the same way, except that DBMS_CRYPTO only accepts the RAW data type.To confuse things, there is sometimes implicit conversions, and SQL*Plus may display different data types in different ways. But according to the UTL_RAW.CAST_TO_RAW documentation, "The data itself is not modified in any way, but its datatype is recast to a RAW datatype".

To compare raw values on both databases, compare select select dbms_crypto.hash(src=>utl_raw.cast_to_raw('Tom'), typ=>2) from dual; on Oracle with select upper(md5('Tom')); on Postgres - they both return D9FFACA46D5990EC39501BCDF22EE7A1.

edited Mar 20 '21 at 01:38

answered Mar 19 '21 at 16:35

Jon Heller

34,999
6
74
132

Are you saying that `CAST_TO_RAW()` generates binary data that sqlplus is then interpreting as hex? – RonJohn Mar 19 '21 at 17:48
See my edited question. RAWTOHEX() returns the same as CAST_TO_RAW(). Note also that I've got to use dbms_crypto.hash() because the xml data can be more than 4000 bytes. – RonJohn Mar 19 '21 at 17:56
The issue seems to be that `dbms_crypto.hash()` and `STANDARD_HASH()` do different things. – RonJohn Mar 19 '21 at 17:59
Yes, I think that SQL\*Plus is just displaying the data differently; per the [manual](https://docs.oracle.com/database/121/ARPLS/u_raw.htm#ARPLS71383): "The data itself is not modified in any way, but its datatype is recast to a RAW datatype". On the Postgres side, you'll get the "D9..." value if you remove the `ENCODE` function. – Jon Heller Mar 19 '21 at 18:06

Oracle m5 hash returns different string than do Postgresql and bash md5sum(1)

1 Answers1