CODEWITHSUNDEEP
postgresqloracle-databaseoracle12cpostgresql-12md5sum
RonJohn
RonJohn

Reputation: 425

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

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

Upvotes: 1

Views: 1073

Answers (1)

Jon Heller
Jon Heller

Reputation: 36862

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.

Upvotes: 3

Related Questions