• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

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!
0
traport
Asked:
traport
  • 2
2 Solutions
 
nmaranoCommented:
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
 
_agx_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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now