vincevincevince - 1:14 am on Nov 4, 2012 (gmt 0)
It's fine to put HTML straight into MySQL through the CMS. But only if the CMS is escaping it 'on the way'.
MySQL commands are long text strings split up by various delimiters, including ' and ` marks:
INSERT ... VALUES ('your string')
If you fail to escape, and try inserting something with quote marks, you will have problems (e.g. your string = Fred's car):
INSERT ... VALUES ('Fred's car')
MySQL ends the string at "Fred"; and then dies with an error when it sees "s car'" after it.
When you escape, then the ' in Fred's becomes \':
INSERT ... VALUES ('Fred\'s car')
There are also other characters and edge cases which need to be escaped. The use of mysql_real_escape_string() ensures that you escape exactly what needs to be escaped in the current character set.
When MySQL retrieves data, the escaping is 'not there'. In fact, it was 'decoded' as the data was inserted and never stored. So, you get back what you had before you escaped it.
Using mysql_real_escape_string() twice (or a combination of that and something else which escapes it) just makes a mess:
Fred's car -> Fred\'s car -> Fred\\\'s car
Typically, when you then edit and save it, the damage multiplies:
Fred\\\'s car -> Fred\\\\\\\'s car
(Yes, MySQLi sidesteps much of this, but it's a lot of work if your application is already built and using MySQL)
Hence: my recommendation is to see if you can push "Fred's car" through successfully without adding any escaping yourself. If it works fine, then the CMS is already doing all the escaping you need (the CMS may even be using MySQLi behind the scenes). If it dies with an error, add one layer of escaping, etc.
And yes: absolutely, ensure that magic_quote is OFF. And if your CMS provider says it has to be on, start finding a new CMS urgently.