adding html to mysql

[whatson]

In my CMS I have a field that I want to use to add html to the database.
I am facing some issues, and it seems to be quite intermittent.
It is adding slashes, e.g.
<div class=\"heading\">

I am using mysql_real_escape_string but I am not sure if that is helping.
Can someone please explain what are the issues with adding html to the database? And how do you overcome these?

[vincevincevince]

First ensure that magic_quotes are turned firmly OFF in your PHP settings (phpinfo()) will show you this.

Second figure out if you are escaping twice: the first escape turns " into \" (which is right, and prepares it for MySQL inserting). The second escape turns it into \\\", which then 'decodes' on retrieval to \".

If all else fails (and I hope it doesn't), use stripslashes on retrieval. But that's messy.

[whatson]

right, sometimes it is /// so I must be escaping twice somewhere. But how can that be?
In theory should I even need mysql_real_escape_string?

[rainborick]

My philosophy is that you should use mysql_real_escape_string on any data that comes from the HTTP request via $_POST or $_GET that will be used in a MySQL query. This does require close attention to detail since you must use stripslashes() on the data whenever you retrieve it from the database. So you need to know which fields contain data that is being escaped.

[vincevincevince]

You may have escaping happening at another layer of your application, e.g. the CMS may be using a database object which already escapes values.

Take out all escaping that you've added, and then do a test - insert '" and then read the database directly to see whats been inserted.

a) If you've inserted '" and it's stored as '" then it's being perfectly escaped already and you need add nothing.
b) If you've inserted '" and it's been stored with \ marks, then you're already escaping twice and you need to find one to remove.
c) If you've inserted with '" and it's not inserted (error/silent fail) then you need to escape, once.

[whatson]

Ok, starting to get a bit confused.
1. What is the issue with injecting straight html into mysql through my CMS? Why does mysql reject this?

2. What is the best way to get the html straight into the mysql through the CMS?

[swa66]

The best way ?

1. magic_quotes OFF -> it's evil.
2. stop using the mysql interface and use the mysqli (note the i) interface
3. use prepared statements instead of messing with escape sequences to stop mysql from eating up data as if it were commands.

[vincevincevince]

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.

[whatson]

Thanks vincevincevince, so essentially, the issue is the same as creating a variable, you have to escape the '

[vincevincevince]

Yes, essentially. There is more involved, but if you use mysql_real_escape_string() all the 'more' will get taken care of.