In this post I'm going to show you how to use PDO to access your database and how to use this to safely pass user inputted data into your queries to help protect against SQL injection attacks.

Checking if PDO is installed

Firstly you're going to need to make sure that your build of PHP has the PDO extension installed along with the necessary PDO drivers for your chosen database system (MySQL, PostgreSQL, etc etc).

You can check for PDO in a number of ways and I'll demonstrate the quickest ways to do this below.

Method 1


echo phpinfo();

The above function will return a lot of data about your PHP environment, within all of this you will be able to find the information you need. A simple search for the string "PDO" will take you to the section you need, that is of course if PDO is available. If you manage to find the section headed by the title "PDO", you will also see what PDO drivers are available for use.

Method 2

A very simple way to detect support for PDO and if found display what PDO database drivers are available.


if(class_exists('PDO')) print_r(PDO::getAvailableDrivers());

// output
Array
(
    [0] => mysql
    [1] => pgsql
)

If you've found that you don't have access to the PDO extension I suggest speaking to your host about having it installed, I believe as of PHP 5.1.0, PDO is enabled by default.

Establishing a connection

So firstly you'll need to know your database name, database user, password, host and type of database you'll be using. With these 5 values you'll be able to initiate a successful standard database connection. So what does establishing a database connection actually look like in PDO?


define("DB_TYPE", "mysql");
define("DB_HOST", "localhost");
define("DB_NAME", "sample_name");
define("DB_USER", "sample_user");
define("DB_PASS", "random_password");

try {
	$db = NEW PDO(DB_TYPE . ":host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASS);
	$db->SetAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	
} catch(PDOException $e) {
	echo "ERROR: " . $e->GetMessage();
	
}

The above will establish a connection to your database and you're now ready to start issuing queries.

Querying the database

Using PDO:query

PDO::query statements will need to be sanitised if you're using any sort of user inputted data and so for this reason you should avoid PDO::query like the plague if you plan on passing external variables into the query.


$db->query("INSERT INTO table_name (column1, column2, column3) VALUES ('value1', 'value2', 'value3')");

$result = $db->query("SELECT * FROM table_name")->fetchAll(PDO::FETCH_ASSOC);
print_r($result);

Using prepared statements

Now this is where the real value of using PDO begins to shine, PDO:prepare allows us to bind named or unnamed place holders into our query without having to worry about sanitising them from SQL injection attacks, as PDO will take of all this for us. For example if we wanted to pass a users search term into a database query, we can do this a prepared statement.


$search = $db->prepare("SELECT * FROM table_name WHERE column_name = :user_input");
$search->bindParam(":user_input", $_POST["search_term"]);
$search->execute();
$result = $search->fetchAll();
print_r($result);

The above example is using what is known as a named placeholder and is bound to :user_input which can then be used in our prepared statement. This mitigates the need to sanitise the users input against an SQL injection attack as PDO will take of this for us. You can accomplish the above query in a number of different ways but still keep the hardening that PDO provides for our statements.


$search = $db->prepare("SELECT * FROM table_name WHERE column_name = ?");
$search->execute(array($_POST["search_term"]));
$result = $search->fetchAll();
print_r($result);

$search = $db->prepare("SELECT * FROM table_name WHERE column_name = :user_input");
$search->execute(array(":user_input" => $_POST["search_term"]));
$result = $search->fetchAll();
print_r($result);

Source Code


/*
THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
*/

<?php

define("DB_TYPE", "mysql");
define("DB_HOST", "localhost");
define("DB_NAME", "sample_name");
define("DB_USER", "sample_user");
define("DB_PASS", "random_password");

try {
	$db = NEW PDO(DB_TYPE . ":host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASS);
	$db->SetAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	
	// standard sql statement, do not use with external data
	$db->query("INSERT INTO table_name (column1, column2, column3) VALUES ('value1', 'value2', 'value3')");
	$result = $db->query("SELECT * FROM table_name")->fetchAll(PDO::FETCH_ASSOC);
	print_r($result);
	
	// named and unnamed placeholders
	$search = $db->prepare("SELECT * FROM table_name WHERE column_name = :user_input");
	$search->bindParam(":user_input", $_POST["search_term"]);
	$search->execute();
	$result = $search->fetchAll();
	print_r($result);
	
	$search = $db->prepare("SELECT * FROM table_name WHERE column_name = ?");
	$search->execute(array($_POST["search_term"]));
	$result = $search->fetchAll();
	print_r($result);

	$search = $db->prepare("SELECT * FROM table_name WHERE column_name = :user_input");
	$search->execute(array(":user_input" => $_POST["search_term"]));
	$result = $search->fetchAll();
	print_r($result);
		
} catch(PDOException $e) {
	echo "ERROR: " . $e->GetMessage();
	
}

?>