User inputs, clean and sanitize before sending to db

Question

I've searched a lot of the questions here and I found that they either very old or suggesting using prepared statements PDO which I am not using. So I need your help please.

I have a small discussion/chat box where a user submit a message using a <textarea>

What I need is sanitize and filter the user input so it only accepts plain texts (e.g. no tags, no html tags, no scripts no links, etc). Also, it is important to allow line breaks.

Based on my reading I am doing the following in the following order:

1. trim()
2. htmlentities($comment, ENT_NOQUOTES)
3. mysqli_real_escape_string()
4. nl2br()

Is what I am doing is right? or I am missing something?

Also is there anything I have to do when echoing the data from the db?

really, appreciate your help and kindness

Answer 1

You also have another option. You can use prepared statements with mysqli

They aren't very difficult to learn and work a bit better than mysqli_real_escape_string() in that you don't need to worry about escaping every single variable that will be in your query. They are by nature "prepared" before they go into the database. There are other advantages to this as well, in that:

1. you do not need to addslashes() to be able to handle characters with apostrophes etc.

2. for large databases, they will considerably speed up your queries (much like PDO).

Here's how to do it:

You connect to the database by creating a new mysqli object like this:

$conn = new mysqli($host, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $dbc->connect_error);
    }

Next you want to convert your variables from your form.

Say you have a form field like this:

<input type="text" name="var1">

you can use htmlentities and trim together like so, and create your $var1 variable:

$var1 = htmlentities(trim($_POST['var1']));

Then you can create your transaction like this:

$stmt= $conn->prepare("insert into tablename (key1, key2) values (?,?)");
$stmt->bind_param("is",$var1, $var2);
$stmt->execute();
$stmt->close();

That's basically it. You do a query just like you normally would, but instead use the ? placeholders, assigning the datatype (above is i for integer, and s for string) and then bind them to your placeholders in the query.

That's basically it.

if you want to do it with a select with a variable, you use the normal select syntax and the same way with a ? with the variable, and then bind it. You can then bind your results into variables easily like so (assuming var3 is an integer):

$stmt= $conn->prepare("select var1, var2  from tablename where var3 = ?");
$stmt = bind_param("i", $var3);
$stmt->bind_result($var1, $var2);
$stmt->execute();
$stmt->close()

and then you can fetch your variables using this

$stmt->fetch();

or if your query brings back multiple rows

while ($stmt->fetch() {
    echo $var1 . $var2;
}

nl2br() is used for output, you don't need to worry about input; it can be stored in the database as \n, and when you need it spits it out as breaks. If one of these variables needs the new lines turned into <br/> tags, you can, as you suggest use nl2br() on the variables (note this adds no security, but as you said you needed it), like so

echo nl2br($var1, false);

you can also use trim() and htmlentities() on this if it is being echoed into, say, a form input field and you don't want your form to break if there are html characters in the output.

Answer 2

Your question can lead me to build a full project with many features ;) lol

Before we start with out steps, we need a dummy (test) database for this scenario. We call out database chatbox with table called chat. You can simply create it by executing the following sql statement in your MySQL test environment:

CREATE TABLE `chat` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `msg` VARCHAR(200) NOT NULL DEFAULT '0',
    `user_id` INT(11) NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)
ENGINE=InnoDB
;

Now you can go a head and follow the steps here:

Step 1: Create project folder in your web server. Build database connection based on PDO and call it dbConnect.inc.php:

<?php
// check if PDO driver not available
if (!defined('PDO::ATTR_DRIVER_NAME'))
    echo 'PDO driver unavailable <br />';

// database configuration
$dbHost = "localhost";
$dbPort = "3306";
$dbName = "chatbox";
$dbUser = "root";
$dbPass = "";
$strDSN = "mysql:host=$dbHost:$dbPort;dbname=$dbName";

// database connection
try
{
    $dbConn = new PDO($strDSN, $dbUser, $dbPass);
    //Activate following line to view all error messages 
    $dbConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e)
{
    die("Could not connect to the database $dbName, error info: <br />"
        . $e->getMessage());
    exit();
}

I will test this works before go to next step. Btw the prepared method does not require mysqli_real_escape_string().

I have used PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION in stead of if statements, this method will give you useful error details while development the project. You will find out which method is more practical for getting error message while your development process of the project.

Step2: Create a file call filter.inc.php:

<?php
// filter for input
function filterInput($content)
{
    $content = trim($content);
    $content = stripslashes($content);

    return $content;
}

//filter for viewing data
function filterOutput($content)
{
    $content = htmlentities($content, ENT_NOQUOTES);
    $content = nl2br($content, false);

    return $content;
}

This file contain a function to filterInput to sanitize or filter your input content for comments or other inputs. And filterOutput that effect your data view.

All depending on your strategy and what you need, like if you need to allow people post url's or email address, should url and email become active link or only viewed as text etc. that way you can define which filter should be use for your content input and which filter should be used for you content output.

You can add or delete extra features to functions. There are many features for text input and output, you can test those individually and evaluate it, and even extend the filter function or create your own function.

Final step 3: Now we put the puzzles together in our index.php file:

<!DOCTYPE html>
<html>
<head lang="en">
    <meta charset="UTF-8">
    <title>Chat box</title>
</head>

<body>
<?php include './dbConnect.inc.php'; ?>
<?php include './filter.inc.php'; ?>

<h1>Chat box</h1>

<p>
    <?php
    // this is dummy user id, but use the id over user id when login or the way you want
    // this is only example
    $user_id = 1;

    if (isset($_POST["msg"]))
    {
        $msg = filterInput($_POST["msg"]);
        $sql = "INSERT INTO chat "
            . "(msg, user_id) "
            . "VALUES "
            . "(:msg, :user_id)";

        $stmt = $dbConn->prepare($sql);
        $fieldsArr = [':msg' => $msg, ':user_id' => $user_id];
        $stmt->execute($fieldsArr)
        // refresh page after insert
        header("Location: " . $_SERVER['REQUEST_URI']);
    }
    ?>

<form action="index.php" method="post">
    <textarea name="msg" id="msg" required></textarea>
    <input name="submit" type="submit">
</form>
</p>

<p>Comments</p>

<p>
    <?php
    $sql = "SELECT * FROM chat WHERE user_id = (:user_id);";
    $stmt = $dbConn->prepare($sql);
    $fieldsArr = [':user_id' => $user_id];
    $stmt->execute($fieldsArr)

    while ($result = $stmt->fetch())
        echo "<h3>" . filterOutput($result['msg']) . "</h3>";

    $dbConn = null;
    ?>
</p>

</body>
</html>

This is to demonstrate how things works. You have insert, select statement as example and filter functions. You can make tests, extend it the way you like or further develop your own project.

Here is screen shot of the chatbox example I made:

Answer 3

Your code looks fine, if you don't want to prepare statements then escaping is the next best thing. And when you echo it should be straightforward, it's only plain text.

Answer 4

filter_input could be another one you are looking for. It can save you hours from writing sanitizing and validation code. Of course, it does not cover every single case, but there is enough so that you can focus more on specific filtering/validating code.

Though it is strongly recommended to use prepared statements with PDO/mysqli. But sometimes it is not so easy to convert the whole project in the tail end of the project. You should learn PDO/mysqli for your next project.

$comment = filter_input(INPUT_POST, 'comment', FILTER_SANITIZE_STRING);

There are different Types of filters for you. You can select depending on your needs. You can also use filter_has_var to check for variable set.

Answer 5

First, keep the text logical and clean:

trim() -- OK
htmlentities($comment, ENT_NOQUOTES)  -- No; do later
mysqli_real_escape_string()  -- Yes; required by API
nl2br()  -- No; see below

The logic behind those recommendations: The data in the database should be just plain data. Not htmlentities, not br-tags. But, you must do the escape_string in order to pass data from PHP to MySQL; the escapes will not be stored.

But... That is only the middle step. Where did the data come from? Older versions of PHP try to "protect" you be adding escapes and other junk that works OK for HTML, but screws up MySQL. Turn off such magic escaping, and get the raw data.

Where does the data go to? Probably HTML? After SELECTing the data back out of the table, then first do htmlentities() and (optionally) nl2br();

Note, if you are expecting to preserve things like <I> (for italic), you are asking for trouble -- big trouble. All a hacker needs to do is <script> ... to inject all sorts of nastiness into your web page and possibly your entire system.