How to avoid SQL injection while giving the line break in textarea in PHP and MySQL?

Question

What I want is when user give line break in description box ie: textarea in HTML then all the data is stored securely in SQL database, but if users give links then links becomes also text but when user give enter or line break in description textbox then it saves in the database and while retrieving it from the database the line break will show again.

<textarea name"description" id="description" rows="4" cols="50">
//In The text area the user enter the text. Here I want if user gives html links then links becomes text and if user give line break then line break store in the database and I can get back as it is how user submitted the form. Also I want to store data securely for avoiding sql injection but dont have the exact idea how to do it.
</textarea>

$text = $_POST['description'];
$text = htmlentities($text); // Here I want to remove html entities for avoiding sql injection
$text = mynl2br($text);
// Now Fire Insert Query All the data save but while retrieving the data I am not able to get line break and turning links to the text.


function mynl2br($text) { 
   return strtr($text, array("\r\n" => '<br />', "\r" => '<br />', "\n" => '<br />')); 
} 

This is what I am doing it but the links is still the links and when I retrieve the text is showing without line break. All the text in one line or one paragraph.

Kindly suggest what I am doing wrong in the above code. And how can I avoid sql injection and can store and retrieve data securely.

Answer 1

To keep the new lines you have to keep the original line breaks, if you replace them you either have to replace them back or you have to use nl2br only when displaying the text on page (recommended) not before storing or retrieving it for editing.

Same applies to links too, do not change them before storing, only replace it when you are displaying it on the page, that way you won't have problem editing it again and you can modify the link generation anytime since you don't store the result but the original.

To avoid SQL injections you don't want to use these magic functions (htmlentities, strip_tags, magic_quotes, etc - there are plenty, but none of them is perfect) - the best solution is to use prepared statements: https://dev.mysql.com/doc/apis-php/en/apis-php-mysqli.quickstart.prepared-statements.html

It solves the problem by giving you placeholders to the values so the user input can't modify the query.

Answer 2

There's a number of false assumptions in this question that need to be addressed.

1. You should never inject user-supplied data into a query. You must escape this data, and the easiest way to do that is with prepared statements using placeholder values. That is your query looks like INSERT INTO x (a,b) VALUES (?,?) and then you bind against each place-holder. The SQL driver takes care of the rest, you don't need to worry. As an immediate bonus you won't spend any more time tracking down simple syntax errors in your queries because they'll be very easy to read and mistakes will be obvious.
2. You should never display user content without escaping appropriate for the context it's being displayed in. That is for HTML you use HTML escaping functions like htmlspecialchars to avoid XSS (cross-site scripting) attacks.
3. You should never pre-escape content when inserting into the database. You must insert as raw and neutral as possible. If you pre-escape for HTML you run the risk of double-escaping, leading to things like &amp& showing up in your content if you slip up. Remember, not everything is HTML. Sometimes it's JSON. Sometimes it's CSV. Sometime's it's an email subject line. Each has their own particular escaping method.
4. If you're feeling like you're in over your head here, and it's easy to be overwhelmed because security is a complicated and many-faceted concern, you should start with a known-secure foundation and build up from there using best-practices. Core PHP is not enough, you need a framework like Laravel, Fat-Free Framework or any popular, proven, community-supported framework out there that provides protection against these concerns out of the box.

If you take all of these into account your problem won't exist.

When testing your code it's a good idea to have a bunch of deliberately abusive snippets you can copy-paste into your input fields to test for common mistakes.

Test your HTML escaping where a failure makes the whole screen turn red:

<div style="position:absolute;top:0;left:0;right:0;bottom:0;background:red">Uh oh</div>

Test for SQL injection issues:

It's not so <strong>"Bad"</strong>!

Test for UTF-8 support, both 3-byte "symbols" and 4-byte emoji:

Do you want to build a ☃? How about a ❄️☃️?

I also create random strings of length 255, 256, 1024, and 65535 to see what happens to the page formatting when inserting long, unbroken strings of letters. Many pages will utterly explode unless this sort of testing is done explicitly. Some applications will crash with long input due to pathologically bad regular expressions, or will fail to detect over-length content before inserting and hit an unexpected truncation error.

Frameworks often have ways of expressing constraints on your form and for cleanly handling any input validation errors before exploding with a "SQL error" screen.