Dave Watts wrote:
> I'm a big fan of CFQUERYPARAM,
As any right-thinking person would be.
> but this is a bad example to justify its use. You don't need to do all
> that work. There's a function called ListQualify that takes care of
> this for you:
Forgot about that one! Cheers!
> <cfquery name="categories" ...>
> SELECT categoryId, title, description
> FROM categories
> WHERE categoryId IN
> (#ListQualify(FORM.categoryList, "'")#)
> </cfquery>
That's not quite the same though. For instance, what if the fields
contain quotes? I check the docs just in case and it doesn't mention
anything about escaping the qualifier, which is what I though. Just
in case, I knocked out the following bit of code:
<cfset lst = "this,is,a,b'i'g,test">
<cfoutput>
<div>#lst#</div>
<cfset lst = ListQualify(lst, "'")>
<div>#lst#</div>
</cfoutput>
and got the following results:
this,is,a,b'i'g,test
'this','is','a','b'i'g','test'
Now, I haven't checked yet to see if CF will escape the single quotes in
the fourth list element, but I think it should balk at this. Of course,
I need to check.
Ok, I will... after the above code, I added:
<cfscript>
foo = QueryNew("bar");
QueryAddRow(foo, 1);
QuerySetCell(foo, "bar", "b'i'g", 1);
</cfscript>
<cfquery name="baz" dbType="query">
SELECT *
FROM foo
WHERE bar IN (#lst#)
</cfquery>
And boom! Query of queries error. Ok, I expected that. We'll try some
voodoo: This is probably because I omitted PreserveSingleQuotes() or
something. I rarely drop variable values straight into queries. So I'll
wrap it around lst in the query...
And kaboom again. Ok, time for more voodoo: I'll get rid of the
PreserveSingleQuotes() and move the ListQualify() in there instead, as
in the example you gave...
And it works! Now, that isn't the behaviour I'd expect, so what am I
missing?
Anyway, the main thrust of my code was to say that <cfqueryparam> made
the type of the list unimportant. It just makes inserting lists into
queries a bit easier.
I wrote that little rant after digging though a big heap of code that
did somewhat horrible things in queries with lists. It wasn't any of
ours, thank goodness, but a third-party bit of code.
Point taken though. Must get some sleep. :-)
K.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:195254
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
