double quotes and number symbol are stripped out when submitting text data into SQL DB

I have a form with a text box.  When the user submits the form and reviews the submitted text in a browser the double quotes and # symbol that was submitted in the form is stripped out.  When the data is inserted into an SQL db table these symbols are removed as well.

Right now my code is:

<cfset form.attr=REREPLACENOCASE(form.attr,"[^(a-z|0-9|,|'|:|.|?|!|$|%|*|+|=|;|\-|\"")]"," ","ALL") > 

but I can't get the double quotes and number symbol to be excepted.
CTerreriAsked:
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.

Seth_BienekCommented:
Hello,

You can add the pound sign (#) and double-quotes (") to your regex easily by referencing their ascii codes (43 and 34, respectively):

<cfset form.attr=REREPLACENOCASE(form.attr,"[^(a-z|0-9|,|'|:|.|?|!|$|%|*|+|=|;|\-|\|#chr(43)#|#chr(34)#"")]"," ","ALL") >

Regards,

Seth
0
CTerreriAuthor Commented:
I can't get the pound sign to appear.  Is there something missing from the code?
0
Seth_BienekCommented:
Yes, a comma between #chr(34)# and "".

My bad. :)

Seth
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

CTerreriAuthor Commented:
I'm still not getting this to work.  chr(34) is the ascii code for quotes and this is working.  If I change the code to:

<cfset form.attr=REREPLACENOCASE(form.attr,"[^(a-z|0-9|,|'|:|.|?|!|$|%|*|+|=|;|\-|\|#chr(43)#|#chr(34)#,"")]"," ","ALL") >

there is no change....The pound sign still does not appear.

Hmmmmm.....
0
Seth_BienekCommented:
Oddly enough, this works:

<cfset attr = rereplacenocase("This is some special text, with quotes: "", and hash marks: ##.", "[^(a-z|0-9|,|'|:|.|?|!|$|%|*|+|=|;|\-|\|##|#chr(34)#,"")]"," ","ALL") >

The difference is escaping the pound signs instead of using the ASCII code.  Weird.

Seth
0
CTerreriAuthor Commented:
When I view the output through the browser the pound symbol and double quotes appear as I would like.  However, when I review the data after inserting into the SQL db the text gets truncated right before the double quote symbol.  I think this has something to do with using the pound symbols in the REREPLACENOCASE function.

Is there a way to work around this?
0
Seth_BienekCommented:

Are you using <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.attr#"> in your query?

If not, try that. :)

Seth
0
CTerreriAuthor Commented:
I'm not using CFQUERY here. I'm passing hidden form variables from one form to another.  I do perform an INSERT within a CFQUERY on the last page using  the form variables.

I'm not sure what to do here.
0
Seth_BienekCommented:
Is the text correctly formatted right before it goes into the database?

Do a <cfdump><cfabort> directly above your <cfquery> tag..

Seth
0
CTerreriAuthor Commented:
the text is truncated right before the double quotes just as it appears in the db after the INSERT.
0
CTerreriAuthor Commented:
The text is being truncated when it gets passed to the next page and this is how I'm passing it:  

 <INPUT TYPE="hidden" NAME="ATTR"  SIZE=15   VALUE="#form.ATTR#">
0
Seth_BienekCommented:
Kill the "size" parameter in the input tag.

Not sure if this is the problem, but since you're specifying the value, you don't neet to validate the length.

Dose that fix it?

Seth
0
CTerreriAuthor Commented:
No it doesn't help.  I'm playing with the INPUT TAG to see if there's something else going on here.
0
CTerreriAuthor Commented:
I'm increasing the points to 500. To other experts,  do you have any input on this?
0
MartinCMSCommented:
What is the format of your field in SQL?  Make sure it is nVarchar or Text, otherwise, you will have problem with double quotes and pound key.
0
CTerreriAuthor Commented:
I changed the field type from varchar to nvarchar and I still have the same problem: the text is being truncated  right before the double quote symbol.  I can verify that this occurs when it gets passed to the next page as a form variable.
0
Seth_BienekCommented:
Is the text truncated the very first time the form is submitted, or does it happen sometime between that first form post and the time it goes into the database?  Try using a <cfdump..> on every page in the process to find out at what point the text is being truncated.

Regards,

Seth
0
cdman2k3Commented:
I'm looking at that INPUT tag which this.....
     VALUE="#form.ATTR#"

If the form field has double quotes then the value of the input tag will be truncated. You might want to try this instead...

<INPUT TYPE="hidden" NAME="ATTR"  VALUE="#HTMLEditFormat(form.ATTR)#">

The generated HTML source will show   &quot;   where the form variable had a double quote but will be received by the next page as a double quote.

Try the following test files... open formtest.cfm in browser, and see the html generated by formtest2.cfm each time you click submit, and you'll notice that the form.attr survives, even when re-posted (from formtest2.cfm back to itself)

Example - formtest.cfm
<html><body>
<form action="formtest2.cfm" method="post">
<input type="hidden" name="attr" value="x&quot;x">
<input type="submit">
</form>
</body></html>

Example - formtest2.cfm
<html><body>
<cfoutput>form.attr == |#form.attr#|</cfoutput>
<form action="formtest2.cfm" method="post">
<cfoutput><input type="hidden" name="attr" value="#HTMLEditFormat(form.attr)#"></cfoutput>
<input type="submit">
</form>
</body></html>

By the way if you're performing queries to insert user edited data into a database be very careful of single quotes, otherwise if a user types something like   "don't need this"   you get ColdFusion errors... e.g. #PreserveSingleQuotes(form.attr)# inside the <cfquery> --- dead handy and something I invariably forget....
0

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
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
Web Servers

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.