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:

  • Entity Framework Code First: How to map SQL Server's computed columns into CF model?
  • Schema, Owner for objects in MS SQL
  • How can I specify the current schema for sql server in a jboss data source url?
  • ORDER BY enum integer field SQL
  • How to us a Subquery to Update Multiple Column Values in SQL?
  • How come the message “Table is marked for deletion” keeps appearing?
  • <cfset stringToHash = "Hello world!">
    <cfquery name="sqlserver" datasource="#mySqlServerDSN#">
        SELECT RIGHT( 
                CAST(<cfqueryparam value="#stringToHash#" cfsqltype="cf_sql_varchar">  AS nvarchar(max))      
       , 32) AS HASHED
    CF UFT-8:   #hash(stringToHash, 'MD5', 'UTF-8')#
    CF UFT-16:  #hash(stringToHash, 'MD5', 'UTF-16')#
    SQL Server: #sqlserver.hashed#


    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.

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

    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.

        helloWorld = "Hello, World!";
        javaString = CreateObject( "java", "java.lang.String" ).Init(helloWorld);
        javaHash = CreateObject( "java", "" ).getInstance("MD5");
        javaBigInt = CreateObject( "java", "java.math.BigInteger" ).Init(1,javaHash.digest());
        utf8HashCF = lcase(hash(helloWorld, 'MD5', 'UTF-8'));
        utf8HashJava = variables.javaBigInt.toString(16);
        #utf8HashCF# <br />
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.