We use mysql_real_escape_string() before inserting any data into our MySQL db for those data that the user can enter the data into a text field.
But when we retrieve data from MySQL db, then any character which was escaped with a back slash before being inserted into MySQL has that back slash added to the back of all bad characters. For example a line comes out this way:
this is a test\" line showing how mysql add\’ back slashes\"
So my question is how does one display the results of a MySQL SELECT without displaying the \ which was inserted due to usage of mysql_real_escape_string()?
One does either get rid of deprecated magic_quotes_gpc ini setting or clear data once it came to the script if it was spoiled with this setting.
Using get_magic_quotes_gpc() to check
mysql_real_escape_string() must be applied to any data that goes to the query. Not only data that the user can enter into a text field or even data that weren’t entered by user at all
1st, why should mysql_real_escape_string() be applied to every data before it is inserted into MySQL? I mean for example if the user chose a data from a drop down list or a Radio button then there is NO chance that a bad char may have been introduced.
2nd, I do not quite follow the 1st part of what you have written!
that’s wrong approach. these functions shouldn’t be used together.
WorldNews
I mean for example if the user chose a data from a drop down list
Because this function has nothing to do with user’s input. It is not for user input, but for SQL query. It is part of proper SQL syntax.
As I said above, this function should be used even if there was no user input at all.
Are you saying that any data before it is inserted into MySQL should be
sent to this function which will then both do th emysql_real_escape_string()
as well as remove the \ from before chars like "?
Now I am confused.
So u are saying not to use the function call that the other gent had provided.
Ok, so then how do you make sure that when data is retrieved from
MySQL chars like " or ’ do not have a \ before them?
At first you must understand that these symbols came not from mysql, but from the browser.
So, you have to eliminate it not then it coming from database but then it coming from the browser.
You can use a code like in this:
One note 2 WorldNews
At the moment you have already spoiled data in your database.
If there’s just test data and can be deleted - just delete it.
I there is something important, it must be cleaned out.
mysql_real_escape_string() will insert a SQL native backslash to string containing ’ or ", that this SQL native backslash will not exist/visible outside the database?
it is the magic quotes that adds a backslash to string with ’ or " even if the string comes right out of database is plain ’ " like O’connor ?
Example:–
first, string O’connor is stored into database with backslash by mysql_real_escape_string() – so it’s O’/connor inside database
then database feeds string as O’connor not O’/connor before processed by PHP.
finally, magic quotes adds a blackslash to O’connor and now become O’/connor, which is out put to browser.
i think it is an important knowledge to keep in mind, since we now must mysql_real_escape_string() again for even the datas retrieved from TABLE 1 which we will insert into TABLE 2.
because datas retrieved from TABLE 1 is just a plain ’ or " like O’connor, even they were escaped before by mysql_real_escape_string()
Quite contrary :).
there are 2 kinds of magic quotes - magic_quotes_gpc and magic_quotes_runtime
second one will act as you described, adding a backslash to the data retrieved from the database. But it almost always turned off by default. Can be turned off manually, using set_magic_quotes_runtime(FALSE) to be sure.
But we were talking of magic_quotes_gpc, which adds these garbage backslashes to the data that coming from the outside. So, corrected Example:–
first, string O’connor were posted from the form in the user’s browser.
magic quotes adds a blackslash to O’connor and now become O\'connor
mysql_real_escape_string() adds another slash for the each special character, and our poor fellow become O\\\'connor
Once this string goes to the database, one pair of slashes get stripped off, so, O\'connor again. And forever.
To make it right way we have to either eliminate item 2 by setting magic_quotes_gpc directive to off, or disable it’s mean trick by using stripslashes_deep code I posted above.
craqgerbil, we are talking about someone tampering with data from a drop down list to add a ’ which would cause a MySQL error for that submission, something that would affect them only.
So how do you go from creating a browser warning that MySQL could not add your data, due to you having added a ’ to an item from the drop down list to being able to issue a command to the MySQL such as “Drop database”???