This discussion is archived
5 Replies Latest reply: Nov 26, 2012 8:15 AM by rp0428 RSS

DBMS_CRYPTO MD5 hash value does not match 3rd party MD5 free tool

975554 Newbie
Currently Being Moderated
Hello,

I am using Oracle Version: 11.2.4.

I have a problem where the MD5 value from DBMS_CRYPTO does not match the hash value from 3rd party MD5 free tool (MD5 Checksum Calculator 0.0.5.58 or WinMD5Free v1.20) and also the MD5 hash value calculated by an ingestion tool where I am transferring files to. The MD5 hash value that the ingestion tool calculates is the same as the 3rd party MD5 free tools I have. This occurs only on some of the XML files that I generate using XSQL(xmlserialize, xmlagg, xmlelement, etc.) and DBMS_XSLPROCESSOR on a Linux OS. The XML files are transferred from the Unix OS to my Windows 7 OS via filezilla.

I found a thread on this forum that also had a similar issue so I copy/paste the java functions. They are listed below(both are the same expect for the character set):
create or replace java source named "MD5_UTF_8" as
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Clob;
import java.sql.Blob;

public class MD5_UTF_8 {

private static final byte [] hexDigit = {
'0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F'
};

/** Converts a byte array to a hex string
* Returns an empty string if the byte array is null
*/
public static final String toHexString(byte [] bytes) {
if (bytes == null) return new String("");
StringBuffer buf = new StringBuffer(bytes.length * 2);
for (int i = 0; i < bytes.length; i++) {
buf.append((char) hexDigit[((bytes >>> 4) & 0x0F)]);
buf.append((char) hexDigit[(bytes & 0x0F)]);
}
return buf.toString();
}

// Convert Hex String to Byte Array

public static final byte[] byteArrayFromHexString(String str) {
byte[] bytes = new byte[str.length() / 2];
for (int i = 0; i < bytes.length; i++)
{
bytes = (byte) Integer.parseInt(str.substring(2 * i, 2 * i + 2), 16);
}
return bytes;
}

public static String getMD5HashFromClob(Clob inhalt) throws Exception{

MessageDigest algorithm;
StringBuffer hexString;
String s = null;
String salida = null;
int i;
byte[] digest;

String tepFordigest = inhalt.getSubString(1L, (int)inhalt.length());

try {
algorithm = MessageDigest.getInstance("MD5_UTF_8");
algorithm.reset();
algorithm.update(tepFordigest.getBytes("UTF-8"));

digest = algorithm.digest();

s = toHexString(digest);

} catch (java.security.NoSuchAlgorithmException nsae) {
s = "No es posible cifrar MD5";
}
return s;

}
}
/
sho err

alter java source "MD5_UTF_8" compile
/
sho err

CREATE OR REPLACE FUNCTION get_md5_UTF_8_CLOB(inhalt CLOB) RETURN VARCHAR2 DETERMINISTIC
AS LANGUAGE JAVA
name 'MD5_UTF_8.getMD5HashFromClob(java.sql.Clob) return java.lang.String';
/

create or replace java source named "MD5" as
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Clob;
import java.sql.Blob;

public class MD5 {

private static final byte [] hexDigit = {
'0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F'
};

/** Converts a byte array to a hex string
* Returns an empty string if the byte array is null
*/
public static final String toHexString(byte [] bytes) {
if (bytes == null) return new String("");
StringBuffer buf = new StringBuffer(bytes.length * 2);
for (int i = 0; i < bytes.length; i++) {
buf.append((char) hexDigit[((bytes >>> 4) & 0x0F)]);
buf.append((char) hexDigit[(bytes & 0x0F)]);
}
return buf.toString();
}

// Convert Hex String to Byte Array

public static final byte[] byteArrayFromHexString(String str) {
byte[] bytes = new byte[str.length() / 2];
for (int i = 0; i < bytes.length; i++)
{
bytes = (byte) Integer.parseInt(str.substring(2 * i, 2 * i + 2), 16);
}
return bytes;
}

public static String getMD5HashFromClob(Clob inhalt) throws Exception{

MessageDigest algorithm;
StringBuffer hexString;
String s = null;
String salida = null;
int i;
byte[] digest;

String tepFordigest = inhalt.getSubString(1L, (int)inhalt.length());

try {
algorithm = MessageDigest.getInstance("MD5");
algorithm.reset();
algorithm.update(tepFordigest.getBytes());

digest = algorithm.digest();

s = toHexString(digest);

} catch (java.security.NoSuchAlgorithmException nsae) {
s = "No es posible cifrar MD5";
}
return s;

}
}
/
sho err

alter java source "MD5" compile
/
sho err

CREATE OR REPLACE FUNCTION get_md5_CLOB(inhalt CLOB) RETURN VARCHAR2 DETERMINISTIC
AS LANGUAGE JAVA
name 'MD5.getMD5HashFromClob(java.sql.Clob) return java.lang.String';
/

I created the above java functions and added the calls to them in my package to see what hash values they would produce but I am getting "ORA-29532: Java call terminated by uncaught Java exception: java.nio.BufferOverflowException " the XML is about 60mb.

package code sniippets:
declare
l_hash raw(2000);
l_checksum_md5 varchar2(2000);
l_checksum_md5_utf_8 varchar2(2000);
Begin
:
:

t_checksum := lower(RAWTOHEX(dbms_crypto.hash(src=>l_clob,typ=>dbms_crypto.hash_md5)));

l_hash := get_md5_CLOB (l_clob);

l_checksum_md5 := lower(rawtohex(l_hash));

l_hash := get_md5_UTF_8_CLOB (l_clob);

l_checksum_md5_UTF_8 := lower(rawtohex(l_hash));
Please help,

Thank You in advance
Don

Edited by: 972551 on Nov 21, 2012 12:18 PM

Edited by: sabre150 on Nov 21, 2012 11:06 PM

Moderator action : added [code ] tags to format properly. In future please add them yourself.
  • 1. Re: DBMS_CRYPTO MD5 hash value does not match 3rd party MD5 free tool
    rp0428 Guru
    Currently Being Moderated
    >
    I have a problem where the MD5 value from DBMS_CRYPTO does not match the hash value from 3rd party MD5 free tool (MD5 Checksum Calculator 0.0.5.58 or WinMD5Free v1.20) and also the MD5 hash value calculated by an ingestion tool where I am transferring files to. The MD5 hash value that the ingestion tool calculates is the same as the 3rd party MD5 free tools I have.

    I found a thread on this forum that also had a similar issue so I copy/paste the java functions.
    >
    And in that thread (Re: MD5 HASH computed from DBMS_CRYPTO does not match .NET MD5 I provided the reason why DBMS_CRYPTO may not match hashes produced by other methodologies.

    I have no idea why you copied and posted all of that Java code the other poster and I provided since code has NOTHING to do with the problem you say you are having. Thte other poster's question was how to write Java code that would produce the same result as DBMS_CRYPTO.

    You said your problem was understanding why DBMS_CRYPTO 'does not match the hash value from 3rd party MD5 free tool ...'. and I answered that in the other forum.
    >
    The Crypto package always converts everything to AL32UTF8 before hashing so if the .NET character set is different the hash will likely be different.

    See DBMS_CRYPTO in the PL/SQL Packages and Types doc
    http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_crypto.htm

    If you look at the spec header for the DBMS_CRYPTO package it shows this note:
    -- Prior to encryption, hashing or keyed hashing, CLOB datatype is
    -- converted to AL32UTF8. This allows cryptographic data to be
    -- transferred and understood between databases with different
    -- character sets, across character set changes and between
    -- separate processes (for example, Java programs).
    --
    If your 3rd party MD5 free tool (MD5 Checksum Calculator 0.0.5.58 or WinMD5Free v1.20) do not use the AL32UTF8 character set then the hashes will likely be different. You can't modify DBMS_CRYPTO so if the hashes need to match you need to use 3rd party tools that either use the correct character set or can be configured to use the correct character set.

    The problem in the other thread was how to WRITE Java code that uses the correct character set and I showed that OP how to do that.
    So unless you are writing your own Java code all of that code you copied and pasted is useless for your use case.
  • 2. Re: DBMS_CRYPTO MD5 hash value does not match 3rd party MD5 free tool
    975554 Newbie
    Currently Being Moderated
    Thank You for your reply,

    The reason I used the code was because I want to find out what MD5 hash value that was produced from the MD5 function without utf_8 but when I tried using it I received an unhandle Java exception error:

    ORA-29532: Java call terminated by uncaught Java exception: java.nio.BufferOverflowException

    Edited by: 972551 on Nov 22, 2012 8:40 AM
  • 3. Re: DBMS_CRYPTO MD5 hash value does not match 3rd party MD5 free tool
    rp0428 Guru
    Currently Being Moderated
    >
    The reason I used the code was because I want to find out what MD5 hash value that was produced from the MD5 function without utf_8
    >
    So just use the code outside the database. You don't need to load the classes into Oracle and use a Java stored procedure.
  • 4. Re: DBMS_CRYPTO MD5 hash value does not match 3rd party MD5 free tool
    975554 Newbie
    Currently Being Moderated
    Thank You for your reply. I am new to java. How would I execute it outside of Oracle?
  • 5. Re: DBMS_CRYPTO MD5 hash value does not match 3rd party MD5 free tool
    rp0428 Guru
    Currently Being Moderated
    >
    Thank You for your reply. I am new to java. How would I execute it outside of Oracle?
    >
    If you are new to Java you should not be trying to use Java within Oracle if you can't use it outside the database.

    You create your Java source files and then compile them with 'javac', the Java compiler. Or you can use NetBeans or another IDE.

    The code you copied did not use Java inside Oracle.

    See the Java tutorial for instructions and example code.
    http://docs.oracle.com/javase/tutorial/getStarted/cupojava/index.html