[SOLVED] How can I escape commas inside cfparam VARCHAR lists generated with ValueList?

How can I escape commas inside cfparam VARCHAR lists generated with ValueList?

Let's say there is a SQL table Fruit

id | name
--- ------
 1 | 'apples'
 2 | 'pears'
 3 | 'kiwi'
 4 | 'bananas, peaches and plumbs'

Given the following queries

<cfquery name="qAllFruit" datasource="#DSN#">
    SELECT name FROM Fruit
</cfquery>
<cfquery name="qLeftoverFruit" datasource="#DSN#">
    SELECT name FROM Fruit
     WHERE name NOT IN (<cfqueryparam CF_SQL_TYPE="CF_SQL_VARCHAR" 
                                      value="#ValueList(qAllFruit.name)#" 
                                      list="yes" />)
</cfquery>

then qLeftoverFruit will incorrectly return 1 row: bananas, peaches and plumbs because the expanded cfqueryparam list is interpreted incorrectly:

WHERE name NOT IN ('apples','pears','kiwi','bananas','peaches and plumbs')

Is there a way to correct this while still using the cfqueryparam tag and ValueList?

UPDATE Here's a gist you can use to recreate this issue: http://gist.github.com/a642878c96b82b21b52c

Solution

@Daniel Mendel, I think the problem is with the default separator used by ColdFusion. You have data that is having a ',' in it and the default separator in CF is ',' incidentally.

Change your query like this -

WHERE name NOT IN ( <cfqueryparam CFSQLType="CF_SQL_VARCHAR" 
                       value="#ValueList(qTags.tag,';' )#" 
                       list="Yes" separator=";" />
                  )

Just change the separator in valueList to ';' from default ',' and also set the QueryParam separator to ';'.