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

Question

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

Why do you want to use `valuelist` in this scenario? Why not `where not in (select name from fruit)` in your second query or a join? — Antony, Jan 22 '13 at 00:34
@Antony That would be better than this query, but the question is a just meant as a simplified example so that it's easy to understand the problem. The actual situation I came across this in is a lot more complex, and we can assume makes a reasonable case for using `ValueList`. — Daniel Mendel, Jan 22 '13 at 02:01
If it helps, instead pretend that the input is an `Array` of phrases from a plain-text document. We are searching for them in a database of keywords in order to get the `ids`. We want to use `cfparam` for obvious reasons, but get bad results because of this same comma problem. — Daniel Mendel, Jan 22 '13 at 02:09
@DanielMendel what are the 'obvious reasons' for `cfparam` in this situation? Are you using it to protect against user-supplied data and prevent execution? For performance reasons? — Antony, Jan 22 '13 at 02:17
@Antony Let's say both. That said, I'm only interested in a solution for the specific problem in the question, the actual context is sort of irrelevant. — Daniel Mendel, Jan 22 '13 at 02:20
If the data is coming from a plain text document, as @DanielMendel suggests, then one obvious reason to use cfqueryparam is to escape other special characters such as apostrophe's. — Dan Bracuk, Jan 22 '13 at 02:20
@DanBracuk That's right, it's trivial to build the string for SQL without `cfqueryparam`, but depending on the variety and quality of your input, it could be a huge maintenance headache. — Daniel Mendel, Jan 22 '13 at 02:22
@DanielMendel, in theory, cfquerparam should work even with commas in the data. What are you looking at that makes you think it doesn't? — Dan Bracuk, Jan 22 '13 at 02:23
@DanBracuk I ran into it in some production code recently -- I've used this pattern successfully many times before and this is the first I'd come across this limitation. I'll try to write a coldfusion gist that reproduces it. — Daniel Mendel, Jan 22 '13 at 02:29
You can use QueryNew(), QueryAddRow(), and QuerySetCell() to simulate qAllFruit. — Dan Bracuk, Jan 22 '13 at 02:34
I've added a test that recreates this issue to the question: https://gist.github.com/a642878c96b82b21b52c — Daniel Mendel, Jan 22 '13 at 02:40
I ran that code and got the results you predicted. I was very surprised. I'm not sure how to solve it. — Dan Bracuk, Jan 22 '13 at 02:57
@DanielMendel - Nothing to do with your question, but is there a reason for storing lists? That structure is notoriously problematic. In most all cases you are better off normalizing the data and storing it in separate rows. — Leigh, Jan 22 '13 at 04:44

score 5 · Accepted Answer · edited Jan 22 '13 at 13:02

5

@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 ';'.

edited Jan 22 '13 at 13:02

Leigh

28,765
10
55
103

answered Jan 22 '13 at 03:08

Sanjeev

1,838
1
16
28

If you are going to do that, you need a more obscure delimiter like ¿. However, that just reduces the liklihood of having the delimiter in the data, it doesn't completely eliminate it, – Dan Bracuk Jan 22 '13 at 03:15
@DanBracuk I agree with you. Any delimiter (or special character) will work as long as it is not present in your data. – Sanjeev Jan 22 '13 at 03:21
2

Can use `Chr(65536)` for a delimiter that almost certainly wont appear in regular text. (Is a shame you can't just pass an array in directly.) – Peter Boughton Jan 22 '13 at 03:25
3

@PeterBoughton: I use chr(31) for this - unit separator - as it's the closest in intent to what one wants, rather than picking some "unlikely" character and hoping for the best. I think this is better than using something like chr(65536) as it doesn't require inventing a special "rule" that that character has special meaning. – Adam Cameron Jan 22 '13 at 06:56
It's not _"picking some "unlikely" character and hoping for the best"_ - it is specifically a character value in the private use section of unicode - i.e. has been reserved as having no formal role, so can use it for own purposes. That said, having looked up 31, it is described as _"dividing plain-text data items"_ which seems to be an exact fit for what is needed here. – Peter Boughton Jan 22 '13 at 16:36

score 0 · Answer 2 · answered Jan 22 '13 at 22:25

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?

Thank you, but the accepted answer is exactly what I was looking for. The question isn't really about the queries -- they were just a very simple example to express the issue with `cfqueryparam`. — Daniel Mendel, Jan 22 '13 at 23:22

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

2 Answers2