11 Replies Latest reply on May 26, 2009 2:15 PM by cwmcguire

    Compare Encrypted String Using SQL

    cwmcguire Level 1

      I have some data encrypted in SQL.

       

      I want to use the LIKE command in SQL to compare a submitted form value to an encrypted value in the table.

       

      How can I do this?

       

      <cfquery>

      SELECT *

      FROM table

      WHERE decrypt(name,key) = '#form.name#'

      </cfquery>

       

      Of course the decrpyt does not work in the example above as it gives an error. But how can I achieve the results without error?

       

      Thanks.

       

      Chuck

        • 1. Re: Compare Encrypted String Using SQL
          mack_ Level 3

          Your encryption/decryption needs to happen entirely in the database

          layer for this to work. Because you didn't say what database you are

          using:

          - as far as I know MS SQL doesn't have encryption functions;

          - mysql can encrypt using a DES algorithm (maybe others also, you'll

          need to check the manual).

           

          Mack

          • 2. Re: Compare Encrypted String Using SQL
            GrumpyJoe Level 1

            How is the data in your SQL server encrypted?  Did Coldfusion do it before insert or is the server doing it?

             

            If it is Coldfusion, then you just need to reverse the logic in your SQL.

             

            <cfquery>

            SELECT *

            FROM table

            WHERE name = '#encrypt(form.name,key)#'

            </cfquery>

             

            Don't decrypt the sql server value, encrypt the coldfusion one and compare encrypted to encrypted -

            unfortunately this will not work with a LIKE.

            • 3. Re: Compare Encrypted String Using SQL
              Dan Bracuk Level 5

              Why wouldn't it work with a like?

              • 4. Re: Compare Encrypted String Using SQL
                cwmcguire Level 1

                Because the MS SQL table has an encrypted value that looks like this:

                 

                ag49glvme39@4$9 0<;323290

                 

                Which when decrypted is really: Oklahoma City

                 

                When I use the LIKE command SQL sees this:

                 

                <cfquery>

                SELECT *

                FROM table

                WHERE [ag49glvme39@4$9 0<;323290] LIKE 'Oklahoma'

                </cfquery>

                 

                This would come back zero results.

                 

                The string has to be decrypted during SQL for it to compare apples to apples. If I did a query and replaced Oklahoma with "ag49glvme", it would bring back a result.

                 

                So is there no way to decrypt this on the fly so that I can get the results I need?

                 

                Chuck

                • 5. Re: Compare Encrypted String Using SQL
                  mack_ Level 3

                  Because MS SQL doesn't have encryption function (at least last time I

                  checked) you'll not be able to do what you want. I think you'll need

                  to get all the results, decrypt each string in ColdFusion into a new

                  column in the same query (QuerySetCell) and then use QoQ on the query

                  with the decrypted string.

                   

                  Mack

                  • 6. Re: Compare Encrypted String Using SQL
                    GrumpyJoe Level 1

                    Seconded.

                    • 7. Re: Compare Encrypted String Using SQL
                      cwmcguire Level 1

                      Making some headway here.

                       

                      Here is what I have so far.

                       

                      <cfquery name="cust" datasource="#DSN#" username="#USER#" password="#PASS#">
                      SELECT *
                      FROM customer
                      </cfquery>


                      <CFLOOP query="cust">
                      <CFSET temp = QuerySetCell(cust,"decust_co","#Decrypt(cust.cust_co,variable.ekey)#",#cust.currentrow#)>
                      <CFSET temp = QuerySetCell(cust,"decust_last","#Decrypt(cust.cust_last,variable.ekey)#",#cust.currentro w#)>
                      <CFSET temp = QuerySetCell(cust,"decust_first","#Decrypt(cust.cust_first,variable.ekey)#",#cust.current row#)>
                      </CFLOOP>

                       

                      [Didn't think you would have to loop the QuerySetCell's, but you do


                      <cfquery dbtype="query" name="decust">
                      SELECT *
                      FROM cust
                      WHERE custid > 0
                      AND (decust_co LIKE '%#search#%'
                      OR decust_last LIKE '%#search#%'
                      OR decust_first LIKE '%#search#%'
                      OR acct_no LIKE '%#search#%')

                      ORDER BY decust_co
                      </cfquery>

                       

                      OK...the part that is not working is my wildcard seach values. When I send a search variable of a single letter - C, M, H, etc...it will bring back results. If I send a NULL search variable - it will bring back all results...when I send a search variable with more than two characters - zero results. I am using two characters like "Ch" and one of the values is "Chuck". It should bring back a result. This query worked fine, prior to encrypting this data. I have verified the data in the QuerySetCell variables above are populating and decrypting correctly when I dumped the query.

                       

                      Any idea?

                      • 8. Re: Compare Encrypted String Using SQL
                        cwmcguire Level 1

                        OK...strange.

                         

                        The search string is CASE-SENSITIVE. I type in "Ch" and it brings results back. I type "ch" - no results.

                         

                        Why is it case sensitive when using the QuerySetCell command?

                         

                        Not using the QuerySetCell command, this would not be case sensitive.

                         

                        Anyone know how to bypass this problem?

                         

                        Chuck

                        • 9. Re: Compare Encrypted String Using SQL
                          -==cfSearching==- Level 4

                          The search string is CASE-SENSITIVE


                           

                          QoQ are case sensitive. The usual solution is to convert both values to the same case.  See the upper and lower functions in documentation

                           

                          http://livedocs.adobe.com/coldfusion/8/htmldocs/using_recordsets_7.html

                          1 person found this helpful
                          • 10. Re: Compare Encrypted String Using SQL
                            GrumpyJoe Level 1

                            Try trimming your search variable - spaces may be getting through

                            somehow?

                             

                            %#Trim(Search)#%

                            • 11. Re: Compare Encrypted String Using SQL
                              cwmcguire Level 1

                              Great. This problem is solved.

                               

                              I just put a lcase() around the decrypt in the QuerySetCell.

                               

                              Thanks for everyones help.

                               

                              Chuck