3 Building a Database Access Class
The Oracle database functionality for the AnyCo application will be abstracted into a class that handles all PHP OCI8 access.
This chapter contains the following topics:
Connection Constants
Create a PHP file ac_cred.inc.php. The '.inc' component of the name is a common convention indicating that the file only contains definitions such as functions or classes. Giving it a final .php extension means that if a user somehow makes a HTTP request for that file, the web server will not send its text to the user (a security risk) but will run it as a PHP script. Because the file only contains definitions there will be no output sent to the user. This will prevent undesirable results or code exposure.
The ac_cred.inc.php file should initially look like:
<?php
/**
* ac_cred.inc.php: Secret Connection Credentials for a database class
* @package Oracle
*/
/**
* DB user name
*/
define('SCHEMA', 'hr');
/**
* DB Password.
*
* Note: In practice keep database credentials out of directories
* accessible to the web server.
*/
define('PASSWORD', 'welcome');
/**
* DB connection identifier
*/
define('DATABASE', 'localhost:pooled');
/**
* DB character set for returned data
*/
define('CHARSET', 'UTF8');
/**
* Client Information text for DB tracing
*/
define('CLIENT_INFO', 'AnyCo Corp.');
?>
To connect to an Oracle DB requires a user name, password, and a string identifying which DB to connect to. These are set as the constants SCHEMA, PASSWORD, and DATABASE using PHP's define() command. A character set is an optional but recommended connection parameter. Here UTF8 is chosen in the CHARSET constant.
Most PHP applications connect to the DB using one constant database account. In this example the database user is HR. This has some security implications that should not be discounted. Even though the file has the .php extension, in practice it is recommended to keep any files containing credentials or other sensitive information out of directories that Apache can access, and use PHP's require() command to load them. To avoid hard coding credentials in a file, some sites require applications read the values from environment variables set before starting Apache.
The database connection syntax used in DATABASE is Oracle's "Easy Connect" syntax. This specifies the host name where the database is running and identifies the service name of the database. Here the computer is given as localhost, meaning that PHP and the database need to be on the same computer. The :pooled suffix says that the connection should use the DRCP pool. If you did not start the DRCP pool in the section "Post PHP Installation Tasks on Windows and Linux", then omit this suffix and change DATABASE to localhost. This is the only application change that is necessary to determine whether to use DRCP.
A connection identifier could also be an Oracle Net tnsnames.ora alias, depending on your site standards.
The CLIENT_INFO constant will be used for end-to-end application tracing in the database. This is discussed in Chapter 13, "Monitoring Database Usage of the Application."
Creating the Db class
Create a new PHP file ac_db.inc.php to hold a database access class. Initially the file contains:
<?php
/**
* ac_db.inc.php: Database class using the PHP OCI8 extension
* @package Oracle
*/
namespace Oracle;
require('ac_cred.inc.php');
/**
* Oracle Database access methods
* @package Oracle
* @subpackage Db
*/
class Db {
/**
* @var resource The connection resource
* @access protected
*/
protected $conn = null;
/**
* @var resource The statement resource identifier
* @access protected
*/
protected $stid = null;
/**
* @var integer The number of rows to prefetch with queries
* @access protected
*/
protected $prefetch = 100;
}
?>
The ac_db.inc.php file sets the namespace to Oracle, defining the namespace of classes declared or used in the file. This avoids clashes if there are different implementations of classes in an application that happen to have the same name.
The database credentials are included with require(). If a required file does not exist a compilation error will occur. PHP also has an include() function that will not display an error for a missing file. You can use variants require_once() and include_once() to prevent a sub file from being included more than once.
The Db class attributes will be discussed soon.
The comments are in a format that the open source tool PHPDocumentor will parse, for example @package defines the overall package that this file belongs to. NetBeans 7.0 can use these tags to automatically generate application documentation.
Add the following two methods into the Db class, between the $prefetch attribute and the closing brace:
/**
* Constructor opens a connection to the database
* @param string $module Module text for End-to-End Application Tracing
* @param string $cid Client Identifier for End-to-End Application Tracing
*/
function __construct($module, $cid) {
$this->conn = @oci_pconnect(SCHEMA, PASSWORD, DATABASE, CHARSET);
if (!$this->conn) {
$m = oci_error();
throw new \Exception('Cannot connect to database: ' . $m['message']);
}
// Record the "name" of the web user, the client info and the module.
// These are used for end-to-end tracing in the DB.
oci_set_client_info($this->conn, CLIENT_INFO);
oci_set_module_name($this->conn, $module);
oci_set_client_identifier($this->conn, $cid);
}
/**
* Destructor closes the statement and connection
*/
function __destruct() {
if ($this->stid)
oci_free_statement($this->stid);
if ($this->conn)
oci_close($this->conn);
}
When a PHP object instance is created its __construct() method will be called. The Db class constructor opens a connection to Oracle Database and keeps the connection resource in the $conn attribute for use when running statements. If a connection does not succeed an error is generated. This error will be displayed to the user if PHP's php.ini parameter display_errors is On and sent to the Apache log files if log_errors is On. In the section "Post PHP Installation Tasks on Windows and Linux", display_errors was set to On to help development. A production application should never display errors to the user because this is an information security leak.
The constructor passes the connection credentials to an oci_pconnect() function. The AnyCo application uses oci_pconnect() to create a "persistent" DRCP connection, as described in Database Resident Connection Pooling.
The character set is also passed to oci_pconnect(). It specifies the character set that data will be in when returned from Oracle to PHP. Setting it is optional but recommended. If the character set is not passed to oci_pconnect(), then PHP will determine the character set from the environment settings, which can be slower and may lead to an unexpected value being used.
A consequence of using the one database user name is that all statements in the application are recorded in the database as being run by HR. This makes analysis and tracing difficult or impossible. The oci_set_client_identifier() function allows an arbitrary string to be recorded with the connection and processed statement details in the database. By setting the identifier to the name of the web user this allows DBAs to explicitly associate an end user with database usage. The following article describes in detail where client identifiers can be used in Oracle Database:
http://www.oracle.com/technetwork/articles/dsl/php-web-auditing-171451.html
Also to aid database tracing, two other pieces of metadata are set for each connection: the Client Information and the Module Name. Chapter 13, "Monitoring Database Usage of the Application" shows where they are useful.
If a connection error occurs, an exception is thrown. The Exception class name is fully qualified. If the leading '\' was removed then an attempt to call \Oracle\Exception would occur, causing a run time error because a class called Exception has not been defined in the Oracle namespace. The namespace separator in PHP is a backslash(\) because it was the only feasible character available when namespaces were introduced in PHP 5.3.
The Db instance destructor explicitly closes any open connection. For a persistent DRCP pooled connection like shown, this returns the database server process to the DRCP pool for reuse. Because PHP variables internally use a reference counting mechanism, any variable that increases the reference count on the connection resource must be freed before the underlying database connection will be physically closed. Here this means closing the statement resource, which is used later in this manual when the class is enhanced to run statements.
Because of PHP's reference counting mechanism, the destructor shown simply emulates the default behavior when an instance of the object is destroyed. Statement and connection resources will be terminated when variables referencing them are destroyed. This particular implementation of the destructor could therefore be omitted.
General Example of Running SQL in PHP OCI8
Running a statement in PHP OCI8 involves parsing the statement text and running it. In procedural style an INSERT would look like:
$c = oci_pconnect($un, $pw, $db, $cs);
$sql = "INSERT INTO mytable (c1, c2) VALUES (1, 'abc')";
$s = oci_parse($c, $sql);
oci_execute($s);
If a statement will be re-run in the database system with different data values, then use bind variables:
$c = oci_pconnect($un, $pw, $db, $cs);
$sql = "INSERT INTO mytable (c1, c2) VALUES (:c1_bv, :c2_bv)";
$s = oci_parse($c, $sql);
$c1 = 1;
$c2 = 'abc';
oci_bind_by_name($s, ":c1_bv", $c1, -1);
oci_bind_by_name($s, ":c2_bv", $c2, -1);
oci_execute($s);
Binding associates PHP variables with the bind identifier place holders in the SQL statement. The bind lengths are set to -1 telling PHP to deduce internal buffer sizes from the lengths of the PHP values. When using oci_bind_by_name() to return data from the database (such as when assigning a PL/SQL function return value to a bind variable), the actual expected data length should be specified so enough internal space can be allocated for the PHP variable.
Bind variables are important for performance and security reasons. They allow the database to reuse statement metadata for repeated statements where only the variable values change. An alternative PHP coding style would concatenate PHP variable values into the SQL statement text. Each such statement would appear unique to the DB and caching would be reduced. This severely impacts DB performance. Also concatenation introduces SQL Injection security risks, where concatenation with malicious user input changes the semantics of the SQL statement.
In PHP, a SQL query is similar to execution but has a subsequent fetch call, of which PHP has several variants. For example to fetch all rows at once:
$c = oci_pconnect($un, $pw, $db, $cs);
$sql = "SELECT * FROM mytable WHERE c1 = :c1_bv AND c2 = :c2_bv";
$s = oci_parse($c, $sql);
$c1 = 1;
$c2 = 'abc';
oci_bind_by_name($s, ":c1_bv", $c1, -1);
oci_bind_by_name($s, ":c2_bv", $c2, -1);
oci_execute($s);
oci_fetch_all($s, $res, 0, -1, OCI_FETCHSTATEMENT_BY_ROW);
The query results would be in $res. The OCI_FETCHSTATEMENT_BY_ROW constant indicates the results will be in an array with entries for each row. The rows themselves are represented by a sub-array.
If the query returns a large number of rows the memory use might be undesirably large. Other PHP OCI8 functions like oci_fetch_array() could be called instead. This function returns only one row of the result set. After the script has processed the row, it could call oci_fetch_array() again to fetch the next row.
Running SQL with the Db Class
To make our Db class in ac_db.inc.php useful add these two methods to the class:
/**
* Run a SQL or PL/SQL statement
*
* Call like:
* Db::execute("insert into mytab values (:c1, :c2)",
* "Insert data", array(array(":c1", $c1, -1),
* array(":c2", $c2, -1)))
*
* For returned bind values:
* Db::execute("begin :r := myfunc(:p); end",
* "Call func", array(array(":r", &$r, 20),
* array(":p", $p, -1)))
*
* Note: this performs a commit.
*
* @param string $sql The statement to run
* @param string $action Action text for End-to-End Application Tracing
* @param array $bindvars Binds. An array of (bv_name, php_variable, length)
*/
public function execute($sql, $action, $bindvars = array()) {
$this->stid = oci_parse($this->conn, $sql);
if ($this->prefetch >= 0) {
oci_set_prefetch($this->stid, $this->prefetch);
}
foreach ($bindvars as $bv) {
// oci_bind_by_name(resource, bv_name, php_variable, length)
oci_bind_by_name($this->stid, $bv[0], $bv[1], $bv[2]);
}
oci_set_action($this->conn, $action);
oci_execute($this->stid); // will auto commit
}
/**
* Run a query and return all rows.
*
* @param string $sql A query to run and return all rows
* @param string $action Action text for End-to-End Application Tracing
* @param array $bindvars Binds. An array of (bv_name, php_variable, length)
* @return array An array of rows
*/
public function execFetchAll($sql, $action, $bindvars = array()) {
$this->execute($sql, $action, $bindvars);
oci_fetch_all($this->stid, $res, 0, -1, OCI_FETCHSTATEMENT_BY_ROW);
$this->stid = null; // free the statement resource
return($res);
}
These methods do the same as the previous procedural samples, with the addition of another piece of database tracing metadata called the Action, and a way to tune performance of queries, called prefetching. Prefetching is discussed later in Chapter 8, "Query Performance and Prefetching".
All the tracing metadata set in the Db class is optional, however it is easier to include it in the design instead of having to retrofit it. It can be painful to troubleshoot performance or access issues on production applications without it.
Setting the statement identifier resource $this->stid to null initiates the same internal cleanup as oci_free_statement() (used in the destructor) and also sets the attribute to null so later methods can test for validity.
Our Db::execute() method allows us to write our INSERT statement as:
$db = new \Oracle\Db("Test Example", "Chris");
$sql = "INSERT INTO mytable (c1, c2) VALUES (:c1_bv, :c2_bv)";
$c1 = 1;
$c2 = 'abc';
$db->execute($sql, "Insert Example", array(array(":c1_bv", $c1, -1),
array(":c2_bv", $c2, -1)));
The query example would be:
$db = new \Oracle\Db("Test Example", "Chris");
$sql = "SELECT * FROM mytable WHERE c1 = :c1_bv AND c2 = :c2_bv";
$c1 = 1;
$c2 = 'abc';
$res = $db->execFetchAll($sql, "Query Example",
array(array(":c1_bv", $c1, -1),
array(":c2_bv", $c2, -1)));
The Db instance creation uses a fully qualified namespace description.
The bind variables are encapsulated in an array of arrays. Each sub-array describes one bind variable.
As coded, the Db class automatically commits each time oci_execute() it is called. This has performance and transactional consistency implications if the class is to be reused in future applications. To make Db more general purpose you could consider changing Db::execute() to do:
...
oci_execute($this->stid, OCI_NO_AUTO_COMMIT);
...
In this case you would need to add commit and rollback methods to the Db class that call oci_commit() and oci_rollback() respectively. The examples in this manual do not require these changes. In PHP any oci_connect() or any oci_pconnect() call that uses the same connection credentials will reuse the same underlying connection to the database. So if an application creates two instances of Db, they will share the same transaction state. Rolling back or committing one instance will affect transactions in the other. The oci_new_connect() function is different and will create its own new connection each time it is called.
Testing the Db Class
Test the Db class by creating a new PHP file called test_db.php:
<?php
// test_db.php
require('ac_db.inc.php');
$db = new \Oracle\Db("test_db", "Chris");
$sql = "SELECT first_name, phone_number FROM employees ORDER BY employee_id";
$res = $db->execFetchAll($sql, "Query Example");
// echo "<pre>"; var_dump($res); echo "</pre>\n";
echo "<table border='1'>\n";
echo "<tr><th>Name</th><th>Phone Number</th></tr>\n";
foreach ($res as $row) {
$name = htmlspecialchars($row['FIRST_NAME'], ENT_NOQUOTES, 'UTF-8');
$pn = htmlspecialchars($row['PHONE_NUMBER'], ENT_NOQUOTES, 'UTF-8');
echo "<tr><td>$name</td><td>$pn</td></tr>\n";
}
echo "</table>";
?>
The require() command includes the content of ac_db.inc.php giving the script access to the Db class.
The module name parameter for the Db instance creation is set to the file name base test_db. This allows anyone doing database tracing to identify where the connection was initiated from. The connection identifier is arbitrarily set to a fictitious user's name. The Action parameter to $db->execFetchAll() is set to the operation in the file.
No bind variables are passed in this example so the optional bind parameter is not specified in the $db->execFetchAll() method call. The definition of Db::execFetchAll() sets the bind variable list to an empty array when there is no final argument and therefore won't attempt to bind any data.
The query results are returned in $res as an array of row data. You can see the array structure by un-commenting the var_dump() function, which is useful for simple PHP debugging. The $res array is iterated over in a foreach() loop which processes each row in turn. The two columns in each row's sub-array are accessed by $row['FIRST_NAME'] and $row['PHONE_NUMBER']. By default, columns in Oracle database tables are case insensitive. They will be returned to PHP as upper case array indexes. If the table had been created in Oracle with a case sensitive column name like
CREATE TABLE mytab ("MyCol" NUMBER);
then in PHP you would need to use a case sensitive array index $row['MyCol'].
In test_db.php, the returned data is processed with htmlspecialchars() to ensure that any text that happens to look like HTML is treated as displayable text and not as HTML markup. This escaping of output is very important for security in web applications to make sure there are no cross-site scripting (XSS) security issues.
The exact htmlspecialchars() options you use would depend on context. PHP also has an htmlentities() function that might be useful. The character set should match the HTML page character set. The AnyCo application will do this.
Load test_db.php in a browser: http://localhost/test_db.php. Or, in NetBeans, right click the file in the Projects navigator and select Run.
It displays:
If you have problems connecting, resolve any PHP interpreter errors. Make sure all methods are located inside the class definition braces. Review the section Testing PHP Connections to Oracle for other common problems.