Why does the ColdFusion hash function return different results in these scenarios?

Question

This is the first time I've noticed this behaviour from ColdFusion. I came across this while updating a query to use <cfqueryparam>. The code runs on a ColdFusion 8 platform. I do not have reference for its behaviour outside of this version.

Additionally, we normally would use <cfqueryparam> for the inserted values, which is what prompted investigation into this problem.

For this example, we will assume that "first_string" and "second_string" contain some text, such as a title and some paragraph text with newlines from a <textarea> form post respectively.

We can also assume that the SQL data type for the "md5_hashed_value" column is char(32).

Scenario 1:

<cfset hashed_value = hash(first_string & second_string, "MD5")>
<cfquery name="my_query" datasource="#my_datasource_name#">
    INSERT INTO my_table
    (
        md5_hashed_value
    )
    VALUES
    (
        '#hashed_value#'
    )
</cfquery>

In this first scenario above, a hashed value is created.

Scenario 2:

<cfquery name="my_query" datasource="#my_datasource_name#">
    INSERT INTO my_table
    (
        md5_hashed_value
    )
    VALUES
    (
        '#hash(first_string & second_string, "MD5")#'
    )
</cfquery>

In this second scenario, the hashed value calculated is DIFFERENT from the first case.

Scenario 3

<cfquery name="my_query" datasource="#my_datasource_name#">
    INSERT INTO my_table
    (
        md5_hashed_value
    )
    VALUES
    (
        <cfqueryparam value="#hash(first_string & second_string, "MD5")#" CFSQLType="CF_SQL_CHAR">
    )
</cfquery>

In this third scenario, the value inserted into the database is identical to the value inserted in the first scenario.

Why is this happening? The hash() function is being called in the same way in all scenarios. Its location in the code is the only difference. There appears to be something strange happening when hash() is called directly inside a SQL query instead of via a <cfqueryparam> value or a <cfset>.

Do either one of the strings have whitespace or any special characters? — James A Mohler, Nov 22 '19 at 19:07
@JamesAMohler It's running on ColdFusion 8. There is definitely white space in the strings and it is possible there are special characters contained within as well. — Ryan Covert, Nov 22 '19 at 19:56
Do the hashes differ, when the input does not come from a textbox but from a constant string? — Bernhard Döbler, Nov 22 '19 at 21:27
@BernhardDöbler The origin of the text does not appear to have any effect on the results. I threw the original text values into `` during tests with the same results. — Ryan Covert, Nov 28 '19 at 17:11

score 1 · Answer 1 · answered Nov 22 '19 at 21:16

Here is my half baked guess.

<cfquery></cfquery> is not exactly the same as <cfsavecontent></cfsavecontent>. I don't think the & is doing a normal string concatination. So here is a test of my hypothesis. Try these:

<cfset hashed_value = hash("" &first_string & second_string, "MD5")>
<cfquery name="my_query" datasource="#my_datasource_name#">
    INSERT INTO my_table
    (
        md5_hashed_value
    )
    VALUES
    (
        '#hashed_value#'
    )
</cfquery>

Does this do the same has as before? I suspect it does.

<cfquery name="my_query" datasource="#my_datasource_name#">
    INSERT INTO my_table
    (
        md5_hashed_value
    )
    VALUES
    (
        '#hash("" & first_string & second_string, "MD5")#'
    )
</cfquery>

Does this do the same has as before? I suspect it does not.

Workaround

<cfquery name="my_query" datasource="#my_datasource_name#">
    DECLARE @md5_hashed_value = '#hash(first_string & second_string, "MD5")#'

    INSERT INTO my_table
    (
        md5_hashed_value
    )
    VALUES
    (
        @md5_hashed_value
    )
</cfquery>

While this does not have all the benefits of <cfqueryparam>, at least it parameterized your query

Thank you for the suggestion on trying DECLARE. Unfortunately, no matter where the inline `hash()` exists in a string inside SQL statements, it ends up with a different value in ColdFusion. — Ryan Covert, Nov 28 '19 at 17:09
I have now tested this against the latest Lucee release and it **does not exhibit this same problem**. I've also attempted to specify UTF-16 as the character encoding for all calls to `hash()`, to no avail. Doing tests against temp tables with nvarchar as the column types should result in the same values regardless of the _database collation_, but perhaps the collation is the underlying problem. At this point, I'm prepared to update all locations of this code to utilize `` as originally intended and deal with any of the fallout. — Ryan Covert, Nov 28 '19 at 17:09
You might get some performance improvment with ``. Then again, you may want to consider, using `QueryExecute`. — James A Mohler, Nov 28 '19 at 21:15

Why does the ColdFusion hash function return different results in these scenarios?

1 Answers1