Writing PHP PDO (Prepared Statements)

Since I started doing dynamic PHP development I've been using the trusty mysql_* functions that PHP provides. These have worked great for me throughout the years but I've been on a kick to make my programs a bit more secure. I've long since verified data input but it still didn't seem to be good enough.

I started some research and learned about PHP Prepared Statements. I'm not much of an Object Oriented Programming guy yet but I've been trying to learn more about it and this seemed like a good one to look into. In this blog post and a followup to this I'm going to cover using PHP Prepared Statements in real world scenarios of writing a dynamic program. The followup blog post will cover importing large ammounts of data using PDO statements.

For this example we're going to query a database from a set of user inputted $_GET data.

Here is our MySQL table layout:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11   )   | NO   | PRI | NULL    | auto_increment |
| fname      | varchar(60)  | YES  |     | NULL    |                |
| lname      | varchar(60)  | YES  |     | NULL    |                |
| age        | int(6)       | YES  |     | NULL    |                |
| city       | varchar(100) | YES  |     | NULL    |                |
| state      | varchar(50)  | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

We're going to assume a form was passed using GET to this PHP script and we are looking to query on First Name, Last name and age. I'm also going to assume you have your own verify function setup to ensure valid input.

Before we start out though we need to change the way we connect to the database.

With MySQL functions we'd use:

$mysql_link = mysql_connect($dbhost, $dbuser,$dbpw);   
mysql_select_db($dbname) or die("Could not select database");

With PDO we need to do things a bit different:

$pdo = new PDO( 
    'mysql:host='.$dbhost.';dbname='.$dbname.'', 
    $dbuser, 
    $dbpw, 
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8") 
);

Starting into the actual code we use the $_GET data and verify each piece creating two arrays. One for a MySQL where statement and one for the values to pass to that where statement. As I mentioned above I'm assuming you have your own data sanitizer function to use. Mine is called verify().

 
$where_arr = array();
$where_val = array();
if (verify($_GET['fname'],array('type'=>'str','len'=>30))): //The verify function should ensure clean data. 
  $where_arr[] = 'tbl.fname LIKE ?'; 
  $where_val[] = '%'.$_GET['fname'].'%';
endif;
if (verify($_GET['lname'],array('type'=>'str','len'=>30))): //The verify function should ensure clean data. 
  $where_arr[] = 'tbl.lname LIKE ?'; 
  $where_val[] = '%'.$_GET['lname'].'%';
endif;
if (verify($_GET['age'],array('type'=>'int','len'=>3))): //The verify function should ensure clean data. 
  $where_arr[] = 'tbl.age = ?'; 
  $where_val[] = '%'.$_GET['age'].'%';
endif;

Now using MySQL functions we'd make a statement similar to:

if (count($where_arr) > 0):
$where = 'WHERE '.implode(' AND ',$where_arr);
endif;
$sql = "SELECT * FROM tbl ".$where;
$result = mysql_query($sql);
if (!$result):
  die();
endif;
$row = mysql_fetch_assoc($result);

To convert this to PDO we simple do the following:

if (count($where_arr) > 0):
$where = 'WHERE '.implode(' AND ',$where_arr);
endif;
$sql = 'SELECT * FROM tbl '.$where;
$sth = $pdo->prepare($sql);
if ($sth->execute($where_val)):
  $rows = $sth->fetchAll(PDO::FETCH_ASSOC);
endif;

From there you can loop through the $rows variable for your results. Enjoy.

Recent Blogs

Friends

Recent Tweets

User login