One of the tables in an online application stores entire html templates in a blob field, this data is pulled out and used to build emails, replacing specific keywords.
I recently needed to update some of this data, but there is not an administration system for it, so a manual SQL script would have to do.
The problem I encountered was that creating a html template as a variable using cfsavecontent and inserting it into MySql was causing all the ' to appear as ?. I read a blog entry based on Coldfusion MX and setting the character encoding in your coldfusion administrator, for each datasource. Or you could do it in the template, like this:
<cfcontent type="text/html; charset=iso-8859-1">
This didn't seem to work at all, in fact I found that it would error whilst trying to insert the cfsavecontent variable using cfqueryparam. There seemed to be some sort of encoding issue between the two tags.
Html template code
<body> etc </body>
INSERT INTO table (fields)
This didn't work either with Cfqueryparam, or as a non param input. It turns out that you simply need to escape the characters (double them, or use character representations). IE don't use a single quote, use two . In this way the MySql encoding treats the character as a character, rather than a potential command. I still think that this is something that can be fixed by setting the encoding type, but in this case there was a far simpler solution.