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:

  • How to convert rows into columns in SQL Server?
  • joining multiple tables with filter by date
  • Sql Server doing a full table scan when first field in PK has few distinct values
  • Get SQL Server plan cache statements without system statements
  • Clustered indexes on non-identity columns to speed up bulk inserts?
  • “Select in” with “ordered pairs”
  • <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.

    3 Solutions collect form web for “How to Generate Matching MD5 Hash: SQL Server nvarchar Field vs. ColdFusion”

    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>
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.