CODEWITHSUNDEEP
sqlhashparameterscoldfusioncoldfusion-8
Ryan Covert
Ryan Covert

Reputation: 41

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

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>.

Upvotes: 3

Views: 180

Answers (1)

James A Mohler
James A Mohler

Reputation: 11120

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

Upvotes: 1

Related Questions