Coldfusion: authenticating hashed password

Question

I have a login authentication system, the passwords in the database are stored as SHA-384. The following login script does nothing, when I include the Hash function. Where am I going wrong?

I'm using MSSQL Server 2008 R2, Coldfusion 10.

loginform.cfm

<cfif IsDefined("FORM.email")>
  <cfset redirectLoginSuccess="admin.cfm">
  <cfset redirectLoginFailed="login.cfm">
  <cfquery  name="UserAuth" datasource="sql1007539">
  SELECT email,userPass FROM customers WHERE email=<cfqueryparam value="#FORM.email#" cfsqltype="cf_sql_clob" maxlength="255"> 
  AND userPass=<cfqueryparam value="#Hash(form.userPassword, "SHA-384")#" cfsqltype="cf_sql_clob" maxlength="255">
  </cfquery>
  <cfif UserAuth.RecordCount NEQ 0>
    <cftry>
      <cflock scope="Session" timeout="30" type="Exclusive">
        <cfset Session.Username=FORM.email>
        <cfset Session.UserAuth="">
      </cflock>
      <cfif IsDefined("URL.accessdenied") AND true>
        <cfset redirectLoginSuccess=URL.accessdenied>
      </cfif>
      <cflocation url="#redirectLoginSuccess#" addtoken="no">
      <cfcatch type="Lock">
      </cfcatch>
    </cftry>
  </cfif>
  <cflocation url="#redirectLoginFailed#" addtoken="no">
  <cfelse>
  <cfset LoginAction=CGI.SCRIPT_NAME>
  <cfif CGI.QUERY_STRING NEQ "">
    <cfset LoginAction=LoginAction & "?" & XMLFormat(CGI.QUERY_STRING)>
  </cfif>
</cfif>

Edit: The script works if no HASH functions are used. Edit: I can also confirm the passwords are stored in SHA-384. I checked using the following HASH identifier: duncanwinfrey.com/tools/hashid/hash.php

Edit 29/05/13

**Code returns error, when I remove the cfparam tag **

<cfquery  name="UserAuth" datasource="sql1007539">
  SELECT email,userPass FROM customers WHERE email="#FORM.email#" 
  AND userPass="#hash(form.userPassword, "sha-384")#"
  </cfquery>

Error returned

Answer 1

I managed to get it working with help from (agx) from the Experts Exchange forum. It turns out it was human error on my part. I had an extra space in my insert query of the registration process and also set the encoding to UTF-8:

'(space)#hash(form.password, "sha-384" ,'UTF-8')#'

I changed the password type to char(96), and amended the cfqueryparam, as suggested. Thank you all for your help and guidance. Below is troubleshooting code, I used to help me figure this out:

Entered an email and password of an existing entry, to grab record from the db:

<cfset form.email = "some known email">
<cfset form.userPassword = "real password before hashing">

<!--- ONLY match on email ---->
<cfquery name="qGetData" ....>
     SELECT  * 
     FROM    yourTable
     WHERE   email =<cfqueryparam value='#FORM.email#' 
                            cfsqltype="cf_sql_varchar">
</cfquery>

<!--- Checking to see if the password is hashed or is in clear text --->
<cfdump var="#qGetData#">

Hashed the clear text password and compared it to the db value.

<cfset newhash = hash(form.userPassword,'SHA-384')>
<cfif compare(newHash, qGetData.userPass) eq 0>
    SAME
<cfelse>
    DIFFERENT
</cfif>

At first sight the values looked the same. To make sure both the stored password in db and password from the login form were the same, the following code was used:

<cfoutput>
   db |#qGetData.userPass#|<br>
   form |#hash(form.userPassword,'SHA-384')#|<br>
</cfoutput>

I then used a handy website to compare the outputs. The results were the same again. After all this hard work, it turned there was an extra space in front of the #hash(...)#.

Answer 2

When using double quotes, the value is parsed as an object (table, columns, etc). Always stick with the cfqueryparam, which is secure and fast.

Try adding a third argument to the Hash function which forces a different encoding; eg:

<cfquery  name="UserAuth" datasource="sql1007539">
  SELECT email,userPass FROM customers WHERE email=<cfqueryparam value="#FORM.email#" cfsqltype="cf_sql_clob" maxlength="255"> 
  AND userPass=<cfqueryparam value="#Hash(form.userPassword, "SHA-384", "UTF-8")#" cfsqltype="cf_sql_clob" maxlength="255">
  </cfquery>

Note the UTF-8 argument. Common encodings are: ISO-8859-1, ISO-8859-11 (Latin9).

Gl !

Answer 3

I would go with the encoding issue. I believe CLOB/BLOB are typically Oracle or DB2 datatypes and not native to MS SQL Server. I don't think you can assign CLOB/BLOB as a datatype in SQL Server. When you're passing a cf_sql_clob into the cfqueryparam, it's using the JDBC driver to try to convert to text or varchar(max) when it talks back to SQL Server. Something may be getting lost in translation. Since you're connecting to a SQL Server, try passing the correct datatype to the cfqueryparam. Look at the properties of the database columns for email and userPass. You should be able to set the cfsqltype to something like cf_sql_char or cf_sql_varchar. I'm kind of surprised the query isn't throwing an error, but the error may be getting masked by the datatype conversions, and it's simply not returning any results.

http://help.adobe.com/en_US/ColdFusion/10.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f6f.html

http://msdn.microsoft.com/en-us/library/ms378813(v=sql.105).aspx

EDIT: Try changing your query to:

SELECT email,userPass FROM customers 
WHERE email = <cfqueryparam value="#FORM.email#" cfsqltype="cf_sql_varchar" maxlength="255"> 
AND userPass = <cfqueryparam value="#Hash(form.userPassword, "SHA-384")#" cfsqltype="cf_sql_varchar" maxlength="255">

Answer 4

Not sure if this is a typeo or this is your code directly copy and pasted:

You are effectively bombing your cfquery tag with the double quotes inside the hash function of the value attribute.

• You have this: value="#Hash(form.userPassword, "SHA-384")#"
• Replace it with this: value="#Hash(form.userPassword, 'SHA-384')#"

Please note the Single quotes around the SHA-384 value. This should fix your problem.