Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

PDO: Database connections and abstraction

connection bound parameter

  • Please log in to reply
25 replies to this topic

#1 Alexander

Alexander

    YOL9

  • Moderator
  • 3963 posts
  • Location:Vancouver, Eh! Cleverness: 200
  • Programming Language:C, C++, PHP, Assembly

Posted 06 October 2010 - 11:32 AM

PDO stands for PHP Data Objects which is a powerful database data abstraction class as a core library in all versions of PHP since 5.1 providing a uniform data access interface with prepared statements, bound parameters and dynamically loadable drivers under the same API.

Now we're talking abstraction here, this means that it can use all the common features such as prepared statements, transactions, and rollback features on each of these DBMS (database management system) engines in a secure and simple manner where available. Most supported engines of which are:

* DBLIB: FreeTDS / Microsoft SQL Server / Sybase
* Firebird (http://firebird.sourceforge.net/): Firebird/Interbase 6
* IBM (IBM DB2)
* INFORMIX - IBM Informix Dynamic Server
* MYSQL (Oracle Secure Enterprise Search - ): MySQL 3.x/4.0
* OCI (http://www.oracle.com): Oracle Call Interface
* ODBC: ODBC v3 (IBM DB2 and unixODBC)
* PGSQL (http://www.postgresql.org/): PostgreSQL
* SQLITE (http://sqlite.org/): SQLite 3.x

Why should you use PDO?


Lets talk about the situation where you used mysql_ functions . The mysql_ function set was introduced in early PHP 4.0 which originates to around July of 2000, the featureset was increased and some basic security precautions were placed into effect, but the function set never changed much more after that. It is fairly archaic.

mysql_real_escape_string() was introduced to aid in preventing SQL injection, even by checking character sets but it is rarely implemented in a proper manner, and with dynamic queries (a query made out of a string) there are just too many places where a malformed data can make a query malicious.

The mysqli_* improved function set is an implementation with basic prepared statements but only available to MySQL and certain versions of PHP, leaving others out of the jump. This is where PDO fits nicely, filling in the gap: PDO allows a uniform set of functions to handle data to the database connection, with prepared statements casting immunity to many of the problems the old functions had.

Lets start.


How do you connect to a database?


You used to connect like this to your database host to select a database, note how it is hard to catch more than one error at once, preventing people from learning the true cause without excessive error handlings:

$conn = mysql_connect("127.0.0.1", 'admin', 'pass') or die ('MySQL error: database connection: ..' . mysql_error());
mysql_select_db('myawesomedbase',$conn) or die ('Not again..' . mysql_error());


And now this is what you do with PDO for comparison, note the simplicity of catching database errors:
try {
$db = new PDO("mysql:dbname=myawesomedbase;host=127.0.0.1", "admin", "pass" );
} catch(PDOException $e) {
echo $e->getMessage();
}


Secure and simple. Well, but .. What if you wanted to use PgSQL, or SQLite?
$dbs = new PDO( "sqlite:/dbs/myawesomedbase.db" );
$dbp = new PDO( "pgsql:dbname=$dbname;host=$host", $username, $password );


Did you know it was that simple to work with different databases with just one constructor call!

Let us move on to the guts of the matter, and display some of the commonly used functions in PDO of which you will recognise. If you are already experienced in other database functions, the following may convert you:

PDO common functions:


First some clarification on how to perform functions:

When we are working with $db we are working with the PDO class. When we create a statement, for example $sth = $db->query() , $sth is a PDOStatement class. You must be sure to recognise and check for which class you are working on, they do not all share the same member functions.


PDOstatement::exec: This is a direct query:
$rowscount = $db->exec("INSERT INTO awesome (person_name, person_rating) VALUES ('Nullw0rm', '10000')");


PDOstatement::query: This can return a query result set:
$sql = "SELECT (person_name, person_rating) FROM awesome";
foreach ($db->query($sql) as $row) {
print $row['person_name'] .' - '. $row['person_rating'] . '<br />';
}


PDOstatement::fetch: This can fetch the next row from the dataset under different constraints
$sth = $db->query('SELECT * FROM awesome');
$result = $sth->fetch(PDO::FETCH_ASSOC);

foreach($result as $key => $val) {
echo $key.' => '.$val.'<br />';
}


PDOstatement::fetchAll: See PDOstatement::fetch: except with all rows returned.

The types of constraints on the array return type for fetches are:

PDO::FETCH_ASSOC: returns an array indexed by column name as returned in your result set
PDO::FETCH_BOTH (default): returns an array indexed by both column name and 0-indexed column number as returned in your result set
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 with the PDOStatement::bindColumn() method
PDO::FETCH_CLASS: returns a new instance of the requested class, mapping the columns of the result set to named properties in the class.
PDO::FETCH_INTO: updates an existing instance of the requested class, mapping the columns of the result set to named properties in the class
PDO::FETCH_LAZY: combines PDO::FETCH_BOTH and PDO::FETCH_OBJ, creating the object variable names as they are accessed
PDO::FETCH_NUM: returns an array indexed by column number as returned in your result set, starting at column 0
PDO::FETCH_OBJ: returns an anonymous object with property names that correspond to the column names returned in your result set

Back the the functions:

PDOstatement::rowCount: This can get the number of rows affected in an executed query statement much the like PDOstatement::exec() return value.

PDOstatement::lastInsertId(string $name = NULL): Get last insert ID:

print "Last ID of insertion: " . $db->lastInsertId();


That should cover the guts of the class of which you will need for day to day work. For a complete list of functions available to each respective class, refer to and bookmark the PDOstatement object page on php,net:

PHP: PDO - Manual




PREPARED STATEMENTS


This is if not the most important aspect of which we will be using of PDO and newer DBM functions in general. Prepared statements are a method to prepare paremeters without using a dynamic query like you would do in normal PHP MySQL functions (i.e. "WHERE 'this' = $that"). These prepared statements will handle any escaping and injection issues automatically, leaving no need to run mysql_real_escape_string() in hopes that it is secure.

Prepared statements can be performed in a number of different ways, here we will look at the placeholders of which we can use:


$max_rating = '10000'; // Our parameter

//"Generic" placeholders
$sth = $db->prepare("SELECT person_name FROM awesome WHERE person_rating = ? AND foo = ? ");
$sth->execute(array($max_rating, 'bar'));

/* or the following: */

//"Named" placeholders:
$sth = $db->prepare("SELECT person_name FROM awesome WHERE person_rating = :rating AND foo = :bar");

//PARAM_INT for int, PARAM_STR for string, PARAM_BOOL for bool
$sth->bindParam(':rating', $max_rating, PDO::PARAM_STR);
$sth->bindParam(':bar', 'baz', PDO::PARAM_STR);
$sth->execute();


And now we can run fetch or fetchAll on $sth, being sure that no data will malform the entry. Feel the freedom of these prepared statements.

Did you know with prepared statements you are invulnerable to silly multibyte quote injection? Quotes or no quotes, the prepared data is not part of the query commands themselves, therefor the offending data cannot "mend" the query into something it shouldn't be. No need to learn how to stop the crazy methods of SQL injection whenever they are discovered!


DATABASE TRANSACTIONS


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

Note: MyISAM and possibly others run effectively in autocommit always, so it may not apply to that engine. Always look up documentation for your database engine if it supports transactions.

This is an example transaction where important (mission critical) data is sent to the database, but on an unreliable infrastructure, maybe it will crash and we do not want corruption:

try {

//connect to database
$db = new PDO("mysql:host=fbi-home-02;dbname=$dbname", $username, $password);

//ensure exceptions are thrown
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

//important: begin transaction
$db->beginTransaction();

//create a table
$table = "CREATE TABLE secrets ( sec_id MEDIUMINT(8) NOT NULL AUTO_INCREMENT PRIMARY KEY,
sec_type MEDIUMTEXT(50) NOT NULL,
sec_name MEDIUMTEXT(400) NOT NULL
)";
$db->exec($table);

//insert sample data
$db->exec("INSERT INTO secrets (sec_type, sec_name) VALUES ('class 1', 'D-Day')");
$db->exec("INSERT INTO secrets (sec_type, sec_name) VALUES ('class 5', 'Fridge broken')");

//important: commit the transaction to the database
$db->commit();

} catch(PDOException $e) {

//a nasty exception has appeared, did something happen? our data will be safe (for now!)
$db->rollback();
}
?>


ERROR HANDLING


PDO gives you the option of handling errors as warnings, errors, or exceptions. However, when you create a new PDO connection object, PDO always throws a PDOException object if an error occurs. If you do not catch the exception, PHP prints a backtrace of the error information which might expose your database connection credentials so you may wish to set up a handler. First we catch the exception:

(Do not think about Java while doing this!)
try {
$db = new PDO("sqlite::memory");
$stmt = $db->exec("SELECT usernamee FROM users_list");
} catch(PDOException $e) {
print $e->getMessage();
}
This may print:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'usernamee' in 'users_list'
When we want to let errors be more useful and log them per se, we would set up an error handler. First we tell PDO to throw errors instead of exceptions to allow PHP to use them in a useful manner:
$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
And create any generic error handler, or if display_errors is off and log_errors is on (as it should be), it will automatically do this for you.

To learn about creating your own special error handler, read more about it at php.net:
PHP: set_error_handler - Manual


Extending the PDO class


We can extend the PDO class to add something such as query statistics, custom functions into the mix and a constructor to make the job easier. A simple example class would be:
<?php
class myPDO extends PDO
{
//example class variables
public $lastQuery;
private $err;
private $log;

//constructor for parent class
public function __construct (
$dsn, $username=null, $password=null, $driver_options=null
){
parent::__construct($dsn, $username, $password, $driver);
}

//sample function to extend prepared statements.
public function prepare($query, $bindArrParams){
try {
$this->lastQuery = $query;
$sth = parent::prepare($query);
$key = array_keys($bindArrParams);
$this->logEvent("Prepare ran: query: $query params: " . join(', ', $bindArrParams));
if (!is_numeric($key[0]) && (substr($key[0],0,1)==':')){
foreach ($bindArrParams as $keyParams => $valueParams){
$this->logEvent(" Binding param $keyParams to $valueParams");
$sth->bindParam($keyParams,$valueParams);
$sth->execute();
}
} else {
$this->logEvent(" Binding placeholder parameters: " . join(', ', $bindArrParams));
$sth->execute($bindArrParams);
}
$this->logEvent("prepare returns: " . join(', ', $sth));
$this->result = $sth->fetchAll();
//close statement
$sth->closeCursor();
$this->logEvent('result value :' . $this->result);
return $this->result;
} catch (PDOException $e) {
$this->logErr('Error in function ' . __FUNCTION__ . ' on line ' . __LINE__ . '! Message: ' . $e->getMessage() . "\n", $e);
}
}

public function logErr(...);
public function logEvent(...);

}
This will purely depend on your needs to extend the PDO class to add specific features you need such as a full blown database abstraction class, or multi-database support and better performing connections if you are scaling a high traffic site.

There is a word of warning in writing these extended classes:
.... DO NOT USE A SINGLETON ANTIPATTERN OF THIS CLASS!
You can look at many real-world examples[SO] of why this is a bad idea, try to stick with what you need and nothing more for the implementation.


TERMINATING CONNECTION


You may be used to calling mysql_close() or similar functions, here all we will need to do is take the PDO object out of scope for the deconstructor to be called, which will take care and handle of all the gritty details for you, and also flush any further data in the connection pipe that is needed to be sent.
//close instance of $db
$db = null;


FINAL NOTES

  • You may wish to add persistant connections later on, you can perform this at start, or through setAttribute():
$dbh = new PDO('mysql:host=127.0.0.1;dbname=test', $user, $pass, array(
PDO::ATTR_PERSISTENT => true
));

  • I also personally suggest you read up on php.net's PDO reference to look deeper into the technical caveats or problems users had with PDO functions in the past:
http://www.php.net/m.../en/ref.pdo.php
  • Remember: Do not use dynamic queries, only uses binded parameters in your prepared statements.
"SELECT $colname WHERE foo = :bar" will end up in a problem somewhere down the road.
  • Remember too: Use the abstraction wisely; Do not overdo something or implement a crazy pattern into your extended class because somebody wrote one. Extending as well should be complementry, not rewriting the base class in a wrapper!

Hopefully I covered the most important aspects, feel free to comment on or suggest anything, if you need help getting PDO set up I will most certainly be here to answer your questions and provide more examples.

Happy coding!

Edited by Alexander, 12 April 2012 - 04:09 PM.
Revision maintained for IPB

  • 3

#2 Vswe

Vswe

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1989 posts
  • Programming Language:Java, C#, PHP, Python, JavaScript, PL/SQL, Visual Basic .NET, Lua, ActionScript

Posted 06 October 2010 - 01:48 PM

This seems very neat and useful but I don't really get what PDO actually is :confused:
  • 0

#3 Alexander

Alexander

    YOL9

  • Moderator
  • 3963 posts
  • Location:Vancouver, Eh! Cleverness: 200
  • Programming Language:C, C++, PHP, Assembly

Posted 06 October 2010 - 08:42 PM

Thanks. :) I'll edit my tutorial to reflect more details when I have the time. PDO is a database (data) abstraction class, allowing you to add great security (such as prepared statements) into almost any database engine all with the same function set. There is no need to run old insecure mysql_query functions.

Edited by Alexander, 07 October 2010 - 02:23 AM.

  • 0

All new problems require investigation, and so if errors are problems, try to learn as much as you can and report back.


#4 Vswe

Vswe

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1989 posts
  • Programming Language:Java, C#, PHP, Python, JavaScript, PL/SQL, Visual Basic .NET, Lua, ActionScript

Posted 06 October 2010 - 11:14 PM

Ok, now I see :)
Remember to repair your fridge when you have time :P
  • 0

#5 John

John

    CC Mentor

  • Moderator
  • 4450 posts
  • Location:New York, NY

Posted 07 October 2010 - 11:59 PM

Nice tutorial Nullw0rm. Have you used ADOdb?
  • 0

#6 Alexander

Alexander

    YOL9

  • Moderator
  • 3963 posts
  • Location:Vancouver, Eh! Cleverness: 200
  • Programming Language:C, C++, PHP, Assembly

Posted 08 October 2010 - 10:31 AM

I had considered ADOdb before, the only caveat I would be against using it for is in that it is an external library, it is hard to introduce them in large existing projects.
  • 1

All new problems require investigation, and so if errors are problems, try to learn as much as you can and report back.


#7 Vswe

Vswe

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1989 posts
  • Programming Language:Java, C#, PHP, Python, JavaScript, PL/SQL, Visual Basic .NET, Lua, ActionScript

Posted 09 October 2010 - 08:40 AM

I've started to use PDO on my sub domain. It works as it should and is easier to use that the "normal" way. :)
  • 0

#8 Vswe

Vswe

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1989 posts
  • Programming Language:Java, C#, PHP, Python, JavaScript, PL/SQL, Visual Basic .NET, Lua, ActionScript

Posted 10 October 2010 - 07:11 AM

Is it possible to use Prepared statements for adding DateTime objects? :)
  • 0

#9 Alexander

Alexander

    YOL9

  • Moderator
  • 3963 posts
  • Location:Vancouver, Eh! Cleverness: 200
  • Programming Language:C, C++, PHP, Assembly

Posted 10 October 2010 - 07:47 AM

@Vswe: DateTime field is usually plugged with a string date (PDO::PARAM_STR), say for example:
CREATE TABLE ex (c1 int, c2 datetime)
INSERT INTO ex VALUES(1, '20071023 14:23:16')
Is that what you mean? It's early for me.
  • 0

All new problems require investigation, and so if errors are problems, try to learn as much as you can and report back.


#10 Vswe

Vswe

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1989 posts
  • Programming Language:Java, C#, PHP, Python, JavaScript, PL/SQL, Visual Basic .NET, Lua, ActionScript

Posted 10 October 2010 - 07:51 AM

Well, I tried just using the PDO::PARAM_STR to store the value from date() in a DateTime column but the value only showed as "0000-00-00 00:00:00" when retrieving the value. But maybe the error is something else.
  • 0

#11 FireGator

FireGator

    CC Regular

  • Just Joined
  • PipPipPip
  • 35 posts

Posted 12 October 2010 - 01:45 AM

Well, I tried just using the PDO::PARAM_STR to store the value from date() in a DateTime column but the value only showed as "0000-00-00 00:00:00" when retrieving the value. But maybe the error is something else.


PDO would work with talking with the database rather than constructing anything in the query, it shouldn't be your problem, check what you are sending to the datetime column is correct!

+rep Nullw0rm, another great tutorial, this one is simply great.
  • 0

#12 DEViANT

DEViANT

    CC Addict

  • Just Joined
  • PipPipPipPipPip
  • 265 posts

Posted 12 October 2010 - 03:12 AM

A Question : If I want to build up an array containing all the data from the DB, would this be the only way to do it?

$sql = "SELECT * FROM user ORDER BY u_id ASC";
foreach($db->query($sql) as $row)
{
$users[] = array('u_id' => $row['u_id'],'u_fullname' => $row['u_fullname'], 'u_email' => $row['u_email'], 'u_password' => $row['u_password']);
}


OR


$sql = "SELECT * FROM user ORDER BY u_id ASC";
foreach($db->query($sql) as $row)
{
foreach($row as $key => $val)
{
if(!is_int($key))
$array[$key] = $val;
}
$users[] = $array;
unset($array);
}


As opposed to the usual way :

$sql = mysql_query("SELECT * FROM users ORDER BY u_id ASC") or die(mysql_error());
while($row = mysql_fetch_assoc($sql))
{
$users[] = $row;
}

  • 0

:D You should rep+ me so that I can win :D

My Blog | Ask me!
Error : Satan did it





Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download