Solved

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

Posted on 2004-08-03
21
297 Views
Last Modified: 2013-12-24
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.
0
Comment
Question by:CTerreri
21 Comments
 
LVL 5

Expert Comment

by:Seth_Bienek
ID: 11706358
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
 

Author Comment

by:CTerreri
ID: 11707102
I can't get the pound sign to appear.  Is there something missing from the code?
0
 
LVL 5

Expert Comment

by:Seth_Bienek
ID: 11707357
Yes, a comma between #chr(34)# and "".

My bad. :)

Seth
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:CTerreri
ID: 11707968
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
 
LVL 5

Expert Comment

by:Seth_Bienek
ID: 11708090
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
 

Author Comment

by:CTerreri
ID: 11708407
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
 
LVL 5

Expert Comment

by:Seth_Bienek
ID: 11708646

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

If not, try that. :)

Seth
0
 

Author Comment

by:CTerreri
ID: 11709739
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
 
LVL 5

Expert Comment

by:Seth_Bienek
ID: 11709779
Is the text correctly formatted right before it goes into the database?

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

Seth
0
 

Author Comment

by:CTerreri
ID: 11709917
the text is truncated right before the double quotes just as it appears in the db after the INSERT.
0
 

Author Comment

by:CTerreri
ID: 11710263
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
 
LVL 5

Expert Comment

by:Seth_Bienek
ID: 11710320
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
 

Author Comment

by:CTerreri
ID: 11710440
No it doesn't help.  I'm playing with the INPUT TAG to see if there's something else going on here.
0
 

Author Comment

by:CTerreri
ID: 11717584
I'm increasing the points to 500. To other experts,  do you have any input on this?
0
 
LVL 8

Expert Comment

by:MartinCMS
ID: 11726236
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
 

Author Comment

by:CTerreri
ID: 11757612
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
 
LVL 5

Expert Comment

by:Seth_Bienek
ID: 11758973
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
 
LVL 1

Accepted Solution

by:
cdman2k3 earned 500 total points
ID: 11782398
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
cloud web Service looking for a home... 3 111
Unsearchable in Google,Yahoo and Bing. 6 63
Intranet Solution - Sharepoint Foundation or up 4 67
Problem to Eclipse 16 132
This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question