Using COALESCE but the column is still updated

Question

The objective is, if $_POST['password'] is empty, do not update the password column but it is.

try {
        $stmt = $db->prepare('UPDATE users SET email = :email, password = COALESCE(NULLIF(:password, ""), password) WHERE user_id = :user_id');
        $stmt->bindValue(':user_id', (int) $_POST['user_id'], PDO::PARAM_INT);
        $stmt->bindValue(':email', $_POST['email'], PDO::PARAM_STR);
        $stmt->bindValue(':password', password_hash($_POST['password'], PASSWORD_BCRYPT), PDO::PARAM_STR);
        $stmt->execute();
        $_SESSION['success'] = 'User updated successfully.';
        header('Location: '.DIRADMIN.'user.php');
        exit;
    } catch(PDOException $e) {
        $_SESSION['error'] = 'An error occurred while updating the user.';
        error_log($e->getMessage(), 0);
    }

Any thoughts?

Edit: In my example I am using COALESCE to return the first non-NULL string. So if NULLIF returns NULL because :password equals to "", then the first non-NULL string should be the value of the column password.

Answer 1

The problem is that you're binding :password to the result of password_hash. When you hash an empty password, the result is not an empty string. Try:

$stmt->bindValue(':password', 
                 empty($_POST['password']) ? '' : password_hash($_POST['password'], PASSWORD_BCRYPT), 
                 PDO::PARAM_STR);

Answer 2

I personally would not delegate this kind of check to your database code; rather I would probably use php before writing to the database; that way you avoid making an unnecessary database connection.

For example something like:

if (isset($_POST['password']) && !empty($_POST['password'])) {
    // write to the database
} else {
    // some error logic to flash the error back to the user
}