Welcome to WebmasterWorld Guest from 54.144.246.252

Forum Moderators: coopster & jatar k

adding html to mysql

   
12:00 am on Nov 2, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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?
12:22 am on Nov 2, 2012 (gmt 0)

WebmasterWorld Senior Member vincevincevince is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
10:46 pm on Nov 2, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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?
4:03 am on Nov 3, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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.
7:35 am on Nov 3, 2012 (gmt 0)

WebmasterWorld Senior Member vincevincevince is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
8:28 pm on Nov 3, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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?
10:11 pm on Nov 3, 2012 (gmt 0)

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
1:14 am on Nov 4, 2012 (gmt 0)

WebmasterWorld Senior Member vincevincevince is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
7:36 pm on Nov 4, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Thanks vincevincevince, so essentially, the issue is the same as creating a variable, you have to escape the '
1:16 am on Nov 5, 2012 (gmt 0)

WebmasterWorld Senior Member vincevincevince is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

Featured Threads

Hot Threads This Week

Hot Threads This Month