how to handle special characters in data

[teedoff]

Hi I have a dynamic page that doesnt validate because I have special characters such as "&" in the data. I was wondering if I should do a find and replace of all those ampersand signs and replace them with the safe "&" code. Thanks for any help or suggestions in advance!

[griswolf]

How is this a MySql issue?

Yes, I would just go ahead and replace the HTML/XML special characters. There are utilities available that can do this 'semi-automatically' for you.

[teedoff]

How is this a MySql issue?

Its not??? Well then where would you suggest I post this?

I already replace the characters IN my html code, I was asking about the same characters within my database thats called dynamically. So I posted in the MySQl forum.

[griswolf]

OK, I didn't correctly read your mind about 'dynamic page'.

I I were posting your question in this forum, I hope I'd have thought to ask it like: Is there a way to have MySql convert HTML entities (such as '&' --> '&') for use in a dynamic web page?.

Moving on: A quick look at the MySql docs doesn't show anything very useful (you can apparently write a function to do something like this, but I prefer pushing the work off the database for this kind of thing: Separation of concerns).

Depending on what happens between the database and the web page, you may find that you can pass the data out of the database through a 'clean this for html use' function. PHP has two: http://php.net/manual/en/function.htmlspecialchars.php; http://us2.php.net/manual/en/function.htmlentities.php Python has one also: http://docs.python.org/library/cgi.html#cgi.escape I bet Java and Perl do too. (Heck, I could probably do it in sed without too much pain: Not a particularly hard task, just a bit picky)

The alternative is to store the already html-ready text directly in your database. Downsides: Viewing it outside a web browser; editing it from the web interface.

[teedoff]

lol no problem I guess I assumed when I said dynamic page, it meant it was a page that is pulling content from a database. I wasn't very clear in my question.

I'll check out the links you provided. Thanks for your help!

[teedoff]

oh sorry, I just noticed you suggested PHP functions...lol Im using coldfusion..I think I might see if I can find a CF solution. I'm sure there is one similar to the ones you posted.

Thanks again for your help!

[griswolf]

Google says "use XMLFormat" (after a couple of less helpful links)
http://stackoverflow.com/questions/2187991/how-can-i-escape-html-character-entities-when-using-coldfusion-function-xmlformat