How to Generate Matching MD5 Hash: SQL Server nvarchar Field vs. ColdFusion

I am trying to figure out how to generate a matching MD 5 hash value between SQL Server and ColdFusion. The root cause appears to be that the SQL Server field is an nvarchar datatype, which seems to mean I need to do something with the encoding of the string I would hash in ColdFusion or Java to make it match, but I am unable to figure it out. To be clear if this was a SQL Server varchar field, everything works.

Here's the code I'm trying:

<cfset stringToHash = "Hello world!">

<cfquery name="sqlserver" datasource="#mySqlServerDSN#">
    SELECT RIGHT( 
    master.dbo.fn_varbintohexstr( 
        HashBytes( 
            'MD5', 
            CAST(<cfqueryparam value="#stringToHash#" cfsqltype="cf_sql_varchar">  AS nvarchar(max))      
        ) 
    ) 
   , 32) AS HASHED
</cfquery>

<cfoutput>
<pre>
CF UFT-8:   #hash(stringToHash, 'MD5', 'UTF-8')#
CF UFT-16:  #hash(stringToHash, 'MD5', 'UTF-16')#
SQL Server: #sqlserver.hashed#
</pre>
</cfoutput>

Produces

CF UTF-8:   86FB269D190D2C85F6E0468CECA42A20
CF UTF-16:  0C89A9720D83539E3723BB99C07D069F
SQL Server: f9a6119c6ec37ce652960382f8b59f2c

So I'm guessing 开发者_开发问答I need to change the final argument I'm passing to hash() to be a different encoding, but I can't figure it out. I've also tagged this question as Java too, because I'm more than happy to take an answer in that language as well.

By default SQL Server uses the UTF-16 in little-endian byte order character set for nvarchar fields. In ColdFusion you must use the 'UTF-16LE' character set.

<cfscript>
    helloWorld = "Hello, World!";
    utf8HashCF = lcase(hash(helloWorld, 'MD5', 'UTF-16LE'));
</cfscript>
<cfoutput>
    #utf8HashCF# <br />
</cfoutput>

I'm curious why your sql server column is nvarchar; it's not necessary for hashes. nvarchar is for storing extended character sets, which you shouldn't be getting back from a hash function.

Regardless, I tried all of the hash algorithms available in CF9 and none of them generate the hash you're looking for.

Unless you need to keep the column set to nvarchar for some reason you haven't already explained, why not change it to varchar?

I don't think it's the CF hashing because if you compare the CF to Java they create the same hash. Both the CF & Java output "65a8e27d8879283831b664bd8b7f0ad4" on my box and it matched the SQL hash when I changed the cast to varchar(32).

In the past when I've needed to do any sort of hash creation and comparison, I created a service that returns a string so you don't have to worry about cross platform algorithm issues. You could also just have sql do it all for you, but then you have the business logic in the wrong layers but to each their own.

<cfscript>
    helloWorld = "Hello, World!";

    javaString = CreateObject( "java", "java.lang.String" ).Init(helloWorld);
    javaHash = CreateObject( "java", "java.security.MessageDigest" ).getInstance("MD5");

    javaHash.reset();
    javaHash.update(javaString.getBytes("UTF-8"),0,javaString.length());

    javaBigInt = CreateObject( "java", "java.math.BigInteger" ).Init(1,javaHash.digest());

    utf8HashCF = lcase(hash(helloWorld, 'MD5', 'UTF-8'));
    utf8HashJava = variables.javaBigInt.toString(16);
</cfscript>
<cfoutput>
    #utf8HashCF# <br />
    #utf8HashJava#
</cfoutput>