coldfusion 10 and mysql - problems decrypting previously stored data

Question

I'm moving a site from CF8 to CF10 and one of the problems that has come up is retrieving data storied in mysql using aes_encrypt in mysql. The column is blob. When I access the CFC that retrieves the decrypted string I'm getting back a binary object with an error:

ByteArray objects cannot be converted to strings. 

On the CF8 system I could just output with:

<cfoutput>#qryResult.decryptedString#</cfoutput>

Under CF10, I have to wrap it in a toString()

<cfoutput>#toString(qryResult.decryptedString)#</cfoutput>

I don't know if this is a CF, MySQL or driver issue, and the best way to solve it, or if I need to take another step in converting this site over. In other words is "toString()" the correct solution or is there a database parameter that can be put into the connect string to keep things as they were?

Answer 1

I ran a few tests with ColdFusion 8 and 10 (against the same database) and got similar results. The decrypted result was returned as a String in ColdFusion 8 (and in MySQL), but as binary in ColdFusion 10. So I suspect it is a driver bug, or possibly a deliberate change (I am not sure).

Test Query

    <cfquery name="qTest" datasource="mySQL51">
        SELECT AES_DECRYPT(encryptedColumn, 'xxxxxxx') AS DecryptedResult
        FROM   test
        WHERE  ID = 1
    </cfquery>

    <cfoutput>
        class name = #qTest.decryptedResult[1].getClass().name#
    </cfoutput>

Results

    ColdFusion  8  / class name = java.lang.String 
    ColdFusion 10  / class name = [B   (ie binary/byte array)

---

Other than testing different drivers, the simplest solution is to convert the value to a string as you are doing now. You can either do the conversion in SQL:

   SELECT CAST(AES_DECRYPT(encryptedColumn, 'xxx') AS CHAR) AS DecryptedResult

.. or in CF code. Just be sure to specify an encoding like UTF-8

  <!--- if result is binary, convert it to a string first --->
  <cfif IsBinary( qTest.decryptedResult )>
      <cfset decryptedString = charsetEncode( qTest.decryptedResult, "utf-8" )>
  </cfif>