Database Class (osC_Database)

The database class (osC_Database) adds a layer in the core framework to standardize the way database queries are performed and to perform them securely. The primary goal of the database class is to parse all user input before inserting the data into the database and to parse the data already stored in the database when presenting it to the user.

The database class provides the following powerful features:

Value bindings
Query debugging
Result caching
Result splitting (page-sets)
Data logging

Connecting To A Database Server

Connecting to a database server can be performed as:

<?php
  require('includes/classes/database.php');

  $db_host = 'localhost';
  $db_username = 'db_00001';
  $db_password = 'db_00001';
  $db_database = 'db_oscommerce';
  $db_type = 'mysql';

  $osC_Database = osC_Database::connect($db_host, $db_username, $db_password, $db_type);
  $osC_Database->selectDatabase($db_database);
?>

Performing Queries

Database queries can be performed in the following manner:

Simple Query
Simple Binded Query
Query With Results

Simple Queries

Simple queries are performed straight away with the query passed to the simpleQuery() class method.

A simple query can be performed as:

<?php
  $osC_Database->simpleQuery('update osc_table_1 set field_1 = "value" where field_2 = "1"');
?>

Simple Binded Query

Binded queries provide extra security compared to simple queries where the variables used in the database query are forced a certain type value. String values are commonly parsed with the bindValue() class method and numerical values with bindInt().

A binded query can be performed as:

<?php
  $Qupdate = $osC_Database->query('update :table_1 set field_1 = :field_1 where field_2 = :field_2');
  $Qupdate->bindTable(':table_1', 'osc_table_1');
  $Qupdate->bindValue(':field_1', 'value');
  $Qupdate->bindInt(':field_2', '1');
  $Qupdate->execute();
?>

The :field_1 placeholder used in the database query would be replaced with a string value of value and the :field_2 placeholder would be replaced with an integer value of 1. The end query sent to the database server would be:

update osc_table_1 set field_1 = "value" where field_2 = 1

The following binding methods are available:

Class Method	Value Type
bindValue()	String values.
bindInt()	Integer values.
bindFloat()	Float or decimal values.
bindRaw()	No parsing performed.
bindTable()	Alias to bindRaw(). Used specifically to bind table names.

For security reasons binded queries are the preferred method to perform queries on the database server.

Query With Results

Retrieving data from a database table can be performed as:

<?php
  $Qselect = $osC_Database->query('select field_1, field_2 from :table_1 where field_3 = :field_3');
  $Qselect->bindTable(':table_1', 'osc_table_1');
  $Qselect->bindInt(':field_3', '1');
  $Qselect->execute();

  while ( $Qselect->next() ) {
    echo '<p>Field 1 = ' . $Qselect->valueProtected('field_1') . '<br />' .
         'Field 2 = ' . $Qselect->valueInt('field_2') . '</p>';
  }
?>

In addition to the variables being parsed within the database query with the bind*() class methods, the data retrieved from the database server is also parsed before being presented to the user. This is done with the value*() class methods to securely display user-stored information.

The following wrapper methods are available:

Class Method	Value Type
value()	No parsing performed.
valueProtected()	HTML safe output.
valueInt()	Integer output.
valueDecimal()	Float or decimal output.

It is important to parse all user-stored information with the valueProtected() class method to output a HTML safe value. User input must never be trusted and can be protected for displaying their name, street address, telephone number, and even their date of birth.

Debugging Queries

Database queries can be flagged in a debugging mode to log queries to a text file, to time the execution of the query, and to display on the page where the query is being executed.

Queries can either be debugged at a global level to debug all database queries made, or on a query-by-query basis.

Global Debugging
Query Debugging

Global Debugging

Global debugging can be set at the database class level by setting the setDebug() class method as follows:

<?php
  $osC_Database = osC_Database::connect($db_host, $db_username, $db_password, $db_type);
  $osC_Database->selectDatabase($db_database);
  $osC_Database->setDebug(true);
?>

Query Debugging

If global debugging is disabled, individual queries can be manually flagged to be debugged by setting the setDebug() class method at the query level as follows:

<?php
  $Qselect = $osC_Database->query('select field_1, field_2 from :table_1 where field_3 = :field_3');
  $Qselect->bindTable(':table_1', 'osc_table_1');
  $Qselect->bindInt(':field_3', '1');
  $Qselect->setDebug(true);
  $Qselect->execute();
?>

Caching Database Results

The data returned from a database query can be cached to a file for a period of time. This greatly improves the performance on the web and database servers by only performing database queries when needed.

Queries can be cached at the query level by using the setCache() class method as follows:

<?php
  $Qselect = $osC_Database->query('select field_1, field_2 from :table_1 where field_3 = :field_3');
  $Qselect->bindTable(':table_1', 'osc_table_1');
  $Qselect->bindInt(':field_3', '1');
  $Qselect->setCache('table_1_data', 60);
  $Qselect->execute();

  while ( $Qselect->next() ) {
    echo '<p>Field 1 = ' . $Qselect->valueProtected('field_1') . '<br />' .
         'Field 2 = ' . $Qselect->valueInt('field_2') . '</p>';
  }

  $Qselect->freeResult();
?>

This will save the results of the query to a file named table_1_data.cache for 60 minutes. Once 60 minutes have passed, the database query will be performed again to store the results again in the cache file. If the cache file is removed by other means, the query will be automatically performed to save the results again in a new cache file.

It is mandatory that the freeResult() class method be called once all data has been retrieved from the database query. A cache file will not be created if a call to this method is not performed.

Splitting Database Result Sets

The results of a database query can be split into page-sets for easier navigation. This can be performed by calling the setBatchLimit() class method as follows:

<?php
  if ( !isset($_GET['page']) || !is_numeric($_GET['page']) ) {
    $_GET['page'] = 1;
  }

  $Qselect = $osC_Database->query('select field_1, field_2 from :table_1 where field_3 = :field_3');
  $Qselect->bindTable(':table_1', 'osc_table_1');
  $Qselect->bindInt(':field_3', '1');
  $Qselect->setBatchLimit($_GET['page'], 10);
  $Qselect->setCache('table_1_data-page_' . $_GET['page'], 60);
  $Qselect->execute();

  while ( $Qselect->next() ) {
    echo '<p>Field 1 = ' . $Qselect->valueProtected('field_1') . '<br />' .
         'Field 2 = ' . $Qselect->valueInt('field_2') . '</p>';
  }

  $Qselect->freeResult();
?>

The $_GET['page'] variable holds the current page of the result set, and has a listing of 10 entries per page-set defined.

If the results of the database query is to be cached, it is important to give the cache filename a unique name for the current page set. This can be done by adding the current page number to the filename as shown above.

The links to travel between the result page-sets can be automatically generated by calling the getBatchPageLinks() class method as follows:

<?php echo $Qselect->getBatchPageLinks(); ?>

The current page-set number can be displayed by calling the getBatchTotalPages() class method as follows:

<?php echo $Qselect->getBatchTotalPages($osC_Language->get('result_set_number_of_entries')); ?>

Data Logging

The data logging feature of the database class is used solely on the Administration Tool to log the database changes a store administrator has performed. This can be performed by calling the setLogging() class method as follows:

<?php
  $Qupdate = $osC_Database->query('update :table_1 set field_1 = :field_1 where id = :id');
  $Qupdate->bindTable(':table_1', 'osc_table_1');
  $Qupdate->bindValue(':field_1', 'new_value');
  $Qupdate->bindInt(':id', 1);
  $Qupdate->setLogging('configuration', 1);
  $Qupdate->execute();
?>

This will log the changes to the osc_administrators_log database table under a module name of configuration and identifies the changes having belonged to the field ID of 1.