Issue adding a variable with list of values into SQL

[ACS LLC]

Does anybody have any idea why this works -->

	SELECT DISTINCT(countryLong) AS CountryLong
	FROM ipcountry
	WHERE countryShort IN ('US','UK')
	ORDER BY countryLong

But this does not work -->

<CFSET target_country = "'US','UK'">

	SELECT DISTINCT(countryLong) AS CountryLong
	FROM ipcountry
	WHERE countryShort IN (#target_country#)
	ORDER BY countryLong

When I run with the list as a variable I get an error, Invalid Syntax near 'US'

(CF8 - MS SQL2005)

Thanks

Mark

[-__cfSearching__-]

CF automatically escapes single quotes within variables as a defense against sql injection. So the single quotes end up being doubled causing a syntax error

 ie

WHERE countryShort IN (''US'',''UK'')

But if you use cfqueryparam instead, you do not have to worry about quoting the values

    )

[ACS LLC]

I've used CFqueryparam a few times a long time ago, but never in SQL, how would that actually look?

Thanks

Mark

[-__cfSearching__- · Accepted solution]

I actually posted an example via email. But as usual the forum at my response. Strangely, it worked a few days ago. At least once anyway.  Must have been a fluke.  Anyway here is what response should have looked like:

But if you use cfqueryparam instead, you do not have to worry about quoting the values

<cfset yourList = "US,UK">

  ...

WHERE countryShort IN ( <cfqueryparam value="#yourList#" list="true" cfsqltype="cf_sql_varchar"> )

[ACS LLC]

ahhh. got it.. tried it... it worked .. wonderful... thanks for the help

Mark