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>
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.
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.
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.
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.
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!
Copy link to clipboard
Copied
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.