Shaun Mccran

My digital playground


Quotes being changed to question marks using cfsavecontent and MySql

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:

view plain print about
1<cfprocessingdirective pageencoding="iso-8859-1">
2<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.

view plain print about
1<cfsavecontent variable="htmlContent">
2Html template code
3<body> etc </body>
5<cfquery datasource="#application.dsn#">
6INSERT INTO table (fields)
7VALUES ('#htmlContent#');

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.

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
jerry's Gravatar The cf save content and the my sql is going to be one of the most exciting ares in any programming. In my project I have been using the following procedures in the back end which helped me a lot to do more functions come alive.
# Posted By jerry | 03/11/15 01:13
Back to top