ListQualify issue

Ooops - wrong syntax.. it should be


<cfif attributes.background NEQ "">
AND background IN (<cfqueryparam cfsqltype="cf_sql_char"
value="#attributes.background #" list="yes">)
</cfif>

And yes- it does work just fine with just one item in the list.

thanks again everyone!!!

Thanks for all the great responses - I didn't realize you could use the
list type with cfqueryparam - I use that in all my other vars (ints,
varchars etc. ) Thanks for that. Now I can simplify my code even more.
When I figured the workaround my code ended up like this just for one
"and" segment...

<cfif attributes.background NEQ "">
<cfif listLen(attributes.background ) eq 1>
AND background = <cfqueryparam cfsqltype="cf_sql_char"
value="#attributes.background #">
<cfelse>
AND background IN (#ListQualify(attributes.background ,"'")#)
</cfif>
</cfif>

It sounds like I can just do this...

<cfif attributes.background NEQ "">
AND background = <cfqueryparam cfsqltype="cf_sql_char"
value="#attributes.background #" list="yes">
</cfif>

Will it work with only one item in the list? I will find out in a
moment :)

Thanks again all....

Chris

Ian Skinner wrote: ...
BKBK wrote: ..
JR "Bob" Dobbs wrote: ..

Whoops. This is what happens when you get called away in the middle of a response and do not hit refresh ;-)

Does this do it for you?

<cfparam name="URL.Background" default="black,blue,red">

<cfset URL.Background = ListQualify(URL.Background,"'",",","CHAR")>

<cfquery name="q" datasource="myDSN">
SELECT ID
FROM table
WHERE background IN (#preserveSingleQuotes(URL.background)#)
</cfquery>

Chris Luksha (Echowebs) wrote:
> So why would it work directly in the select statement - but not if you
> PRE qualified it?
>
> Anyone wanna venture a guess?
>

NOPE! But I'll try my best to explain the reason.

Unless you tell it otherwise with a preserveSingleQuotes() function,
ColdFusion automatically escapes single quotes by doubling them in any
variable used inside a <cfquery...> block. This is so if you have a
variable that contains a string like O'Mally it will be properly escaped
to O''Mally so that it does not cause a database error in your SQL code.

When you pre-qualified your list to something like 'one','two','three'
and then passed it into the <cfquery...> block the quotes where escaped
to ''one'',''two'',''three'' which was passed to the database. Of
course, this does not work.

By doing the function inside the <cfquery...> block, ColdFusion knows
not to escape these quotes, that they are supposed to be singles.
preserveSingleQuotes(myPreQualifiedList) would have also worked. But
this is much more dangerous since it by passes the natural quote
escaping in such a manner that it makes SQL injection much easier to
pull off.

But best of all would be using code such as this:

<cfqueryparam value="#myList#" list="yes" cfsqltype="cf_sql_varchar">
<!--- make sure the 'cfsqltype' parameter matches the actual database
type of the column --->

So I went diggin' - some more and found an example of someone else
using listqualify directly in the IN qualifier - so I tried it and low
an dbehold this works.....


AND background IN (#ListQualify(URL.Background,"'")#)</cfif>

So why would it work directly in the select statement - but not if you
PRE qualified it?

Anyone wanna venture a guess?


thanks,
chris


Chris Luksha (Echowebs) wrote:
> The situation is this ...
>
> I have a 'get' form that has many 'multiple select' fields. Some of the
> fields contain numeric values and some text values.
>
> So when I am searching on the returned values - I can easily use the
> following syntax..
>
> SELECT ID FROM table WHERE style IN(#URL.style#) When url.style=1,2,3,4
>
> However If I use
>
> SELECT ID FROM table WHERE background IN(#URL.background#) When
> URL.background = black,blue,red
>
> Of course it will not work.
>
> So I tried using the following
> <cfset URL.Background = ListQualify(URL.Background,"'",",","CHAR")>
>
> Now if I dump URL.background - I get 'black','blue','red'
>
> This is what I want. However if I then use the above SQL statement - I
> get a cf error stating Error Executing Database Query and the sample
> query reads like this...
>
> SELECT ID FROM table WHERE background IN (''Black'',''Blue'')
>
> NOW I know that isn't right - but where did the extra single quotes come
> from?
>
> I don't know the semantics of it all - but when I submit the form - the
> url reads like this...
>
> someurl/file.cfm?Background=Black&Background=Blue I don't know if that
> would shed some light.
>
> Any ideas - anyone?
>
> Thanks!
> Chris
>