Skip to main content
Known Participant
March 13, 2011
Answered

MYSQL SELECT WHERE IN issue

  • March 13, 2011
  • 2 replies
  • 2096 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 has been closed for replies.
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/

2 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