The PHP database abstraction layers are a great achievement to unify database access in PHP. But none of them are yet an universal standard that can be expected on any "normal" PHP server.
If you have full control over all servers running your applications, this is of course no problem, as you can install any PHP extensions you need. But what about the users of your applications? Different users, hosting providers, employers, customers and communities all have their own ideas of a "normal" and - increasingly important - secure and stable PHP server. Adding new PHP extensions for your application is often not an option.
If you have a look at PHP applications available today, the waste majority don't support more than one PHP database extension. And mostly that is not an abstraction layer but one of the vendor specific database extensions. Why? Well, mostly because adding database support used to be really hard work. The Connector library hopes to change that.
The Connector library provides independent database access using any of the supported PHP database extensions available on a host server - abstraction layer or vendor specific. For this purpose, the Connector library defines a database independent API that is very easy to use. The main features are:
Database independent: The Connector library is designed as a database independent API for any PHP database extension. It's kept very simple for that exact reason, see "Database independent API".
Extendable: You can add support for multiple additional databases without changing your application code. If a suitable connector is not included in this package, you can add your own custom connector with a limited effort, see "Extend the library".
Flexible: Allows the use of multiple options to fit your needs, see "Options".
Secure: Built-in parameter validation, protection against SQL-Injection and (limited) protection against XSS.
Fast: Built-in query result caching and support for parameterized queries.
The core element of the Connector library is the IConnector interface. This interface defines database independent methods and constants for all database connectors. It contains four methods for database interaction:
These methods are typically used for - but not limited to - SQL SELECT, INSERT, UPDATE and DELETE statements respectively. The arguments are identical for all methods, but their usage and return values differ.
Three additional methods are available for any database connector. Use the first two methods to get and set global options. The third method returns the PHP database link identifier currently used by a IConnector instance. You can (but shouldn't) use the link identifier for direct access to PHP database functions.
Mostly, platform independent PHP applications are the higher objective. Database independency is only one aspect of that goal. The Connector library only requires default PHP modules.
Global elements: The Connector library defines a few global elements. Check your application for possible naming conflicts before getting started:
Standard SQL: It's not the intention of the Connector library to provide any SQL rewriting to unify different SQL dialects. For truly database independent applications, use standard SQL (SQL-92 is supported by most vendors) and avoid proprietary extensions whenever possible.
Even if your SQL queries comply with the SQL standard, some database vendors may not (sic). In some cases, it might be inevitable to write different versions of the same SQL query to cover all databases supported by your application. With the PHP get_class() function, you can identify the currently used database connector and pass on the matching SQL query.
The Connector library is designed as a database independent API. Behind the common IConnector interface, several IConnector classes are implemented using different PHP database extensions. Currently, five IConnector classes are implemented and available in this package (feel free to add our own):
A personal note: You might think that the selection above is too focused on Microsoft products. Well, currently it is, but you can do something about it. The selection expresses my personal experience, not my personal preference. I would be really happy to include additional IConnector classes for any PHP database extension. In fact, it's the very objective of this project to cover as many different databases as possible - open source or commercial.
If you are an experienced user of one or more PHP database extensions, your contribution to this project is warmly welcome, provided it's thoroughly tested, stable, secure and well documented. See "Extend the library" for how to write additional IConnector classes.
To use the Connector library, just include ConnectorFactory.php in your application, preferably with require_once(). Normally, there is no need to include any other files of the Connector library.
After including ConnectorFactory.php, the IConnector constants and the static ConnectorFactory::create() method are available for use. Note: To avoid loading needless code, no IConnector classes are loaded before create() is called. Native (database dependent) constants are not yet available.
Call the static ConnectorFactory::create() method to load and get an instance of the selected IConnector class. This method takes the following arguments:
If an IConnector instance with identical class name and connection parameters already exists, the existing instance is returned. Otherwise, a new IConnector instance is created. If the class name is unknown or a connection error occurs, null is returned.
Pass an associated array of option/value pairs as the third argument to set global options on the returned instance. See "Options" for more information.
When a new IConnector instance is created, the connection settings are used to open a database connection. You don't need to do this manually (in fact, you can't). If the connection attempt fails, create() returns null and an error message is written to the PHP error log by default. So a valid (not null) IConnector instance will always contain an open database connection.
Use the following IConnector constants to specify the connection settings. Additional (native) settings may be recognized by some IConnector classes. The type identifier prepending each constant is not the type of the constant itself (they are all strings) but the type of the expected connection value.
string CONN_HOSTNAME The name of the server host to connect to (ignored by ODBC). You can also include a port number, e.g. "hostname:port".
string CONN_DATABASE The name of the default database to use or an ODBC Data Source.
string CONN_USERNAME The database user name.
string CONN_PASSWORD The database password.
bool CONN_PERSISTENT Open a persistent connection to a database when true. The default value is false. Be careful when enabling this setting and read the feature description in the PHP manual first: Persistent Database Connections.
string CONN_CHARSET Some databases (MySQL) support a "connection character set", see "Connection Character Sets and Collations". Normally, you should set CONN_CHARSET to the character set of your application (not the database). Note that MySQL has different names for character sets than PHP. Use the MySQL names.
The following example shows the basic steps of how to connect to a database and use the connection for a simple SQL query. All settings in angle brackets ("<>") must be replaced with real connection values. <classname> can be any of the available IConnector classes. See "Get a database connection" for a list of available classes.
Use the IConnector::select() method to retrieve data from a database. Typically, the select() method sends a SQL SELECT query to a database and returns the query result. You can also use this method for other SQL statements (e.g. stored procedures) returning a query result.
This function normally returns a table as a numeric array of rows. Each row is an associated array. The array keys in the rows correspond to the column names of your query.
If you use the RESULT_KEY_FIELD option, the resulting table is an associated array of rows. If you send multiple SQL statement to the database, a numeric array of tables is returned, one for each statement. Note: Some databases (e.g. MySQL) don't support multiple SQL statements. Avoid this in database independent applications.
This example shows the use of the select() method in it's basic form, with only the required $query argument given. In this and all following examples, the creation of the IConnector instance is omitted. Regard the variable $conn as a valid IConnector instance. See "Connection example" for how to get an IConnector instance.
This example shows the use of the select() method with query placeholders and the actual values given in the separate argument $param. It's highly recommended to use query placeholders. The result output is omitted here and in the following examples. See "Using placeholders" for more information on placeholders.
For security reasons, you should provide an additional type definition map to enforce correct parameter types (and speed up debugging). If a required parameter doesn't exist or has the wrong type, an exception is thrown. See "Type validation" for more information.
There are several options to control the behavior of the Connector library. You can define global and local options. Global options apply to an IConnector instance. Local options are only used within the scope of a single method call and override any global options. See "Options" for more information.
Use insert(), update() and delete() to modify data on a database. These methods are typically used for - but not limited to - SQL INSERT, UPDATE and DELETE statements respectively. For SQL CREATE and ALTER statements, the update() method is recommended. The delete() method should be used for SQL DROP statements.
The arguments are identical for all methods and are used in exactly the same way as in the select() method. See "Retrieve data: select()" for examples.
In the following examples, the creation of the IConnector instance is omitted. Regard the variable $conn as a valid IConnector instance. See Connection example for how to get an IConnector instance. All examples demonstrate the use of placeholders and type validation. As with the select() method, these arguments are recommended but not required.
Use IConnector::insert() to execute a SQL INSERT statement on a database. The return value is the IDENTITY ID (also called auto increment) of the last inserted row, or null if the query didn't generate an IDENTITY ID. If the query fails, false is returned.
Note: Only the last single IDENTITY ID is returned. If your SQL query adds multiple rows to the database, or database triggers create additional rows internally, this method might not return the expected result.
As most PHP database extensions don't support this feature, some IConnector classes will extend your SQL query to retrieve the IDENTITY ID. This is one of the very rare cases where the Connector library does any SQL rewriting.
The IConnector::update() method executes a SQL UPDATE statement on a database and returns the number of rows updated by the query. If the query fails, false is returned. This method is also recommended for SQL CREATE or ALTER statements.
The IConnector::delete() method executes a SQL DELETE statement on a database and returns the number of rows deleted by the query. If the query fails, false is returned. This method is also recommended for SQL DROP statements.
The attentive reader might have noticed the similarity between the update() and delete() methods. Well, the truth is that both methods are identical for all current implementations of the IConnector classes. Still, it's highly recommended to use the methods as intended. Further versions or additional IConnector classes might make a real difference.
Query placeholders is a common database concept used in "prepared statements", "parameterized queries" and "stored procedures" and supported - in one way or another - by many databases. The common pattern is to use placeholder "variables" in SQL statements and pass the actual values to the database in additional arguments and/or function calls. Although the use of placeholders has been widely adopted, support, syntax and implementation models differ.
The Connector library offers database independent use of placeholders in SQL queries. All IConnector classes support the use of placeholders and all use the same common syntax. This is true even for PHP database extensions with no native placeholder support. If a PHP database extension doesn't support placeholders, the IConnector class will merge the query with the actual values before sending the merged query to the database. All values are escaped and quoted according to the database in use.
The use of query placeholders is highly recommended over traditional string manipulation:
Easy to use: Placeholders are simply easier to use and much easier to read and debug than queries clogged up with escaped PHP variables.
Security Poor quoting and escaping are the main reasons for SQL injection attacks. You only have to forget once...
Database independent: Quoting and/or escaping rules may differ for different databases.
Functionality: Placeholders are required for many features and options provided by the Connector library, like type validation, parameter stripping, string encoding, array-to-string conversion and parameterized queries.
The Connector library applies the common "named parameter" syntax, also used by PHP Data Objects (PDO). A query placeholder is a single name with a colon prefix (":name"). The "name" may only contain letters and digits (a-z, A-Z, 0-9). Don't quote any placeholders. The placeholder syntax is valid for all IConnector classes and is not bound to a specific database.
The actual parameters are given in the separate $param argument as an associated array of name/value pairs. The names (array keys) in $param must correspond exactly (case sensitive) to the query placeholder without the colon (":") prefix.
Even if PHP is very tolerant about types, most databases are not. So your application should be very strict on types too, especially when handling user input. The Connector library provides build-in support for type validation. For security reasons, you should always use type validation to enforce correct parameter types (and speed up debugging).
All IConnector query methods accept a type definition map as the third argument ($map). The map defines the required names and types of the second argument ($param). A type definition map is an array of type definitions. A type definition is an array of two or three elements.If the type of $param[name] is wrong and can't be converted to the required type, an exception is thrown. If $param[name] doesn't exist or is null, the optional default value is used or - if no default value is given - an exception is thrown. To accept null, set null as the default value.
Note: The "date" type identifier makes use of the PHP DateTime class, which was introduced with PHP 5.2. On earlier PHP versions, a "date" parameter is validated with strtotime() but not casted to DateTime.
Options can be used globally on an IConnector instance or locally on an IConnector method. Global options are effective every time an instance's method is called. Local options are only effective for one single method call and override any global options. Call getOptions() to get the current values of one or more global options.
There are three ways to set options to control the behavior of the Connector library. Note that some options are only accepted by the select() method and can't be set globally. See the Option elements for more information.
Call the static ConnectorFactory::create() with an associated array of option/value pairs as the third argument to set global options on the returned instance. See the section "Get a database connection" for more information.
Call an instance's setOptions() method to set one or more global options on the instance.
To use local options, call select(), insert(), update() or delete() with an associated array of option/value pairs as the fourth argument. The RESULT_* options are only accepted by the select() method (see Option elements).
Use the IConnector::getOptions() method to get the current values of one or more global options. getOptions() has the following signature:
If the $element argument is given, the value of that option element is returned. If the element doesn't exist, null is returned. Without arguments, getOptions() returns all global options as an associated array of option/value pairs.
Use the IConnector::setOptions() method to set one or more global options on an IConnector instance. Note that some options can't be set globally. See the Option elements for more information.
If the $element argument is given, $options takes the new value for that option element. If the $element argument is omitted, setOptions() takes an associated array of option/value pairs as the first argument. In both cases, the old option value(s) are returned.
Use IConnector constants when setting and getting options. Additional (native) options may be accepted by some IConnector classes. The type identifier prepending each constant is not the type of the constant itself (they are all strings) but the type of the expected option value.
string CHAR_APPLICTION
Set this option to the character set being used by your appication. It is only used when CHAR_DATABASE is defined and different.
Valid values are the PHP supported character encodings. The default value is UTF-8.
string CHAR_DATABASE
Set this option to the character set being used by the database. If your database uses a different character set than your web application, you can set this option to automatically convert strings being sent to and retrieved from the database. Note: This option requires mbstring OR iconv.
Valid values are the PHP supported character encodings. The default value is null (no conversion). Note: Only ASCII-compatible character sets like ISO-8859-*, CP125* and UTF-8 are supported. See "PHP Character Encoding Requirements" for more information.
bool LOG_DEBUG
Write debug information in the php log file when true. The default value is false. Don't use this option in a production environment.
bool LOG_ERROR
Error information is written in the php log file when true. The default value is true.
bool PARAM_NAMED
true, when named placeholders are used, false otherwise. The default value should be correctly set for each IConnector class, so normally you won't need to care about this option.
string PARAM_PREFIX
The placeholder prefix to be used in parameterized queries. Valid values are a string (mostly a single character). The default value should be correctly set for each IConnector class, so normally you won't need to care about this option.
bool PARAM_QUERIES
Set this option to true to send sql statements as parameterized queries to the database. The default value depends on the IConnector class. true when parameterized queries are supported and stable (sqlsrv), false when not supported (mssql, mysql) or buggy (odbc). This option is ignored for PHP database extensions that don't support parameterized queries.
It's recommended to use parameterized queries whenever possible. See the post Database Tip: Use Parameterized Queries for a good introduction to parameterized queries.
bool PARAM_STRIP_MAGIC
Strip magic quotes from string parameters when true. The default value is true when magic_quotes_gpc are enabled, false otherwise. Normally, you don't need to manually set this option.
bool PARAM_STRIP_TAGS
Strip all PHP and HTML tags from string parameters when true. The default value is false.
This option offers a rough protection against PHP injection and XSS attacks, but might be overkill for some applications. If you need a more subtle solution, set this option to false and strip all string parameters before calling insert() or update().
bool PARAM_TRIM
Strip leading and trailing white space from string parameters when true. The default value is false.
bool RESULT_CACHE
Enable internal caching of all tables returned by IConnector::select() when true. The default value is true.
Query results are only cached within the scope of a single (http) request. For obvious reasons, no caching is done on the insert(), update() or delete() methods.
string RESULT_KEY_FIELD
When enabled, IConnector::select() returns the query result table as an associated array of rows (which again are associated arrays). This option element is not accepted as a global option and can only be given as a select() argument for one single call.
Set the value of this option to the field name containing a unique ID of any row in the table. Most often, this is the column name of the primary key column in a database table. The unique ID will be used as the key in the associated array of rows.
This option is very useful if you need fast and direct access to specific rows in the table returned by the select() method. Use this option to merge two or more query results or to create a hierarchical representation of a flat database table. This option is required for tables used in the Tree library (another package). In most other cases, there is no need to use this option. Be aware that some PHP functions (e.g. the SOAP encoder) expects numeric arrays.
int RESULT_LENGTH
The maximum number of rows returned by the IConnector::select() method. This option element is not accepted as a global option and can only be given as a select() argument for one single call. Valid values are positive integer numbers. There is no default value (no length restriction).
Note: Some IConnector classes will use SQL rewriting to limit the number of rows returned by the database. Others will just chop off the spare rows, which will be a performance issue when more than 1000 rows are returned.
int RESULT_OFFSET
The number of rows to skip from the beginning of the query result table returned by the IConnector::select() method. This option element is not accepted as a global option and can only be given as a select() argument for one single call. Valid values are positive integer numbers. There is no default value (no offset).
Note: Some IConnector classes will use SQL rewriting to limit the number of rows returned by the database. Others will just chop off the spare rows, which will be a performance issue when more than 1000 rows are returned.
If a suitable connector is not available in this package, you can add your own IConnector class with a limited effort. The recommended way is to extend the BaseConnector abstract class. The BaseConnector implements common tasks for all IConnector classes in this package. To extend it, you must (at least) implement four methods defined by the IConnector interface:
For a flying start, make a copy of MssqlConnector.php or MysqlConnector.php. Replace all mssql_* or mysql_* function calls with the PHP database functions of your choice. Check the BaseConnector::$options and make any necessary adjustments to the default values in your class constructor. Some BaseConnector methods might need reimplementation as well. The usual suspects would be:
When you're finished, add your new connector to the class name switch in ConnectorFactory::create() and start testing...
If you are an experienced user of one or more PHP database extensions, your contribution to this project is warmly welcome, provided it's thoroughly tested, stable, secure and well documented.
Documentation generated on Wed, 03 Jun 2009 12:41:49 +0200 by phpDocumentor 1.4.1