Saturday, December 27, 2008

Manual Query Safe List via ReReplace (Regex) on a Comma Delimited list of Numbers

So, I've been using this a little more often, here and here, because it's handy and I think it's really safe.

Making the string safe, easily and quickly:

<!--- not a digit or comma = replace it out. Allow only digits and commas = safe --->
<cfset variables.itemtypeid = REREPLACE(variables.itemtypeid,"[^\d,],","","ALL")>

On top of this, if you know on the processing side that there cna be a max of 20 items, truncate the list at the 20th. This makes it even safer.

Now you can safely query:

Select id, text
from table
where id IN (#variables.idlist#)

Happy SQLing...

And that's a wrap.