Htmlspecialchars not working to escape apostrophe

Question

Edit for clarification: The form is on a different page (page1.php) and sends the information to this page (page2.php) via POST. It's just the basic html form. What I find is that it submits to the database fine UNLESS I use an apostrophe or type in a paragraph, then hit enter to type a second one in the same box.

With the apostrophe it throws an error saying the syntax is wrong. I have determined it's because it is reading the input as part of the html and thus the apostrophe in the sentence makes it think I am ending a line of code. So I can't use apostrophe's, like I did in that word (apostrophe's) just now. As for the paragraph issue it simply doesn't save it to the database. It runs everything together. I have tried to escape the apostrophe with htmlspecialchars as shown in my code below but it doesn't work. What am I doing wrong here?

if ($_SERVER["REQUEST_METHOD"] 
== "POST") {
// collect value of input field
$ListName = 
test_input($_POST['ListName']);
$Task1 = 
test_input($_POST['Task1']);
$Task2 = 
test_input($_POST['Task2']);
$Task3 = 
test_input($_POST['Task3']);
$Task4 = 
test_input($_POST['Task4']);
$Task5 = 
test_input($_POST['Task5']);
$Task6 = 
test_input($_POST['Task6']);
$Task7 = 
test_input($_POST['Task7']);
$Task8 = 
test_input($_POST['Task8']);
$Task9 = 
test_input($_POST['Task9']);
$Task10 = 
test_input($_POST['Task10']);

}

function test_input($data) {
$data = trim($data);
$data = stripslashes($data);
$data = 
htmlspecialchars($data);
return $data;
}

$sql = "INSERT INTO 
AutoPilotTaskLists (ListName, 
Task1, Task2, Task3, Task4, 
Task5, Task6, Task7, Task8, 
Task9, Task10) VALUES 
('$ListName', '$Task1', 
'$Task2', '$Task3', '$Task4', 
'$Task5', '$Task6', '$Task7', 
'$Task8', '$Task9', 
'$Task10')";

if ($conn->query($sql) === 
TRUE) {
echo "New List Created";
} else {
echo "Error: " . $sql . "<br>" 
. $conn->error;
}

$conn->close();


?>

Answer 1

I have discovered the answer. The following "prepares" the values.

if ($_SERVER["REQUEST_METHOD"] == "POST") {
// collect value of input field
$ListName = mysqli_real_escape_string($conn, $_POST['ListName']);
$Task1 = mysqli_real_escape_string($conn, $_POST['Task1']);
$Task2 = mysqli_real_escape_string($conn, $_POST['Task2']);
$Task3 = mysqli_real_escape_string($conn, $_POST['Task3']);
$Task4 = mysqli_real_escape_string($conn, $_POST['Task4']);
$Task5 = mysqli_real_escape_string($conn, $_POST['Task5']);
$Task6 = mysqli_real_escape_string($conn, $_POST['Task6']);
$Task7 = mysqli_real_escape_string($conn, $_POST['Task7']);
$Task8 = mysqli_real_escape_string($conn, $_POST['Task8']);
$Task9 = mysqli_real_escape_string($conn, $_POST['Task9']);
$Task10 = mysqli_real_escape_string($conn, $_POST['Task10']);

}

The only issue left is that if I try to write in paragraphs in the textarea of the form it doesn't record it in the database as such. It treats the white space between the paragraphs as it doesn't exist.

BUT it DOES save it the way it is written into the database. So the way to solve that is using css on the output page to display the white space properly.