I am providing a very simple test dataset and my code to try to resolve this issue. If the question has been asked before I have not been able to find it. I'm self-taught from books and the web so this is not slick programming. I hope I am providing enough information.
The dataset consists of one table ‘comments’ with fields id (autoincrement), name (text), comments (textarea), date, display (enum) The mysql connection collation and all text and text area fields are set to utf8_general_ci
The <head> includes
<meta http-equiv="Content-Type" content="text/html" charset="UTF-8" />
All post and get statements are escaped as follows
$fieldname = mysqli_real_escape_string($dbc, $_POST['fieldname']);
My code successfully enters the problem characters into the database and they are retrieved successfully if I just want to display them, however in the real world I need to vet user entries to ensure they are suitable for public viewing and I also need to be able to edit them. When I open my comments-edit page, anything after an & in a field disappears and if there is a # or " in any field nothing is retrieved at all.
First the comments-admin.php page:
<?php
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
or die('Error connecting to MySQL server.');
?>
<table width="100%"><tr><td>
<b>ID:</b></td><td>
<b>Name:</b></td><td align="left">
<b>Comments:</b></td><td>
<b>Date Posted:</b></td><td>
<b>Display:</b></td></tr><tr><td>
<?php
$query = "SELECT * FROM comments ORDER BY date DESC";
$result = mysqli_query($dbc,$query);
while ($row=mysqli_fetch_array($result)){
echo '<tr valign="top" ><td>';
echo $row['id'] . '</td><td>';
echo $row['name'] . '</td><td>';
echo $row['comments'] . '</td><td>';
echo $row['date'] . '</td><td>';
echo $row['display'] . '</td><td>';
echo '<td><a href="comments-edit.php?id=' . $row['id'] .
'&name=' . $row['name'] .
'&comments=' . $row['comments'] .
'&date=' . $row['date'] .
'&display=' . $row['display'] .
'">Edit</a></td></tr>';
}
?>
</table>
This results in a table which looks like this (without the decoration).
ID: Name: Comments: Date Posted: Display:
12 Fred Just wanted to say "Thank You" 2015-11-04 No Edit
11 Ricky I live at #2, Anywhere Street. 2015-11-04 No Edit
9 Ethel Why can't I make this work! 2015-10-24 Yes Edit
4 Lucy Went to A&W today 2015-04-23 Yes Edit
"Edit opens my comments-edit.php form. In this example the only one which will open correctly is #9. #'s 11 & 12 will not retrieve anything at all, and #4 will not have anything after the &.
The code for comments-edit follows:
<?php
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
or die('Error connecting to MySQL server.');
if (isset($_GET['id']) &&
isset($_GET['name']) &&
isset($_GET['comments']) &&
isset($_GET['date']))
{
// Grab the data from the GET
$id = ( $_GET['id']);
$name = mysqli_real_escape_string($dbc, $_GET['name']);
$comments = mysqli_real_escape_string($dbc, $_GET['comments']);
$date = ( $_GET['date']);
}
else if (isset($_POST['id']) &&
isset($_POST['name']) &&
isset($_POST['comments']) &&
isset($_POST['date']) &&
isset($_POST['display']))
{
// Grab the data from the POST
$id = ( $_POST['id']);
$name = mysqli_real_escape_string($dbc, $_POST['name']);
$comments = mysqli_real_escape_string($dbc, $_POST['comments']);
$date = ( $_POST['date']);
$display = ( $_POST['display']);
}
<form class="gap" method="post" action="comments-update.php">
<input type="hidden" name="ud_id" value="<?php echo "$id;" ?>" />
<p class="gap"><b>Select "Yes" before clicking "Edit Data" to confirm any changes</b></p>
<input type="radio" name="confirm" value="Yes" /> Yes
<input type="radio" name="confirm" value="No" checked="checked" /> No
<input type="submit" value="Edit Data" name="update" /><br />
<label class="appsm">Comment Date:</label>
<input type="text" size="30" name="ud_date" value="<?php echo $date; ?>"
<label class="appsm">ID:</label>
<input type="text" size="5" name="ud_id" value="<?php echo $id; ?>" />
<label class="appsm">Display:</label>
<select id="ud_display" name="ud_display">
<option value="yes" <?php if (!empty($display) && $display == 'yes') echo 'selected = "selected"'; ?>>Yes</option>
<option value="no" <?php if (!empty($display) && $display == 'no') echo 'selected = "selected"'; ?>>No</option>
</select><br />
<label class="appsm">Name:</label>
<input type="text" size="50" name="ud_name" value="<?php echo $name; ?>" />
<br />
<label class="appsm">comments:</label>
<textarea name="ud_comments" wrap="physical" class="left" cols="105" rows="2">
<?php echo stripslashes($comments); ?></textarea>
</form>
I'm sure I've missed something very simple and I've searched my books and the web but I have not found an answer to this specific issue. I would really like to be able to remove all the warnings not to use these characters from my real websites, especially since users rarely read the instructions.
