Remove Special Characters in ColdFusion Database Query

I have a Microsoft Access database that holds information about grants available to our clients. That database is connected to a set of ColdFusion web pages where our staff can add new grants, update existing grants and delete grants. There is also the option to export a set of grants search results to an Excel spreadsheet.

Recently I noticed that the formatting on the Excel spreadsheet was thrown all off because certain records in the database contained special characters, most notably line returns. These special characters were a result of staff copying/pasting grant information into the respective form fields on the web page.

How do I strip out line returns, carriage returns etc. without stripping out necessary spacing before the data gets posted to the database? I've done some research and found that using the ColdFusion REReplace function or the Replace function may work, but I'm not sure where to add that code. Would I add it to my SQL INSERT and UPDATE statements?

Here is my INSERT statement:

<cfquery name="qAdd" datasource="#datasource#">
      INSERT INTO Grants (Funder, Summary, Deadline, Awards, Website, Eligibility)
      VALUES ('#arguments.Funder#','#arguments.Summary#','#arguments.Deadline#','#arguments.Awards#','#arguments.Website#','#arguments.Eligibility#')
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Would I add it to my SQL INSERT and UPDATE statements?

IMO it's cleaner to do the manipulation before the INSERT.  

            <cfset arguments.Summary = replace( arguments.summary, ....etc...)>
            <cfquery ...>  INSERT INTO Grants .....</cfquery>

How do I strip out line returns, carriage returns etc.

All depends on what characters they pasted. Some common replacements are:

            <!--- replace CR+LF with space --->
           <cfset cleanText = replace( yourString, chr(13)&chr(10), chr(32), "all")>

            <!--- replace LF with space --->
           <cfset cleanText = replace( yourString, chr(10), chr(32), "all")>

Open in new window

- chr(13)   Carriage return
- chr(10)   Line feed
- chr(32)   Space

You should do some testing with your local data.  If your users are pasting from MS Word, see also this function at
Fixes text using Microsoft Latin-1 &quot;Extentions&quot;, namely ASCII characters 128-160.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kenjpeteAuthor Commented:
That worked perfectly! In my CFC I added the following line to replace line feeds and carriage returns with blank spaces:

<cfset cleanSummary = replace(arguments.Summary, chr(13)&chr(10),"","all")>

Then in my INSERT statement I replaced #arguments.Summary# with #cleanSummary#.

I did this process for each "memo" field in that database table, and did similarly for my UPDATE statement.

Great.  Since you mentioned a CFC,  be sure to VAR/LOCAL scope the cleanSummary variable, OR you could just use the arguments variable instead, ie:

<cfset arguments.Summary = replace(arguments.Summary, chr(13)&chr(10),"","all")>
... do INSERT / UPDATE using #arguments.Summary#

Side note, though MS Access is slightly less susceptible to sql injection, you should  still use cfqueryparam on all parameters. Then the db won't be vulnerable if you ever upgrade to SQL Server. Not to mention, cfqueryparam has a bunch of other helpful benefits for any db type (validation, etc..).
kenjpeteAuthor Commented:
I changed the name of the variables for the INSERT and the UPDATE statements because I was worried I'd create a naming conflict. Thanks for the reminder on cfqueryparam!
If you prefix the variable with the "ARGUMENTS" scope, it should simply update the existing variable. However, using a separate variable is fine too, just be sure to VAR/LOCAL scope it. Otherwise it will leak over to the VARIABLES scope creating a different problem :) ie potential race conditions
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.