Form submission can't take apostrophes

PHP
1

I inherited a form on a small hospital’s site that sends an ecard. It takes a patient’s first and last name, room number, message from the sender, and signature. However, if an apostrophe is used, like “I can’t wait until you’re home,” it throws the code off. Instead of going to the preview page, the user is greeted with an error page. Submitting the phrase “Testing, please don’t print,” comes up with:

“Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘t print’,‘Sincerely,’,‘Webmaster’,‘iris’,‘321’)’ at line 1”

I know I’m missing something simple and feel rather dumb asking, but I need assistance with this. I also didn’t find any help when searching here, but I’m sure someone will point to a thread, or 10, I missed. I know the submitted text needs to cleared of html, but my little PHP experience isn’t working. I’ve tried adding htmlspecialchars and magic quotes, but they didn’t help anything.

The code for the ecard page and resulting preview page are below. These pages need cleaning up, such as the number of calls to the server, CSS used instead of tables, and other items, but that’s after the functionality is improved.

ecard.php

<?php

if($_GET[action]=="post"){
	$tof=$_POST[tof];
	$tol=$_POST[tol];
	$roomnum=$_POST[roomnum];
	$msg=$_POST[msg];
	$closing=$_POST[closing];
	$from=$_POST[from];
	$picture=$_POST[picture];
	$con = mysql_connect("localhost","username","password");
	if (!$con)
	{
		die('Could not connect: ' . mysql_error());
	}
	mysql_select_db("c28db1", $con);
	$sql=mysql_query("INSERT INTO ecard (tof,tol,msg,closing,fromm,picture, roomnum) VALUE ('$tof','$tol','$msg','$closing','$from','$picture','$roomnum')", $con);
	if(!$sql){
		die('Error: ' . mysql_error());
	}else{
		?>
		<SCRIPT LANGUAGE="JavaScript">
		<!--
		setTimeout ("changePage()", 300);
		function changePage() {
			if (self.parent.frames.length != 0)
				self.parent.location="http://www.rchmtayr.org/ecard/viewcard.php?preview=yes&id=<?php
				$con = mysql_connect("localhost","username","password");
				if (!$con)
				{
					die('Could not connect: ' . mysql_error());
				}
				mysql_select_db("c28db1", $con);
				$sqlid=mysql_query("SELECT * FROM ecard", $con);
				while( $rows = mysql_fetch_array($sqlid,MYSQL_ASSOC)){
					$id = $rows[id];
				}	
				mysql_close($con);
				echo($id);
		?>";
		}
		// -->
		</SCRIPT>
<?php
	}
		mysql_close($con);
}else{
?>
<form action="ecard.php?action=post"method="post" ENCTYPE="multipart/form-data">
<table width="510">
	<tr>
		<td width="275" align="right">
			Patient's First Name:
		</td>
		<td align="center">
			<input type="text" size="40" name="tof" />
		</td>
	</tr>
	<tr>
		<td width="250" align="right">
			Patient's Last Name:
		</td>
		<td align="center">
			<input type="text" size="40" name="tol" />
		</td>
	</tr>
	<tr>
		<td width="250" align="right">
			Patient's Room Number:
		</td>
		<td align="center">
			<input type="text" size="40" name="roomnum" />
		</td>
	</tr>
	<tr>
		<td align="right">
			Message body:
		</td>
		<td align="center">
			<textarea name="msg" rows="10" cols="30"></textarea>
		</td>
	</tr>
	<tr>
		<td align="right">
			Closing (Sincerely, Love, etc...):
		</td>
		<td align="center">
			<input type="text" size="40" name="closing" value="Sincerely," />
		</td>
	</tr>
	<tr>
		<td align="right">
			From:
		</td>
		<td align="center">
			<input type="text" size="40" name="from" />
		</td>
	</tr>
	<tr>
		<td>
		</td>
		<td align="center">
			<table width="100%">
				<tr>
					<td align="center" valign="top">
						<a href="images/large/balloons.jpg" target="_blank"><img src="images/balloonssm.gif" border="0" /></a><br />
						Balloons<br />
						<input type="radio" size="40" name="picture" value="balloons" /><br /><br />
					</td>
					<td align="center" valign="top">
						<a href="images/large/christian.jpg" target="_blank"><img src="images/christiansm.gif" border="0" /></a><br />
						Thoughts and Prayers<br />
						<input type="radio" size="40" name="picture" value="christian" /><br /><br />
					</td>
				</tr>
				<tr>
					<td align="center" valign="top">
						<a href="images/large/getwellroses.jpg" target="_blank"><img src="images/getwellrosessm.gif" border="0" /></a><br />
						Get Well Soon<br />
						<input type="radio" size="40" name="picture" value="getwellroses" /><br /><br />
					</td>
					<td align="center" valign="top">
						<a href="images/large/iris.jpg" target="_blank"><img src="images/irissm.gif" border="0" /></a><br />
						Thinking of you<br />
						<input type="radio" size="40" name="picture" value="iris" /><br /><br />
					</td>
				</tr>
				<tr>
					<td align="center" valign="top">
						<a href="images/large/kitten.jpg" target="_blank"><img src="images/kittensm.gif" border="0" /></a><br />
						Get Well Soon<br />
						<input type="radio" size="40" name="picture" value="kitten" /><br /><br />
					</td>
					<td align="center" valign="top" target="_blank">
						<a href="images/large/pigstar.jpg" target="_blank"><img src="images/pigstarsm.gif" border="0" /></a><br />
						You'll be lookin' this good in no time!<br />
						<input type="radio" size="40" name="picture" value="pigstar" /><br /><br />
					</td>
				</tr>
				<tr>
					<td align="center" valign="top">
						<a href="images/large/sun.jpg" target="_blank"><img src="images/sunsm.gif" border="0" /></a><br />
						We hope you feel better soon!<br />
						<input type="radio" size="40" name="picture" value="sun" /><br /><br />
					</td>
					<td align="center" valign="top">
						<a href="images/large/tranquil-1.jpg" target="_blank"><img src="images/tranquilsm.gif" border="0" /></a><br />
						Wishing you tranquility as you recover.<br />
						<input type="radio" size="40" name="picture" value="tranquil-1" /><br /><br />
					</td>
				</tr>
			</table>
		</td>
	</tr>
	<tr>
		<td>
		</td>
		<td>
			<input type="submit" value="Preview" />
		</td>
	</tr>	
</table>
</form>
<?php
}
?>

viewcard.php

<?php
if (get_magic_quotes_gpc())
{
	$process = array(&$_GET, &$_POST, &$_COOKIE, &$_REQUEST);
	while (list($key, $val) = each($process))
	{
		foreach ($val as $k => $v)
			{
			unset($process[$key][$k]);
			if (is_array($v))
			{
				$process[$key][stripslashes($k)] = $v;
				$process[] = &$process[$key][stripslashes($k)];
			}
			else
			{
				$process[$key][stripslashes($k)] = stripslashes($v);
			}
		}
	}
	unset($process);
}

if($_GET[action]=="post"){
	$tof=$_POST[tof];
	$tol=$_POST[tol];
	$roomnum=$_POST[roomnum];
	$msg=$_POST[msg];
	$closing=$_POST[closing];
	$from=$_POST[from];
	$picture=$_POST[picture];
	$con = mysql_connect("localhost","username","password");
	if (!$con){
		die('Could not connect: ' . mysql_error());
	}
	mysql_select_db("c28db1", $con);
	$sql=mysql_query("INSERT INTO ecard (tof,tol,msg,closing,fromm,picture, roomnum) VALUE ('$tof','$tol','$msg','$closing','$from','$picture','$roomnum')", $con);
	if(!$sql){
		die('Error: ' . mysql_error());
	}
	mysql_close($con);
}
$id=$_GET[id];
if($id == null){
	$con = mysql_connect("localhost","username","password");
	if (!$con)
	{
		die('Could not connect: ' . mysql_error());
	}
	mysql_select_db("c28db1", $con);
	$sqlid=mysql_query("SELECT * FROM ecard", $con);
	while( $rows = mysql_fetch_array($sqlid,MYSQL_ASSOC)){
		$id = $rows[id];
	}	
	mysql_close($con);
}
$con = mysql_connect("localhost","username","password");
	if (!$con)
	{
		die('Could not connect: ' . mysql_error());
	}
	mysql_select_db("c28db1", $con);
	$sql=mysql_query("SELECT * FROM ecard WHERE id='$id'", $con);
	while( $rows = mysql_fetch_array($sql,MYSQL_ASSOC)){
?>
<table>
<tr>
<td>
<table cellpadding="0" cellspacing="0" border="0">
	<tr cellpadding="0" cellspacing="0" border="0">
		<td id="tl" cellpadding="0" cellspacing="0" border="0"><img src="images/tl.gif" /></td>
		<td id="top" cellpadding="0" cellspacing="0" border="0"><img src="images/top.gif" /></td>
		<td id="tr" cellpadding="0" cellspacing="0" border="0"><img src="images/tr.gif" /></td>
	</tr>
	<tr cellpadding="0" cellspacing="0" border="0">
		<td  cellpadding="0" cellspacing="0" border="0"><img src="images/left.gif" /></td>
		<td cellpadding="0" cellspacing="0" border="0">
			<table width="910" cellpadding="0" cellspacing="0" border="0">
				<tr cellpadding="0" cellspacing="0" border="0">
					<td width="450" cellpadding="0" cellspacing="0" border="0">
						<img src="images/large/<?php echo($rows[picture]); ?>.jpg" />
					</td>
					<td width="10" cellpadding="0" cellspacing="0" border="0">
					</td>
					<td width="450" cellpadding="0" cellspacing="0" border="0">
						<font size="5" face="Monotype Corsiva">
							Dear <?php echo($rows[tof]); ?>,<br />
							<?php echo(htmlspecialchars($rows[msg], ENT_QUOTES, 'UTF-8')); ?><br /><br />
							<?php echo(htmlspecialchars($rows[closing], ENT_QUOTES, 'UTF-8')); ?><br />
							<?php echo(htmlspecialchars($rows[fromm], ENT_QUOTES, 'UTF-8')); ?>
						</font>
					</td>
				</tr>
			</table>		
		</td>
		<td cellpadding="0" cellspacing="0" border="0"><img src="images/right.gif" /></td>
	</tr>
	<tr cellpadding="0" cellspacing="0" border="0">
		<td cellpadding="0" cellspacing="0" border="0"><img src="images/bl.gif" /></td>
		<td cellpadding="0" cellspacing="0" border="0"><img src="images/btm.gif" /></td>
		<td cellpadding="0" cellspacing="0" border="0"><img src="images/br.gif" /></td>
	</tr>
</table>
</td>
</tr>
<?php		
	}
	if($_GET[action]=="post"){
?>
<tr>
<td>
<center>
<table>
<tr>
<td>
<form action="scripts/formmail.php" method="post" ENCTYPE="multipart/form-data">
	<input name="subject" type="hidden" id="subject" value="E-card Notification" />
    <input name="redirect" type="hidden" id="redirect" value="http://www.rchmtayr.org/ecardsent.html" />
    <input name="order" type="hidden" id="order" value="sort:cards,ptFirstName,ptLastName,roomNumber,message,signature,senderFirstName,senderLastName" />
    <input type="submit" value="Send this eCard" />
</form>
</td>
<td>
<form>
<center><input type="button" value="Edit this eCard" onClick="window.location='http://www.rchmtayr.org/ecard.php?id=<?php echo($id); ?>'" /></center>
</form>
</td>
</tr>
</table>
</center>
</td>
</tr>
<?php		
	}
?>
</table>
2

Yes you will often find problems with old code.
At the very least you should add mysql_real_escape_string() to any data being inserted into your database. This should fix the apostrophe issue. I also added trim() as well to your top section and single quoted all POST and GET keys.

<?php
if($_GET['action']=="post"){
    $tof = mysql_real_escape_string(trim($_POST['tof']));
    $tol = mysql_real_escape_string(trim($_POST['tol']));
    $roomnum = mysql_real_escape_string(trim($_POST['roomnum']));
    $msg = mysql_real_escape_string(trim($_POST['msg'];
    $closing = mysql_real_escape_string(trim($_POST['closing']));
    $from = mysql_real_escape_string(trim($_POST['from']));
    $picture = mysql_real_escape_string(trim($_POST['picture']));
3

Well, you’ve correctly identified that the single quote is the thing causing you problems.

The simplest answer to this is “use double quotes to encapsulate your field values instead”.
The second easiest would be to escape your strings.
The third would be to use prepared statements via either MySQLI or PDO (because mysql_ is being deprecated, and eventually will be removed).

4

Been a little bit since working with mysql… As I recall, you need to establish DB connection before using mysql_real_escape_string(). Sound right? In that case you would escape and set these variables after connection.

<?php
if($_GET['action']=="post"){
    $con = mysql_connect("localhost","username","password")));
    if (!$con)
    {
        die('Could not connect: ' . mysql_error());
    }
    mysql_select_db("c28db1", $con);
    $tof = mysql_real_escape_string(trim($_POST['tof']));
    $tol = mysql_real_escape_string(trim($_POST['tol']));
    $roomnum = mysql_real_escape_string(trim($_POST['roomnum']));
    $msg = mysql_real_escape_string(trim($_POST['msg'];
    $closing = mysql_real_escape_string(trim($_POST['closing']));
    $from = mysql_real_escape_string(trim($_POST['from']));
    $picture = mysql_real_escape_string(trim($_POST['picture']));
    $sql=mysql_query("INSERT INTO ecard (tof,tol,msg,closing,fromm,picture, roomnum) VALUE ('$tof','$tol','$msg','$closing','$from','$picture','$roomnum')", $con);
    if(!$sql){
        die('Error: ' . mysql_error());
    }else{
5

PDO can make your life so much easier:


error_reporting(E_ALL);

function get_connection()
{
    $conn = new PDO("mysql:host=localhost; dbname=DBNAME", 'USER', 'PASSWORD');
    $conn->setAttribute(PDO::ATTR_ERRMODE,           PDO::ERRMODE_EXCEPTION);
    $conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);
}
function insert_ecard($post)
{
    // Pull out just the insert data
    $params = array();
    foreach(array('tof','tol','msg','closing','fromm','picture','roomnum') as $key)
    {
        $params[$key] = $post[$key];
    }
    $sql = <<<EOT
INSERT INTO ecard
       ( tof, tol, msg, closing, fromm, picture, roomnum)
VALUES (:tof,:tol,:msg,:closing,:fromm,:picture,:roomnum);
EOT;
    $conn = get_connection();
    $stmt = $conn->prepare($sql);
    $stmt->execute($params);
}
if($_GET[action]=="post")
{
    insert_ecard($_POST);
6

Thanks for the help, it’s now working as it should. Going back to Sitepoint’s PHP Novice to Ninja book didn’t quite cut it.

Ahundiak and others, do you have recommended resource for PDO? I haven’t seen a Sitepoint book or Learnable class on it, but I also don’t do much with PHP and don’t give it much attention. Thanks again.