Table of Contents
Before attempting to use the Database component, you should familiarize yourself with the PHP Data Objects (PDO) documentation. The Database component builds upon PDO and we do not provide examples or explanations of the PDO basics.
The Database component consists of two main parts:
The Database component currently supports:
This section gives you an overview of the main classes of the Database component.
This section shows how to use the factory and the instance as well as how to execute some typical queries. For more information on how to perform queries using the handlers, we recommend reading the PHP PDO documentation.
Executing a simple query and returning the result right away can be done with the PDO::query() method:
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 transferred to the SQL server independent of the main query. See the section 'Avoiding SQL injection' below.
Lazy initialization is a mechanism to load and configure a component, only when it is really used in your application. This mechanism saves time for parsing the classes and configuration, when the component is not used at all during one request. You can find a description how you can use it for your own components and how it works in the ezcBase tutorial. The keyword for the database component is ezcInitDatabaseInstance.
ezcBaseInit::setCallback accepts as a first parameter a component specific key, which lets the component later request the right configuration callback. The second parameter is the name of the class to perform the static callback on. This class must implement the ezcBaseConfigurationInitializer class. Each component's lazy initialization calls the static method configureObject() on the referenced class.
This example shows a way to configure multiple database handlers, only when they are really requested in your application. The example does basicaly the same like the first example in this tutorial, but creates the connection not before it is really required.
In line 24 the default database handler is first requested in this example, which does not exist yet, so that the configuration class earlier referenced through the setCallback() call will be asked for a new instance for the current instance name, which is (bool) false for the default instance.
In the configureObject() method in line 8 we switch on the instance name and create and return the right newly created database handler. Line 27 shows, that this will also work with multiple database instances, creating an additional SQLite connection.
This section gives you a basic introduction on how to build queries using the query abstraction layer.
We will start out by recreating the first query example:
As you can see, building the query itself follows the build-up of a normal query and is rather straightforward. 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 ensure that you get a query of the correct type, regardless of what database you use. Please note that if you want to use table/column names that are SQL keywords, such as group, you need to quote the table/column names manually with ezcDbHandler::quoteIdentifier().
The next example is based on the previous one, but builds a more complex query and introduces the usage of bind parameters in the query:
The query will fetch the first ten quotes by Robert Foster, sorted by the quote itself. Note that string parameters must either be bound using ezcQuery::bindParam()/ezcQuery::BindValue() or escaped and quoted manually.
As you can see, logical expressions are built up using the expression object of the type ezcQueryExpression. Note that the methods for logical or and and are named lOr and lAnd, respectively. This is because and and or are reserved names in PHP and cannot be used in method names.
The next example shows that you can insert, update and delete rows from a table using the query abstraction layer, in a similar way to the SELECT query.
The example below shows how to create and use basic INSERT, UPDATE and DELETE query objects.
The next examples show how to use multi-join syntax to build queries with several joined tables using inner, right or left join.
The innerJoin(), rightJoin() and leftJoin() methods can be used in three forms:
This is a simplified version of form 1, where the join condition is always set to "equal".
rightJoin( 'table1', 'table1.id', 'table2.id' ) is a shorter equivalent of rightJoin( 'table1', $this->expr->eq('table1.id', 'table2.id' ) );
This final example shows how to build subselect queries inside SELECT:
SQL injection is possibly the biggest single cause of major security problems in web applications. SQL injections are caused when building SQL statements that include untrusted data. If the untrusted data is not escaped properly or checked for proper input, you are susceptible to SQL injections.
With the introduction of bound values, it is possible to avoid SQL injection altogether. Simply use bind to insert untrusted data into a query. This is usually more efficient as well, since you do not need to escape the data and the SQL server does not have to parse it as part of the query string.
This component currently does not deal with character sets support. It relies on the different databases' own mechanisms to deal with this. For MySQL that means that you might have to set the character set yourself on the ezcDatabaseHandler instance with something like:
$db->query("SET NAMES utf8");
Oracle treats character sets totally different, and their driver needs to know which character set to use before connecting to the database, or starting the database. For this you need to make an environment setting. An example of such setting could be:
Even though the query abstraction layer creates syntactically equal queries for the supported databases, the results may still differ. This is due to a large number of differences between the databases.
For example, SQLite does not support the rebinding of values. If you have an insert query and want to reuse it as outlined below, an error will be returned:
$q->insertInto( 'query_test' ) ->set( 'id', 1 ) ->set( 'company', $q->bindValue( 'eZ systems' ) ) ->set( 'section', $q->bindValue( 'Norway' ) ) ->set( 'employees', 20 ); $stmt = $q->prepare(); $stmt->execute(); $q->insertInto( 'query_test' ); $q->set( 'id', 2 ); $q->set( 'company', $q->bindValue( 'Trolltech' ) ); $q->set( 'section', $q->bindValue( 'Norway' ) ); $q->set( 'employees', 70 ); $stmt = $q->prepare(); $stmt->execute();
Instead, you should use bindParameter() to achieve the same effect:
$company = 'eZ systems'; $section = 'Norway'; $q->insertInto( 'query_test' ) ->set( 'id', 1 ) ->set( 'company', $q->bindParam( $company ) ) ->set( 'section', $q->bindParam( $section ) ) ->set( 'employees', 20 ); $stmt = $q->prepare(); $stmt->execute(); $q->insertInto( 'query_test' ); $q->set( 'id', 2 ); $q->set( 'employees', 70 ); $company = 'Trolltech'; $section = 'Norway'; $stmt = $q->prepare(); $stmt->execute();
To keep the compability between different relational database management systems, there are restrictions you have to cope with:
Lowercase column names
As some RDBMS always do not store column names case sensitive, but return and strore them completely as upper case letters, ezcDatabase converts all column names to lower case to offer a uniform interface.
This section explains the basic steps to take to create support for a new database. The following steps are rudimentary but should help you along the way.