Extra Sanitisation When Dealing With Fetched Data

Question

I have some data being fetched from a database (that is sanitised on the way in to the database with a prepared statement). When using this data I thought I'd have to use the htmlspecialchars() function, but having used a password which contained special characters this broke the code because it obviously turned the < into an html entity.

Am I correct in thinking if the code is sanitised going into the database, and isn't being physically outputted on to the html page, I don't have to add any extra security to what I have below?

I initially wrapped the code inside the while loop in the htmlspecialchars() e.g. $db_id = htmlspecialchars($row['ID']); which is how I found it was breaking the code.

if (isset($_POST['login'])) {

    $email = $_POST['email'];

    $stmt = $connection->prepare("SELECT * FROM users WHERE email = ? ");
    $stmt->bind_param("s", $email );

    $stmt->execute();

    $result = $stmt->get_result();

    // assign columns from the database to variables
    while ($row = mysqli_fetch_array($result)) {

       $db_id = $row['ID'];
       $db_firstname = $row['firstname'];
       $db_email = $row['email'];
       $db_password = $row['password'];

    }

    $stmt->close();
    $connection->close();

    // code will go here that checks if the email and password match and then create a $_SESSION

}

Answer 1

Your current code is correct and safe. You don't need anything else.

There is no such thing as input sanitization. Prepared statements do not sanitize data. They protect against SQL injection because the data is sent separately from the query.

htmlspecialchars() protects against XSS. You use it when you output the data into HTML. Do not use it on input as it will damage your data.

Never modify passwords in any way!. Use password_hash() directly on the input and save that into the database.