mysql_real_escape is ruining my life!

Davidc316

ANTI 'RTFM' DISCLAIMER: I'm absolutely sure other folk have ran into this problem, but I've looked around and can't find any threads which address this. I've also looked at the manual and had no luck so please don't lecture me about reading the ****** manual.
END OF DISCLAIMER

Ok, here's the problem...

In the past if somebody filled out a textarea in a form and submitted it (with a view to inserting it into MySQL and then later displaying on a webpage) I would simply use nl2br (along with a few other functions) to get the thing displaying new lines properly when displaying on the website. So, I would use nl2br when calling the info from the database to display on the page. It was good. It worked. Life was fine.

Then one day I discovered that I need to use mysql_real_escape_string before inserting any user submitted variables into mySQL.

But now, thanks to mysql_real_escape_string() there are tonnes of \r\n's all over my database and all over my discussion forum on my website.

As far as I can tell mysql_real_escape_string is not capible of living in harmony with nl2br.

Is there any way I can fix this problem without using string replace functions all over the place?

Thanks!

website maintenance

Davidc316

Can I suggest an answer to my own post?

Would it be possible to replace mysql_real_escape_string with a combination of addslashes and htmlentities?

So, I'm proposing that running variables through addslashes and htmlentities (and stripslashes) would make them safe.

Does anyone agree with that?

NogDog

mysql_real_escape_string() can coexist just fine with nl2br(). Without knowing the exact sequence of events involved in your situation, I have no idea what to suggest to help you out. It would also help to know whether or not your PHP configuration has magic_quotes_gpc() enabled, as if it is, it could be the root source of your problems.

In general, what I would recommend is:

If magic_quotes_gpc is enabled, first do a stripslashes() on the input data.
Use mysql_real_escape_string() to sanitize the data prior to using it in your SQL.
Do NOT use nl2br() to store the data in the database, but rather only use it when outputting the data to the client after retrieval from the database (along with any other filtering needed such as htmlentities()).

Davidc316

Well, that was such a good response that I don't even feel there's a need for me to post any more info about what I do with my variables. Thank you!

But, can I just ask one question before I hit the resolved button- Is it stupid, pointless and a complete waste of time to use both mysql_real_escape AND addlashes on a variable?

(I've heard it said that real_escape_string does everything that addslashes does, so if that's true then it means that I am adding slashes twice, which could cause the problems)

NogDog

Davidc316 wrote:
Well, that was such a good response that I don't even feel there's a need for me to post any more info about what I do with my variables. Thank you!

But, can I just ask one question before I hit the resolved button- Is it stupid, pointless and a complete waste of time to use both mysql_real_escape AND addlashes on a variable?

(I've heard it said that real_escape_string does everything that addslashes does, so if that's true then it means that I am adding slashes twice, which could cause the problems)

That is correct: you do not want to use both. If using a MySQL database, mysql_real_escape_string() should be the preferred choice.

Davidc316

Thanks a lot! All I need to do now is find the Thread Resolved button.

laserlight

All I need to do now is find the Thread Resolved button.

Look for a bar near the top (under the bar with "Search") with a "Thread Tools" tab.