Best approach to remove special characters using ColdFusion and Microsoft SQL?

I want to remove all special characters (",/{}etc.) from an input field being saved as a string to the DB.

What is the best approach?

Should this check开发者_运维知识库 be tackled with JS, ColdFusion or Microsoft SQL - Maybe all three?

How would I go about coding this using ColdFusion or Microsoft SQL?

You mean everything not alphanumeric?

I'd probably use a REReplace in the data layer.

<cfqueryparam 
  cfsqltype="cf_sql_varchar" 
  value="#REReplace(myVar,"[^0-9A-Za-z ]","","all")#" 
/>

Update: changed to include "space".

Use a regular expression in Coldfusion

<cfset cleanInput = rereplace(form.input,"[^A-Za-z0-9]","","all") />

This says replace any character that is not A through Z or a through z or 0 through 9 with nothing and do it for everyone encountered.

Are you sure you want to blacklist only those characters? Usually a much safer approach is to whitelist only the acceptable characters.

If you want to ensure your data is kept pure, the safest place to do this is at source, using an INSERT/UPDATE trigger.

You could write a UDF that does this in T-SQL, or for best performance, implement it as a CLR function using C# or similar.

Doing this only in SQL could cause validation issues, though. E.g., if the user has only entered invalid characters on a required field, they essentially have given you no input, so your GUI will likely need to throw a validation error. So, best to have validation checks for usability in your front-end, and triggers for data integrity on the back end.

I used this as a check to get a false back if the characters were not on the whitelist.

<cfif len(testString) EQ len(rereplaceNocase(testString,"[^A-Za-z0-9-+$. _[]","","all"))>
     TRUE<br>
<cfelse>
     FALSE<br>
</cfif>