Oracle Database Discussions

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[i] >>> 4) & 0x0F)]);
buf.append((char) hexDigit[(bytes[i] & 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[i] = (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[i] >>> 4) & 0x0F)]);
buf.append((char) hexDigit[(bytes[i] & 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[i] = (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:45 PM