To escape for the purposes of having your queries made successfully, and to prevent SQLi (SQL injection)/stored and/or reflected XSS, it's a good idea to go with the basics first, then make sure nothing gets in that can be used for SQLi or stored/reflected XSS, or even worse, loading remote images and scripts.
For example:
<?php
$name = mysqli_real_escape_string($conn, $_POST['name']);
$comments = mysqli_real_escape_string($conn, $_POST['comments']);
$name = htmlspecialchars($name);
$comments = htmlspecialchars($comments);
$insert_sql = "INSERT INTO tbl_comments ( c_id, c_name, c_comments ) VALUES ( DEFAULT, '" . $name . "', '" . $comments . "')";
$res = mysqli_query($conn, $insert_sql);
if ( $res === false ) {
}
?>
// Assume we're in a table with each row containing a name and comment
<?php
$res = mysqli_query($conn, "SELECT c_name, c_comments FROM tbl_comments ORDER BY c_name ASC");
if ( $res === false )
while ( $row = mysqli_fetch_array($res, MYSQLI_BOTH) ) {
echo "<tr><td>" . $row['c_name'] . "</td>";
echo "<td>" . $row['c_comments'] . "</td></tr>";
echo "<tr><td>" . htmlspecialchars_decode($row['c_name']) . "</td>";
echo "<td>" . htmlspecialchars_decode($row['c_comments']) . "</td></tr>";
}
mysqli_free_result($res);
mysqli_close($conn);
?>
In most cases, you wouldn't want to go way overboard sanitizing untrusted user input, for instance:
<?php
$my_input = htmlspecialchars( strip_tags($_POST['foo']) );
?>
This will junk a lot of input you might actually want, if you're rolling your own forum or comments section and it's for web developers, for example. On the other hand, if legitimate users are never going to enter anything other than text, never HTML tags or anything else, it's not a bad idea.
The take-away is that mysqli_real_escape_string() is not good enough, and being overly-aggressive in sanitizing input may not be what you want.
Be aware that in the above example, it will protect you from sqli (run sqlmap on all your input fields and forms to check) but it won't protect your database from being filled with junk, effectively DoS'ing your Web app in the process.
So after protecting against SQLi, even if you're behind CloudFlare and take other measures to protect your databases, there's still effectively a DoS attack that could slow down your Web App for legitimate users and make it a nightmare filled with rubbish that some poor maintainer has to clean out, if you don't take other measures.
So aside from escaping your stings, and protecting against SQLi and stored/reflected XSS, and maliciously loaded images or JS, there's also checking your input to see if it makes sense, so you don't get a database full of rubbish!
It just never ends... :-)