Skip to main content
Known Participant
March 13, 2011
Answered

MYSQL SELECT WHERE IN issue

  • March 13, 2011
  • 12 replies
  • 2097 views

I am trying to use the WHERE fieldname IN ("#form.names#") but it won't work.

I know for example:

SELECT *
FROM tablename
WHERE
fieldname IN ('US,UK,GB,CN');

will NOT work, but

SELECT *
FROM tablename
WHERE
fieldname IN ('US','UK','GB','CN');

will work.

But here is my problem.  I get the list from a form with checkboxes, so they are stored in a form variable call form.names.  The form.names value is US.UK,GB,CN etc.  So if I put

SELECT *
FROM tablename
WHERE
fieldname IN (#form.names#);

nothing will work, because there is no quotes around it but if I put the quotes

SELECT *
FROM tablename
WHERE
fieldname IN ("#form.names#");

will work ONLY if the list has one item.  When there is more than one like the above, it won't work at all.

How can I manipulate the #form.names# so that each item will have quotes around it?  Any help is appreciated.

This topic is closed to new replies. Start a new post to keep the conversation going.
Correct answer Dave Watts

You should be using CFQUERYPARAM anyway, and it lets you fix this problem using the LIST attribute:

WHERE field IN (<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.names#">

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

12 replies

Owainnorth
Inspiring
March 13, 2011

As Dave points out, what *will* work perfectly here is if someone posts the value '"); Drop table tablename;' to your page. Bye-bye all your data

His solution is correct, but he did accidentally miss out the list="true" attribute, which will automatically sort your quotes issue for you.

But seriously, get cfqueryparams in there before it's too late!

Community Expert
March 13, 2011

I mentioned the LIST attribute, but then didn't actually use it in my code example! D'oh!

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

Dave Watts, Eidolon LLC
Dave WattsCommunity ExpertCorrect answer
Community Expert
March 13, 2011

You should be using CFQUERYPARAM anyway, and it lets you fix this problem using the LIST attribute:

WHERE field IN (<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.names#">

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

Dave Watts, Eidolon LLC