ColdFusion md5 hash directy in MSSQL

by Paul Kukiel 4:21 AM 3 comments CFML SQL

Ever needed to compare a ColdFusion hash() directly in MS SQL? I did today and I knew I had accomplished this before but forgot how to do it. Searching the net and twitter I came to some dead end or longish solutions. I knew Steve Good and I worked this out once before but I also remember there being an easier way.

Well there is and here is where I took the solution from: http://orangepips.instantspot.com/blog/Database

The funny thing is I found this nearly a year ago and actually commented on the post just forgot all about it.

<br /> <cfquery datasource="SQL"><br /> insert <br /> into Contact<br /> ( firstName, firstNameHash )<br /> values ( <cfqueryparam cfsqltype="cf_sql_varchar" value="Paul" />,<br /> <cfqueryparam cfsqltype="cf_sql_varchar" value="#hash('Paul')#" /> )<br /> </cfquery><br />

<br /> select id, firstName, firstNameHash, upper(right(master.dbo.fn_varbintohexstr(HashBytes('MD5', firstName)), 32)) AS sqlHash<br /> from Contact<br />

The result: