PDO(PHP DATA
OBJECTS)
PDO is an object
oriented class which allowed users to connect and execute the functions on various
databases in an object oriented way. By using PDO,we can get around SQL
injection by using “Prepared Statements". The Mysql secure
functions is less and older work , but with consequences.Using the function
mysql_query to execute queries at the database level lead
to major security flaws
commonly referred to as sql injection.
Why PDO?
- Support nice
variety of info systems supported by PHP.
- Your code
less difficult to put in. don't want third party code.
you do not want
editing of the many line code for every info. simply
write one and run anyplace.
- Speed: PDO has compiled language, PHP libraries
(ADOdb, PEAR DB) written in associate
taken language.
- You need to enable the PDO and PDO_MYSQL extensions
in your php.ini file.
On a
windows server you can add the following lines
in your php.ini to enable the pdo
- extension = php_pdo.dll
- extension = php_pdo_mysql.dll
PDO – connect and close
# MySQL with PDO_MYSQL
$DBH = new
PDO("mysql:host=$host_name;dbname=$dbname_name", $user_name,
# close the db connection
$DBH = null;
PDO – exceptions
PDO can use exceptions to
handle error, which means your code with PDO should be wrapped in a
try/catch block to catch the errors.
we can use PDO with one
of 3 error modes by setting the error mode attributes on your newly created
database handle
- $DBH->setAttribute( PDO::ATTR_ERRMODE,
PDO::ERRMODE_SILENT ); # default error mode
- $DBH->setAttribute( PDO::ATTR_ERRMODE,
PDO::ERRMODE_WARNING ); # it is useful for debugging,program
continue to execute
- $DBH->setAttribute( PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION ); # most situations ,it fires an exception
PDO create and update
Using PDO, create and update is normally
a two-step process
Prepare
-> Bind ->Execute
- We can executed prepared statement multiple times by
just sending the data to the server because prepared statement is a precompiled
statement.
- It has the added advantages of automatically making
the data used in the placeholder safe from SQL injection attacks.
# The most
basic type of insert, STH means "Statement Handle", no binding here
$STH = $DBH->prepare("INSERT
INTO folks ( first_name ) values ( 'Cathy' )");
$STH->execute();
<?php
# no placeholders - ripe for SQL Injection!
$STH =
$DBH->prepare("INSERT INTO folks (name, addr, city) values ($name,
$addr, $city)");
# unnamed placeholders
$STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values (?,
?, ?)");
# named placeholders
$STH = $DBH->prepare("INSERT INTO folks (name, addr, city) value
(:name, :addr, :city)");
?>
PDO - prepared statements 1
• Unnamed placeholders
$STH = $DBH->prepare("INSERT
INTO folks (name, addr, city) values (?, ?, ?)");
# assign variables to each place holder, indexed 1-3
$STH->bindParam(1, $name); $STH->bindParam(2, $addr);
$STH->bindParam(3, $city);
# insert one row - once the query have been prepared ...
$name = "Daniel";
$addr = "1 Wicked Way";
$city = "Arlington
Heights";
$STH->execute();
PDO - prepared statements 2
• Named placeholders
$STH = $DBH->prepare("INSERT
INTO folks (name, addr, city) value (:name, :addr, :city)");
# the first argument is the named placeholder name - notice
named placeholders always start with a colon
$STH->bindParam(':name', $name); $STH->bindParam(':addr', $addr); $STH->bindParam(':city', $city);
# insert one row - insert as many rows as you want just updating
the variables and ->execute()
$name = "Daniel"; $addr = "1 Wicked Way"; $city = "Arlington
Heights";
$STH->execute();
PDO - prepared statements 3
• Update and delete with named
placeholders
<?php
// update using named place holders
$id = 5;
$name = "Joe the Plumber";
try {
$DBH = new PDO('mysql:host=localhost;dbname=someDatabase', $username, $password);
$DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$STH = $DBH->prepare('UPDATE
someTable SET name = :name WHERE id = :id');
$result = $STH->execute(array(':id' => $id, ':name' => $name));
echo $STH->rowCount(), " - ", $result;
}
catch(PDOException $e) {
echo 'Error: ' . $e->getMessage();
}/
/ delete using named place holders and the bindParam method
$id = 5;
try {
$DBH = new PDO('mysql:host=localhost;dbname=someDatabase', $username, $password);
$DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$STH = $DBH->prepare('DELETE FROM
someTable WHERE id = :id');
$STH->bindParam(':id', $id);
$result = $STH->execute();
echo $STH->rowCount(), " - ", $result;
}
catch(PDOException $e) {
echo 'Error: ' . $e->getMessage();
}?
>
