CF Equivalent MD5 Hashing Functions for SQL Server 2005+ & Oracle 8+

Fri, 23 May 2008 19:00:00 GMT

Both Oracle and SQL Server have built-in hashing functions. The trick is neither returns a value that is immediately comparable to a hashed value in ColdFusion (i.e. 32 character, hex based, upper case). So some additional conversion is required.

Oracle 8+

I couldn't figure out how to make it work inline with a SELECT statement, so first create a database function MD5_HASH:

CREATE OR REPLACE FUNCTION MD5_HASH (v_input_string in varchar2) RETURN VARCHAR2 IS
    v_checksum VARCHAR2(20);
BEGIN
   v_checksum := dbms_obfuscation_toolkit.md5 (input_string => v_input_string);
   RETURN utl_raw.cast_to_raw(v_checksum);
END;

Then use this with a SELECT statement. The "dbms_obfuscation_toolkit.md5()" generates a RAW value that is then converted with "utl_raw.cast_to_raw()" to return a string than is equivalent to a CF hash() call. So an example SELECT statement:

SELECT MD5_HASH([column]) FROM [table]

SQL Server 2005+

Note there is an option for earlier versions of SQL Server that involve installing a .dll, but this example takes advantage of a function introduced in 2005 "HashBytes()".

SELECT UPPER( RIGHT( master.dbo.fn_varbintohexstr( HashBytes( 'MD5', [column] ) ), 32) )
FROM [table]

A few notes:

master.dbo.fn_varbintohexstr() is an undocumented function, but it has been around for awhile, and doesn't appear to be going anywhere, and I don't know another way to do the conversion
RIGHT( [value], 32) is needed because the value returned has a leading 0x to indicate the value is hexadecimal, which ColdFusion does not do.
Finally UPPER() is there because the value returned is lower cased.

XML StAX Processing with Coldfusion

Wed, 28 Mar 2007 15:04:00 GMT

Several problems with Coldfusion XML processing and solution set:

Slow: due to DOM processor (Xerces)
Inconsistent Round-Tripping (i.e. serialization / deserialization) :
1. (un-) escaping characters
2. non-printable characters
3. missing / multiple XML declaration(s)
4. character set problems
5. et al.
Kludgy Tree API yes you can use structure and array functions, just don't like it.

Note, don't see this as a flame on Adobe/Macromedia. The Coldfusion XML implementation uses best available technology at the time and APIs that fit in with the language paradigm. Just have better options now.

Slow Solution

Woodstox: Jim Collins wrote a CFDJ article about using a StAX XML processor with Coldfusion. Unfortunately, as near as I can tell, his open source project to integrate Coldfusion with Woodstox was never released. Testing demonstrates a StAX processor, such as Woodstox, provides very efficient (read: fast) XML processing, and a more intuitive API, than a SAX processor. But still not intuitive enough for me. So enter...

Round-Tripping & API Solutions

XOM provides a very easy to use API that roundtrips XML like a champ. Correct I/O is XOM's middle name. But it doesn't integrate natively with a StAX processor unless you use...

NUX is like a Swiss Army knife for XML. But the purpose of this entry just know it's the glue between XOM and Woodstox (note that NUX includes the XOM jar so you do not need to download it separately).

Code

Download the Woodstox and NUX jars and configure them in your Coldfusion classpath. The examples below build upon each other.

Setup

Creates a XOM Builder object backed by a StAX processor.

inputFactory = createObject("java", "com.ctc.wstx.stax.WstxInputFactory").init()
builder = createObject("java", "nux.xom.io.StaxUtil").createBuilder(local.joInputFactory, javacast("null", "")

XMLParse() Alternative

Returns a XOM Document object. Note: provide XMLString variable.

document = builder.create(XMLString)

XMLSearch() Alternative

Returns a XOM Nodes object (i.e. an iterator of Node objects). Note: provide XQueryString variable.

nodes = createObject("java", "nux.com.xquery.XQueryUtil").xquery(document, XQueryString)

loop from="0" to="#nodes.size() - 1#" index="idx"
element = nodes.get(javacast("int", idx))
attributeValue = element.getAttribute("attributeName").getValue()
attributeText = element.getValue
/loop

ToString() Alternative

Creates a string representation of the XML.

XMLString = document.toXML()

RSS feed for InstantSpot site orangepips

CF Equivalent MD5 Hashing Functions for SQL Server 2005+ & Oracle 8+

Oracle 8+

SQL Server 2005+

XML StAX Processing with Coldfusion