Php function htmlspecialchars doesn't work

Question

In a project I have to do for school it is required to prevent XSS (Cross-Site Scripting) attacks at my website, so tags such as <script type="text/javascript">alert("hello");</script> (which I can insert in every input tag in my website) shouldn't work (I should not see the alert window in this case). At school I was advised to use the php function htmlspecialchars, but the browser still shows me the alert window, when I insert this tag in a comment in my website. I can't understand what I'm doing wrong. (NOTE: I've also inserted the js code, but I think that the problem is in php, all the code works correctly)

$("#new-comment").on('click',function(){ 
  var $newReview = $("<input class='new-input' type='text' id='insert' name='insert' placeholder='write here...'>");  
  $("#reviews").append($newReview);   
  $newReview.on('keypress',function(e){
    if(e.which == 13){
      var comm = $(this);
      var datatime = new Date($.now());
      correctHour = printMysqlFormat(datatime);
      $.ajax({
        url:'reviews/reviews_query.php',
        data: {put:true, title: $("#right_title").text(), script: comm.val(), time: correctHour},     
        datatype:'json',
        success: function(json){
          var output = jQuery.parseJSON(json);
          var newName = "<span class='rev_name'>"+ output + "</span>";
          var newComment = "<span class='rev_comment'>"+ comm.val() + "<div class='rev_time'>" + correctHour + "</div></span>";
          $("#reviews > ul").html($("#reviews > ul").html()+"<li style='margin-bottom:20px'>" + newName + " " + newComment + "</li>");
        },
        error: function(e){
          console.log(e.message);
        }
      });
      $newReview.remove(); 
    }
  });
});

    if(isset($_GET["show"]) && isset($_GET["title"])){  //to show all comments
        $db = new PDO("mysql:host=localhost;dbname=music", "username", "password");
        $titolo = $_GET["title"];
        $ti = $db->quote($titolo);
        $rows = $db->query("SELECT * FROM reviews WHERE titolo=$ti ORDER BY ora ASC");
        if($rows->rowCount() == 0){ 
            echo 0;
        }else{
            $res = $rows->fetchALL(PDO::FETCH_ASSOC);
            echo json_encode($res);
        } 
    }
    
    if(isset($_GET["put"]) && isset($_GET["title"]) && isset($_GET["script"]) && isset($_GET["time"])){  //to write a new comment
        $db = new PDO("mysql:host=localhost;dbname=music", "username", "password");
        $username = $_SESSION["name"];
        $us = $db->quote($username);
        $titolo = $_GET["title"];
        $ti = $db->quote($titolo);
        $commento = $_GET["script"];
        $commento = htmlspecialchars($commento);
        $comm = $db->quote($commento);
        $timestamp = $_GET["time"];
        $tim = $db->quote($timestamp);
        $rows = $db->query("INSERT into reviews VALUES ($us, $ti, $comm, $tim)");     
        $res = $_SESSION["name"];
        echo json_encode($res); 
    } 

EDIT: I've tried with this code but nothing changes:

    if(isset($_GET["show"]) && isset($_GET["title"])){  
        $db = new PDO("mysql:host=localhost;dbname=music", "username", "password");
        $titolo = $_GET["title"];
        $ti = $db->quote($titolo);
        $rows = $db->query("SELECT * FROM reviews WHERE titolo=$ti ORDER BY ora ASC");
        if($rows->rowCount() == 0){
            echo 0;
        }else{
            $res = $rows->fetchALL(PDO::FETCH_ASSOC);
            $res['comment_clean'] = htmlspecialchars($res['commento'], ENT_QUOTES, 'UTF-8'); //commento is an attribute of my table, where I save the comment
            echo json_encode($res);
        } 
    }

Answer 1

I will list here a few useful answers for both the question you've asked as well as the issues that are obvious from your code:

1)

How to correctly set up your PDO connection -- A good guide to settings to use.

How to use PDO prepared statements - Prepared statements are fundamentally more secure than the previous method of inserting data. You are using PDO but you're not Preparing your statement, so you're almost there....

2)

How to prevent XSS with PHP

NOTE: You should use htmlspecialchars when outputting data, NOT when inputting data into the database.

3)

If you want to allow any HTML in your source input then you should use HTML Purifier on your form data, once its sent back to your server and prior to inserting into the database.

4)

There is a lot of MySQL Best Practise to find and use, and I can't find a nice single post to explain it all, but the one I really need to outline to you is

You should never use the root login to access/save data via the website. ALWAYS use another custom user which has only the limited permissions required.

(If someone has a link to a URL reference for 4 that would be great!)

Good Luck

---

Yes, but in php or jquery? I understood that this was a php function, so I was thinking about the first part of my php code.. if you could also write the line I have to insert it would be very useful

Your process should be roughly:

Saving Data

• HTML: Collect data via HTML <form> (etc.), data is sent to the server with a form submit.
• PHP Optional processing of the data as you require (checking validity, etc.)
• PHP Use HTMLPurifier or similar, as required to safely remove unwanted HTML.
• PHP This collected data should be put into a PDO Prepared Statement
• SQL The data is saved into the database.

Outputting Data

• PHP Build SQL request for which data you want, typically with an id reference call.
• PHP Load the data intended to be returned to the Client (the user's browser)
• PHP Run htmlspecialchars on this data to disable HTML elements
• HTML data is displayed to the client browser.

---

I understood the process and I've also read that thread, but I don't know how to write it in my code

Here is an example from your code:

It is also worth noting you should always manually reference all the column names and NOT SELECT * FROM on your SQL query.

    $rows = $db->prepare("SELECT id, name, htmlcode FROM reviews WHERE titolo=:title ORDER BY ora ASC");
    $rows->execute(['title' => $ti]); //SAFE SQL query.
    if((int)$rows->rowCount() === 0){ 
        echo 0;
    } else{
        $res = $rows->fetchALL(PDO::FETCH_ASSOC);
        // Res will be a multilevel array so you need to apply changes to each subarray key. 
        foreach($res as &$resRow){
        //disable HTML. For example only this is saved into a new array value.
            $resRow['commento'] = htmlspecialchars($resRow['commento'], ENT_QUOTES, 'UTF-8'); 
        }
        echo json_encode($res);
    } 

You should also get used to using absolute comparison operators === rather than vague comparison operators == .