Encryption with escape chars - "\"

[Guest]

Hello,

I am encrypting a password to put into a mySQL db table.  It appears that it is stripping the character "\" thinking it is an escape character.

Encrypted entered PW:   0_ZGA68!,N4AE( T\@ ]*H            (This is the password encrypted in the login page)

Table password:             0_ZGA68!,N4AE( T@ ]*H             (I compare it to what is in the table - see the "\" is missing)

PW does not match what is in the table.

Is it possible for this to be the case, that it sees "\@" in the encrypted string and escapes it so it is just the "@" that ends up in the string that goes into the db table  and it can no longer match up to the PW being examined?

If so,  not fun...

Thanks for any advise/solutions,

Lee

[12Robots]

If you are inserting the password into the database proeprly (with cfqueryparam) then this should not happen. Can you show the code for hwo you are addign this to the DB?

[BKBK · Accepted solution]

As you yourself apparently realize, the backslash \ is a special character in MySQL, the escape character. You should therefore escape it.

You can do this, for example, by manually appending an extra \ to each \ yourself, like this

<cfset mySQL_escaped_PW = replace(encryptedPW,"\","\\","all")>

<cfquery>

insert into myTBL(pw)

values ('#mySQL_escaped_PW#')

</cfquery>

or by letting ColdFusion do it for you, as 12Robots has pointed out:

<cfquery>

insert into myTBL(pw)

values (<cfqueryparam cfsqltype="cf_sql_varchar" value="#encryptedPW#">)

</cfquery>

[Guest]

Ok, I was not using cfqeryparam.  I have added it to any queries.  It seemed to work fine.  It does not remove any escape characters; however, when I compare a particular password entered in the form field with the password in the DB, it says they do not match, but they do.

<cfquery name="findMember" datasource="#application.dsn#">

select * from boardmembers

where email = '#form.username#'

</cfquery>

<cfif findMember.password_actv eq 1>

         <cfset variables.enc_entered_password = #encrypt(form.password,findMember.pwrd_ky,"AES")#>

         <!--- If equal then go to portal page --->

          <cfif variables.enc_entered_password eq findMember.password>

          <!--- BINGO --->

             <cflocation url="index.cfm">

          <cfelse>

             <cfoutput>Encrypted entered PW:  ***#variables.enc_entered_password#***<br />

             Table password:  ***#findMember.password#***<br /></cfoutput>

             PW does not match what is in the table.  Go <a href="login.cfm">here</a> to try again.

             <cfset session.loginAttempts = session.loginAttempts + 1>

          </cfif>

Here is the output that should  match:  Why does this not match, they look the same to me.

crawfordL@kent-school.edu

Entered Password: 0123456789zxcvbnm,./

  Encrypted entered PW:       @O7 ]KCO]8=55>RX?76=TP\RKAIE+U].-=.44(XQ+*HP

Password in table:              @O7 ]KCO]8=55>RX?76=TP\RKAIE+U].-=.44(XQ+*HP

  PW does not match what is in the table.  Go here to try again.

It seems to be this particular PW with a period in it.  I try other special characters and it does not matter.Is there something else I should be doing or looking for?

Thanks for your answers.  Getting further along. 

Lee

[12Robots]

Again, you should be using cfqueryparam. Even though this likely has nothing to do with your problem.

<cfquery name="findMember" datasource="#application.dsn#">

select * from boardmembers

where email = <cfqueryparam value="#form.username#" cfsqltype="cf_sql_varchar" />

</cfquery>

As for the problem you are having, try removign white space.

<cfif trim(variables.enc_entered_password) eq trim(findMember.password)>

[Guest]

Thanks.  White space seemed to be the problem.

I thought cfqueryparam was only needed when inserting/updating data in the DB table but it apparently is for any query.

Thanks again.