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?
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.
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.
Remove it on the app layer (i.e. CF) makes most sense.
Maybe you'll find this useful:
For UI, You should check out JQuery Masked input.