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

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

   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.

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.

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.

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) )

<cffunction name="getDetails" returntype="query">

    <cfargument name="userIDs" type="array">

    <cfset var IDList = arrayToList(userIDs)>

      <cfquery name="details" datasource="mysql" result="res">

              SELECT *

              FROM Users

              WHERE Id IN (#IDList#)

    </cfquery>

      <cfreturn details>

</cffunction>

<cfset aIDs = [1, 4, 5, 3, "0); UPDATE Users SET Name = 'hacked' WHERE Id NOT IN (1000"] />

<cfset getDetails(aIDs) />

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.

@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.