Converting static pages to dynamic pages using PHP and MySQL, Part 5

The challenge:

Take Adam Walker’s Carrajina dictionary (http://carrajina.conlang.org/dicthome.html) and stick it in a database so that Adam can add an entry whenever he wants without re-writing an html page and possibly messing up his html. One stricture is that Adam does not want to learn too much new stuff – no SQL, no PHP, just HTML. And if he can enter SAMPA and have it turn into IPA, that’s a plus!

Step 1 (data prep) is here, Step 2 (create database and load data) is here, and Step 3 (generating listings) is here. Part 4 on adding new entries is here. This is part 5.

Updating an entry uses the same form as adding an entry, but with the values pre-filled. Here is a screenshot of the form with an entry to edit. screenshot of edit-entry form

And here is the code to generate this form. First some code in the html head:

switch ($lang) {
   case 'eng':
      if ((isset($id)) && (is_numeric($id))) {
         $_SESSION['id'] = $id;
         $data = new EnglishEntry($mysqli, $id);
         $task = 'edit';
         $_SESSION['task'] = $task;
         $dbc = new EnglishController();
      }
   break;
   case 'car':
      if ((isset($id)) && (is_numeric($id))) {
         $_SESSION['id'] = $id;
         $data = new CarrajinaEntry($mysqli, $id);
         $task = 'edit';
         $_SESSION['task'] = $task;
         $dbc = new CarrajinaController();
      }
   break;
}
?>

Now for code in the body:

<!-- the big form -->
<form method="post" action="edit.php">
<?php
$term = null;
$pron = null;
$defs = null;

if (isset($data)) {
   $term = $data->getTerm();
   $pron = $data->getPron();
   $delforms[] = $dbc->formDelete($id, 'delentry',
      "Delete Full Entry "$term"");
   $defs = $data->getDefs();
}
?>
<!-- term and pronounciation -->
<p>
Word &nbsp; <input type="text" id="term" name="term"
value="<?php echo $term; ?>" />
&nbsp; Sampa or IPA &nbsp; <input
type="text" id="pron" name="pron" value="<?php echo $pron; ?>"
class="ipa" />
<span class="replace">click for ipa</span>
</p>

Easy enough. The $delforms array is used to hold delete links for each item that can be independently deleted.

<!-- definitions -->
<?php
$i = 1;
if (isset($defs)) {
   foreach ($defs as $defno => $defentry) {
      $delforms[] = $dbc->formDelete($defentry[0],
      'deldef', "Delete Def #$defno");
?>
<p>
Def # <input type="text"  class="num"
name="<?php echo "def[$defno][defno]"; ?>"
value="<?php echo $defno; ?>" />
&nbsp; POS <input type="text"
class="dp" name="<?php echo "def[$defno][pos]"; ?>"
value="<?php echo $defentry[1]; ?>" />
&nbsp; Definition Text
<textarea rows='3' cols='48' name="<?php echo
"def[$defno][deftext]"; ?>"><?php echo $defentry[2]; ?></textarea>
<input type="hidden" name="<?php echo "def[$defno][defid]"; ?>"
value="<?php echo $defentry[0]; ?>" />
</p>
<?php
      $i = $defno + 1;
   }
}
?>
<!-- add a definition -->
<div class="animate">
<p class="toggleable">New Definition +</p>
<div class="newitem">
<p>Def # <input type="text"  class="num"
name="<?php echo "def[$i][defno]"; ?>"  /> &nbsp; POS
<input type="text" class="dp" name="<?php echo "def[$i][pos]"; ?>" />
&nbsp; Definition Text <textarea rows='3' cols='48'
name="<?php echo "def[$i][deftext]"; ?>"></textarea>
</p>
</div>
</div>

This code prints definition form fields for each definition plus form fields for adding a new definition. The following adds the other form fields for English or for Carrajina entries.

<!-- extras -->
<!-- English first -->
<?php
if ($lang == 'eng') {
   //subentries
   $j = 1;
   if (isset($data)) {
      $subids = $data->getSubIDs();
      $count = count($subids);
      if ($count != 0) {
         foreach ($subids as $subid) {
            $sub = new EnglishEntry($mysqli, $subid);
            $sterm = $sub->getTerm();
            $spron = $sub->getPron();
            $delforms[] = $dbc->formDelete($subid, 'delsub',
               "Delete Subentry "$sterm"");
?>
<p>
Subentry <input type="text"
name="<?php echo "subentry[$j][sterm]"; ?>"
value="<?php echo $sterm; ?>" /> &nbsp; Sampa or IPA
<input type="text" name="<?php echo "subentry[$j][spron]"; ?>"
value="<?php echo $spron; ?>" class="ipa" />
<span class="replace">click for ipa</span>
<input type="hidden" name="<?php echo "subentry[$j][subid]"; ?>"
value="<?php echo $subid; ?>" />
</p>
<?php
            //subentry defs
            $defs = $sub->getDefs();
            $k = 1;
            if (!empty($defs)) {
               foreach ($defs as $defno => $defentry) {
                  $delforms[] = $dbc->formDelete($defentry[0],
                    'deldef', "Delete Subentry "$sterm" Def #$defno");
?>
<p>
Def # <input type="text"  class="num" value="<?php echo $defno; ?>"
name="<?php echo "subentry[$j][sdef][$defno][defno]"; ?>" />
&nbsp; POS <input type="text"  class="dp"
value="<?php echo $defentry[1]; ?>"
name="<?php echo "subentry[$j][sdef][$defno][pos]"; ?>"  />
&nbsp; Definition Text <textarea rows='3' cols='48'
name="<?php echo "subentry[$j][sdef][$defno][deftext]"; ?>"><?php
echo $defentry[2]; ?></textarea>
<input type="hidden"  value="<?php echo $defentry[0]; ?>"
name="<?php echo "subentry[$j][sdef][$defno][defid]"; ?>" />
</p>
<?php
               $k = $defno + 1;
            }//end foreach ($defs as $defno => $defentry)
         }//end if (!empty($defs))
?>
<div class="animate">
<p class="toggleable">New Subentry Definition +</p>
<div class="newitem">
<p>
Def # <input type="text"  class="num"
name="<?php echo "subentry[$j][sdef][$k][defno]"; ?>" />
&nbsp; POS <input type="text"  class="dp"
name="<?php echo "subentry[$j][sdef][$k][pos]"; ?>"  />
&nbsp; Definition Text <textarea rows='3' cols='48'
name="<?php echo "subentry[$j][sdef][$k][deftext]"; ?>"></textarea>
<input type="hidden"
name="<?php echo "subentry[$j][sdef][$k][defid]"; ?>" />
</p>
</div>
</div>
<?php
         $j++;
      }//end foreach ($subids as $subid)
   }//end if ($count != 0)
}//end if (isset($data))
?>
<!-- new subentry -->
<div class="animate">
<p class="toggleable">New Subentry +</p>
<div class="newitem">
<p>
Subentry <input type="text"
name="<?php echo "subentry[$j][sterm]"; ?>"  />
&nbsp; Sampa or IPA
<input type="text" name="<?php echo "subentry[$j][spron]"; ?>"
class="ipa" />
<span class="replace">click for ipa</span>
</p>
<p>
Def # <input type="text"  class="num"
name="<?php echo "subentry[$j][sdef][1][defno]"; ?>" />
&nbsp; POS  <input type="text"  class="dp"
name="<?php echo "subentry[$j][sdef][1][pos]"; ?>" />
&nbsp; Definition Text  <textarea rows='3' cols='48'
name="<?php echo "subentry[$j][sdef][1][deftext]"; ?>"></textarea>
</p>
</div>
</div>

<!-- Carrajina extras -->
<?php
} else if ($lang == 'car') {
   $etym = null;
   if (isset($data)) {
      $etym = $data->getEtymology();
   }//end if (isset($data))
?>
<!-- etymology -->
<p>Etymology   
<textarea rows='3' cols='48' name="etymology"><?php
echo $etym; ?></textarea>
</p>

<!-- Idioms -->
<p class="help">Idioms should be entered in lower case.</p>
<?php
   $idioms = null;
   $p = 1;
   if (isset($data)) {
      $idioms = $data->getIdioms();
      if (!empty($idioms)) {
         foreach ($idioms as $idiomentry) {
            $delforms[] = $dbc->formDelete($idiomentry[2],
              'delidiom',"Delete Idiom "$idiomentry[0]"");
?>
<p>
Idiom <input type="text" name="<?php echo "idioms[$p][idiom]"; ?>"
value="<?php echo $idiomentry[0]; ?>" />
&nbsp; Definition <textarea rows='3' cols='48'
name="<?php echo "idioms[$p][idiomdef]"; ?>"><?php
echo $idiomentry[1]; ?></textarea>
<input type="hidden"  name="<?php echo "idioms[$p][idiomid]"; ?>"
value="<?php echo $idiomentry[2]; ?>" />
</p>
<?php
            $p++;
         }//end foreach ($idioms as $idiomentry)
      }//end if (!empty($idioms))
   }//end if (isset($data))
?>
<!-- New idioms -->
<div class="animate">
<p class="toggleable">New Idiom +</p>
<div class="newitem">
<p>Idiom
<input type="hidden"  name="<?php echo "idioms[$p][idiomid]"; ?>"  />
<input type="text"  name="<?php echo "idioms[$p][idiom]"; ?>" />
&nbsp; Definition
<textarea rows='3' cols='48' name="<?php
echo "idioms[$p][idiomdef]"; ?>"></textarea>
</p>
</div>
</div>

<!-- Note -->
<?php
   $note = null;
   if (isset($data)) {
      $note = $data->getNote();
      if ($note[0] > 0) {
         $delforms[] = $dbc->formDelete($note[0], 'delnote',
            "Delete Note for "$term"");
      }
   }
?>
<p>
Note   
<input type="hidden" name="note[noteid]"
value="<?php echo "$note[0]"; ?>"  />
<textarea rows='3' cols='48' name="note[note]"><?php
echo "$note[1]"; ?></textarea>
</p>
<?php
}//end if lang
?>

<!-- Submit! -->
<p><input type="hidden"  name="task" value="<?php echo $task; ?>" />
<input type="hidden"  name="lang" value="<?php echo $lang; ?>" />
<!-- this will be blank for new entries -->
<input type="hidden" name="entryID"
value="<?php if (isset($id)) {echo $id;} ?>" />
<input type="submit" value="Submit!" /></p>
</form>

<!-- Now for those nasty deletes -->
<div>
<?php
if (isset($delforms)) {
   echo "<h2>Deletes</h2>";
   foreach ($delforms as $form) {
      echo $form;
   }
}
?>
</div>

That’s the form. One thing that might bite: Make sure there is no space or other characters between empty <textarea></textarea> code. If there is, the space will show up as a value when processing the data.

The code for processing updates is very similar to the code for processing adds.

public function updateEntry($mysqli, $entry) {
   $id = $this->sanitize($entry['entryID']);
   $term = $this->process($entry['term']);
   $pron = $this->process($entry['pron']);
   $etym = $this->process($entry['etymology']);
   $csort = $this->getSort($term);

   $query = "UPDATE carrajina_entry SET carrajina_sort=?,
      carrajina_term=?, carrajina_pronounciation=?,
      etymology=? WHERE carrajina_entryID =?";

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("ssssi", $csort, $term, $pron, $etym, $id);
      $stmt->execute();
      $stmt->close();
   }

   //update defs and add new defs for entry, if
   foreach ($entry['def'] as $def) {
      if (!empty($def['defid'])) {
         $this->updateCardefs($mysqli, $def);
      } else {
         $this->addDefs($mysqli, $def, $id);
      }
   }

   //update idioms, if and add new, if
   if (isset($entry['idioms'])) {
      foreach ($entry['idioms'] as $idiom) {
         if (!empty($idiom['idiomid'])) {
            $this->updateIdioms($mysqli, $idiom);
         } else {
            $this->addIdioms($mysqli, $idiom, $id);
         }
      }
   }

   //update note, if
   if (!empty($entry['note']['noteid'])) {
      $noteid = $this->process($entry['note']['noteid']);
      $note = $this->process($entry['note']['note']);

      $query = "UPDATE carrajina_note SET note=? WHERE noteID =?";

      $stmt = $mysqli->stmt_init();
      if ($stmt = $mysqli->prepare($query)) {
         $stmt->bind_param("si", $note, $noteid);
         $stmt->execute();
         $stmt->close();
      }
   } else if (!empty($entry['note']['note'])) {
      $this->addNote($mysqli, $entry['note'], $id);
   } else {
      //nada
   }

   return $id;
}

And that is pretty much it. I enjoyed doing this and I think Adam enjoyed having it done. 🙂

10 PHP string functions

Here are 10 useful PHP string functions.

$string = "Hello World";

1, 2, 3: trim() along with ltrim() and rtrim() strip characters from a string. trim($string) without any further arguments will strip all spaces, carriage returns, new lines, null characters, and vertical tabs from $string.

echo trim($string, 'ld');

returns “Hello Wor” because trim() only trims the edges of a string. ltrim() trims from the left only, rtrim() from the right only, and trim() from both edges.

4: str_replace() replaces all instances of a given character with another. To strip out all ‘l’s from our string, use

echo str_replace('l', '', $string);

This returns “Heo Word”.

5, 6: strtolower() transforms a string into all lower-case letters.

echo strtolower($string);

returns “hello world”. strtoupper() makes everything upper case – “HELLO WORLD”.

7: strlen() returns the length of a string.

echo strlen($string);

returns “11”. This can be a useful way to check if you have the right string.

8: substr() returns a portion of the string. It helps to know the length of the string, because

echo substr($string, 3);

will start at the 4th character (or after the 3rd, if that’s easier for you) in the string and continue to the end, returning “lo World”.

echo substr($string, 10);

will return “d”. One can use negative numbers:

echo substr($string, -1);

will also return “d”. Here substr() starts at the end of the string and returns one character.

echo substr($string, -3);

return “rld”. You can also specify a length for substr() to return.

echo substr($string, 0, 3);

returns “Hel”. It starts at position 0, and returns 3 characters. Lengths can be negative. Though with a negative length, the beginning point is the end of the word.

echo substr($string, 0, -3);

returns “Hello Wo”.

echo substr($string, 1, -3);

removes the first character (i.e. it starts after the first character, and removes the last three characters, returning “ello Wo”.

9, 10: strpos() finds the first position of a character in a string.

echo strpos($string, 'l');

returns “2”. “H” is position 0, “e” is position 1, and “l” is position 2 (and 3, and 9). strpos() is case-sensitive. stripos() is the equivalent case-insensitive function.

Converting static pages to dynamic pages using PHP and MySQL, Part 4

The challenge:
Take Adam Walker’s Carrajina dictionary (http://carrajina.conlang.org/dicthome.html) and stick it in a database so that Adam can add an entry whenever he wants without re-writing an html page and possibly messing up his html. One stricture is that Adam does not want to learn too much new stuff – no SQL, no PHP, just HTML. And if he can enter SAMPA and have it turn into IPA, that’s a plus!

Step 1 (data prep) is here, Step 2 (create database and load data) is here, and Step 3 (generating listings) is here.

Here is the html for adding a new English entry.

<h2>Add New Entry</h2>

<form action="edit.php" method="post">
<p>
Word  
<input id="term" type="text" value="" name="term" />
  Sampa or IPA
<input id="pron" class="ipa" type="text" value="" name="pron" />
<span class="replace">click for ipa</span>
</p>

<div class="animate">
<p class="toggleable">New Definition +</p>
<div class="newitem" style="display: none;">
<p>
Def #
<input class="num" type="text" name="def[1][defno]" />
  POS
<input class="dp" type="text" name="def[1][pos]" />
  Definition Text
<textarea name="def[1][deftext]" cols="48" rows="3"></textarea>
</p>
</div>
</div>

<div class="animate">
<p class="toggleable">New Subentry +</p>
<div class="newitem" style="display: none;">
<p>
Subentry
<input type="text" name="subentry[1][sterm]" />
  Sampa or IPA
<input class="ipa" type="text" name="subentry[1][spron]">
<span class="replace">click for ipa</span>
</p>
<p>
Def #
<input class="num" type="text" name="subentry[1][sdef][1][defno]">
  POS
<input class="dp" type="text" name="subentry[1][sdef][1][pos]">
  Definition Text
<textarea name="subentry[1][sdef][1][deftext]"
     cols="48" rows="3"></textarea>
</p>
</div>
</div>

<p>
<input type="hidden" value="add" name="task">
<input type="hidden" value="eng" name="lang">
<input type="hidden" value="" name="entryID">
<input type="submit" value="Submit!">
</p>

</form>

This uses some Javascript (or rather jQuery), stored at the bottom of the page:

<script type="text/javascript">
//hides and shows new item sections
$(document).ready(function () {
	$('.newitem').hide();

	$('.animate').children('.toggleable').click(function () {
		$(this).parent().children('.newitem').toggle();
	});
});

//click for ipa
$('.replace').click(function () {
	var inputtext = $(this).prev('.ipa');
	var str = inputtext.val();
	str = str.replace(/a/g, 'ä')
				 .replace(/E/g,'ɛ')
				 .replace(/I/g, 'ɪ')
				 .replace(/J/g, 'ɲ')
				 .replace(/N/g, 'ŋ')
				 .replace(/O/g, 'ɔ')
				 .replace(/U/g, 'ʊ')
				 .replace(/S/g, 'ʃ')
				 .replace(/T/g, 'θ')
				 .replace(/U/g, 'ʊ')
				 .replace(/V/g, 'ʌ')
				 .replace(/W/g, 'ʍ')
				 .replace(/Z/g, 'ʒ')
				 .replace(/3/g, 'ɜ')
				 .replace(/4/g, 'ɾ')
				 .replace(/@/g, 'ə')
				 .replace(/&/g, 'æ')
				 .replace(/,/g, 'ˌ')
				 .replace(/\?/g, 'ʔ');
	var language = "<?php echo $lang; ?>";
	if (language == "eng") {
		str = str
				 .replace(/m=/g, 'm̩')
				 .replace(/n=/g, 'n̩')
				 .replace(/l=/g, 'l̩')
				 .replace(/r/g, 'ɹ');
	}
	if (language == "car") {
		str = str.replace(/d/g, 'd̪')
				 .replace(/d̪ʒ/g, 'd͡ʒ')
				 .replace(/t/g, 't̪')
				 .replace(/t̪ʃ/g, 't͡ʃ')
			     .replace(/n/g, 'n̪');
	}
	$(this).prev('.ipa').val(str);
});
</script>

The click for ipa code is simply a series of global replace functions.

Processing this form works like so:

public function addEntry($mysqli, $entry) {
   $hentryID = null;
   $term = $this->process($entry['term']);
   $pron = $this->process($entry['pron']);

   $query = "INSERT INTO english_entry (english_term,
      english_pronunciation, head_entryID) VALUES (?,?,?)";

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("ssi", $term, $pron, $hentryID);
      $stmt->execute();
      $stmt->close();
   }

   $entryid = $mysqli->insert_id;
   $this->addDefs($mysqli, $entry['def'][1], $entryid);

   if (isset($entry['subentry'])) {
      $this->addSubentry($mysqli, $entry['subentry'][1], $entryid);
      $subid = $mysqli->insert_id;
      $this->addDefs($mysqli, $entry['subentry'][1]['sdef'][1],
         $subid);
   }

   return $entryid;
}

This function calls the process() and addDefs() functions and sometimes the addSubentry() function. process() is part of the parent class, PageController.

class PageController {

private $fake;
private $real;

function __construct() {
   $this->fake = array('[i]', '[/i]', '[b]', '[/b]',
      '[sup]', '[/sup]');
   $this->real = array('<i>', '</i>', '<b>', '</b>',
      '<sup>', '</sup>');
}

public function makeReal($str) {
   $str = str_replace($this->fake, $this->real, $str);
   return $str;
}

public function makeFake($str) {
   $str = str_replace($this->real, $this->fake, $str);
   return $str;
}

public function sanitize($str) {
   $str = trim($str);
   $str = strip_tags($str);
   $str = htmlspecialchars($str, ENT_QUOTES, "UTF-8");
   return $str;
}

public function process($str) {
   $str = $this->makeFake($str);
   $str = $this->sanitize($str);
   $str = $this->makeReal($str);
   return $str;
}

}

So process() changes any allowed html tags into [tag] and [/tag], then trims the string, and strips all quotes and special characters, and then changes [tag] and [/tag] into <tag> and </tag>.

Here is addDefs():

private function addDefs($mysqli, $def, $id) {
   $defno = $this->sanitize($def['defno']);
   $pos = $this->process($def['pos']);
   $deftext = $this->process($def['deftext']);

   $query = "INSERT INTO english_definition (english_entryID,
      definition_number, part_of_speech, definition)
      VALUES (?,?,?,?)";

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("iiss", $id, $defno, $pos, $deftext);
      $stmt->execute();
      $stmt->close();
   }
   $defid = $mysqli->insert_id;

   return $defid;
}

Note that we don’t actually use the returned value, but it is useful to have for debugging purposes. Likewise with addSubentry().

private function addSubentry($mysqli, $sub, $id) {
   $term = $this->process($sub['sterm']);
   $pron = $this->process($sub['spron']);

   $query = "INSERT INTO english_entry (english_term,
      english_pronunciation, head_entryID)
      VALUES (?,?,?)";

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("ssi", $term, $pron, $id);
      $stmt->execute();
      $stmt->close();
   }
   $subid = $mysqli->insert_id;

   return $subid;
}

Adding an entry to the Carrajina side is similar. The html form is different, of course, and the code is somewhat different, too. Here is addEntry():

public function addEntry($mysqli, $entry) {
   $term = $this->process($entry['term']);
   $pron = $this->process($entry['pron']);
   $etym = $this->process($entry['etymology']);
   $csort = $this->getSort($term);

   $query = "INSERT INTO carrajina_entry (carrajina_sort,
      carrajina_term, carrajina_pronunciation, etymology)
      VALUES (?,?,?,?)";

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("ssss", $csort, $term, $pron, $etym);
      $stmt->execute();
      $stmt->close();
   }
   $entryid = $mysqli->insert_id;

   //add defs
   $this->addDefs($mysqli, $entry['def'][1], $entryid);

   //add idioms, if
   if (isset($entry['idioms'])) {
      $this->addIdioms($mysqli, $entry['idioms'][1], $entryid);
   }

   //add note, if
   if (isset($entry['note'])) {
      $this->addNote($mysqli, $entry['note'], $entryid);
   }

   return $entryid;
}

carrajina_sort is a column added to the carrajina_entry table to enable a custom sort value. getSort() works like so:

private function getSort($value) {
   $chararray = array('<sup>', '</sup>', 'ch', 'dj', 'rr', 'ñ',
      'Ch', 'Dj', 'Rr', 'Ñ', 'ó',
      'a', 'b', 'c', 'd', 'e', 'f',
      'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p',
      'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z',
      'A', 'B', 'C', 'D', 'E', 'F',
      'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P',
      'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
      '-', "'", ' ', '...');
   $numberarray = array('000', '', '13', '15', '31', '26',
      '13', '15', '31', '26', '27',
      '10', '11', '12', '14', '16', '17',
      '18', '19', '20', '21', '22', '23', '24', '25', '27', '28',
      '29', '30', '32', '33', '34', '35', '36', '37', '38', '39',
      '10', '11', '12', '14', '16', '17',
      '18', '19', '20', '21', '22', '23', '24', '25', '27', '28',
      '29', '30', '32', '33', '34', '35', '36', '37', '38', '39',
      '', '', '00', '00');
   $value = str_replace($chararray, $numberarray, $value);
   return $value;
}

This converts a Carrajina word into a string of numbers that correspond to the Carrajina sort order.

Carrajina’s addDefs():

private function addDefs($mysqli, $def, $id) {
   $defno = $this->sanitize($def['defno']);
   $pos = $this->process($def['pos']);
   $deftext = $this->process($def['deftext']);

   $query = "INSERT INTO carrajina_definition (carrajina_entryID,
      definition_number, part_of_speech, definition)
      VALUES (?,?,?,?)";

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("iiss", $id, $defno, $pos, $deftext);
      $stmt->execute();
      $stmt->close();
   }
}

addIdioms():

private function addIdioms($mysqli, $idiom, $id) {
   $iterm = $this->process($idiom['idiom']);
   $idef = $this->process($idiom['idiomdef']);
   $csort = $this->getSort($iterm);

   $query = "INSERT INTO carrajina_idiom (carrajina_entryID,
      carrajina_sort, carrajina_idiom, carrajina_idiom_definition)
      VALUES (?,?,?,?)";

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("iiss", $id, $csort, $iterm, $idef);
      $stmt->execute();
      $stmt->close();
   }
}

addNote():

private function addNote($mysqli, $entry, $id) {
   $note = $this->process($entry['note']);
   $query = "INSERT INTO carrajina_note (note, carrajina_entryID)
      VALUES (?,?)";

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("si", $note, $id);
      $stmt->execute();
      $stmt->close();
   }
}

And that’s enough for today. I’ll do updating an entry in Part 5.

Converting static pages to dynamic pages using PHP and MySQL, Part 2

The challenge:
Take Adam Walker’s Carrajina dictionary and stick it in a database so that Adam can add an entry whenever he wants without re-writing an html page and possibly messing up his html. One stricture is that Adam does not want to learn too much new stuff – no SQL, no PHP, just HTML. And if he can enter SAMPA and have it turn into IPA, that’s a plus!

Step 1 (data prep) is here

Step 2 involves importing data into a database. I  will skip blogging Step 2 for now. Putting data into a database is more complicated than taking it back out again. But I will blog it eventually. Promise.

Step 3: Getting the data back out of the database to generate the dictionary listings.

So, the first thing we need to do is connect to the database. I mentioned in a previous entry that PHP’s mysqli extension is the preferred extension to use with a MySQL database. Here’s how that works:

$host = "my_host"; //"localhost" or "http://mysql.host.com"...
$user = "my_username"; //an authorized user of MySQL
$password = "my_password"; //my_username's password
$database = "my_database"; //the database we want to use.

$mysqli = new mysqli($host, $user, $password, $database);

if ($mysqli->connect_error) {
    die('Connect Error ('. $mysqli->connect_errno .') '
            . $mysqli->connect_error);
}

After connecting to the database, the next step is writing a select query to find the appropriate data. Querying the database with mysqli uses the following steps, initialize the statement, prepare the (parameterized) statement, bind the variables to the parameterized statement, and execute the query. (More on this in a future post.) One of the nice things about using PHP (or any programming language, really) to make dynamic pages is that you do not have to write a complicated SQL query to get everything from the database at once. Instead, you can write a sequence of queries and use PHP to do things with the results.

Adam currently has his dictionary set up so that each letter is on its own page. To duplicate this, I’ve used the following queries:

public function getList($mysqli, $letter) {
   $like = $letter . '%';  //% is the SQL wildcard character

   $query = "SELECT english_entryID FROM english_entry
   WHERE head_entryID IS NULL  /* so we don't get subentries */
   AND english_term LIKE ? ORDER BY english_term ASC";

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("s", $like);
      $stmt->execute();
      $stmt->bind_result($entryID);
      while ($stmt->fetch()) {
         $listIDs[] = $entryID;
      }
      $stmt->close();        
   }
   return $listIDs;
}

What this does, when called, is execute an SQL statement that looks in the table english_entry and finds all the english_entryIDs in the table that match the following conditions: head_entryID does not exist (and therefore this is not a subentry) and english_term starts with the passed $letter. The ‘?’ in the query is replaced by the variable $like, which is $letter followed by the ‘%’ sign to match anything. So, if $letter = ‘a’, then the SQL statement becomes:

SELECT english_entryID FROM english_entry WHERE head_entryID IS NULL
AND english_term LIKE 'a%' ORDER BY english_term ASC;

This SQL statement returns a list of english_entryIDs

english_entryID
1
2
3

which is then put into a PHP array array( [0] => 1, [1] =>2, [2] =>3, ...); for ease of use.

Now that we have the array of english_entryIDs that start with ‘a’ and are not subentries, we can use more code to get each actual entry. For this project, I have put all the code that gets an English language entry into a class called EnglishListing. The following code is then used on the dictionary pages to generate a listing.

//make sure $letter is lower case
$letter = strtolower($letter);

$eng = new EnglishListing();

$list = $eng->getList($mysqli, $letter);
foreach ($list as $entryID) {
    echo $eng->getEntry($mysqli, $entryID);
}

So after calling getList(), for each english_entryID returned by the getList function, we call getEntry:

public function getEntry($mysqli, $entryID) {
    //an entry has a term, a pronunciation,
    //one or more defs (num, pos, deftext),
    //and 0 or more subentries (just like an entry).
    $termrow = $this->getTerm($mysqli, $entryID);
    $defrow = $this->getDefs($mysqli, $entryID);
    $subrow = $this->getSubentry($mysqli, $entryID);

    return "<dt>$termrow</dt><dd>$defrow $subrow</dd>\n";
}//end function

OK. That code breaks up the process of getting an entry into 3 steps: getTerm(), getDefs(), and getSubentry().

getTerm() executes an SQL statement that returns english_term and english_pronunciation. It then wraps each of these in span tags and returns $termrow:

private function getTerm($mysqli, $entryID) {
   $termrow = "";
   $query = "SELECT english_term, english_pronunciation
              FROM english_entry WHERE english_entryID =?";

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("i", $entryID);
      $stmt->execute();
      $stmt->bind_result($term, $pron);
      while ($stmt->fetch()) {
         $termrow .= "<span class='term'>$term</span>
                      <span class='pron'>$pron</span>";
      }
      $stmt->close();   
   }
   return $termrow;
}//end function

getDefs() executes an SQL statement that returns definition_number, part_of_speech, definition for each definition matched by the given english_entryID. Where only 1 definition exists, $defrow contains only the part_of_speech and definition values. Otherwise, it contains all the values. ‘.=’ in the PHP code concatenates the value to the existing variable. An entry with two definitions, then would have $defrow equal to “$definition_number. $part_of_speech $def. $definition_number. $part_of_speech $def. “.

private function getDefs($mysqli, $entryID) {
   $defrow = "";

   $query = "SELECT definition_number, part_of_speech, definition
      FROM english_definition
      WHERE english_entryID = ?
      ORDER BY definition_number ASC";

   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("i", $entryID);
      $stmt->execute();
      $stmt->bind_result($defno, $pos, $def);
      $stmt->store_result(); //so I can use $stmt->num_rows
      while ($stmt->fetch()) {
         $defno = "<span class='defno'>$defno</span>";
         $pos = "<span class='pos'>$pos</span> ";
         $def = "<span class='deftext'>$def</span> ";
         if (($stmt->num_rows) == 1) {
            $defrow .= "$pos $def ";
         } else {
            $defrow .= "$defno. $pos $def ";
         }
      }//end while
      $stmt->close();        
   }//end if 

   return $defrow;
}//end function

getSubentry() starts by calling another function checkSubentry() which checks to see if the entry has any subentries. If there is a subentry, then checkSubentry() returns the english_entryIDs for the subentries. Otherwise it returns an empty array. getSubentry() then checks the length (number of values) of that array (must be greater than or equal to 1), and for each value in the array it calls getTerm() and getDefs().

private function getSubentry($mysqli, $entryID) {
   $subrow = "";

   $headentryIDs = $this->checkSubentry($mysqli, $entryID);

   if (count($headentryIDs) >= 1) {
      foreach ($headentryIDs as $entryID) {
         $subrow .= "<br />&#x2022; "
            . $this->getTerm($mysqli, $entryID);
         $subrow .= $this->getDefs($mysqli, $entryID);
      }
   } 

   return $subrow;
}//end function

private function checkSubentry($mysqli, $entryID) {
   $headentryIDs=array();

   $query = "SELECT english_entryID FROM english_entry
             WHERE head_entryID =?"; 

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("i", $entryID);
      $stmt->execute();
      $stmt->bind_result($eentryID);
      while ($stmt->fetch()) {
         $headentryIDs[] = $eentryID;
       }
      $stmt->close();
   }
   return $headentryIDs;
}

That is the code for the EnglishListing class. The Carrajina side looks similar. There were some challenges in that ‘ch’ and ‘dj’ could not be listed under ‘c’ and ‘d’. I’ll talk about that later.

<?php

class CarrajinaListing {

public function getList($mysqli, $letter) {
   //extra letters ch, dj, ñ, rr
   $like = $letter . '%';  //% is the SQL wildcard character
   $like2 = ucfirst($letter) . '%'; 

   //BINARY means that MySQL doesn't ignore accented characters,
   //n-tilde, and the like. it also doesn't ignore capitalization
   //without the BINARY keyword, accents and capitalization are
   //ignored.
   $query = "SELECT carrajina_entryID FROM carrajina_entry ";

   switch ($letter) {
      case 'c':
         $query .= "WHERE (carrajina_term LIKE BINARY ?
                    OR carrajina_term LIKE BINARY ?) AND
                    (carrajina_term NOT LIKE 'ch%') ";
      break;
      case 'd':
         $query .= "WHERE (carrajina_term LIKE BINARY ?
                    OR carrajina_term LIKE BINARY ?) AND
                    (carrajina_term NOT LIKE 'dj%' ) ";
      break;
      default:
         $query .= "WHERE carrajina_term LIKE BINARY ?
                    OR carrajina_term LIKE BINARY ? ";
      break;
   }

   $query .= " ORDER BY carrajina_term ASC";
   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("s", $like);
      $stmt->execute();
      $stmt->bind_result($entryID);
      while ($stmt->fetch()) {
         $listIDs[] = $entryID;
      }
      $stmt->close();        
   }
   return $listIDs;
}//end function

private function getEntry($mysqli, $entryID) {
   //an entry has a term, a pronunciation,
   //one or more defs (num, pos, deftext),
   //one or more idioms (idiom, idiomdef),
   //an etymology, and sometimes a note
   $termrow = $this->getTerm($mysqli, $entryID);
   $defrow = $this->getDefs($mysqli, $entryID);
   $idioms = $this->getIdioms($mysqli, $entryID);
   $etymology = $this->getEtymology($mysqli, $entryID);
   $note = $this->getNote($mysqli, $entryID);

   return "<dt>$termrow</dt>
           <dd>$defrow $etymology $idioms $note</dd>\n";
}//end function

private function getTerm($mysqli, $entryID) {
   $query = "SELECT carrajina_term, carrajina_pronunciation
             FROM carrajina_entry WHERE carrajina_entryID = ?";
   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("i", $entryID);
      $stmt->execute();
      $stmt->bind_result($term, $pron);
      while ($stmt->fetch()) {
           $termrow .= "<span class='term'>$term</span>
                        <span class='pron'>$pron</span>";
      }
      $stmt->close();        
   }//end if 

   return $termrow;
}//end function

private function getDefs($mysqli, $entryID) {
   $defrow = "";

   $query = "SELECT definition_number, part_of_speech, definition
             FROM carrajina_definition WHERE carrajina_entryID = ?
             ORDER BY definition_number ASC";
   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("i", $entryID);
      $stmt->execute();
      $stmt->bind_result($defno, $pos, $def);
      $stmt->store_result(); //so I can use $stmt->num_rows
      while ($stmt->fetch()) {
         $defno = "<span class='defno'>$defno</span>";
         $pos = "<span class='pos'>$pos</span> ";
         $def = "<span class='deftext'>$def</span> ";
         if (($stmt->num_rows) == 1) {
            $defrow .= "$pos $def ";
         } else {
            $defrow .= "$defno. $pos $def ";
         }
      }//end while
      $stmt->close();        
   }//end if 

   return $defrow;
}//end function

private function getIdioms($mysqli, $entryID) {
   $idioms = "";

   $query = "SELECT carrajina_idiom, carrajina_idiom_definition
             FROM carrajina_idiom WHERE carrajina_entryID = ?
             ORDER BY carrajina_idiom ASC";
   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("i", $entryID);
      $stmt->execute();
      $stmt->bind_result($idiom, $idiomdef);
      while ($stmt->fetch()) {
         $idioms .= "<span class='idiom term'>&#x2022; $idiom</span>
                     <span class='idiomdef'>$idiomdef</span> ";
      }//end while
      $stmt->close();        
   }//end if 

   return $idioms;
}//end function

private function getEtymology($mysqli, $entryID) {
   $etymology = "";

   $query = "SELECT etymology FROM carrajina_entry
             WHERE carrajina_entryID =?";

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("i", $entryID);
      $stmt->execute();
      $stmt->bind_result($etym);
      while ($stmt->fetch()) {
         $etymology .= "<span class='etymology'>$etym</span>";
      }//end while
      $stmt->close();        
   }//end if 

   return $etymology;
}//end function

private function getNote($mysqli, $entryID) {
   $note = "";

   $query = "SELECT note FROM carrajina_note
             WHERE carrajina_entryID =?";

   $stmt = $mysqli->stmt_init();
   if ($stmt = $mysqli->prepare($query)) {
      $stmt->bind_param("i", $entryID);
      $stmt->execute();
      $stmt->bind_result($cnote);
      while ($stmt->fetch()) {
         $note .= "<span class='note'>$cnote</span> ";
      }//end while
      $stmt->close();        
   }//end if
   return $note;
}//end function

}//end class
?>

I know there are things in here that I didn’t explain. Consider this your excuse to take me to task and ask questions in the comments.

Edited 9-20-2011 for aesthetic reasons.

Using mysqli and parameterized statements

mysqli is the PHP extension that is recommended for use with current versions (4.1.3 and later) of MySQL. The way to connect to a MySQL database using mysqli is:

$host = "my_host"; //"localhost" or "http://mysql.host.com"
$user = "my_username"; //an authorized user of the MySQL database
$password = "my_password"; //my_username's password
$database = "my_database"; //the database we want to use.

$mysqli = new mysqli($host, $user, $password, $database);

if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') '
            . $mysqli->connect_error);
}

Keep note of that $mysqli variable. It gets used a lot.

Querying the database can be as simple as:

$mysqli->query("some valid SQL statement");

This will return TRUE if the SQL statement was successful. So, if you are not expecting any data to be returned from the server, this bit of code will work fine:

if ($mysqli->query("some valid SQL statement") === TRUE) {
    //do stuff, or not, like print a success statement
}
$mysqli->close();

If you are using a SELECT statement to get data from the server, then you can do so:

if ($result = $mysqli->query("some valid SQL statement")) {
    //do stuff with the data
    while ($row = $result->fetch_row()) {
        //do stuff with the data in the $row array
    }
    $result->close();
}
$mysqli->close();

In addition to fetch_row(), there is also fetch_array() and fetch_assoc(). The full list of $result functions is here.

But the true wonderfulness of mysqli comes from the parameterized queries.

$stmt = $mysqli->stmt_init();
if ($stmt->prepare("some valid SQL query")) {
    $stmt->bind_params("s...", $vars);
    $stmt->execute();
    $stmt->bind_result($vars);
    while ($stmt->fetch()) {
        //do stuff with the data
    }
    $stmt->close();
}
$mysqli->close();

Yeah, right, you say. It looks complicated, but really, it isn’t. And some parts are optional.

I mentioned earlier that parameterized queries were one line of defense against sql injection attacks because they force the database to interpret anything within the bound variable as data and not as SQL instructions. So, here is how that works.

First, we initialize the statement with $stmt = $mysqli->stmt_init();. This step is optional, but it is good practice. By initializing, the next function called on $stmt has to be $stmt->prepare().

$stmt->prepare() takes a query (either directly or as a variable) and prepares it for the server. If the query requires data input, such as in a WHERE clause or in an INSERT statement, then one uses ‘?’ markers as placeholders for the data. Like so:

SELECT FirstName, LastName FROM Friends WHERE FavoriteColor = ?;

In PHP:

$stmt = $mysqli->stmt_init();
if ($stmt->prepare("SELECT FirstName, LastName
    FROM Friends WHERE FavoriteColor = ?")) {
    ...

So far, so good. Make sure not to include the normal final semi-colon in the SQL statement.

The next step is to bind the parameters, or tell the database what value the ? has. This is done using $stmt->bind_param(); This function takes a minimum of two arguments. The first is a string with information on the type of data. The second is the variable that holds the data. The information string can consist of the following characters only: b, d, i, and s. These stand for blob, double, integer, and string. Since FavoriteColor is a word, we use ‘s’ to identify the input as a string. The number and sequence of characters in the string must correspond to the number and sequence of ?s in the query. The number and sequence of the data- holding variables must correspond to the same number and sequence.

$color = "purple";
$stmt = $mysqli->stmt_init();
if ($stmt->prepare("SELECT FirstName, LastName
    FROM Friends WHERE FavoriteColor = ?")) {
       $stmt->bind_param("s", $color);
       ...

OK so far? Want another example? How about:

INSERT INTO Friends (FirstName, LastName, FavoriteColor)
     VALUES (?, ?, ?);
//the data
$firstname = "David";
$lastname = "Peterson";
$color = "orange";

$stmt = $mysqli->stmt_init();
if ($stmt->prepare("INSERT INTO Friends (FirstName, LastName,
    FavoriteColor) VALUES (?, ?, ?)")) {
       $stmt->bind_param("sss", $firstname, $lastname, $color);
       ...

See, three ?s, three characters in the first argument, and three variables with our data.

Then we execute the statement, using $stmt->execute(), which sends our query to the database.

$color = "purple";
$stmt = $mysqli->stmt_init();
if ($stmt->prepare("SELECT FirstName, LastName FROM Friends
    WHERE FavoriteColor = ?")) {
       $stmt->bind_param("s", $color);
       $stmt->execute();
       ...

Since this SELECT statement is returning two values (FirstName and LastName), we can also bind the results. This part is optional, though still a good idea.

$color = "purple";
$stmt = $mysqli->stmt_init();
if ($stmt->prepare("SELECT FirstName, LastName FROM Friends
    WHERE FavoriteColor = ?")) {
       $stmt->bind_param("s", $color);
       $stmt->execute();
       $stmt->bind_result($firstname, $lastname);
       ...

After binding the results, we can use $stmt->fetch() to get the data from the result set. All that’s left to do is close the statement with $stmt->close(), freeing up resources, and close the connection with $mysqli->close().

$color = "purple";
$stmt = $mysqli->stmt_init();
if ($stmt->prepare("SELECT FirstName, LastName FROM Friends
    WHERE FavoriteColor = ?")) {
       $stmt->bind_param("s", $color);
       $stmt->execute();
       $stmt->bind_result($firstname, $lastname);
       while ($stmt->fetch()) {
           //do stuff with the data
           echo "$firstname $lastname";
       }
       $stmt->close();
}
$mysqli->close();

The other example:

//the data
$firstname = "David";
$lastname = "Peterson";
$color = "orange";

$stmt = $mysqli->stmt_init();
if ($stmt->prepare("INSERT INTO Friends (FirstName, LastName,
    FavoriteColor) VALUES (?, ?, ?)")) {
       $stmt->bind_param("sss", $firstname, $lastname, $color);
       $stmt->execute();
       $stmt->close();
}
$mysqli->close();

INSERT does not return any results for binding. If you want information regarding the success of the query, you can use $mysqli->affected_rows to get back a count of affected rows. Even more useful is $mysqli->insert_id which returns the auto-incremented ID for the new row (if there is one, and if you call it right away before inserting or updating anything else.)

If you chose to skip binding the results, then you cannot use $stmt->fetch(). Instead, you must use  $stmt->get_result() and then $result->fetch_row() or an equivalent. Like so:

$color = "purple";
$stmt = $mysqli->stmt_init();
if ($stmt->prepare("SELECT FirstName, LastName FROM Friends
    WHERE FavoriteColor = ?")) {
       $stmt->bind_param("s", $color);
       $stmt->execute();
       $result = $stmt->get_result();
       while ($row = $result->fetch_row()) {
           //do stuff with the data in the $row array
       }
       $result->close();
}
$mysqli->close();

And that’s how you use mysqli and parameterized statements.

Oct 19, 2011: Updated to fix typos in the method name.
Apr 19, 2014: Updated to add missing commas in the code.

PHP 5.38

As of last week, PHP 5.3.8 is available and everyone ought to upgrade to it. It contains fixes to a bug introduced by 5.3.7. The full list of changes is here. People still using 5.2 especially need to upgrade, as 5.2 is no longer supported. (Which means that ISPs and others can no longer get official support from Zend and such.) So if you have a choice, use 5.3.8 or 5.3.6 if 5.3.8 is not available.

Protecting Your Data from an SQL Injection Attack

Introduction

Generally speaking, SQL injection is an attack on a website via a database attached to the site. This database might contain site content or it might contain user login and password information. A successful attack would allow the attacker complete access to the database, able to add, change, delete, and download the database content.

So how does the attack work? It works by using quote characters to end an input string prematurely, and then adding SQL statements to the input.

xkcd #327 “Exploits of a Mom” has an example of an SQL injection attack with a child named “Robert’); DROP TABLE Students; –”. The “’);” after “Robert” is a typical end to the SQL insert statement. It is followed by another SQL statement “DROP TABLE Students;” And the final “–” starts an SQL comment, thus guaranteeing that whatever the original statement was, the rest of it would be commented out.

In more detail: presumably the school has a database with a table called Students. In the normal course of things, someone would enter “Robert” into the first_name field, and Robert’s last name (let’s say it’s “Smith”) into the last_name field, and so on. The SQL statement that would be sent to the database would look like this:

INSERT INTO Students (first_name, last_name) VALUES (‘Robert’, ‘Smith’);

And one row would be inserted into the Students table. However, if instead of “Robert”, someone entered “Robert’); DROP TABLE Students; –” into the first_name field, then the following would be sent to the database:

INSERT INTO Students (first_name, last_name) VALUES (‘Robert’); DROP TABLE Students; --’, ‘Smith’);

One row would be inserted into the Students table (though last_name would be blank), and then the table Students would be deleted from the database. The last bit –’, ‘Smith’); would be interpreted as a comment and so ignored. See SQL Injection Attacks by Example for a more detailed examination of how this attack works.

The database is vulnerable anytime a user interacts with it. However, there are ways to defend against such an attack. I will discuss three of these ways:

  1. sanitize inputs
  2. use parameterized queries
  3. restrict permissions

None of these strategies is a guaranteed, will-stop-100%-of-attacks method. However, each can help lower the likelihood of a successful attack. So, these different defense strategies are not mutually exclusive, and it is best, whenever possible, to use all three.

Sanitize Inputs

The punchline of the xkcd comic has the mother saying “I hope you’ve learned to sanitize your database inputs.” One common way of sanitizing input is to escape the characters by adding another quote mark or a backslash to any quotes in the input. If the hapless school had escaped the input, then this is what would have resulted:

INSERT INTO Students (first_name, last_name) VALUES (‘Robert\’); DROP TABLE Students; --’, ‘Smith’);

The student would have a first name of “Robert’); DROP TABLE Students; –” and a last name of “Smith” and no tables would have been deleted. By escaping the single-quote with a backslash, the database interpreted it as part of the input. Characters that ought to be routinely escaped are: single quotes, double quotes, backslashes, and the NUL character. Control characters like CTRL-Z also ought to be escaped as they, too, can interfere with the interpretation of input strings.

Since this blog deals primarily with PHP, I will discuss several PHP functions that can assist.

  1. addslashes(): This function adds a backslash to single quotes, double quotes, backslashes, and NUL automatically.
  2. mysql_real_escape_string(): This is a MySQL-specific PHP function that escapes nulls, newlines, carriage returns, substitute characters, double quotes, single quotes, and backslashes. The  mysql_escape_string() function is deprecated, and should not be used, as it disregards the character set the string is in. The mysqli (mysql-improved) equivalent of mysql_real_escape_string() is mysqli::real_escape_string().

The following commands help to keep any html tags from getting into the database. Thus, if you end up displaying this data, it won’t change the html markup or add a javascript function to your page. This also helps to prevent someone from inserting a malicious script into your database.

  1. htmlspecialchars(): transforms ampersands, double quotes, less than and greater than into html entities. If the ENT_QUOTES flag is set, it also transforms single quotes. The htmlentities() function does the same thing, also encoding most Latin-1 non-ascii characters.
  2. str_replace(): can be used to replace any characters, like so:
    $string = "{user_input}";
    $bad = array('&', '"', "'", '<', '>', '\\', '%', '_');
    $good = array('&amp;', '&quot;', '&#039;', '&lt;', '&gt;', '&#092;', '&#037;', '&#095;');
    $sanitized = str_replace($bad, $good, $string);

    The backslash in $bad needs to be escaped in order to be recognized as a regular character.
  3. strip_tags() can be useful, too, as it strips the input of any html and php tags that are found.

Use parameterized queries

Parameterized queries are fun, convenient, faster, and much, much safer than merely escaping a string. Parameterized queries take an SQL statement like INSERT INTO Students (first_name, last_name) VALUES (?, ?); and then bind or assign the inputted values into the query, in essence escaping it for you. This is preferable to escaping the string yourself because it is less error-prone and you can’t forget to do it.

The mysql extension for PHP does not support parameterized queries. However, the mysqli (the ‘i’ stands for “improved”) extension (recommended for MySQL v4.1.3 and later) and the PDO extension do support parameterized queries. If you are not sure which to use, see this overview. I am partial to PDO, but mysqli is a good extension as well. I will write a future post on migrating your PHP code from mysql to mysqli.

Restrict permissions

If you are using your database to provide site content only (that is, you have no registered users), then it is a fairly simple thing to make your database read-only. That is, create a user for your database that has select permissions only and cannot insert, update, or delete any content. Use this account to connect the website with the database. That way anyone who manages to get around the escaped string in a parameterized query method cannot add, change, or delete your data through the website, accidentally or otherwise.

Of course, this is more complicated if you do have registered users. Make sure that any statements that deal with input from a user that is not logged in use a restricted, select-only account. Also, if you have your own administrative interface (a form where you enter stuff into your database rather than using phpMyAdmin or some other application), that page will need to access the database using a different account.