thanks for everyones help, its now solved!
the reason i am building it through a string is because it is interacting with
various extjs grids, which apply filters. i have a generic function that
creates the havingclause to filter the data.
i ran the havingclause through a loop printing out 1 character at a time. the
apostophe was printed as '%apos;' even though it appeared as ' in the sql
statement!
have taken on board comments re cfqueryparam and now that this error is fixed
will update it to include the sql injection
thanks for all the help :)
> hi, we are running a cfquery with the following sql statement:
>
> SELECT viewname, categoryname FROM views LEFT OUTER JOIN
> viewcategories on views.categoryid = viewcategories.categoryid GROUP
> BY viewname HAVING categoryname IN ("aaaa's") ORDER BY viewname ASC
> LIMIT 0, 300
>
> if we type this directly into mysql it works fine, similarly if we
> type it directly into a <cfquery> it works fine. however the having
> clause is getting built from following code and is causing the
> problem:
>
> <cfset value = arraynew(1) />
> <cfset value[1] = "aaaa's" />
> <cfloop index="a" from="1" to="#arraylen(value)#">
> <cfset value[a] = '"#value[a]#"' />
> </cfloop>
> <cfset havingClause = "HAVING categoryname IN (#arraytolist(value)#)"
> />
>
> the problem is occuring due to the apostrophe '. we have tried
> preserveSingleQuotes but still does not work.
>
> is there any way around this?
>
> thanks
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337159
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
