homepage Welcome to WebmasterWorld Guest from 54.204.215.209
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
adding html to mysql
whatson




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

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




msg:4514873
 12:22 am on Nov 2, 2012 (gmt 0)

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




msg:4515224
 10:46 pm on Nov 2, 2012 (gmt 0)

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




msg:4515297
 4:03 am on Nov 3, 2012 (gmt 0)

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




msg:4515321
 7:35 am on Nov 3, 2012 (gmt 0)

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




msg:4515439
 8:28 pm on Nov 3, 2012 (gmt 0)

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




msg:4515468
 10:11 pm on Nov 3, 2012 (gmt 0)

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




msg:4515490
 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.

whatson




msg:4515649
 7:36 pm on Nov 4, 2012 (gmt 0)

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

vincevincevince




msg:4515695
 1:16 am on Nov 5, 2012 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved