MYSQL SELECT WHERE IN issue

[jackhuang]

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.

[Dave Watts · Accepted solution]

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/

[Owainnorth]

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!

[Dave Watts]

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/

[jackhuang]

Thanks a lot guys.  I think it worked.

But isn't cfqueryparam suppose to solve the security problem of cfquery?  The LIST="yes" in this instance, but what if I simply use it to query a single string e.g. SELECT * FROM tablename WHERE fieldname = '#form.name#'?  If someone post that value, '"); Drop table tablename;' wouldn't I be screwed?

[Dave Watts]

Yes, CFQUERYPARAM prevents SQL injection attacks, so you want to use it everywhere you embed values that come from the browser or any other untrustworthy source. You should be using it as a matter of course, for all your queries. It also happens to solve the problem you had upthread. You should certainly use it in this example too, without the LIST attribute of course.

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

[Owainnorth]

If someone post that value, '"); Drop table tablename;' wouldn't I be screwed?

No, because CFQUERYPARAM does more than just protect your security, it improves the performance of your query by using bind variables.

What it actually sends the database is two things: firstly it sends 'SELECT * FROM tablename WHERE fieldname = :1'. It then sends a message saying right, you know that :1 I gave you? Well that's actually a placeholder for a string variable, which I'd like you to stick in the middle once you've figured out how you're going to run the query.

That way there's no confusion as to what it's meant to mean. If you dump out your cfquery when using params you'll actually see the query and parameters listed out separately.

As Dave points out, the list=true isn't really anything to do with the security side, it's just a little bonus attribute they added in while they were at it.

O.

[Owainnorth]

Oh, and I know yours is just an example but I hope you're not using "select *", it's for amateurs

[Dan_Bracuk]

Regarding, "But isn't cfqueryparam suppose to solve the security problem of cfquery?"

no.

cfqueryparam has a lot of redeeming qualities and should always be used unless there is a reason not to.  However, if it is your only security measure, then your site is not secure.

[jackhuang]

I am using SELECT fieldname1, fieldname2 in my CFQUERY hope that is ok.

What are other security measures?  I learned my CF4 way back and didn't upgrade my acknowledge

[Dave Watts]

Here's a good reference to get you started:

http://www.owasp.org/index.php/Category:OWASP_Top_Ten_Project

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

[jackhuang]

Thanks all for your help.

The top 10 security risks doesn't tell me how to prevent it from CF point of view.  However, I am seeing on my CF Admin message like this:

Error Executing Database Query.Unknown column '2013923index.php' in  'where clause' The specific sequence of files included or processed is:  /home/httpd/vhosts/domain/httpdocs/directory/Forum.cfm,  line: 2

and

Form entries are incomplete or  invalid.<ul><li>STATE/PROVINCE is required  </li></ul> Go <a  href=""javascript:history.back()"">back</a> and correct the  problem. The specific sequence of files included or processed is:  /home/httpd/vhosts/domain/httpdocs/404.cfm''

The forum.cfm don't have anything like '2013923index.php' but it looks like someone is trying to do something through the forum.cfm template.  The second error is because I removed the template that I used to use from the site and now it is throwing the 404.cfm because some outside site is trying to post something and couldn't find my old template.

What to do in this case?

[Dan_Bracuk]

Decide how you want to handle attempts to submit javascript.  You have choices and they each have their pros and cons.