• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

CF Hash and SQL Server

New Here ,
Jun 10, 2014 Jun 10, 2014

Copy link to clipboard

Copied


Hi,

Basically,  I receive a hash of a pk and would like to compare that hash value a pk in SQL Server.  There is no stored hash value in that table.  So I have to use CF function Compare to compare while looping the whole table.  As a result, it's slow down.  Is there any functions in SQL server or something that I can get a quicker comparison result?

Here is basically the loop of the table and comparing.

<cfquery name="name">

     SELECT id

     FROM tbl

</cfquery>

<cfloop query="name">

     <cfset hashID = HASH(name.id,"SHA")>

     <cfif COMPARE(url.id,hashID) EQ 0>

          [do something]

     </cfif>

</cfloop>

Views

996

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Guide , Jun 11, 2014 Jun 11, 2014

You definitely don't want the single quotes around the id, as that would literally be passing the string "id" to the function.  You might need to wrap the id in a CAST statement to convert it from an int to a string.

So

HASHBYTES('SHA1',id)

would become

HASHBYTES('SHA1',CAST(id as varchar(10)))

-Carl V.

Votes

Translate

Translate
Guide ,
Jun 11, 2014 Jun 11, 2014

Copy link to clipboard

Copied

Not sure which version of SQL Server you are using, but take a look at the HashBytes function.  You could then do:

<cfquery name="name">

     SELECT id

     FROM tbl

     WHERE HashBytes('SHA', id) = <cfqueryparam value="url.id" cfsqltype="CF_SQL_VARCHAR">

</cfquery>

<cfif name.RecordCount>

          [do something]

</cfif>

I'm not sure if "CF_SQL_VARCHAR" is the right cfsqltype, or if it needs to be "CF_SQL_BLOB" (I think hashes are still essentially text, but SQL Server returns a VARBINARY from HashBytes).

EDIT: Make sure that ColdFusion and SQL Server are producing the same results, as they use different hashing libraries.  Also, if you are hashing the table id in the URL for security, SHA is a pretty weak hashing algorithm.  If you are using SQL Server 2012 or above, you should consider bumping that up to at least SHA-256 (SHA2-256 on the SQL Server side).

-Carl V.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jun 11, 2014 Jun 11, 2014

Copy link to clipboard

Copied

Thank you for answering.

I use this to check for whether both hash are the same where id = 1212

SELECT Substring(master.dbo.fn_varbintohexstr(HASHBYTES('SHA1','1212')),3,64).

Here is my sql following your suggestion:

SELECT id

FROM tbl

WHERE Substring(master.dbo.fn_varbintohexstr(HASHBYTES('SHA1','id')),3,64). 

If I remove quote on id, then it throws an error: Argument data type int is invalid for argument 2 of hashbytes function

If I add the quote, then the id is text but return empty.  I know for sure both hash are matched each other.  I add substring and other functions to remove 0x in front of the hash value.  Any other suggestions?

Thanks for your help.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Jun 11, 2014 Jun 11, 2014

Copy link to clipboard

Copied

You definitely don't want the single quotes around the id, as that would literally be passing the string "id" to the function.  You might need to wrap the id in a CAST statement to convert it from an int to a string.

So

HASHBYTES('SHA1',id)

would become

HASHBYTES('SHA1',CAST(id as varchar(10)))

-Carl V.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jun 13, 2014 Jun 13, 2014

Copy link to clipboard

Copied

That CAST function seems to solve the issue, but the performance is very slow comparing when using a straight ID without hash.  I am not sure why hash ID is a lot slower than normal ID.

Thanks for you help!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Jun 13, 2014 Jun 13, 2014

Copy link to clipboard

Copied

LATEST

I think computing the hash does require some processing.  And since it is doing this in the where clause, it has to do it for every record in the table.  As the number of rows in the table grows, it will get even slower.  You might copy the query into SQL Server Management Studio and look at the Execution Plan.  Might give some hints on how to improve performance.

-Carl V.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation