Apostrophe in form submission to add row to MYSQL causes error... not sure if I should use "mysql_escape_string" and if so how?

Question

I've done some googling but am still confused on how to handle apostrophes when I insert data into a php form to add my database. Currently I enter everything without the apostrophe then manually go in through phpmyadmin to add apostrophes where they should be which is a pain. I understand why I get the error but am not sure what to do to fix it (error is You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax).

What I've found are using real escape string and add slash / remove slash. I am thinking that real escape string is what I need to be using. I attempted to add it to my code but obviously not in the right place because it just shows me a blank page.

Here is my code. Everything else works fine... I know I'm not a php/sql expert so don't judge the code please. I just need help with how to handle the apostrophe issue. The only column that would have a possible apostrophe is the horse's name.

<?php
$detailsform = "
<form method='post' action='resultspage.php'>
<p>Year: <input type='text' name='yearofshow' size='4' maxlength='4' value='2013'></p>
<p>Show Name: <input type='text' name='showname' size='20' maxlength='100'></p>
<p>Level: <Select name='level'><option>Age A</option><option>Age B</option></select></p>
<p># Horses In Level: <input type='text' name='nohorses' size='20' maxlength='100'></p>
<p><input type='submit' name='details' value='Submit'></p>
</form>
";

if(isset($_POST['details']))

{
echo "
<form method='post' action='results_feh.php'>
<p><strong>Year:</strong> {$_POST['yearofshow']} <input type='hidden' name='yearofshow' value='{$_POST['yearofshow']}' size='4' maxlength='4'></p>
<p><strong>Show Name:</strong> {$_POST['showname']} <input type='hidden' name='showname' value='{$_POST['showname']}' size='20' maxlength='100'></p>
<p><strong>Level:</strong> {$_POST['level']} <input type='hidden' name='level' value='{$_POST['level']}' size='20' maxlength='100'></p>
<p><strong># Horses In Level:</strong> {$_POST['nohorses']} <input type='hidden' name='nohorses' value='{$_POST['nohorses']}' size='20' maxlength='100'></p>
";

$count = 0;

echo "<table><tr><td></td><td><b>Horse Name</b></td><td><b>Owned By</b></td></tr>";

while ($count < $_POST['nohorses'])
{

$count = $count + 1;
echo "<tr><td>{$count}</td><td><input type='text' name='horse{$count}' size='20' maxlength='100'></td><td><input type='text' name='rider{$count}' size='20' maxlength='100'></td></tr>";

}

echo "
</table><p><input type='submit' name='horses' value='Generate Scores'></p>
</form>
";
}

elseif(isset($_POST['horses']))

{

$countscores = 0;

echo "<P>[b]FEH {$_POST['level']}[/b]</P>";

while ($countscores < $_POST['nohorses']){

$countscores = $countscores + 1;
$starters = $_POST['nohorses'];

**A bunch of data goes here to randomize a big set of scores**  

$row = array ("name" => "$_POST[$horse]", "rider" => $_POST[$rider], "conformation" =>"$average", "frame" =>"$average2", "legsfeet" =>"$average3", "walk" =>"$average4", "trot" =>"$average5", "correctness" =>"$average6", "impression" =>"$average7", "score" => "$overall");
$horse_scores[] = $overall;
$horse_list[] = $row;

} // End of loop. 


for ($i = 0; $i < count($horse_scores); $i++){

$scores[] = $horse_scores[$i];
}
array_multisort($scores, SORT_DESC, $horse_scores, $horse_list);

for ($i = 0; $i < $starters; $i++){
   $place = $i + 1;

   echo "$place  
         {$horse_list[$i]['name']} owned by 
         {$horse_list[$i]['rider']} [size=85][i]
         [{$horse_list[$i]['conformation']} | 
         {$horse_list[$i]['frame']} | 
         {$horse_list[$i]['legsfeet']} | 
         {$horse_list[$i]['walk']} | 
         {$horse_list[$i]['trot']} | 
         {$horse_list[$i]['correctness']} | 
         {$horse_list[$i]['impression']}][/i][/size] 
         {$horse_list[$i]['score']}<br>";


$query ="INSERT into `fehresults`(name,level,date,event,conformation,frame,legsfeet,walk,trot,correctness,impression,final,starters,place,points)
VALUES ('{$horse_list[$i]['name']}','{$_POST['level']}','{$_POST['yearofshow']}','{$_POST['showname']}','{$horse_list[$i]['conformation']}','{$horse_list[$i]['frame']}','{$horse_list[$i]['legsfeet']}','{$horse_list[$i]['walk']}','{$horse_list[$i]['trot']}','{$horse_list[$i]['correctness']}','{$horse_list[$i]['impression']}','{$horse_list[$i]['score']}','$starters','$place','$points')";
$result = mysql_query($query) or die ("Could not execute query : $query." .     mysql_error()); 
if ($result) {
$text = "The results for this show have been added to the FEH/YEH database."; 
}else{ 
$text = "There was a problem adding the results to the database, please try again."; 
}   

} // End of for loop. 
echo "<p align='center'><a href='resultspage.php'>RANDOMIZE ANOTHER SET OF RESULTS</a>    </p>";

} // End of horses. 

else {

echo $detailsform;

}

?></body>

Answer 1

That's why escaping your data or using prepared statements are important. They sanitize your data and make it safe to insert into your database. Note that prepared statements are more secure while escaping your query means you need less queries run (prepared statements need at least 2 queries).

Escaping is simple

$sql = 'INSERT table SET
    field = "' . mysqli_real_escape_string($_POST['field']) . '"
    WHERE value = "' . mysqli_real_escape_string($_POST['value']) . '"';

The good side is this is only one query you need to run and you're done. The bad side is there are some scenarios where this could have security holes (mainly where you're not careful on character encoding)