5 Replies Latest reply on Jun 13, 2014 8:51 AM by Carl Von Stetten

    CF Hash and SQL Server

    thnguyen


      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>

        • 1. Re: CF Hash and SQL Server
          Carl Von Stetten Adobe Community Professional & MVP

          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.

          • 2. Re: CF Hash and SQL Server
            thnguyen Level 1

            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.

             

             

             

            • 3. Re: CF Hash and SQL Server
              Carl Von Stetten Adobe Community Professional & MVP

              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.

              • 4. Re: CF Hash and SQL Server
                thnguyen Level 1

                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!

                • 5. Re: CF Hash and SQL Server
                  Carl Von Stetten Adobe Community Professional & MVP

                  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.