ListQualify issue

[Newsgroup_User]

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

[Newsgroup_User]

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
>

[JR__Bob__Dobbs-qSBHQ2]

Try using the list attribute of cfqueryparam.


SELECT ID
FROM table
WHERE background IN ( <cfqueryparam value="#URL.background#" cfsqltype="cf_sql_varchar" list="yes" /> )

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_18.html#1102474

[-__cfSearching__-]

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?

CF automatically escapes (ie doubles) single quotes found inside query variables as a security mechanism. So if you pass in a value containing single quotes, they will be doubled. In your first example URL.background does contain single quotes.

<cfset URL.Background = ListQualify(URL.Background,"'",",","CHAR")>
ie. URL.background = 'black', 'blue', 'red'

In your second example URL.Background does not contain single quotes.
URL.Background = black, blue, red.

Which explains why one works and the other does not. However, a simpler method is to just use cfqueryparam with the "list" attribute. Notice how you do not have to worry about quoting list values? CF automatically handles that for you.

<cfset URL.background = "black,blue,red">
...

WHERE ColumnName IN (
<cfqueryparam value="#url.background#" list="true" cfsqltype="cf_sql_varchar">
)

SELECT ID FROM table WHERE style IN(#URL.style#) When url.style=1,2,3,4
That may expose your database to sql injection risks. Consider using cfqueryparam with all of your query parameters.

[Newsgroup_User]

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

[BKBK]

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>

[-__cfSearching__-]

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 😉

[Newsgroup_User]

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

[Newsgroup_User]

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!!!