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

The error is probably because you're passing in a string, not a binary value. Try #BinaryDecode('00010000','Hex')# ? — Peter Boughton
– Peter Boughton, Commented Apr 26, 2013 at 11:22
Also, you might not need to use cfqueryparams here - if the values are static (not dynamically provided by a third party), and you don't have multiple queries with only the values changing (i.e. SQL identical except for binary values), then there's probably no benefit in doing it. — Peter Boughton
– Peter Boughton, Commented Apr 26, 2013 at 11:26
Thank you its working. you are right, for static values there is no benefit. But still our client wants that feature. Thanks once again. — RajVish
– RajVish, Commented Apr 26, 2013 at 11:41
@PeterBoughton NO!!! You should always pass in values through cfqueryparams, why? Simply because you never known how that code will be used one day. Might be it is one day exposed through a API or something, never ever assume it's ok to be lazy and write bad code, especially not when security is concerned. Same thing as never ever no matter what forgetting to entity encode data which is outputted to a browser. Fair enough, there are lots of bad programmers who forget that and that's why half the internet is susceptible for xss attacks, but that's still no exuse </rant> — David Mulder
– David Mulder, Commented Apr 26, 2013 at 19:18
David, if you feel like doing a search, you'll likely see hundreds of times on SO where I've reminded people to use cfqueryparam. You must be misunderstanding what I'm saying here because the above is not lazy or bad code in any way - a static value (and to be clear I'm talking about hard-coded values, not variables ) CANNOT be exposed to an API (without modifying the code, at which point cfqueryparams would be added). — Peter Boughton
– Peter Boughton, Commented Apr 26, 2013 at 21:54

Peter Boughton · Accepted Answer · 2013-04-26 22:06:29Z

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

Collectives™ on Stack Overflow

ColdFusion: cfqueryparam for binary values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest