output data from database using htmlspecialchars() that has been filtered using filter_input()

Question

I have found from different blogs that it is strongly recommended to use htmlspecialchars() to output any data on screen to be safe from XSS Attack.

I am using filter_input() to filter any data that comes from user before inserting into database. filter_input() convert special characters like ' to ' and saved it that way,like

 I'm going to shopping with Molly's sister;Dolly

.My question is

How can I print(output) apostrope or quotes and specific special characters to users screen using htmlspecialchars so that the output would be user friendly

I have tried to use htmlspecialchars($post,ENT_NOQUOTES);,but it gives me same copy of data that is stored in database.If I don't use htmlspecialchars(),just $post gives me expected result,which I think is vulnerable to XSS Attack

Thanks for your time,and look forward to get help from peers.

EDIT

I got suggestions to use htmlspecialchars_decode() or html_entity_decode() on answer,but (https://stackoverflow.com/users/1338292/ja͢ck) and some other suggested not to use these functions to output data on screen.

Please be informed that I am using prepared statement and parameterized query.But I don't want to keep any security holes,that's why filtering data before sending into database.

As I have used filter_input() to filter data before sending to database,is it safe to output data directly($post=$posted_data;) from database without using htmlspecialchars?

If I must need to use htmlspecialchars to output data,then how can I do it in this case?

Code Sample

 $stmt1=mysqli_stmt_init($connect_dude);

 /*Inserting into database*/

 if(isset($_POST['titlex']) && isset($_POST['pricex'])  && isset($_POST['detailx'])){
  $tit=filter_input(INPUT_POST,'titlex',FILTER_SANITIZE_STRING);
  $pri=preg_replace('#[^0-9]#','',$_POST['pricex']);
  $det=filter_input(INPUT_POST,'detailx',FILTER_SANITIZE_STRING); 

  $query2="INSERT INTO `hotel1`.`dine` (user_id,title,price,detail) VALUES (?,?,?,?)";

    mysqli_stmt_prepare($stmt1,$query2);
    mysqli_stmt_bind_param($stmt1, "isis", $logged_id, $tit, $pri, $det);
    mysqli_stmt_execute($stmt1);    
 }

 /*Get Data from DB*/

 $query1="SELECT id101,title,price,detail FROM `hotel1`.`dine` WHERE user_id=?";

    mysqli_stmt_prepare($stmt1,$query1);
    mysqli_stmt_bind_param($stmt1, "i", $user_idx);
    mysqli_stmt_execute($stmt1);
    mysqli_stmt_store_result($stmt1);
    mysqli_stmt_bind_result($stmt1, $id101, $title,$price, $detail);

    while(mysqli_stmt_fetch($stmt1)){
     $id101=$id101;
     $title=$title;        //htmlspecialchars needed
     $price=$price;       //htmlspecialchars needed
     $detail=$detail;    //htmlspecialchars needed

     ........................
     ........................
     }

Answer 1

I am using filter_input() to filter any data that comes from user before inserting into database.

This is a bad practice. Do not mangle your data before you insert it into a database. It's 2015; don't sanitize, use prepared statements instead.

$db = new \PDO(
    'mysql:host=localhost;dbname=mydatabase;charset=UTF-8',
     $username,
     $password
);

// other stuff in between

$statement = $db->prepare(
    "INSERT INTO yourtable (email, username) VALUES (:email, :username);"
);
$success = $statement->execute([
    ':email'    => $_POST['email'],
    ':username' => $_POST['username']
]);

Prepared statements remove the need for filter_input(). You're not adding defense in depth by doing this, you're just ruining data integrity and giving yourself a headache.

When you render your output, if you want to allow HTML, use HTML Purifier.

Otherwise, use htmlspecialchars($output, ENT_QUOTES | ENT_HTML5, 'UTF-8') for best results.

Recommended Reading: Web Application Security by Taylor Hornby.