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, Apr 26 '13 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, Apr 26 '13 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, Apr 26 '13 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 — David Mulder, Apr 26 '13 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, Apr 26 '13 at 21:54
The counter for bad programmers is human code reviews of _every_ line of code merged into trunk (by a developer not involved with writing it), combined with automated testing using a tool such as QueryParam Scanner (https://github.com/boughtonp/qpscanner), and ideally followed up with regular penetration testing. (And of course educating the relevant programmers once the mistakes are found, so they can learn and improve.) — Peter Boughton, Apr 26 '13 at 21:55
@PeterBoughton Yeah you're right, if it's truly static than you don't need a cfqueryparam, however `static !== not dynamically provided by a third party`, as a value provided from inside your own application as for example an argument to the function is by your definition static which it obviously is not, even if that function is always called right now with a hard coded arguments. So not using cfqueryparam's with the above mentioned definition of static is still lazy. Though maybe you just misdefined static, because the rest sounds very reasonable :D (and please use the @user syntax) — David Mulder, Apr 29 '13 at 08:01
The contents of the parentheses were clarification, not definition. I'd go make that more obvious, but SO doesn't let old comments be edited. — Peter Boughton, Apr 29 '13 at 10:22

score 3 · Answer 1 · answered Apr 26 '13 at 22:06

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

ColdFusion: cfqueryparam for binary values

1 Answers1