andrewaiello
asked on
Need help with SQL IN clause regarding a multiple selection box
Hey All,
In the middle of an SQL query i have the following line:
WHERE location IN ('#form.categories#') where the form.categories is the list returned from a multiple selection list box. This query is not behaving properly because that last term gets evaluated as, for instance, the following: Where location in ('Long Island, New York') Where what i really need is the following: Where location in ('Long Island', 'New York') So you see, the quotes are not in the right place for proper functionality. Can anyone tell me what I am doing wrong or how to fix it? thank you.
In the middle of an SQL query i have the following line:
WHERE location IN ('#form.categories#') where the form.categories is the list returned from a multiple selection list box. This query is not behaving properly because that last term gets evaluated as, for instance, the following: Where location in ('Long Island, New York') Where what i really need is the following: Where location in ('Long Island', 'New York') So you see, the quotes are not in the right place for proper functionality. Can anyone tell me what I am doing wrong or how to fix it? thank you.
ASKER
Thanks for replying so fast.
The listbox is being populated from a query so i don't know how to put the quotes in without generating the error. The code for the box is as follows where catText would be like New York:
<cfselect name="categories"
query="getCat"
value="catText"
display="catText"
required="No"
multiple="Yes"
size=4></cfselect>
The listbox is being populated from a query so i don't know how to put the quotes in without generating the error. The code for the box is as follows where catText would be like New York:
<cfselect name="categories"
query="getCat"
value="catText"
display="catText"
required="No"
multiple="Yes"
size=4></cfselect>
ASKER
(reading up on coldfusion stored procedures btw ;)
it's due to the way cfselect passes the data
since cf is parsed before sql, a quick way might be to simply
WHERE location IN ('#replace(form.categories
which basically transforms this
('Long Island, New York')
by replacing the , with ','
so you get
('Long Island', 'New York') (provided i got all my "'''''s in the right place)
btw...cf doesn't -have- stored procedures...it just uses sp's from your dbms
since cf is parsed before sql, a quick way might be to simply
WHERE location IN ('#replace(form.categories
which basically transforms this
('Long Island, New York')
by replacing the , with ','
so you get
('Long Island', 'New York') (provided i got all my "'''''s in the right place)
btw...cf doesn't -have- stored procedures...it just uses sp's from your dbms
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hmmm, pretty slick! I'll give that a try. Thanks for your info on stored procedures btw. Take care.
You should also be able to use #QuotedValueList(FORM.Cate
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
oh dg...yer all about the slick ;)
where ya been...working or something??
of course this illustrates what's good and also what's bad about cf there's many ways to do a thing...but it can be confusing to know which one to choose...
where ya been...working or something??
of course this illustrates what's good and also what's bad about cf there's many ways to do a thing...but it can be confusing to know which one to choose...
btw dg...i'd appreciate some input here..
https://www.experts-exchange.com/questions/22479194/sql-injection-quotes-and-cfqueryparam.html (it's not really a mysql q...stupid zone cross post thing goes where ever)
https://www.experts-exchange.com/questions/22479194/sql-injection-quotes-and-cfqueryparam.html (it's not really a mysql q...stupid zone cross post thing goes where ever)
ASKER
Wow you guys, thanks alot; it works now. I used DG's method (sorry sid! ;) ) Btw, where do you guys go to look for these types of functions that don't seem to be in the standard docs. Thanks
andrew ...
I'm glad it worked
most everything is in the docs.
I know listqualify is - I remember when I first found it.
I'm glad it worked
most everything is in the docs.
I know listqualify is - I remember when I first found it.
Sid - I posted in your other post ...
waiting for your reply ...
waiting for your reply ...
it would eventually be better to use a stored procedure for that, so it makes such complexity transparent to the (coldfusion) code