Coldfusion ENCRYPT and MySQL AES_DECRYPT Working together?

I am using ColdFusion 9, and MySQL 5.1. I am trying to align the ColdFusion encrypt/decrypt functions and mySQL AES_ENCRYPT/AES_DECRYPT so I can开发者_运维知识库 use them interchangeably depending on the situation. Not having much luck with that.

First I created an AES string with ColdFusion:

    <cfset theKey = generateSecretKey("AES") />
    <cfoutput>#theKey#</cfoutput>

Example key: 4OFWUiuqFEkGrSRFm8sLlg==

I use this key to encrypt with MySQL. Note, encrypt_test is an existing table, and fld is a varchar column.

    INSERT INTO encrypt_test 
    SET fld = aes_encrypt('the text to encrypt', '4OFWUiuqFEkGrSRFm8sLlg==')

Next I try to decrypt with ColdFusion:

    <cfset theKey = "4OFWUiuqFEkGrSRFm8sLlg=="
    <cfset theAlgorithm  = "AES" />

Then run a cfquery to get the data (Only 1 record in the table),

    <cfquery name="testDecrypt">
        SELECT fld FROM encrypt_test
    </cfquery`

And finally decrypt

    <cfoutput>#Decrypt(testDecrypt.fld, theKey, theAlgorithm)#</cfoutput>

This results in a Null. I suspect its a padding issue or some other mismatch, anyone have an idea what I am doing wrong, or how to make this work?

I know this thread is old, but the answer came up on a recent thread. So I am posting it for posterity. As explained in this blog entry, the reason for the difference is:

.. the MySQL algorithm just or’s the bytes of a given passphrase against the previous bytes if the password is longer than 16 chars and just leaves them 0 when the password is shorter than 16 chars.

So you need to perform the same manipulations on the key value, before passing it into encrypt/decrypt.

I would stick with just using CF's functions. That way you can add all kinds of layers of security processes, to include things like iterations and multiple keys, to build a custom solution with ease. THe amount of overhead it adds is not much at all for that as well.

Why don't you use ColdFusion's encrypt function instead of MySQL's?

In fact that would be one way to test where the problem might lie : try outputting both the encrypted value from your database and what CF's encrypt function would produce and see if they're identical.

Alternatively just use the aes_decrypt function in your query instead of using ColdFusion's decrypt.

Hmmm, from the docs:

Because AES is a block-level algorithm, padding is used to encode uneven length strings and so the result string length may be calculated using this formula:

16 * (trunc(string_length / 16) + 1)

If AES_DECRYPT() detects invalid data or incorrect padding, it returns NULL.

So assuming CFML doesn't do that padding, you'd have to figure out the reverse of this yourself or something.

I know it's quite an old post but here is what you should do:

Before storing into the DB:

<cfset crypt_fld = #encrypt('the text to encrypt', thekey, 'AES')#>

Then:

INSERT INTO encrypt_test 
SET fld = crypt_fld

It worked for me

Use jBCrypt :: bCrypt is the strongest encryption available ... with the assistance of Mark Mandel's Fantastic JavaLoader implementing jBCrypt is a snap in ColdFusion ...

As far as the password field it really doesn't matter what kind of database you're using ... the field could be varchar(60) or nvarchar(60) if you're dealing with locale support too...

<cfcomponent title="bcrypt (strong; recommended)" hint="I encode passwords using a popular secure password hashing algorithm called bcrypt. I am very slow, but that makes me very secure!" extends="PasswordHash"
alias="bcrypt" seq="9001" workFactor="10">

<cfset variables.loadPaths = [expandPath( "/PATHTOLIBDIR/lib/jbcrypt/jbcrypt-0.3m.jar" )]/>

<cffunction name="init" access="public" output="true" returntype="any" hint="constructor">

    <cfset super.init( )/>

    <!--- Allow java loader to fail silently: we can report the failure via isAvailable() --->
    <cftry>
        <cfset variables.oBCryptClass = createJavaClass( "org.mindrot.jbcrypt.BCrypt" )/>
        <cfcatch></cfcatch>
    </cftry>

    <cfreturn this/>
</cffunction>

<cffunction name="isAvailable" hint="Is the hashing agorithm available in this environment?" access="public" returntype="boolean">
    <cfreturn structKeyExists( variables, "oBCryptClass" )/>
</cffunction>

<cffunction name="matchesHashFormat" hint="Does the string match the format for this hash?" access="public" returntype="boolean">
    <cfargument name="input" type="string" hint="String that may be a password hash" required="true"/>

    <cfreturn REFind( "^\$2a\$\d+\$[\./A-Za-z0-9]+$", arguments.input )/>
</cffunction>

<cffunction name="encode" hint="Convert a clear password to its encoded value" access="public" returntype="string">
    <cfargument name="password" type="string" hint="Input password" required="true"/>

    <cfset var salt = variables.oBCryptClass.gensalt( JavaCast( "int", this.workFactor ) )/>
    <cfreturn variables.oBCryptClass.hashpw( arguments.password, salt )/>
</cffunction>

<cffunction name="getHashWorkFactor" hint="Retrieve the work factor from a hashed string" access="public" returntype="numeric">
    <cfargument name="hashedPassword" type="string" hint="Previously encoded password string" required="true"/>

    <cfset var stMatch = ReFind( "^\$2a\$(\d+)\$([\./A-Za-z0-9]+)$", arguments.hashedPassword, 1, "true" )/>
    <cfif stMatch.pos[1] eq 0>
        <cfreturn 0>
        <cfelse>
        <cfreturn mid( arguments.hashedPassword, stMatch.pos[2], stMatch.len[2] )>
    </cfif>
</cffunction>

<cffunction name="passwordMatch" hint="Compare a plain password against an encoded string" access="public" returntype="boolean">
    <cfargument name="password" type="string" hint="Input password" required="true"/>
    <cfargument name="hashedPassword" type="string" hint="Previously encoded password string" required="true"/>
    <cfargument name="bCheckHashStrength" type="boolean" default="false" hint="If true, the hash strength of the hashed password must also match those generated by encode()"/>

    <cfset var bMatch = variables.oBCryptClass.checkpw( arguments.password, arguments.hashedPassword )/>

    <cfif bMatch and bCheckHashStrength>
        <!--- Hash matched but we also need to match the bCrypt work factor --->
        <cfreturn getHashWorkFactor( arguments.hashedPassword ) eq this.workFactor/>
        <cfelse>
        <cfreturn bMatch/>
    </cfif>
</cffunction>

The PasswordHash.cfc ...

<cfcomponent hint="I am an abstract component for encoding passwords for storage and comparing passwords against previously encoded strings">

<!--- Array of Java class paths required for this component. Leave empty if no special Java libraries are needed. --->
<cfset variables.loadPaths = []/>

<cffunction name="init" access="public" output="true" returntype="any" hint="constructor">

    <cfset var stMetadata = getMetadata( this )/>
    <cfset var attr = ""/>

    <cfloop condition="not structisempty(stMetadata)">
        <!--- Get attributes --->
        <cfloop collection="#stMetadata#" item="attr">
            <cfif issimplevalue( stMetadata[attr] ) and not listcontains( "bindingname,extends,fullname,functions,hint,name,namespace,output,path,porttypename,serviceportname,style,type,wsdlfile", attr ) and not structkeyexists( this, attr )>
                <cfset this[attr] = stMetadata[attr]/>
            </cfif>
        </cfloop>

        <!--- Do the same for ancestors --->
        <cfif structkeyexists( stMetadata, "extends" )>
            <cfset stMetadata = stMetadata.extends/>
            <cfelse>
            <cfset stMetadata = structnew( )/>
        </cfif>
    </cfloop>

    <cfset stMetadata = getMetadata( this )/>

    <!--- If key isn't specified, use the name of the component --->
    <cfif not structkeyexists( this, "alias" )>
        <cfset this.alias = listlast( stMetadata.name, "." )/>
    </cfif>

    <!--- If title isn't specified, use the displayname --->
    <cfif not structkeyexists( this, "title" )>
        <cfset this.title = this.displayname/>
    </cfif>

    <!--- If seq isn't specified, use 9999 --->
    <cfif not structkeyexists( this, "seq" )>
        <cfset this.seq = 9999/>
    </cfif>

    <cfreturn this/>
</cffunction>

<cffunction name="isAvailable" hint="Is the hashing agorithm available in this environment?" access="public" returntype="boolean">
    <cfreturn true/>
</cffunction>

<cffunction name="matchesHashFormat" hint="Does the string match the format for this hash?" access="public" returntype="boolean">
    <cfargument name="input" type="string" required="true" hint="String that may be an encoding of a password"/>

    <cfthrow message="The #this.alias# password encoding needs to implement the matchesHashFormat function"/>
    <cfreturn ""/>
</cffunction>

<cffunction name="encode" hint="Convert a clear password to its encoded value" access="public" returntype="string">
    <cfargument name="password" type="string" required="true" hint="Input password"/>

    <cfthrow message="The #this.alias# password encoding needs to implement the encode function"/>
    <cfreturn ""/>
</cffunction>

<cffunction name="passwordMatch" hint="Compare a plain password against an encoded string" access="public" returntype="boolean">
    <cfargument name="password" type="string" required="true" hint="Input password"/>
    <cfargument name="hashedPassword" type="string" required="true" hint="Previously encoded password string"/>
    <cfargument name="bCheckHashStrength" type="string" default="false" hint="If true, the hash strength of the hashed password must also match those generated by encode()"/>

    <cfthrow message="The #this.alias# password encoding needs to implement the passwordMatch function"/>
    <cfreturn false/>
</cffunction>

<!--- Private Java library helper functions --->

<cffunction access="private" name="getJavaLoader" returntype="any" output="false">

    <!--- Lazy-loading the JavaLoader makes it easier for plugins/projects to add custom crypto libraries --->
    <cfif not structKeyExists( variables, "loader" )>
        <cfset variables.loader = createObject( "component", "PATH.TO.JavaLoader" ).init( variables.loadPaths )/>
    </cfif>
    <cfreturn variables.loader/>
</cffunction>

<cffunction access="private" name="createJavaClass" returntype="any" output="false" hint="Return a java class from the crypto libraries">
    <cfargument name="className" type="string" required="true"/>

    <cfreturn getJavaLoader( ).create( arguments.className )/>
</cffunction>

... yada yada ... more code ...