CODEWITHSUNDEEP
phpmysqli
coder101
coder101

Reputation: 1605

MYSQLi real escape function displaying new lines and carriage returns

i have a text area from which when i try to escape and sanitize through MYSQLi's real_escape function and nl2br and simply output is giving me odd results.

my php code:

 <?php
 $db = new mysqli('localhost', 'user', 'pass', 'demo');

 if($db->connect_errno > 0){
 die('Unable to connect to database [' . $db->connect_error . ']');
 }

 $postText = nl2br($db->escape_string($_POST['posting']));
  ?>

the odd output is :

 i love this\r\n\r\nand this is gonna be funn.,

and strangely when i just use nl2br without real_escape is giving the output fine which obviously can't move ahead with as i cant trust user"s input.

Please help on this..

Upvotes: 3

Views: 1391

Answers (2)

Your Common Sense
Your Common Sense

Reputation: 158005

Yes, it does.
This function's output is not intended to be printed out. But to format SQL string literals only.
Please note that this function is not intended to "sanitize" whatever input either. Please refer here for the details

So, you should never use these 2 functions together.

  • use escape_string to format SQL strings that you are going to place into query dynamically.
  • use nl2br only when printing your text onto HTML page

According to your question in the comments, there should be no case when you have to print your string back immediately.
Because after every POST request your PHP should response with Location: header to tell browser reload the page. Upon such reload you can read your data bask from database and print it out.

Upvotes: 0

Ja͢ck
Ja͢ck

Reputation: 173662

You should only apply SQL escaping when the output is going to be used in a SQL query.

  • If you need to output the value onto a page, you use htmlspecialchars() or htmlentities().

  • If it's going to be used in a JavaScript literal, use json_encode().

  • Etc.

In short, each context has their own escaping; don't mix them up.

Also, don't use nl2br() when you write it into the database; rather, apply it after you fetch it from the database.

Upvotes: 2

Related Questions