Not sure which version of SQL Server you are using, but take a look at the HashBytes function. You could then do:
WHERE HashBytes('SHA', id) = <cfqueryparam value="url.id" cfsqltype="CF_SQL_VARCHAR">
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).
Thank you for answering.
I use this to check for whether both hash are the same where id = 1212
Here is my sql following your suggestion:
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.
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.
HASHBYTES('SHA1',CAST(id as varchar(10)))
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!
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.