How to call Oracle MD5 hash function?

Question

I have below code. I am using Oracle 11g.

SELECT DBMS_OBFUSCATION_TOOLKIT.md5 (input => UTL_RAW.cast_to_raw(
  FIRST_NAME
  ||LAST_NAME
  )) md5_key ,
  FIRST_NAME ,
  LAST_NAME
FROM C_NAME_TAB
WHERE PKEY='1234'

How can i call this code? Can i directly execute this code in sqldeveloper?

Answer 1

In Oracle 12c you can use the function STANDARD_HASH. It does not require any additional privileges.

select standard_hash('foo', 'MD5') from dual;

---

The dbms_obfuscation_toolkit is deprecated (see Note here). You can use DBMS_CRYPTO directly:

select rawtohex(
    DBMS_CRYPTO.Hash (
        UTL_I18N.STRING_TO_RAW ('foo', 'AL32UTF8'),
        2)
    ) from dual;

Output:

ACBD18DB4CC2F85CEDEF654FCCC4A4D8

Add a lower function call if needed. More on DBMS_CRYPTO.

Answer 2

I would do:

select DBMS_CRYPTO.HASH(rawtohex('foo') ,2) from dual;

output:

DBMS_CRYPTO.HASH(RAWTOHEX('FOO'),2)
--------------------------------------------------------------------------------
ACBD18DB4CC2F85CEDEF654FCCC4A4D8

Answer 3

@user755806 I do not believe that your question was answered. I took your code but used the 'foo' example string, added a lower function and also found the length of the hash returned. In sqlplus or Oracle's sql developer Java database client you can use this to call the md5sum of a value. The column formats clean up the presentation.

column hash_key format a34;
column hash_key_len format 999999;
select dbms_obfuscation_toolkit.md5(
          input => UTL_RAW.cast_to_raw('foo')) as hash_key,
       length(dbms_obfuscation_toolkit.md5(
          input => UTL_RAW.cast_to_raw('foo'))) as hash_key_len
 from dual;

The result set

HASH_KEY                           HASH_KEY_LEN
---------------------------------- ------------
acbd18db4cc2f85cedef654fccc4a4d8             32

is the same value that is returned from a Linux md5sum command.

echo -n foo | md5sum
acbd18db4cc2f85cedef654fccc4a4d8  -

1. Yes you can call or execute the sql statement directly in sqlplus or sql developer. I tested the sql statement in both clients against 11g.
2. You can use any C, C#, Java or other programming language that can send a statement to the database. It is the database on the other end of the call that needs to be able to understand the sql statement. In the case of 11 g, the code will work.
3. @tbone provides an excellent warning about the deprecation of the dbms_obfuscation_toolkit. However, that does not mean your code is unusable in 12c. It will work but you will want to eventually switch to dbms_crypto package. dbms_crypto is not available in my version of 11g.

Answer 4

To calculate MD5 hash of CLOB content field with my desired encoding without implicitly recoding content to AL32UTF8, I've used this code:

create or replace function clob2blob(AClob CLOB) return BLOB is
  Result BLOB;
  o1 integer;
  o2 integer;
  c integer;
  w integer;
begin
  o1 := 1;
  o2 := 1;
  c := 0;
  w := 0;
  DBMS_LOB.CreateTemporary(Result, true);
  DBMS_LOB.ConvertToBlob(Result, AClob, length(AClob), o1, o2, 0, c, w);
  return(Result);
end clob2blob;
/

update my_table t set t.hash = (rawtohex(DBMS_CRYPTO.Hash(clob2blob(t.content),2)));