Array in Where clause?

Report · Sep 22, 2011

I would like to create a Coldfusion function where one of my arguments is and array of "user_id"'s. Then I want to:

Select * from database where (each id in the array) = #user_id#

How is this written?

Report · Sep 22, 2011

Use a WHERE IN clause. You can use the arrayToList() function to generate the list of user id's.

http://www.w3schools.com/SQl/sql_in.asp

Report · Sep 23, 2011

Better yet, create a list instead of an array to start with. Less processing that way.

Report · Sep 22, 2011

Hi,

Please try this,

Select * from database where user_id in( <cfqueryparam cfsqltype="cf_sql_integer" list="true" value="#arraytoList(useridarray)#">)

Report · Sep 24, 2011

C-Rock wrote:
I would like to create a Coldfusion function where one of my arguments is and array of "user_id"'s. Then I want to:
Select * from database where (each id in the array) = #user_id#
How is this written?

SELECT *

FROM myTable

WHERE id IN (#IDList#)

</cfquery>

</cffunction>

Report · Sep 24, 2011

WHERE id IN (#IDList#)

Do not forget to use cfqueryparam to avoid sql injection. Also remember to scope query variables too.

Report · Sep 25, 2011

-==cfSearching==- wrote:
WHERE id IN (#IDList#)
Do not forget to use cfqueryparam to avoid sql injection. Also remember to scope query variables too.

I would disagree. If there is a need to use cfqueryparam here, then the design is likely improper.

User IDs don't usually come from users themselves. One may therefore assume that, by the time we arrive at an in-process like this one, the IDs have been validated and are kosher (or halal, as the case may be).

Report · Sep 25, 2011

I would disagree. If there is a need to use cfqueryparam here, then the design is likely improper.

I would respectfully disagree with your disagreement

*Any* variable should use params, so that the database engine can re-use its execution plan and so improve performance. They are not there only for protection from sql injection.

Report · Sep 25, 2011

Owain North wrote:
I would disagree. If there is a need to use cfqueryparam here, then the design is likely improper.
I would respectfully disagree with your disagreement
*Any* variable should use params, so that the database engine can re-use its execution plan and so improve performance. They are not there only for protection from sql injection.

I would respectfully disagree with your disagreement. Protection against SQL injection is a must. However, your 'should' isn't a 'must'.

Report · Sep 25, 2011

And I would respectfully disagree with your respectful disagreement with my respectful disagreement of your previous disagreement.

I agree.

There is no technical *need* to use params, if you're coming at them with a purely security-oriented approach, in this one situation, and the parameters have already been checked for type-safety, which I suspect they have not.

I'm saying there is absolutely no downside to using them - it will be safer, quicker and type-safe. One of ColdFusion's (downsides|advantages, delete as appropriate) is its lack of type-safety. Although the function is, in this case, accepting an array, there is no way of checking what it's an array *of*. ColdFusion will happily try to pass strings into that SQL query. That array of IDs could easily have been populated into from a selection of HTML checkbox elements. All someone needs to do is change the value from an ID to a string in their browser and pow, you've got yourself a SQL Injection attack. I appreciate chances are it wouldn't work for various other reasons, but still - someone could easily get a string into your SQL query which is obviously a big no-no.

You are saying that you should for some reason not bother with that for what reason? To save typing a few characters? In four years of working with ColdFusion I have never found a reason to pass a variable to a database unparam'd.

Report · Sep 25, 2011

Cfqueryparam is handy in more ways than one. There is no argument about that (pun!). However, the context of my statement was clear: SQL injection.

Report · Sep 25, 2011

I accept that, but I'm saying you're incorrect in that case. Take this example:

CREATE TABLE Users (

Id INT PRIMARY KEY AUTO_INCREMENT,

Name VARCHAR(10) )

SELECT *

FROM Users

WHERE Id IN (#IDList#)

</cfquery>

</cffunction>

The function has taken in an array, so that's not a problem - CF sends the query off to the db. However look what gets given to the database:

SELECT * FROM Users WHERE Id IN (1,4,5,3,0); UPDATE Users SET Name = ''hacked'' WHERE Id NOT IN (1000)

I just don't see the need to *ever* advise someone not to use queryparams, especially in such a weakly-typed environment.

Report · Sep 25, 2011

@Owain North

Your examples simply justify what you say. However, they don't necessarily amount to a contradiction of what I have said.

I just don't see the need to *ever* advise someone not to use queryparams, especially in such a weakly-typed environment.

No one is advising anyone not to use cfqueryparam. My point is, there is no need to protect against SQL injection for parameters that should already have undergone validation in the first place.

In other words, having to do so should be a signal that something is amiss. In fact, I am assuming that the user IDs in the original question are coming from the database!

Report · Sep 26, 2011

Just catching up on email ..

should already have undergone validation in the first

place.

Having an extra layer of security at such minimal cost is a good thing. So what if the checks might sometimes be redundant? Given what bad things could easily happen if the input is not ideal, it is far better to err the side of safety. Add in cfqueryparam's other benefits and it makes more sense to use it than not.

-Leigh

Report · Sep 25, 2011

@C-Rock

Should you be interested, the clause where the fuss is all about is:

WHERE id IN (<CFQUERYPARAM VALUE="#IDList#" CFSQLTYPE="CF_SQL_INTEGER" LIST="yes">)

I have assumed the user IDs are whole numbers.