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?
# 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
$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 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.
- extension = php_pdo.dll
- extension = php_pdo_mysql.dll
# 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.
$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();
}?
>
