ColdFusion: cfqueryparam for binary values

Question

I wrote the below query based on the help provided in this link, querying binary column using like in sql server

SELECT * FROM myTable
WHERE TestData >= 0x00010000 
  AND TestData < 0x00020000;

It returned the expected results. I used cfqueryparam and updated the query as:

SELECT * FROM myTable
WHERE TestData >= <cfqueryparam value="0x00010000" cfsqltype="cf_sql_binary"> 
  AND TestData < <cfqueryparam value="0x00020000" cfsqltype="cf_sql_binary">;

but it returned with errors, Error Message: Invalid data 0x00010000 for CFSQLTYPE CF_SQL_BINARY. I tried with cfsqltype="CF_SQL_BLOB" but no results. How to fix this issue? Thanks in advance

Answer 1

As it stands, there's nothing inherently wrong with keeping the query as:

SELECT * FROM myTable
WHERE TestData >= 0x00010000 AND TestData < 0x00020000

(You should ideally be listing individual columns rather than using * though.)

However, whilst there is no security benefit to parameterising these queries (they have no variables and thus are not prone to SQL injection), there may still be a benefit of having parameterised SQL for the purpose of caching a single execution plan.

If you have multiple queries, of the form:

<cfquery...>
    SELECT * FROM myTable
    WHERE TestData >= 0x00010000 AND TestData < 0x00020000
</cfquery>

<cfquery...>
    SELECT * FROM myTable
    WHERE TestData >= 0x00020000 AND TestData < 0x00030000
</cfquery>

<cfquery...>
    SELECT * FROM myTable
    WHERE TestData >= 0x00030000 AND TestData < 0x00040000
</cfquery>

Using cfqueryparam for these would allow a single execution plan to be cached for the multiple queries, potentially leading to better performance.

In this situation, you need to use BinaryDecode to convert your hex string into a binary value that cfqueryparam can handle, like so:

<cfquery...>
    SELECT * FROM myTable
    WHERE TestData >= <cfqueryparam value=#BinaryDecode('00010000','Hex')# cfsqltype="cf_sql_binary" />
    AND   TestData <  <cfqueryparam value=#BinaryDecode('00020000','Hex')# cfsqltype="cf_sql_binary" />
</cfquery>

(Note that the 0x prefix is ommitted.)