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>
<p>
Hash from MS SQL: <strong>#rsHash.myHash#</strong><br>
Hash from ColdFusion: <strong>#hash( "clickfind.com.au is cool!", "MD5" )#</strong><br>
Are they the same? <strong>#yesNoFormat( not compareNoCase( hash( "clickfind.com.au is cool!", "MD5" ), rsHash.myHash ) )#</strong>
</p>
</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
Date added: Sun. January 6, 2008
Posted by: Taco Fleur | Views: 1432 | Tested Platforms: CFMX7 | Difficulty: Advanced
SQL
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. - Date added: Sat. January 5, 2008
|
|
ColdFusion Hosting
Blink
Del.icio.us
Digg
Furl
Google
Simpy
Spurl
Y! MyWeb