SQL query of a ColdFusion List

I have a query and I'm trying to see if one of the SQL variables is LIKE one of the items in a list. At the end of the query I have

  AND tblPerson_RTIDetail.personUnit IN (#PreserveSingleQuotes(session.userRoleUnits)#)

This appears to be doing only a direct match. Can I use "LIKE" in place of "IN" and, if so, how would I place the %?

Thanks a lot!
traportAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
Ok, then you'll need a loop combined with @nmarano's example of LIKE.  But use cfqueryparam too

ie
  AND tblPerson_RTIDetail.personUnit LIKE <cfqueryparam value="#unit#%" cfsqltype="cf_sql_varchar">


Why is it a dangerous function?
It makes queries vulnerable to a common type of sql injection.  
http://kb2.adobe.com/cps/300/300b670e.html
0
 
nmaranoConnect With a Mentor Commented:
You would put the % whereever you want to use the wildcard.  For instance if you wanted to find all cities that started with m you would write.

SELECT *
from cities
where name LIKE 'm%'

This will return all cities that start with m

0
 
_agx_Commented:
Can I use "LIKE" in place of "IN"

If #session.userRoleUnits# contains multiple values - then NO.  What you could do is loop through the list and generate a LIKE statement for each one as @nmarano described above

WHERE  .....
<cfloop list="#session.userRoleUnits#" index="unit">
    AND tblPerson_RTIDetail.personUnit LIKE '#unit#%'
</cfloop>

#PreserveSingleQuotes(session.userRoleUnits)#

( I've probably mentioned it before, but that's a very dangerous function to use on sql server )
0
 
traportAuthor Commented:
Yes it will have multiple values.

Why is it a dangerous function? Thanks for bringing it to my attention.

I'll go try this now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.