Introduction to PDO

What is PDO and Why to use PDO?

Well, PDO stands for PHP Data Objects, an advanced approach to connect to database not only mysql but several others. Here is a list of database supported by PDO-

Micro Soft SQL Server

MySQL

PostgreSQL

Oracle

Sqlite

IBM DB2

Firebird

INFORMIX etc.


The PDO extension defines a lightweight, consistent interface for accessing databases in PHP. It means that, regardless of which database you're using, use the same functions to issue queries and fetch data. There is no need to rewrite your queries again when you change your database.


Why to use PDO?

One can ask that why to use pdo? What are benefits of using PDO? Sure there are reasons to prefer PDO over mysql_* functions.


Prepared statement

Stored Procedure

Transaction

Multiple Query execution

Named Parameter

Object Mapping

Multiple Database Support

Security from SQL Injection Attack

Better Error Handling


How to connect to database?

Always try to connect inside try and catch block like this


$db="mysql";


$databasetype="mysql";


try {

switch($databasetype){

case "mysql": $dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password;

//for persistent connection

case "mysqlp": $dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password,array(PDO::ATTR_PERSISTENT => true));

case "postgres": $dbh = new PDO("pgsql:dbname=$dbname;host=$hostname", $username, $password);

case "sqlite": $dbh = new PDO("sqlite:/path/to/database.sdb");

case "sqlitememory": $dbh = new PDO("sqlite::memory");//to create tables in memory

case "firebird": $dbh = new PDO("firebird:dbname=localhost:D:\Programs\Firebird\DATABASE.FDB", "SYSDBA", "masterkey");

case "informix": $dbh = new PDO("informix:DSN=InformixDB", $username, $password);

case "oracle": $dbh = new PDO("OCI:", $username, $password);

//new PDO("OCI:dbname=$dbname;charset=UTF-8", $username, $password);

case "odbc": $dbh = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\accounts.mdb;Uid=Admin");

case "dblib": $dbh = new PDO ("dblib:host=$hostname:$port;dbname=$dbname",$username,$password);

case "ibm": $dbh = new PDO("ibm:DRIVER={IBM DB2 ODBC DRIVER};DATABASE=accounts; HOSTNAME=1.2.3,4;PORT=56789;PROTOCOL=TCPIP;", $username, $password);

}


$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

// which tells PDO to disable emulated prepared statements and

// use real prepared statements. This makes sure the statement

// and the values aren't parsed by PHP before sending it to the MySQL server

// (giving a possible attacker no chance to inject malicious SQL).


$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

} catch(PDOException $e){

echo $e->getMessage();

}



The above example shows you how to connect to different database using simple approach with PDO.

The best way is to use a class for database connection and include this class in your every page.


<?php

/**

* db class used for connecting to databse with pdo

*

*/


class db {


private static $con=null;


private function __construct() {


}


public static function con(){


if( !self::$con){

if($_SERVER['HTTP_HOST']=='localhost'){

self::$con = new PDO("mysql:host=localhost; dbname=mydb", "root", "admin");

} else{

self::$con = new PDO("mysql:host=localhost; dbname=my_serverdb", "myserverusername", "myserverpass");

}


self::$con->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

// which tells PDO to disable emulated prepared statements and use real prepared statements.

// This makes sure the statement and the values aren't parsed by PHP before sending it to the

// MySQL server (giving a possible attacker no chance to inject malicious SQL).


self::$con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

}


return self::$con;

}


private function __clone() {


}

}



and call like this


db::con()->query("select * from emp");



We will assume this method in our whole tutorials.


Error Handling

PDO has three error handling modes.

PDO::ERRMODE_SILENT : acts like mysql_* where you must check each result and then look at errorInfo() to get the error details.

PDO::ERRMODE_WARNING : throws PHP Warnings

PDO::ERRMODE_EXCEPTION : throws PDOException. It acts very much like or die(mysql_error()); when it isn't caught, but unlike or die() the PDOException can be caught and handled gracefully like the code below shows-


try {

db::con()->query('hi'); //invalid query!

} catch(PDOException $ex) {

echo "An Error occured!"; //user friendly message

some_logging_function($ex->getMessage());

}


CRUD operations with PDO


Insert


$count = db->con()->exec("INSERT INTO emp(name, email) VALUES ('RN', 'abc@pqr.com')");


Update


$count = db->con()->exec("INSERT INTO emp(name, email) VALUES ('RN', 'abc@pqr.com')");


Delete


$count = db->con()->exec("DELETE from emp where id=1");


Select


froeach( db->con()->query("SELECT * FROM emp where id>1 ") as $row){

echo $row['name'];

}


$stmt = db::con()->query('SELECT * FROM table');


while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

echo $row['field1'].' '.$row['field2'];

}


$stmt = db::con()->query('SELECT * FROM table');

$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

print_r($results);


What are Fetch Modes?

You may see above PDO::FETCH_ASSOC whith fetchAll() and fetch() methods. So what they actually means?


PDO::FETCH_ASSOC: This tells PDO to return the rows as an associative array with the field names as keys

PDO::FETCH_NUM : returns the row as a numerical array

PDO::FETCH_BOTH: This is default behaviour and returns an array, indexed by both column-name and numeric-indexed. eg. $row['name'] and $row['1']

PDO::FETCH_BOUND: Returns TRUE and assigns the values of the columns in your result set to the PHP variables to which they were bound.

PDO::FETCH_CLASS: Returns a new instance of the specified class.

PDO::FETCH_OBJ: Returns an object, with property names that correspond to the columns. eg. $row->name


Getting Row Count

Instead of using mysql_num_row use rowCount()


$stmt = db::con()->query('SELECT * FROM table');


$row_count = $stmt->rowCount();


echo $row_count.' rows total';


Getting the Last Insert Id

$result = db::con()->exec("INSERT INTO table(firstname, lastname) VAULES('John', 'Doe')");

$insertId = db::con()->lastInsertId();//on database connection object not on result object


Prepared Statment

A prepared statement is a pre-compiled SQL statement that accepts zero or more named parameters. Its very useful when using the same statement or query multiple times with different parameters, or field values. It also helps prevent SQL injection by calling the PDO::quote() method internally.


Example.


$stmt = db::con()->prepare("SELECT * FROM table WHERE id=? AND name=?");

$stmt->bindValue(1, $id, PDO::PARAM_INT);

$stmt->bindValue(2, $name, PDO::PARAM_STR);

$stmt->execute();

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Named Placeholders

If you have lots of parameters to bind, use named placeholders instead of the '?' use : with column name like


$stmt = db::con()->prepare("SELECT * FROM table WHERE id=:id AND name=:name");

$stmt->bindValue(':id', $id, PDO::PARAM_INT);

$stmt->bindValue(':name', $name, PDO::PARAM_STR);

$stmt->execute();

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);



or execute with array can also bind


$stmt = db::con()->prepare("SELECT * FROM table WHERE id=:id AND name=:name");

$stmt->execute(array(':name' => $name, ':id' => $id));

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Transactions

A PDO transaction begins with the with PDO::beginTransaction() method. This method turns off auto-commit and any database statements or queries are not committed to the database until the transaction is committed with PDO::commit. When PDO::commit is called, all statements/queries are enacted and the database connection is returned to auto-commit mode.


try {

db::con()->beginTransaction();


db::con()->exec("SOME QUERY");


$stmt = db::con()->prepare("SOME OTHER QUERY?");

$stmt->execute(array($value));


$stmt = db::con()->prepare("YET ANOTHER QUERY??");

$stmt->execute(array($value2, $value3));


db::con()->commit();

} catch(PDOException $ex) {

//Something went wrong rollback!

db::con()->rollBack();

echo $ex->getMessage();

}


Object Mapping

One of the neatest aspects of PDO is that it gives us the ability to map the query results to a class instance, or object. See example below:


class Employee {

public $first_name;

public $last_name;


public function full_name()

{

return $this->first_name . ' ' . $this->last_name;

}

}


try {


$result = db::con()->query('SELECT * FROM someTable');


# Map results to object

$result->setFetchMode(PDO::FETCH_CLASS, 'Employee ');


while($user = $result->fetch()) {

# Call our custom full_name method

echo $user->full_name();

}

} catch(PDOException $e) {

echo 'Error: ' . $e->getMessage();

}


What Is an SQL Injection Attack?

An SQL injection attack is when a user injects SQL commands in to an unprotected SQL query. This can lead to a number of issues, including modifying rows you didn't intend for the user to modify, dropped tables, deleted rows, and access to possibly sensitive data like this.


SELECT secret_data FROM mytable WHERE na,e= 'x' OR x'='x


How to prevent SQL In-jection attack?

Use prepared statement


$stmt = db::con()->prepare("SELECT * FROM table WHERE name=:name");


$stmt->bindValue(':name', $name, PDO::PARAM_STR);


$stmt->execute();


$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

No comments: