Introduction

The database component is built upon the PDO library in PHP 5 and consists of two main parts:

1. Database handlers derived from PDO with some added functionality. A database handler provides a common API for all databases to execute queries on a a database. An introduction can be found in the PHP PDO documentation. Most importantly the handlers in the components add support for nested ezcDbHandler::beginTransaction() and ezcDbHandler::commit() calls. The handlers also provide factory methods for the query abstraction layer.
2. The query abstraction layer. This layer provides an object oriented API for creating SELECT, INSERT, UPDATE and DELETE queries. Using a single interface you can create syntactic equal queries for the supported database. This layer removes all need to do string processing in order bo build your queries and helps avoiding syntax errors. Note that the query layer does not remove semantical/logical differences between databases.

Class overview

This section gives you an overview of the main classes of the Database component.

Handler usage

This chapter shows how to use the factory and the instance as well as how to execute some typical queries. For more details on how to perform queries using the handlers we recommend reading the PHP PDO documentation.

In order to get started you need a database handler. The first example shows how to create one using ezcDbFactory and how to store the handler in ezcDbInstance so it can easily be retrieved later:

1. <?php
3. $db = ezcDbFactory::create( 'mysql://user:password@host' );
4. ezcDbInstance::set( $db );
6. // anywhere later in your program you can retrieve the db instance again using
7. $db = ezcDbInstance::get();
9. ?>

Executing a simple query and get the result right away can be done with the PDO::query() method:

1. <?php
3. $db = ezcDbInstance::get();
5. $rows = $db->query( 'SELECT * FROM quotes' );
7. ?>

Next, we show a simple example with statements and the use of bind. Binding values can be very valuable both in terms of efficiency and security. The main difference with normal queries is that the bound value will be transfered to the SQL server independently from the main query. See the chapter 'Avoiding SQL injection' below.

1. <?php
3. $db = ezcDbInstance::get();
4. $stmt = $db->prepare( 'SELECT * FROM quotes where author = :author' );
5. $stmt->bindValue( ':author', 'Robert Foster' );
7. $stmt->execute();
8. $rows = $stmt->fetchAll();
10. ?>

Query abstraction Usage

This chapter gives you a basic introduction on how to build queries using the query abstraction layer.

We will start out with recreating the first query example:

1. <?php
3. $db = ezcDbInstance::get();
5. $q = $db->createSelectQuery();
6. $q->select( '*' )->from( 'quotes' );
8. $stmt = $q->prepare();
9. $stmt->execute();
11. ?>

As you can see, building the query itself follows the build up of a normal query and is pretty straight forward. The rest of the example is a bit more verbose, this is mainly due to the fact that you need to fetch the query object from the handler and that you are required to use prepared statements with the query abstraction layer. The factory methods in the handler to fetch the query object ensures that you get a query of the correct type regardless of what database you use.

The next example builds on the previous one, but builds a more complex query and introduces the usage of bind parameters in the query:

1. <?php
3. $db = ezcDbInstance::get();
5. $q = $db->createSelectQuery();
6. $e = $q->expr; // fetch the expression object
7. $q->select( '*' )->from( 'quotes' )
8. ->where( $e->eq( 'author', $q->bindValue( 'Robert Foster' ) ) )
9. ->orderBy( 'quote' )
10. ->limit( 10, 0 );
12. $stmt = $q->prepare();
13. $stmt->execute();
15. ?>

The query will fetch the ten first quotes by Robert Foster sorted by the quote itself. Note that string parameters must be either bound using ezcQuery::bindParam()/ezcQueryBindValue() or escaped and quoted manually.

The final example shows that you in a similar way to the SELECT query can insert, update and delete rows from a table using the query abstraction layer.

The final example shows how to create and use basic INSERT, UPDATE and DELETE query objects.

1. <?php
3. $db = ezcDbInstance::get();
5. // Insert
6. $q = $db->createInsertQuery();
7. $q->insertInto( 'quotes' )
8. ->set( 'id', 1 )
9. ->set( 'name', $q->bindValue( 'Robert Foster' ) )
10. ->set( 'quote', $q->bindValue( "It doesn't look as if it's ever used!" ) );
11. $q->prepare();
12. $q->execute();
14. // update
15. $q = $db->createUpdateQuery();
16. $q->update( 'quotes' )
17. ->set( 'quote', 'His skin is cold... Like plastic...' )
18. ->where( $q->expr->eq( 'id', 1 ) );
19. $q->prepare();
20. $q->execute();
22. // delete
23. $q = $db->createDeleteQuery();
24. $q->deleteFrom( 'quotes' )
25. ->where( $q->expr->eq( $q->bindValue( 'Robert Foster' ) ) );
26. $q->prepare();
27. $q->execute();
28. ?>

Avoiding SQL injection

SQL injection is possibly the biggest single cause of major security problems in web applications. SQL injections are caused when building SQL statements and parts of the statement is built up of untrusted data. If the untrusted data is not escaped properly or checked for proper input you will have a possible SQL injection problem.

With the introduction of bound values it is possible to avoid SQL injection altogether. Simply always use bind to insert untrusted data into a query. This is usually also more efficient since you don't need to escape the data and the SQL server does not have to parse it as part of the query string.

Adding support for a new database

This chapter explains the basic steps you have to go through when creating support for a new database. The following steps are rudimentary but should help you along the way. If you require additional information, feel free to ask in the forums.

1. Check out the Database component from the eZ systems SVN server. This is necessary in order to use the testing system. This allows you to easily see if your code works as it should.
2. Create a handler for the new database. The handler must inherit from ezcDbHandler. Don't reimplement the methods for the query abstraction layer. They will then default to MySQL syntax.
3. Run the test system and check if any of them fail. If any tests fail you have to extend the class and method in question and make sure that the generated SQL is correct for your database. When no tests fail: congratulations you are done.