How to properly use htmlspecialchars() and htmlspecialchars_decode() with an MYSQL UPDATE statement?


I have a form that receives the rows of a MySQL SELECT statement that are escaped with htmlspecialchars() as their values.
The values may be modified and sent to the DB with an UPDATE statement, but when the form is reloaded after the UPDATE, HTML entities are in the text.
I tried htmlspecialchars_decode() to send the form values to the DB without the HTML entities, but I think I have misunderstood how it works. I did try with the ENT_QUOTES argument as well.

Here are the concerned pieces of code:
The function that SELECTs from the DB. It is linked to a .view.php file for the rest of the HTML.
Thank you.

function tutors_courses() {

  global $session_id;

  $query = "SELECT tutors.tutor_id, username, CONCAT(first_name, ' ', last_name) AS tutor_name, 
  courses.course_id, courses.title, courses.description, courses.price FROM tutors
  JOIN tutors_courses ON tutors_courses.tutor_id = tutors.tutor_id
  JOIN courses ON courses.course_id = tutors_courses.course_id
  WHERE tutors.tutor_id = $session_id";

  global $pdo;
  $result = $pdo->query($query);

  while ($row = $result->fetch()) {
    $title = $row['title'];
    $desc = $row['description'];
    $price = $row['price'];
    $course_id = $row['course_id'];

    $title = htmlspecialchars($title);
    $desc = htmlspecialchars($desc);
    $price = htmlspecialchars($price);
    $course_id = htmlspecialchars($course_id);
    echo <<<_TUTORSCOURSES
    <form action="/update-dashboard" method="post">
      <td class="queries_table-data"><input type="text" name="title" value="$title"></td>
      <td class="queries_table-data"><textarea name="description" rows="3">$desc</textarea></td>
      <td class="queries_table-data"><input type="number" name="price" step="any" value="$price"></td>
      <td class="queries_table-data">
        <input type="hidden" name="courseid" value="$course_id">
        <button class="btn" type="submit" name="edit-course">Edit course</button>
    <form action="/tutor-dashboard" method="post">
      <input type="hidden" name="courseid" value="$course_id">
      <td><button type="submit" name="delete-course" class="btn btn--delete">Delete</button></td>

This part is for the UPDATE that receives the above form data.

if (isset($_POST['edit-course'])) {
  if (
  !empty($_POST['title']) &&
  !empty($_POST['description']) &&
  !empty($_POST['price']) &&
  !empty($_POST['courseid'])) {
    echo "<H1>EDITING</h1>";
    $courseid = htmlspecialchars_decode($_POST['courseid']);
    $title = htmlspecialchars_decode($_POST['title']);
    $desc = htmlspecialchars_decode($_POST['description']);
    $price = htmlspecialchars_decode($_POST['price']);

    $stmt = $pdo->prepare("UPDATE `courses` SET title=?, `description`=?, `price`=? WHERE `course_id`=?");
    $stmt->bindParam(1, $title);
    $stmt->bindParam(2, $desc);
    $stmt->bindParam(3, $price);
    $stmt->bindParam(4, $courseid);
    $stmt->execute([$title, $desc, $price, $courseid]);

First of all don’t use global variables as they will cause you nothing but problems in the long run.

Second, a coder is more concerned about output than user input and besides you are using prepared statements.

I would do something like the following: (Code not tested and I personally would do it a little diferently)

    if ($_SERVER['REQUEST_METHOD'] === 'POST') {

      $course_id = $_POST['courseid'];
      $title = $_POST['title'];
      $description = $_POST['description'];
      $price = $_POST['price'];
      $sql = 'UPDATE courses SET 
      $stmt = $pdo->prepare($sql);

      $result = $stmt->execute(['title' => $title, 'description' => $description, 'price' => $price, 'course_id' => $course_id]);

I would put that at the top of the file or you could use JavaScript/Ajax though that is a different topic.

Here’s a working edit page that I have done that might give you some further help - Edit Page

1 Like

Thank you for that, @Strider64.

So, I don’t need htmlspecialchars() because of the prepared statements?

I learned about having PHP logic in /controllers/page_file.php, and HTML in /views/page_file.view.php.

I think I got confused about how to combine those files effectively, and it looks like functions were the wrong approach.

I had a look at your form. There’s so much I don’t understand yet, but I can see what’s happening. Thank you.

Show us an example of the data that does this. What is the starting value, what form field is it in, and what is the ending value?

You do need htmlspecialchars() (I use htmlentities() so that ALL special characters are converted to html entities) when you output dynamic values in a html context (web page, email.)

User written functions/classes should be general-purpose and do something useful that helps you create applications, i.e. they should be ‘helper’, building-blocks. They should not contain application specific code, such as an sql query or html markup. If you are constantly editing or copy/pasting creating new functions every time you do something different, it is a sign that your functions have the wrong responsibility and are not general purpose, they are actually your application code and should not be inside of a function.


Here’s the form created with the data from the DB:

This is the textarea content:
<textarea name="description" rows="3">You'll learn all there's to know about savage savages.</textarea>

Added another sentence to it:

After clicking edit to execute the UPDATE:

Contents of the textarea:
<textarea name="description" rows="3">You&amp;#039;ll learn all there&amp;#039;s to know about savage savages. Remember to pack some extra boots.</textarea>

The entities are being written to the DB.

Okay. I’ll look into htmlentities() as well.

All right. I’ll do some refactoring.

Thanks for your help.

Here’s a list of items in the posted code, most of which will actually simplify the code -

  1. You should use table aliases in the sql query to simplify all the typing, such as t for tutors, tc for tutors_courses. I also use the table alias in every column reference so that anyone reading the sql query can tell when table every column is coming from without needing to know your table definitions. This help to make your query self-documenting.
  2. Don’t create variables for nothing. Just keep the data as an array variable, then use elements in the original array variable throughout the code.
  3. The posted markup is missing elements and is invalid. You can put a complete html table inside a form and you can put a complete form inside of single html table cell (<td>a complete form here...</td> or <th>...</th>) but you cannot intermix parts of multiple forms inside of html table markup. You should validate your resulting web pages at
  4. Your form and form processing code should be on the same page. To cause the form to submit to the same page it is on, simply leave out the entire action=’…’ attribute.
  5. You should not attempt to detect if the submit button is set, there are cases where it won’t be. Instead, just detect if a post method form was submitted. When there can be more than one form on a page, use a hidden field, such as name=‘action’, with a unique value to identify and control what form processing code to execute.
  6. Your form processing code should keep the form data as a set in a php array variable, then operated on elements in this array variable throughout the rest of the code.
  7. You should trim() all the input data before using it. After you do item #6 in this list, you can accomplish this with one single line of code.
  8. You should then validate all the inputs separately, storing user/validation errors in an array, such as $errors, using the field name as the main array index.
  9. After the end of the validation logic, if the array holding the errors is empty, use the submitted data.
  10. If you use implicit binding, by supplying an array to the ->execute([…]) call, you can eliminate all the bindParam() calls.
  11. Because this UPDATE query could produce duplicate data, for the title, that column should be defined as a unique index. You would then need error handling (you should be using exceptions for database statement errors and in fact php8+, this is the default setting) for the execution of the UPDATE query to detect if a duplicate index error has occurred. If there is a duplicate error, you would setup an error message for the user letting them know what was wrong with the data that they submitted, then let them change the value in the form and resubmit it.
  12. If there are any user/validation errors, you would re-populate the form field values with the submitted form data, not the initial data that’s in the database table, so that the user doesn’t need to keep reentering values over and over.
1 Like

Thank you for your detailed reply. I appreciate that. :+1:

This is very much a work in progress and for me to develop my learning as well as a project for me to use myself. There is much more for me to learn and consider going forwards. I thought I was progressing with my learning, but I seem to be so far away from doing anything for people to use. But, I think that’s part of the coding journey: learn > apply > mistakes > refactor > apply > improve.

I’ll start going through that list tomorrow.

Thanks again.

As to the problem, I suspect that something about your html document doctype/lang/charset values, the invalid markup, or the browser/client you are using is preventing the browser/client from submitting the actual values and is instead submitting the html entity values. I just tested with a minimal form and with your example data and the submitted data does not contain the html entity values, but does contain the expected actual values. What browser/client and version are you using and what is the entire ‘view source’ of the form page?

Also, php8.1 change the default flags for htmlspecialchars()/htmlentities(), so you may be fighting php as well. What php version are you using?

1 Like


I’ve been using Firefox, Vivaldi and Chromium.
PHP 8.2.2

View Source output:

<!DOCTYPE html>
    <html lang="en">
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>Tutor Dashboard</title>
    <link rel="stylesheet" href="../static/style.css">
    <header class="header">
        <p>The header</p>

<div class="sub-header">
   <h1>Tutor Dashboard</h1>
</div><nav class="nav">
   <ul class="nav_ul"><li class="nav_li"><a class="nav_link" href="/tutor-dashboard">Tutor Dashboard</a></li><li class="nav_li"><a class="nav_link" href="/user-logout">Log Out</a></li></ul>   
    <!-- Logged in as: -->
    <div class="nav_avatar">
            Logged in as: lambsheep.
        <img src = "/static/goat.png        ">
<!-- Add a course -->
<main class="main">
  <section class="section">
    <nav class="sub-nav">
      <ul class="sub-nav_list">
        <li class="sub-nav_item"><button class="btn" id="add-course-btn">Add a Course</button></li>
  <section class="section">
      <h2 class="large-heading">Your Courses</h2>
      <p>You may edit a course directly below.</p><br>
      <table class="queries">
          <th class="queries_table-heading">Course Title</th>
          <th class="queries_table-heading queries_table-heading--desc">Course Description</th>
          <th class="queries_table-heading price">Price</th>
          <th class="queries_table-heading price"></th>
          <th class="queries_table-heading price"></th>

  <form action="/update-dashboard" method="post">
    <td class="queries_table-data"><input type="text" name="title" value="How to kill Triffids safely without injury"></td>
    <td class="queries_table-data"><textarea name="description" rows="3">You&#039;ll need this course to survive triffid infestations.</textarea></td>
    <td class="queries_table-data"><input type="number" name="price" step="any" value="16.50"></td>
    <td class="queries_table-data">
      <input type="hidden" name="courseid" value="2">
      <button class="btn" type="submit" name="edit-course">Edit course</button>
  <form action="/tutor-dashboard" method="post">
    <input type="hidden" name="courseid" value="2">
    <td><button type="submit" name="delete-course" class="btn btn--delete">Delete</button></td>
  <form action="/update-dashboard" method="post">
    <td class="queries_table-data"><input type="text" name="title" value="Traffic Cones are fun"></td>
    <td class="queries_table-data"><textarea name="description" rows="3">Friday night traffic cones make great stilts, but you must use them safely.</textarea></td>
    <td class="queries_table-data"><input type="number" name="price" step="any" value="26.64"></td>
    <td class="queries_table-data">
      <input type="hidden" name="courseid" value="19">
      <button class="btn" type="submit" name="edit-course">Edit course</button>
  <form action="/tutor-dashboard" method="post">
    <input type="hidden" name="courseid" value="19">
    <td><button type="submit" name="delete-course" class="btn btn--delete">Delete</button></td>

<div class="modal-wrapper" id="add-course-modal">
  <div class="modal_inner">
    <span class="close-modal">close &times;</span>
    <form class="input-form" action="/tutor-dashboard" method="post">
      <legend>Add a Course</legend>
      <label for="title">Course Title</label>
      <input type="text" name="title" id="title">

      <label for="desc">Course Description</label>
      <textarea name="desc" id="desc" cols="30" rows="10"></textarea>

      <label for="price">price</label>
      <input type="number" name="price" id="price" step="any" placeholder = "9.99">

      <button class="btn" name="add-course" type="submit">Add Course</button>

<footer class="footer">
    <p>Know more about databases.</p>

<script src="/static/js/app.js"></script>


Re: the error in the now deleted post. To display any of the logged in user’s content, e.g. the update/create forms, or process the form submissions, requires a logged in user. Your program logic should test that there is a logged in user before ever running the SELECT query to get the data for the currently logged in user.

I tried the posted view source, in google’s chrome browser, and it properly submits the the actual value, so, I cannot duplicate your result.

You will need to investigate why the html entity is being submitted. Is there a chance that any version of your code had a call to htmlspecialchars() inside the processing code? That the version with the htmlspecialchars_decode() didn’t remove the html entities says something else is going on.

Your page has an app.js javascript file. Any chance it is doing something to the values?

1 Like

Hello phdr,

The help you have provided me has been phenomenal! Thank you. I hope to be able to do the same to help others in the future.

I test for a set session at the top on my pages to test for logged in or not. That was stopping the source code loading up. That abandoned message was getting messy.

I’ve scrapped the table for the form and have opted to put the form in a div, and style it with CSS. The good news is the updating works now, so it seems that the invalid HTML may have been the cause.

The array variable you mentioned has helped to clean up some of the mess and got me away from the functions and globals that @Strider64 also mentioned.

The app.js file just has event listeners to toggle some modals.

One thing I always do is wrap my $_POST asignments in the trim() function, it’s just a built-in habit.

$firstName = trim($_POST['firstName']);

1 Like

Thank you, @web1928.

I’ve applied TRIM() to my MySQL queries to keep the processing away from the client side, but I can see how PHP’s trim() might be useful for any intermediary processes before submission to the DB.

Trimming the data was already mentioned as item #7 in the list of programming practices -

1 Like

Indeed. :grinning: :+1:That’s why I applied it, but on the MySQL query with this:

$stmt = $pdo->prepare("INSERT INTO courses VALUES(?,TRIM(?),TRIM(?),TRIM(?),TRIM(?))");

I have yet to work out getting the $_POST[’’] elements into an array, but I do have arrays for the SELECT queries. I’ll get there. Oh, hang on. $_POST is an array.

Thanks to your list, I’ve been able to strip out a lot of unnecessary code.

Um, No. The reason for trimming the data, before validating it in the serve-side code, is mainly so that you can detect if all white-space characters were entered. Doing that in the sql is too late.

They are already an array. If you where not modifying the values, e.g. not trimming the data, you would just use the elements in $_POST throughout the rest of the code. However, because you should both leave the original data in $_POST as is and you are modifying the values, you can copy them to an array variable, such as $post, and trim them at the same time -

$post = array_map('trim',$_POST); // if any of the fields are arrays, use a recursive trim call-back function here instead of php's trim
1 Like

That’s great, thanks.

It looks like array_map() creates a copy of the referenced array by iterating it, whilst applying a called function to each iteration.

Sponsor our Newsletter | Privacy Policy | Terms of Service