cfqueryparam with like operator in coldfusion

Question

I have been tasked with going through a number of ColdFusion sites that have recently been the subject of a rather nasty SQL Injection attack. Basically my work involves adding <cfqueryparam> tags to all of the inline sql. For the most part I've got it down, but can anybody tell me how to use cfqueryparam with the LIKE operator?

If my query looks like this:

select * from Foo where name like '%Bob%'

what should my <cfqueryparam> tag look like?

Answer 1

select * from Foo where name like '%' + <cfqueryPARAM... > + '%'

Answer 2

@Joel, I have to disagree.

select a,b,c
from Foo
where name like <cfqueryparam cfsqltype="columnType" value="%#variables.someName#%" />

1) Never suggest to someone that they should "select star." Bad form! Even for an example! (Even copied from the question!)

2) The query is pre-compiled and you should include the wild card character(s) as part of the parameter being passed to the query. This format is more readable and will run more efficiently.

3) When doing string concatenation, use the ampersand operator (&), not the plus sign. Technically, in most cases, plus will work just fine... until you throw a NumberFormat() in the middle of the string and start wondering why you're being told that you're not passing a valid number when you've checked and you are.

Answer 3

I can't vote yet but would lean towards Adam's answer.