3 Replies Latest reply on Feb 25, 2010 12:15 PM by Reed Powell

    Need help with preserving double quotes in text fields


      Hi all,

      I've built a form that allows a user to type in whatever they want into a textarea field.

      That field is then inserted into a database table with a field named 'page_text' and that specific field is varchar(6000).


      The problem I'm having isn't making any sense to me.


      For instance, if someone were to type:


      Former President Reagan once said, "We can not have an economy based on delivering pizza to one another", which is often misquoted


      ..when the insert statement runs,  what ends up in the page_text field is the following:


      Former President Reagon once said,


      ..and it stops right where the first double-quote shows up in the textarea input of the user.


      I understand the need to escape double quotes in instances where double quotes are used in query syntax, but in the case of an insert statement

      the code does not use double quotes, it uses single quotes around the value.


      But, for whatever reason, which I am resigned to accepting, the double quotes seem to cause issues.  Thus, I assume I need to find a way to escape the double quotes contained within the submitted textarea field.

      I have tried different things.  I tried using ReReplace on the value to exchange the double quotes for ascii charactors.  I have tried to use ReReplace to exchange a single double quote for a set of two double quotes (side by side).  I have tried using cfqueryparam value="#text#" cfsqltype="CF_SQL_VARCHAR", as well as cfqueryparam value="#form.text#" cfsqltype="CF_SQL_VARCHAR" directly in the input query, and all have failed to get past this issue.


      Using the cfsqltype code actually throws an error about something being a '1', when no '1' exists in the text being processed.

      Using the various ReReplace methods does not throw an error, but the resulting contents of the page_text field are cut off at the first double quote found in the passed textarea content.


      I'm not sure whether I am supposed to parse out the double quotes completely,  try a ReReplace of some kind, use some type of code 'before' the data even reaches the insert query, or do something to manipulate it directly in the insert query itself.


      I've run out of ideas, and while searching both the general internet and these forums, I am only finding references to single quote problems (preserveSingleQuotes) and not double quote issues.


      If anyone has any idea on how to get past this problem, I'd be greatly appreciative.   The amount of yelling and cussing that has enveloped my office over this problem is getting a bit extreme


      Thank you,




      Here are some things I've tried:

      reReplace(blogtextpost, "(#Chr(34)#)","""","ALL")

      reReplace(blogtextpost, ""","""","ALL")

      reReplace(blogtextpost, """","""""","ALL")

      reReplace(blogtextpost, ' " ',' "" ',"ALL")

      as well as the previously mentioned cfsqltype lines

        • 1. Re: Need help with preserving double quotes in text fields
          CFML_MANIAC Level 1

          I dont know if this has anything to do with my problem or not, but I thought I should add this:


          When a user is filling out the form that has the textarea,  they put whatever text they want into the textarea field.

          textarea name="text"></textarea


          Then, they click on a 'Continue' button and the system shows them a preview of what they are about to have inserted

          into the database.

          Within the code on this preview page is a hidden field, which holds the contents of their textarea data so it can be

          passed on through the process once the user verifies the preview.

          This is done using this: input type="hidden" name="text" value="<cfoutput>#text#</cfoutput>"


          Like I said, I don't know if that has anything to do with this problem, but thought it might be good to mention it



          • 2. Re: Need help with preserving double quotes in text fields
            CFML_MANIAC Level 1

            Found it!


            Turns out that the hidden text field that was being used to store the text input data during the preview process was the issue.

            As soon as I yanked the preview out of the process, everything worked just fine.


            So, in order to get around this problem I guess I'm going to have to work on preserving/escaping the doublequotes at that point, and not either 'just before' or 'during' the actual input query.


            Seems like all it takes for me to fix something myself is to finally ask for help hehe..




            • 3. Re: Need help with preserving double quotes in text fields
              Reed Powell Level 3

              I believe that you just answered your question with the additional information you posted.  The first of the double quotes that the user is typing into the textrea is becomming the terminating double quote on the value= part of the hidden form field.  The rest of the user input, including the second double quote, is just "hanging" there in the middle of the input tag.


              Try this:  wrap URLencodedformat() the string in the value= part of the hiddle field's input tag, and then decode it on the next page before you put it into the SQL statement.


              Good luck!