Issue adding a variable with list of values into SQL

Question

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

	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__- · Accepted Answer

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

-__cfSearching__- · Answer

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

)

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded