ColdFusion Tutorials - EasyCFM.COM

ColdFusion tutorial to create same hash as CF, but in MS SQL!

How cool would it be, being able to create the same MD5 hash as ColdFusion, directly in the MS SQL RDMBS?

I know there’s been quite some demand for this function, most people create the MD5 hash in ColdFusion and then pass it the Database, this mean a couple more round trips to the db, which we prefer to avoid at anytime.

We’ll make some assumptions in this article to make things easier, and they are;

you are running MS SQL 2005
you are running ColdFusion

Ready to get started?

The first thing to do is go into your MS SQL database and create the following function that creates the hash and return a string value.

CREATE FUNCTION [dbo].[fn_getHash] (
      -- Add the parameters for the function here
      @myString VARCHAR( 8000 )
)
RETURNS CHAR( 32 )
AS
BEGIN
      -- Declare the return variable here
      DECLARE @returnValue CHAR( 32 )

      -- Add the T-SQL statements to compute the return value here
      DECLARE @binaryValue VARBINARY( 255 )
      SET @binaryValue = (SELECT hashBytes( 'MD5', CONVERT( VARCHAR, @myString ) ) )

      DECLARE @characterValue VARCHAR( 255 )
      DECLARE @i INT
      DECLARE @length INT
      DECLARE @hexadecimalString CHAR( 16 )

      SELECT @characterValue = '
      SELECT @i = 1
      SELECT @length = DATALENGTH( @binaryValue )

SELECT @hexadecimalString = '0123456789abcdef'

WHILE (@i <= @length)
BEGIN

            DECLARE @tempInt INT
            DECLARE @firstInt INT
            DECLARE @secondInt INT

            SELECT @tempInt = CONVERT(INT, SUBSTRING( @binaryValue, @i, 1 ) )
            SELECT @firstInt = FLOOR( @tempInt / 16 )
            SELECT @secondInt = @tempInt - ( @firstInt * 16 )

            SELECT @characterValue = @characterValue +
            SUBSTRING( @hexadecimalString, @firstInt + 1, 1 ) +
            SUBSTRING( @hexadecimalString, @secondInt + 1, 1 )

SELECT @i = @i + 1

END

SET @returnValue = UPPER( @characterValue )

-- Return the result of the function
RETURN @returnValue

END

Now the ColdFusion code to test and make sure it’s the same hash as in ColdFusion.

<cfquery
 name="rsHash"
 datasource="[your dsn here]">
 SELECT [dbo].[fn_getHash] ( 'clickfind.com.au is cool!' ) AS myHash
</cfquery>
<cfoutput>
 
 Hash from MS SQL: #rsHash.myHash# 
 Hash from ColdFusion: #hash( "clickfind.com.au is cool!", "MD5" )# 
 Are they the same? #yesNoFormat( not compareNoCase( hash( "clickfind.com.au is cool!", "MD5" ), rsHash.myHash ) )#
 
</cfoutput>

How easy is that? I know it will certainly benefit our code not having to go to CF to create a hash.

Of course it’s pretty easy to change the function so that it can create the same hash for the following algorithms MD2, MD4, SHA and SHA1.

I hope you enjoyed this article, any questions, feel free to contact coldfusion-tutorial@clickfind.com.au make sure you refer to the original document as we might not know which one you are referring to.

Feel free to copy and distribute this tutorial, but make sure you reference the author and sponsor.

Author: Taco Fleur
This article was sponsored by clickfind

All ColdFusion Tutorials By Author: Taco Fleur

ColdFusion tutorial to create same hash as CF, but in MS SQL!
How cool would it be, being able to create the same MD5 hash as ColdFusion, directly in the MS SQL RDMBS?
Author: Taco Fleur
Views: 1,431
Posted Date: Sunday, January 6, 2008
ColdFusion tutorial to export from database to RSS
Always wanted to export items from your existing database into RSS with ColdFusion and syndicate it, but don't know how? Well, this is your lucky day! We’re going to show you how you can easily export your database records to RSS.
Author: Taco Fleur
Views: 1,426
Posted Date: Saturday, January 5, 2008