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

Question

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?

Solution 1

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

Solution 2

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.

Solution 3

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.

Comments

[Henry]

Remove it on the app layer (i.e. CF) makes most sense. Maybe you'll find this useful: demo.bryantwebconsulting.com/datamgr/word.cfm

[Vikas]

For UI, You should check out JQuery Masked input. digitalbush.com/projects/masked-input-plugin plugins.jquery.com/project/maskedinput

[J.T.]

As for "when to do it": Javascript: When you need the user to know for a convenience Application Server: Always for validation and you can request corrections DB Level: Depends on your organization's policy, but is really just extra, extra. It's easier to give feedback at the application level.

[ale]

Add it as a valid character. I've changed my answer to allow the use of the space character.

[Peter Boughton]

Alternatively: <cfif refind("[^\w\-+$. \[]",testString)>

[Peter Boughton]

Exactly the same as the posted one, but with A-Za-z0-9_ condensed to \w and missing escapes added to - and [ characters. More significantly is that all the len/replace nonsense has removed so it's both more obvious what's going on and will perform faster.

[Peter Boughton]

Saying "work with UTF-8" is ambiguous. It will "work" in the sense it wont error and will do exactly what it has been written to do. But it wont work in the sense that it will remove non-ascii letters, since they're not in the 63 characters that the class excludes. (This is just one example of why the term "special character" should never be used; it doesn't specify what is actually desired.)