Compare Encrypted String Using SQL

[Guest]

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

[mack_]

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

[Guest]

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.

[Dan_Bracuk]

Why wouldn't it work with a like?

[Guest]

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

[mack_ · Accepted solution]

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

[Guest]

Seconded.

[Guest]

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?

[Guest]

Try trimming your search variable - spaces may be getting through

somehow?

%#Trim(Search)#%

[Guest]

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

[-__cfSearching__-]

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

[Guest]

Great. This problem is solved.

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

Thanks for everyones help.

Chuck