Compare Encrypted String Using SQL

Great. This problem is solved.

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

Thanks for everyones help.

Chuck

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

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.currentrow#)>
<CFSET temp = QuerySetCell(cust,"decust_first","#Decrypt(cust.cust_first,variable.ekey)#",#cust.currentrow#)>
</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?

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

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.

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