Skip to main content
O
OgreOne
Known Participant
September 29, 2009
Answered

Receiving error when ampersands present

  • September 29, 2009
  • 2 replies
  • 4291 views

I am using CF and MySQL and have a simple form to upload.  The form requests an URL, however, if the URL contains and ampersand, I get an error.  How can I pass the information form a text input field into MySQL and have it parse the ampersand?

Thanks in advance

    This topic has been closed for replies.
    Correct answer

    You need to escape any special XML characters < > ' " &. You can do this using the built in function XmlFormat() around your variables, or else wrap each value with <![CDATA[...]]>

    <cfloop query="GetNews">
        <item>
            <title>#XmlFormat(GetNews.art_title)#</title>
            <link>#XmlFormat(GetNews.link_one)#</link>
            <description>#XmlFormat(GetNews.art_description)#</description>
            <date>#LSDateFormat(GetNews.art_timestamp,'MM/DD')#</date>
            <pubDate>#LSDateFormat(GetNews.art_timestamp,'DDD, DD MMM, YYYY')#</pubDate>
        </item>
    </cfloop>

    OR

    <cfloop query="GetNews">
        <item>
            <title><![CDATA[#GetNews.art_title#]]></title>
            <link><![CDATA[#GetNews.link_one#]]></link>
            <description><![CDATA[#GetNews.art_description#]]></description>
            <date>#LSDateFormat(GetNews.art_timestamp,'MM/DD')#</date>
            <pubDate>#LSDateFormat(GetNews.art_timestamp,'DDD, DD MMM, YYYY')#</pubDate>
        </item>
    </cfloop>

    -JD

    2 replies

    A
    Anonymous
    Correct answer
    September 29, 2009

    You need to escape any special XML characters < > ' " &. You can do this using the built in function XmlFormat() around your variables, or else wrap each value with <![CDATA[...]]>

    <cfloop query="GetNews">
        <item>
            <title>#XmlFormat(GetNews.art_title)#</title>
            <link>#XmlFormat(GetNews.link_one)#</link>
            <description>#XmlFormat(GetNews.art_description)#</description>
            <date>#LSDateFormat(GetNews.art_timestamp,'MM/DD')#</date>
            <pubDate>#LSDateFormat(GetNews.art_timestamp,'DDD, DD MMM, YYYY')#</pubDate>
        </item>
    </cfloop>

    OR

    <cfloop query="GetNews">
        <item>
            <title><![CDATA[#GetNews.art_title#]]></title>
            <link><![CDATA[#GetNews.link_one#]]></link>
            <description><![CDATA[#GetNews.art_description#]]></description>
            <date>#LSDateFormat(GetNews.art_timestamp,'MM/DD')#</date>
            <pubDate>#LSDateFormat(GetNews.art_timestamp,'DDD, DD MMM, YYYY')#</pubDate>
        </item>
    </cfloop>

    -JD

    O
    OgreOneAuthor
    Known Participant
    September 29, 2009

    You're AWESOME!  I used the XmlFormat method.  The other didn't seem to work, but problem solved!

    Thanks to BKBK for helping as well!

    BKBK
    BKBK
    Community Expert
    September 29, 2009

    I am a confused. There are 4 possible things that could be going on here.

    1) You say upload, but what you seem to be doing is using a form to submit a line of text.

    2) Uploading is normally for a file, not for text. The form field one uses for uploading is of type file, not text.

    3) An upload form requests the path to a file on the client, not a URL.

    4) What error message do you get?  Coldfusion or MySQL?

    How can I pass the information form a text input field into MySQL

    I used the following code to save the text http://127.0.0.1:8501/workspace/tests/page4763.cfm?a=1234&b=5678, without any problems.

    <!--- test_tbl has a single column, myString[type varchar(200)]--->

    <cfif isdefined("form.x")>
        <cfquery name="q" datasource="myDSN">
        insert into test_tbl(myString) values('#form.x#')
        </cfquery>
    </cfif>

    <cfoutput><form action="#cgi.SCRIPT_NAME#" method="post"></cfoutput>
    <input name="x" type="text">
    <input name="s" type="submit" value="send">
    </form>

    O
    OgreOneAuthor
    Known Participant
    September 29, 2009

    Sorry for the confusuin.  I meant submit, not upload.  In a simple form, this is my line of code for the field:

    <input type="text" name="url" value="" size="60" />

    Upon submit, it would input the URL string into the URL field in the MySQL db.  However, if the URL has an ampersand, for example (Fake URL for demonstration): http://forums.adobe.com/post!reply.jspa?message=2280267&2343, I wpould get the following CF error upon submition:

    An error occured while Parsing an XML document.

    The reference to entity "job" must end with the ';' delimiter.

    However, as long as I change all the & to & before submitting, all works fine.  Then if I go and update it later, I the URL changes the & to & and I cannot submit the changes without changing the & back to &

    Therefore, I figure there is an interpretation issue between MySQL and CF somewhere since it looks like MySQL recognizes & well enough to change it to & and spit it back out. MySQL field type is "text" and the collation is "latin1_swedish_ci"

    O
    OgreOneAuthor
    Known Participant
    September 29, 2009

    I know I am an idiot... Forgot to mention that it must not be the CF/MySQL, BUT after I insert, I have it go to another cfm page to create an XML document, and so the parsing error must com from that.

    Here's my Code to parse the XML document:

    <cfquery name="GetNews" datasource="illegal">
    SELECT *
    FROM articles
    ORDER BY art_timestamp DESC
    </cfquery>
    <cfoutput>
      <cfxml variable="XMLDoc">
      <?xml version="1.0" encoding="ISO-8859-1"?>
      <rss xmlns:dc="http://purl.org/dc/elements/1.1" version="2.0">
        <channel>
          <title>News\</title>
          <link>
          http://www.mylink.com
          </link>
          <description>Get a daily News.</description>
          <language>en-US</language>
          <copyright>Copyright 2009, Office.</copyright>
          <managingEditor />
          <webMaster>Jojo@gmail.com</webMaster>
          <pubDate>1 Aug 2009 0:00:00 GMT</pubDate>
          <docs>http://blogs.law.harvard.edu/tech/rss</docs>
          <ttl>240</ttl>
          <cfloop query="GetNews">
            <item>
            <title>#GetNews.art_title#</title>
            <link>
            #GetNews.link_one#
            </link>
            <description>#GetNews.art_description#</description>
            <date>#LSDateFormat(GetNews.art_timestamp,'MM/DD')#</date>
            <pubDate>#LSDateFormat(GetNews.art_timestamp,'DDD, DD MMM, YYYY')#</pubDate>
            </item>
          </cfloop>
        </channel>
      </rss>
      </cfxml>
    </cfoutput>
    <cfdump var="#XMLDoc#">
    <cfset XMLText=ToString(XMLDoc)>
    <cffile action="write" file="d:\home\sites\illegalimmigrationjournal.com\wwwroot\aznr\news\index.xml" output="#XMLText#">
    <cflocation url="upload.cfm">

    So how, in my CF code do I make the XML parse the ampersand?

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices