note 119571 added to mysqli.real-escape-string

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 // Assume this is a simple comments form with a name and comment. $name = mysqli_real_escape_string($conn, $_POST['name']); $comments = mysqli_real_escape_string($conn, $_POST['comments']); // Here is where most of the action happens. But see note below // on dumping back out from the database // We should use the ENT_QUOTES flag second parameter... $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 ) { // Something went wrong, handle it } // Now output page showing comments ?> // 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 ) // Something went wrong // Or as you like... while ( $row = mysqli_fetch_array($res, MYSQLI_BOTH) ) { // This will output safe HTML entities if they went in // They will be displayed, but not interpreted echo "<tr><td>" . $row['c_name'] . "</td>"; echo "<td>" . $row['c_comments'] . "</td></tr>"; // BUT, if you make this mistake... echo "<tr><td>" . htmlspecialchars_decode($row['c_name']) . "</td>"; echo "<td>" . htmlspecialchars_decode($row['c_comments']) . "</td></tr>"; // ... then your entities will reflect back as the characters, so // input such as this: "><img src=x onerror=alert('xss')> // will display the 'xss' in an alert box in the browser. } 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... :-) ---- Server IP: 72.52.91.14 Probable Submitter: 58.6.48.56 ---- Manual Page -- http://php.net/manual/en/mysqli.real-escape-string.php Edit -- https://master.php.net/note/edit/119571 Del: integrated -- https://master.php.net/note/delete/119571/integrated Del: useless -- https://master.php.net/note/delete/119571/useless Del: bad code -- https://master.php.net/note/delete/119571/bad+code Del: spam -- https://master.php.net/note/delete/119571/spam Del: non-english -- https://master.php.net/note/delete/119571/non-english Del: in docs -- https://master.php.net/note/delete/119571/in+docs Del: other reasons-- https://master.php.net/note/delete/119571 Reject -- https://master.php.net/note/reject/119571 Search -- https://master.php.net/manage/user-notes.php