Reputation: 10003
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
Upvotes: 3
Views: 1468
Reputation: 2287
There are two examples provided with this question. Take a step back from CF for a bit and consider doing this work in one query by itself. For the query in the question:
<cfquery name="qLeftoverFruit" datasource="#DSN#">
SELECT name FROM Fruit
WHERE name NOT IN (SELECT name FROM Fruit)
</cfquery>
Using the example from github.com:
<cfquery name="qTest" dbtype="query">
SELECT id FROM qTags
WHERE tag IN (SELECT tag FROM qTags)
</cfquery>
These single queries essentially perform the logic of the two query approach. It eliminates the need to use cfqueryparam, dealing with potentially huge lists, and pitfalls of determining a 'safe' delimiter.
However, I'm not sure why this query is needed. It should always return an empty query (query from question) or all records (query from github.com). Is there an intermediate step between the two queries that isn't mentioned in the original question?
Upvotes: 0
Reputation: 1866
@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 ';'.
Upvotes: 6