Should escaped data be physically stored in MySQL

[bnelson333]

Hey all, new here. Got a question about escaped strings to MySQL. I'm using mysql_real_escape to clean up passed data. My question is, when I do that, should the data be stored physically in MySQL with the slashes or not?

 

That is, if the text is: joe's

Should that be stored in MySQL as joe's, or joe\'s

?

 

To make things more complicated, my dev box doesn't have magic quotes on, but the webhost does (and won't let me disable it), so I have to see if there's a working solution for both environments.

 

(when I use just mysql_real_escape on dev box, it stores just the ' in mysql, but when I move it to webhost, it stores \', and not sure which is right).

 

[trq]

No, the slashes should not show up in the database. They are only there to insure that your data can be used within queries properly.

 

You must double double escaping the data. this can often happen if you have magic quotes enabled on your server. You should check get_magic_quotes_gpc and if that returns true, use stripslashes prior to mysql_real_escape_string.

[mwasif]

To make things more complicated, my dev box doesn't have magic quotes on, but the webhost does (and won't let me disable it), so I have to see if there's a working solution for both environments.

Disable it using .htaccess (if you are using Apache).

[gizmola]

Let me start with magic quotes:  this is something that operates on input - get params, post, cookies.... and it's in essence running addslashes().

 

So to undo the effects of magic quotes, you simply need to run stripslashes on all those things.

 

Otherwise, mysql_real_escape_string is transparent.  Hopefully it's clear that the problem is that on production, magic quotes is gumming things up.

 

With that said, do you work for your ISP?  That decision may be outside of your control, but I certainly wouldn't give my money to a company with such a clearly antiquated configuration.  I just can not understand it when people are dictated how their environment should work by crappy isp's.  I mean magic quotes has been turned off by default for many years, and is officially deprecated and will be removed once and for all in php 6.

 

You might try this in the .htaccess

 

php_flag magic_quotes_gpc Off

or

php_value magic_quotes_gpc Off

[bnelson333]

Thanks for the replies, you have sorted me out.

 

Note - the webhost is for a client who has had the same webhost for a looooong time, he's happy with it, so I'm not going to change anything.

 

Per the manual on mysql_real_escape_string, it says is magic quotes is enabled on the server then I should use stripslashes first to avoid double escaping. That works for what I wanted to do, I just wanted to make sure I understood how it should look in the db.

 

Thanks!