Your encryption/decryption needs to happen entirely in the database
layer for this to work. Because you didn't say what database you are
- 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).
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.
WHERE name = '#encrypt(form.name,key)#'
Don't decrypt the sql server value, encrypt the coldfusion one and compare encrypted to encrypted -
unfortunately this will not work with a LIKE.
Why wouldn't it work with a like?
Because the MS SQL table has an encrypted value that looks like this:
Which when decrypted is really: Oklahoma City
When I use the LIKE command SQL sees this:
WHERE [ag49glvme39@4$9 0<;323290] LIKE 'Oklahoma'
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?
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.
Making some headway here.
Here is what I have so far.
<cfquery name="cust" datasource="#DSN#" username="#USER#" password="#PASS#">
<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#)>
[Didn't think you would have to loop the QuerySetCell's, but you do
<cfquery dbtype="query" name="decust">
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
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.
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?
1 person found this helpful
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
Try trimming your search variable - spaces may be getting through
Great. This problem is solved.
I just put a lcase() around the decrypt in the QuerySetCell.
Thanks for everyones help.