Receiving error when ampersands present

[OgreOne]

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

[BKBK]

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>

[OgreOne]

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"

[OgreOne]

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?

[BKBK]

The value encoding="ISO-8859-1" jumped out at me. What happens when you do the usual encoding="UTF-8"?

[OgreOne]

I think it most likely has something to do with the encoding, but the other way around.  I have another XML file on the same server that works correctly (created another using it's pattern) and it uses ISO-8859-1 and I do not have an error when inserting in the db and parsing the XML.

So paying attention to the encoding, When I parse the XML with the code above, and when the XML document is saved the parser actually writes UTF-8 instead of ISO-8859-1.  I do not know how CF is defaulting to UTF-8 or why.  What in my coding above tells it to, when clearly it says that it shoiuld print ISO-8859-1?

This has me baffled.Something is dynamically changing the way I save stuff.  I am assuming CF since both XML pages reside on the same server and both are parsed.

[BKBK]

So paying attention to the encoding, When I parse the XML with the code above, and when the XML document is saved the parser actually writes UTF-8 instead of ISO-8859-1.  I do not know how CF is defaulting to UTF-8 or why.  What in my coding above tells it to, when clearly it says that it shoiuld print ISO-8859-1?

You don't have to tell Coldfusion anything. As you should expect, Coldfusion defaults to its default encoding, which is UTF-8.

[Guest · Accepted solution]

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

[OgreOne]

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!