• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Compare Encrypted String Using SQL

Guest
May 25, 2009 May 25, 2009

Copy link to clipboard

Copied

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

TOPICS
Advanced techniques

Views

3.9K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Enthusiast , May 26, 2009 May 26, 2009

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

Votes

Translate

Translate
Enthusiast ,
May 25, 2009 May 25, 2009

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
May 26, 2009 May 26, 2009

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 26, 2009 May 26, 2009

Copy link to clipboard

Copied

Why wouldn't it work with a like?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
May 26, 2009 May 26, 2009

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
May 26, 2009 May 26, 2009

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
May 26, 2009 May 26, 2009

Copy link to clipboard

Copied

Seconded.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
May 26, 2009 May 26, 2009

Copy link to clipboard

Copied

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?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
May 26, 2009 May 26, 2009

Copy link to clipboard

Copied

Try trimming your search variable - spaces may be getting through

somehow?

%#Trim(Search)#%

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
May 26, 2009 May 26, 2009

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
May 26, 2009 May 26, 2009

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
May 26, 2009 May 26, 2009

Copy link to clipboard

Copied

LATEST

Great. This problem is solved.

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

Thanks for everyones help.

Chuck

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation