Issue with special characters not being converted properly

[Linkman]

I have an issue where the function mysqli_real_escape_string, which is necessary causes funky behavior with line breaks. For example, when I store a message that looks like this:

$message = mysqli_real_escape_string("This is line 1
This is line 2

This is line 3");

It stores it like this in the database:

This is line 1\r\nThis is line 2\r\n\r\nThis is line 3

That’s fine, but when I retrieve it, like this:

$message = nl2br($data_from_db_table['message']);

The output is:

This is line 1rnThis is line 2rnrnThis is line 3

I am aware that to have special characters escaped, they need to be in double quotes, like: “\r\n” but since it’s being done by the mysqli_real_escape_string function, I’m not sure the right way to get the output to display properly, which would be:

This is line 1
This is line 2

This is line 3

Any help would be appreciated. Thanks in advance!

[phdr]

The symptom you are seeing is (usually) because data has been escaped twice. You should not see the \ characters in the database table. Where is this input data actually coming from and what php version are you using? Php’s magic_quotes, which could cause this, were removed in php5.4, nine years ago.

Next, if you are still using any _escape_string() function, you need to switch to prepared queries, which will provide protection for all data types, not just strings, will simplify the code and query syntax, and switch to the much simpler and more consistent PDO extension.

[Linkman]

Okay I was able to get it. I was using MySQLi::prepare() which I wasn’t aware did the security check for you, so my using the escape_string function was just doing it twice causing the problem. Just removing the escape_sting function fixed it. I’m pretty new to MySQLi. Thanks again for the help!