Having used the standard “mysql” commands in PHP for years I was wary to start using something else.  But now that “mysql” is being deprecated in PHP the need to use an alternative way of connecting to a MySQL database is needed.

First I looked at mysqli, which (if using the procedural method) offers the same function calls as I have previously used except with the added “i” (e.g. mysql_query => mysqli_query), this would make transitioning to the new procedures very easy.  However there is also PDO.

PDO is another way of accessing the MySQL database from the PHP script.  A big selling point for me was its ability to “prepare” SQL statements before inserting the variables (mysqli also offers the ability to prepare statements). So if you want to run the following statement, originally I would:

$sql = “SELECT title,price,description FROM products WHERE supplier=’$supplier’”;
$rs = mysql_query($sql);

Supplier is passed from the querystring or a post so needs to be made safe to stop SQL-Injection:

$supplier = mysql_real_escape_string($_GET[‘supplier’]);

But with a prepared PDO I can prepare the statement first.  Preparing the statement means that I tell the PDO handler what statement to expect, so it knows not to allow any other SQL statements should someone try to inject an SQL statement inside this SQL statement:

$sql = “SELECT title,price,description FROM products WHERE supplier=:supplier”;
$query = $conn->prepare($sql);

The statement is now prepared, as this is hard-coded into the PHP script, it is impossible for a hacker to alter it (without editing the file-scripting manually). We can now pass in an unaltered (no need to insert slashes using mysql_real_escape_string) variable from the querystring:

$query->execute(array(‘:supplier’=>$_GET[‘supplier’]));

PDO will then insert this into the prepared query making sure that it abides by the rules of the prepared statement.

The second advantage with PDO is that it is not database specific.  The mysql and mysqli commands are specifically for a MySQL database whereas PDO can be used on any database (with the necessary drivers installed).  This means that you can build your website for a MySQL database but should you need to change to a different database in the future you don’t need to rebuild all your database query functions, simply change where PDO sends the commands to.

• Retweet
• 
• 
• 
• 
•