5 Replies Latest reply on Mar 1, 2009 9:37 PM by BKBK

    double quote in a SQL update

    plarts Level 1
      Here is the code :
      <CFQUERY name="upd_user" datasource="#eft_formation#">
      update users_eft
      set nom='#form.nom#',
      pnom='#form.pnom#',
      etc ....

      If I put a text with double quote in my form field in previous page,
      all text after the double quote is lost by the update query.
      First time I see this.
      I have a lot of other forms with an update query, with no problem, the double quote pass in the sql update.

      When returning #form.nom# and display before update, the value is good (with the double quotes).
      After sql update , text is lost after the double quote.

      Thanks for any help, direction where to look for ?
        • 1. Re: double quote in a SQL update
          plarts Level 1
          A bit farther in this trouble :

          I have checked the value in the database, it is good,
          the double quote are in the database,
          and when displaying #get_user.nom# as a normal text, I get it.
          and when using it in an <input> tag like :
          <input value="#get_user.nom#" etc... >
          the value is empty after the double quote.
          So it seems this is a problem with the <input tag ?

          Thanks for any clue.
          Pierre.
          • 2. Re: double quote in a SQL update
            Dan Bracuk Level 5
            cfqueryparam solves problems like these. It also makes many queries run faster.
            • 3. Re: double quote in a SQL update
              fober1
              Hi,

              CF will replace your placeholder with the actual value before it sends the html page to the users browser. Have a look into the source code of the page that get's generated.

              You need to replace " with &quot;

              <cfset test= replace(test, """", "&quot;", "all")>

              4 quotes!

              cheers,
              fober
              • 4. Re: double quote in a SQL update
                Dan Bracuk Level 5
                quote:

                Originally posted by: fober1
                Hi,

                CF will replace your placeholder with the actual value before it sends the html page to the users browser. Have a look into the source code of the page that get's generated.

                You need to replace " with &quot;

                <cfset test= replace(test, """", "&quot;", "all")>

                4 quotes!

                cheers,
                fober

                htmleditformat() might include this. I know it looks after angle brackets.
                • 5. double quote in a SQL update
                  BKBK Adobe Community Professional & MVP
                  A bit farther in this trouble :

                  I have checked the value in the database, it is good,
                  the double quote are in the database,
                  and when displaying #get_user.nom# as a normal text, I get it.
                  and when using it in an <input> tag like :
                  <input value="#get_user.nom#" etc... >
                  the value is empty after the double quote.
                  So it seems this is a problem with the <input tag ?


                  No, it's not a problem with the input tag. It's just simple truncation -- by the browser! Suppose you have

                  <cfset x = 'John "The Bull" Richards'>
                  <cfoutput><input type="Text" name="nickname" value="#x#"></cfoutput>

                  The output is

                  <input type="Text" name="nickname" value="John "The Bull" Richards">

                  The browser picks out what it expects to see, value="John ", and ignores the rest. Look at the source code, and you'll see that the text is all there in full.

                  To avoid that, apply Dan's suggestion

                  <cfoutput><input type="Text" name="nickname" value="#htmleditformat(x)#"></cfoutput>

                  or even

                  <cfoutput><input type="Text" name="nickname" value="#xmlformat(x)#"></cfoutput>