Topic: encrypted strings fail in SQL WHERE clause

BriaN    -- 02-12-2004 @ 10:49 AM

I encountered easycfm while trying to track down a solution to the problem I was having.  I am using the encrypt function (on a CF5 test server with an access 97 database.) to store passwords and I got to the point where they would go in and out of the database no problem. I tested my login routine, and it worked, Great! but then...  When I tried to loging under a different userid/password combo, it inexplicably failed. Frown  

So I sallied forth to Google which led me, eventually, to, joy of joys, a tutorial on this site which covered EXACTLY my situation.  I eagerly read it, knowing salvation was at hand.  Unfortunately, I was already doing everything it suggested except using CFPARAM (I test for semi-colons, etc. already. I don't like ugly error pages, just dump-em back to the login routine and let 'em wonder, right?)  So I tried CFPARAM to no avail. I also tried, rather hopelessly, things like different kinds of quotes and using preservesinglequotes, but I didn't expect much.

I was using the encrypted password in a WHERE clause to find the login/password combo in one step, and it seems to fail on those passwords which, when you display the encrypted values on the screen in the browser while debugging, contain single quotes.  They must be getting escaped on the way in, and unescaped on the way out by coldfusion in normal inserts/querys because the passwords go in and out and decrypt just fine.  My workaround was to find the username and just compare the passwords in a <CFIF> clause.

I would like to know if anybody knows what is happening to me?  Is it CF5s fault? Access 97s fault? Is there something wrong with using the results of the encrypt function in a WHERE clause? Should I "manually" escape the single quotes?

BriaN    -- 02-12-2004 @ 11:55 AM
  Ok, I did some more checking and it just got weirder.

I dumped the ascii values of the encrypted string from the database as returned by cfquery, and the results of the encrypt function using the same pword & key.  Then I compared the ascii of the decrypted string from the database to the raw string I was feeding to the encrypt function, look what I got:

Encrypted value from Database:
37 53 40 62 45 54 69 40 35 10
37 53 40 62 45 54 69 41 32 10
value from encrypt function:

Decrypted value from Database:
109 97 114 103 101
109 97 114 103 101
Raw value:

so the encrypted data is different, but it decrypts to the same thing. Is this because cfencrypt UUencodes (base64) the result and somehow the unencoding comes to the same binary value, so the decrypt function is really operating on the same value? or is it possible the the decrypt function will return the same value for multiple inputs?  Ack! What is happening here?

MartinLadner    -- 08-13-2004 @ 6:28 PM
  1.  Consider doing this to create an encrypted string for storage...
encrypt(OriginalString,"{encryption key}")

This way, the string you're saving is "safe".

2.  To retrieve the original string, do this...
StringForStorage),"{encryption key}")

3.  Complementary methods 1 and 2 above may solve your single quote problem as well; because, a string that's safe to use in a URL is safe to use in a database.  See for an example.  =Marty=

Newton    -- 08-29-2004 @ 8:04 PM
  I came across this same issue recently so im bumping an older post

I found out alot of my ecrypted strings would end up with a backslash (\) when a backslash was inputted into the database (mysql) it would drop the backslash and since the slash was gone the decrypted value changed

To over come this I just used hash() and check against it and have had no problems at all since. I used it for password encryption.

Think of Me long enough to make a memory ~ NightWish

Webmaster    -- 08-29-2004 @ 8:39 PM
  When dealing with passwords why not just HASH() it?

You can insert a password into the database encrypted with the ONE way HASH() function.

Then when login in you do:

<cfquery ...>
  FROM Users
  WHERE LTrim(RTrim(userName)) = '#FORM.txtUserName#'
     AND LTrim(RTrim(userPass)) = '#Hash(FORM.txtUserPass)#'

Notice I also LTrim() and RTrim() it to ensure no preceeding and trailing spaces being returned from the DB.

Try that and let me know if it works for you!

Pablo Varando
Team Macromedia Member

EasyCFM.COM ColdFusion Forums :