Transcript PHP Data Objects Layer (PDO)

PHP Data Objects
Layer (PDO)
What is PDO

Common interface to any number of database
systems.

Written in C, so you know it’s FAST!

Designed to make use of all the PHP 5.1
features to simplify interface.
Why is it needed?

LOTS of reason, for us we want to tackle:


SQL injections
Why not just use mysqli
You can if you want
 Only support MySQL


Why not just use mysql_connect() or
mysql_query()

Unsecure, deprecated and unsupported
What Databases are Supported?

At this time PDO offers the following drivers:
MySQL 3,4,5 (depends on client libs)
 PostgreSQL
 SQLite 2 & 3
 ODBC
 DB2
 Oracle
 Firebird
 FreeTDS/Sybase/MSSQL

Detail tutorial

http://wiki.hashphp.org/PDO_Tutorial_for_M
ySQL_Developers
SQL injections

The root of the SQL injection problem is
mixing of the code and the data.
$expected_data = 1;
 $query = "SELECT * FROM users where
id=$expected_data";


will produce a regular query:

SELECT * FROM users where id=1
SQL injections

while this code:
$spoiled_data = "1; DROP TABLE users;";
 $query = "SELECT * FROM users where
id=$spoiled_data";


will produce malicious sequence:
SELECT * FROM users where id=1; DROP
TABLE users;
 OOOOOPPPPSSSSS!!!! PWNED!!!!!

Prepared Statements




How to solve/overcome???
use prepared statements in PDO/mysqli
Prevent mixing of the code and the data
We are sending program to the server first
$db->prepare("SELECT * FROM users where
id=?");
 where the data is substituted by some variable called
"placeholder“ later on.
 $db->execute($data);

Prepared Statements


Sometimes PDO by default is not using
prepared statements but rather just compose
the regular query.
How to overcome:


$dbh->setAttribute(
PDO::ATTR_EMULATE_PREPARES, false );
Error mode (give us try catch capability):

$dbConnection>setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);