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?

Jon Heller · Accepted Answer · 2016-11-22 23:38:18Z

49

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.

edited Nov 22, 2016 at 23:38

Jon Heller

37k6 gold badges84 silver badges150 bronze badges

answered Mar 20, 2014 at 13:42

tbone

15.5k3 gold badges37 silver badges42 bronze badges

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

3 Comments

ZerOne Over a year ago

Is there a way to decrypt this key?

tbone Over a year ago

@ZerOne no, its a one-way hash. See here for more

Jared Still Over a year ago

As standard_hash is callable only in a SQL statement, the maximum size of data that can be hashed is 4000 bytes. Anything longer than that will still require dbms_crypto.

James Ding · Accepted Answer · 2016-11-22 23:24:18Z

5

I would do:

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

output:

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

answered Nov 22, 2016 at 23:24

James Ding

3335 silver badges7 bronze badges

1 Comment

ckujau Over a year ago

Please note, DBMS_CRYPTO may not always be available, see Oracle 11g DBMS_CRYPTO invalid identifier.

Greg · Accepted Answer · 2017-01-26 18:03:00Z

@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  -

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.
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.
@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.

The question pertained to oracle 11g and you are right, the question was not answered for 11g. I was also looking for a solution as of 11g. This solution worked for me, thanks a lot.

Nashev · Accepted Answer · 2017-04-26 17:20:58Z

1

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)));

answered Apr 26, 2017 at 17:20

Nashev

7136 silver badges14 bronze badges

Collectives™ on Stack Overflow

How to call Oracle MD5 hash function?

4 Answers 4

3 Comments

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related