PHP Samples
A Quick Look at Some of my Work
These classes do not present general-purpose database access methods. Instead, they are tailored to the style of access used by my CMS, SiteCommander. The main reason for developing these classes was to centralize database access in a set of methods that use PDO statement binding and thus further reduce the chance of SQL injection attacks. PDO statement binding is slightly more complicated than MYSQLI binding but easier to manage than mysqli_realescape_string().
It may seem strange that I created this module so recently. When I first started with PHP 4, PDO was not available. When PHP 5 arrived, not all hosting companies included all the extensions necessary to support PDO. I was thus forced to start with the MYSQL extension and, shortly thereafter, MYSQLI. Also, early versions of PDO benchmarked significantly slower than MYSQLI. Today all hosting plans have support for PDO for MySQL and PDO is much faster.
I am now in the slow process of modifying all of my code to use my database access layer and remove the MYSQLI code. As a side effect, this will isolate PDO in my layer, minimizing disruption if something better replaces PDO in the future.
/**
* +--------------------------------------------------------------------+
* | SiteCommander by Will Fastie. |
* | Copyright (c) 2009-2017 by Will Fastie. All Rights Reserved. |
* | This source code may not be duplicated, distributed, or transfered |
* | by any means, electronic or otherwise, without the express, |
* | written consent of Will Fastie except for the purposes of backup. |
* +--------------------------------------------------------------------+
* | CLASSES - db_writer, db_reader |
* +--------------------------------------------------------------------+
*/
/**
* This file contains SiteCommander objects relating to database access.
*
* The db_access package contains several database access classes for reading and writing
* a SiteCommander table. Although these methods are superior to my old ones, the main reason
* for this set of classes is to eventually have all database access handled through PDO
* and the better protections it provides.
*
* This package is designed around the SiteCommander data access style and is not intended as
* its own data access layer.
*
* 23 Dec 2016 - New, combined db_writer and db_reader into one master class
* 29 Dec 2016 - Modified where_condition method to accept an SQL function to be applied to the field.
* 12 Jan 2017 - Added destructor methods to force PDO connections closed.
* 07 Feb 2017 - Added several convenience static methods to replace mysqli-based functions in fs-database.php
* 07 Feb 2017 - Added limit feature to db_reader
* 07 Feb 2017 - Removed the order_by argument from the fetch method.
*/
/* Parent Class Definition --------------------------------------------------------------- */
class db_access {
// Constants used by more than one object
const SIMPLE_TYPE_NUMBER = 'number';
const SIMPLE_TYPE_STRING = 'string';
const SIMPLE_TYPE_BOOLEAN = 'boolean';
// Properties used by more than one object
protected $table_name;
protected $sql_numeric_types;
protected $pdo_types;
protected $simple_schema;
protected $record;
protected $pdo;
protected $stmt;
public $pdo_error;
/**
* Function to initialize either the reader or writer
* Sets up the type arrays, creates the connection, and creates the simple schema.
*/
protected function initialize($table, $my_db) {
$this->table_name = $table;
// MySQL/SQL numeric types
$this->sql_numeric_types = array('int', 'float', 'double', 'decimal');
// Map my simple types into PDO parameter types
$this->pdo_types = array(
self::SIMPLE_TYPE_NUMBER => PDO::PARAM_INT,
self::SIMPLE_TYPE_STRING => PDO::PARAM_STR,
self::SIMPLE_TYPE_BOOLEAN => PDO::PARAM_BOOL
);
$this->pdo = pdo_connect($my_db);
$this->get_simple_schema();
}
/**
* Read the table schema and make a "simple schema" consisting of
* an array containing PDO binding types indexed by field name.
* Timestamp fields are ignored.
* A list of booleans is also collected.
*/
protected function get_simple_schema() {
$this->simple_schema = array();
$this->booleans = array();
$query = "SHOW COLUMNS FROM {$this->table_name}";
$result = $this->pdo->query($query);
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
$sql_type = strtolower($row['Type']);
$sql_field = strtolower($row['Field']);
if ($sql_type != 'timestamp') {
$this->simple_schema[$sql_field] = $this->get_simple_type($sql_type);
if ($this->simple_schema[$sql_field] == self::SIMPLE_TYPE_BOOLEAN) {
$this->booleans[] = $sql_field;
}
}
}
}
/**
* Convert an actual SQL type to a simplified type used for PDO binding
* @param string $sql_type - the actual type string returned by the SHOW COLUMNS query
* @return string $simple_type - one of the SIMPLE_TYPE constants
*/
protected function get_simple_type($sql_type) {
$type = $sql_type;
$pos = strpos($type, '(');
if ($pos !== false) { $type = substr($type, 0, $pos); }
if (in_array($type, $this->sql_numeric_types)) {
$simple_type = self::SIMPLE_TYPE_NUMBER;
} else if ($sql_type == 'tinyint(1)' or in_array($type, array('bool', 'boolean'))) {
$simple_type = self::SIMPLE_TYPE_BOOLEAN;
} else {
$simple_type = self::SIMPLE_TYPE_STRING;
}
return $simple_type;
}
// Get and Format an error message
// $error_info is the array returned by PDO for errors
protected function save_error_message($error_info) {
$this->pdo_error = "[{$error_info[0]}:{$error_info[1]}] {$error_info[2]}";
// $this->stmt->debugDumpParams(); // debugging
}
// Process the booleans - posted data does not return true or false but rather present or not present
protected function process_booleans() {
foreach ($this->booleans as $boolean) {
$this->record[$boolean] = (array_key_exists($boolean, $this->record)) ? true : false ;
}
}
} /* end of class db_access */
/* Class Definition for the general purpose DB Writer ----------------------------- */
/*
* The db_writer class provides methods for inserting or updating a single record.
* Both methods use PDO and statement binding to assure that all inputs are sanitized,
* thus avoiding SQL injection attacks.
* The class automatically determines all field types by developing a simple schema that
* converts all actual SQL types into the three types needed by PDO to process fields.
*
* Unlike query_builder, db_writer keeps the database connection alive on the assumption that
* there will always be a call to the update or insert methods. This should be more efficient
* than constantly creating connections.
*/
/* DB Writer ------------------------------------------------------- db_writer ----- */
class db_writer extends db_access {
// CONSTANTS
const USE_POSTED_DATA = NULL;
// PROPERTIES
// CONSTRUCTOR - initializes
public function __construct($table, $my_db = false) {
$this->initialize($table, $my_db);
}
// DESTRUCTOR - makes sure PDO connections are closed
public function __destruct() {
$this->pdo = NULL;
$this->stmt = NULL;
}
// Helper function to select either posted data or the passed source record
private function select_data_source($source_record) {
if ($source_record == self::USE_POSTED_DATA) {
$this->record = $_POST;
$this->process_booleans();
} else {
$this->record = $source_record;
}
}
// Insert query that makes sure the inputs are properly sanitized.
// The source data is assumed to be a fully-formed record. Its id and any timestamp fields are ignored.
public function record_insert($source_record) {
$this->select_data_source($source_record);
// Do the binding
$column_list = array();
$value_list = array();
$binding_type = array();
foreach ($this->record as $field_name => $value) {
// Select any field in the simple schema except for ID
if (array_key_exists($field_name, $this->simple_schema) and $field_name != 'id') {
$column_list[] = $field_name;
$value_list[] = ":$field_name";
$binding_type[$field_name] = $this->pdo_types[$this->simple_schema[$field_name]];
}
}
// Create the SQL statement
$columns = implode(', ', $column_list);
$values = implode(', ', $value_list);
$sql = "INSERT INTO {$this->table_name} ($columns) VALUES ($values)";
$this->stmt = $this->pdo->prepare($sql);
foreach ($column_list as $column) {
$this->stmt->bindValue(":$column", $this->record[$column], $binding_type[$column]);
}
if ($this->stmt->execute()) {
return true;
} else {
$this->save_error_message($this->stmt->errorInfo());
return false;
}
}
/**
* Update query that makes sure the inputs are properly sanitized
* The source record may be a fully-formed record , perhaps read from the database and then altered, or
* a partially formed record containing the ID and the fields requiring update. In the second case,
* a $field_names argument is required.
*
* The ID field MUST be present in the source record and is used to identify the record being updated.
* The field names array identifies which fields are to be updated. Only the named fields are updated.
* If $field_names is NULL, update all the fields contained in the source record and the simple schema except for IDs.
*/
public function record_update($source_record, $field_names = NULL) {
$this->select_data_source($source_record);
// Make a new field list containing those fields that will be updated.
$field_list = array();
if (is_null($field_names)) {
foreach ($this->record as $field_name => $value) {
if (array_key_exists($field_name, $this->simple_schema) and $field_name != 'id') {
$field_list[] = $field_name;
}
}
} else {
foreach ($field_names as $field_name) {
if (array_key_exists($field_name, $this->simple_schema) and array_key_exists($field_name, $this->record) and $field_name != 'id') {
$field_list[] = $field_name;
}
}
}
// Create the PDO binding information for the field list
$nv_pairs = array();
$binding_type = array();
foreach ($field_list as $name) {
$nv_pairs[] = "$name = :$name";
$binding_type[$name] = $this->pdo_types[$this->simple_schema[$name]];
}
// Create the SQL statement
$pairs = implode(', ', $nv_pairs);
$sql = "UPDATE {$this->table_name} SET $pairs WHERE id={$this->record['id']}";
$this->stmt = $this->pdo->prepare($sql);
foreach ($field_list as $name) {
$this->stmt->bindValue(":$name", $this->record[$name], $binding_type[$name]);
}
if ($this->stmt->execute()) {
return true;
} else {
$this->save_error_message($this->stmt->errorInfo());
return false;
}
}
// Get last inserted ID
public function last_inserted_id() {
return $this->pdo->lastInsertId();
}
// Convenience Methods to replace functions from fs-database.php --------------------
// These are usually shorthand functions that do not allow additional where conditions or other constrainst like order.
// The purpose of these functions is to provide replacements for all mysqli-based calls so everything is handled with PDO.
// Set the $value of one $field in record $id in table $table
public static function set_field($field, $value, $table, $id) {
$dbw = new db_writer($table);
$record = array('id' => $id, $field => $value);
$dbw->record_update($record, $field);
unset($dbw);
}
}
/* DB Writer - end ------------------------------------------------- db_writer ----- */
/* DB Reader ------------------------------------------------------- db_reader ----- */
/**
* The db_reader class provides a PDO-based mechanism for reading single records from a result set
* with much simpler coding on the calling side.
* The use of query_buidler is not required. The class automatically constructs the query when the first
* attempt is made to read a record by calling $dbr->next_record().
*
* The typical calling code looks something like this:
* $dbr = new db_reader(TB_NAME);
* $dbr->where_condition('enabled', true);
* $dbr->where_condition('username', strtolower($_POST['username']), '=', 'LOWER');
* while ($row = $dbr->fetch()) {
* ...
* }
* unset($dbr);
*
* In cases where the number of rows in the result set must be known before records are fetched,
* call $dbr->initiate_query(), then $dbr->nr_rows(), and then start fetching.
*
*/
class db_reader extends db_access {
// CONSTANTS
// PROPERTIES
private $where_conditions;
private $order_by;
private $limit;
private $query;
private $nr_rows;
// CONSTRUCTOR - initializes
public function __construct($table, $my_db = false) {
$this->initialize($table, $my_db);
$this->query_reset();
}
// DESTRUCTOR - makes sure PDO connections are closed
public function __destruct() {
$this->pdo = NULL;
$this->stmt = NULL;
}
// Retrieve the current query (for testing and debugging)
public function get_query_text() {
return $this->query;
}
// Reset query info
public function query_reset() {
$this->where_conditions = array();
$this->order_by = '';
$this->limit = '';
$this->query = '';
$this->nr_rows = 0;
unset($this->stmt);
}
// Accept a WHERE condition
public function where_condition($field_name, $value, $comparison = '=', $sql_function = '') {
$this->where_conditions[] = array(
'field' => $field_name,
'value' => $value,
'comparison' => $comparison,
'sql_function' => $sql_function
);
}
// Accept an order by field specification as a string
public function order_by($fields) {
$this->order_by = "ORDER BY $fields";
}
// Set the values for a LIMIT clause, including an offset if any
public function set_limit($nr, $offset = NULL) {
if (is_null($offset)) {
$this->limit = "LIMIT $nr";
} else {
$this->limit = "LIMIT $offset, $nr";
}
}
// Return the number of rows in the result set
public function nr_rows() {
return $this->nr_rows;
}
// Read and return the next record. If the query has not been assembled, do that first.
public function fetch() {
// See if we have a query
if (!$this->query) {
$this->initiate_query();
}
// Retrieve the next row
return $this->stmt->fetch(PDO::FETCH_ASSOC);
}
// Assemble a query given the parts collected so far and get the statement set
public function initiate_query() {
$conditions = array();
// Collect all the where conditions
foreach ($this->where_conditions as $condition) {
// Wrap the SQL field name in the provided function, if present (e.g. LOWER(field))
if ($condition['sql_function']) {
$sql_field = "{$condition['sql_function']}({$condition['field']})";
} else {
$sql_field = "{$condition['field']}";
}
$conditions[] = "$sql_field {$condition['comparison']} :{$condition['field']}";
}
$all_conditions = (empty($conditions)) ? '' : 'WHERE ' . implode(' AND ', $conditions) ;
$this->query = "SELECT * FROM {$this->table_name} {$all_conditions} {$this->order_by} {$this->limit};";
// echo_debug($this->query);
$this->stmt = $this->pdo->prepare($this->query);
// Determine the binding type for each condition and bind values to the query
foreach ($this->where_conditions as $condition) {
$field_name = $condition['field'];
$binding_type = $this->pdo_types[$this->simple_schema[$field_name]];
$this->stmt->bindValue(":$field_name", $condition['value'], $binding_type);
}
// $this->stmt->debugDumpParams(); // debugging
// Execute the query, returning a result set that the fetch method can use.
$this->stmt->execute();
$this->nr_rows = $this->stmt->rowCount();
}
// Private helper function to collect where conditions
// The conditions are collected from the passed array, if any, and added to the object
private function process_where_conditions($where_conditions) {
if (is_array($where_conditions)) {
foreach ($where_conditions as $where_condition) {
$this->where_condition($where_condition[0], $where_condition[1]);
}
}
}
/**
* Retrieve one record from a table by id
* Replaces db_record_get() except that where conditions must be supplied by calls to ->where_condition
* @param string $table name of table
* @param uint $id ID of target record
* @return array $record - the record retrived, false otherwise
*/
public function record_get($id, $where_conditions = false) {
$this->query_reset();
$this->where_condition('id', $id);
$this->process_where_conditions($where_conditions);
return $this->fetch();
}
/**
* Retrieve the first record from a table given a specific order
* Replaces db_record_get_first()
* @param string $table name of table
* @param uint $id ID of target record
* @param array $where_conditions (optional) additional where conditions
* @return array $record - the record retrived, false otherwise
*/
public function record_get_first($order, $where_conditions = false) {
$this->query_reset();
$this->order_by($order);
$this->set_limit(1);
$this->process_where_conditions($where_conditions);
return $this->fetch();
}
/**
* Gets the first record that meets the criteria, returns false otherwise
* Replaces db_record_get_by_field
* @param string $field_name - name of field
* @param string $field_value - value of field
* @return array $record - the record retrived, false otherwise
*/
public function record_get_by_field($field_name, $field_value, $where_conditions = false, $order = '') {
$this->query_reset();
$this->set_limit(1);
$this->order_by($order);
$this->where_condition($field_name, $field_value);
$this->process_where_conditions($where_conditions);
return $this->fetch();
}
/**
* Retrieve one record based on a permalink
* Replaces db_record_get_by_permalink
* @param string $permalink_value
* @return array $record - the record retrived, false otherwise
*/
public function record_get_by_permlink($permalink_value, $where_conditions = false, $order = '') {
$this->query_reset();
$this->set_limit(1);
$this->where_condition($field_name, $field_value);
$this->process_where_conditions($where_conditions);
return $this->fetch();
}
// Convenience Methods to replace functions from fs-database.php --------------------
// These are usually shorthand functions that do not allow additional where conditions or other constrainst like order.
// The purpose of these functions is to provide replacements for all mysqli-based calls so everything is handled with PDO.
// Get One Record
// No additional where conditions
public static function get_one_record($table, $id) {
$dbr = new db_reader($table);
$record = $dbr->record_get($id);
unset($dbr);
return $record;
}
// Get the First Record
// No additional where conditions
public static function get_first_record($table, $order) {
$dbr = new db_reader($table);
$record = $dbr->record_get_first($order);
unset($dbr);
return $record;
}
}
/* DB Reader - end ------------------------------------------------- db_reader ----- */