Coding a PHP 7 Framework #5 - Code Workshop

In part 5 of Coding a PHP Framework, we take a look at the biggest class yet, the lecPDO class - and how to use it!



Coding a PHP 7 Framework #5 - Code Workshop

22nd September 2018 in   PHP Tutorials by Elliott Barratt

You can view the finished framework at any time by visiting the GitHub repo at https://github.com/erbarratt/lectric.

If you need more space to see the code, click "Hide Sidebar >" above the Article Categories box to the right ->

First of all, let's get the code out of the way for /library/Lectric/lecPDO.class.php :

<?php
namespace Lectric;

/**
* lecPDO Class
*
* Handles Strict Select, Update, Insert and Delete functions
*
* @package    Lectric Framework
* @author     Elliott Barratt
* @copyright  Elliott Barratt, all rights reserved.
*
*/ 
class lecPDO 
{ 

	public $DBH;
	
	public const SINGLE = 1;
	public const MULTI = 2;
	public const STRICT = 3;
	public const TABLED = 4;
	public const SQL_ECHO = 5;
	
	private $_selectFields = '*';
	private $_insertFields = null;
	private $_updateFields = null;
	private $_whereArray = null;
	private $_whereOps = null;
	private $_orderByArray = null;
	private $_limit = null;
	private $_groupBy = null;

	function __construct(&$DBH)
	{
		$this->DBH = $DBH;
	}
	
	//Query Functions
		
		/**
		* Strict Select function
		* @param string $table the table to select data from
		* @param string $args array of args for single/multi, strict, tabled prefix return array and echo.
		* @return array
		*/
			public function selStrict(string $table = '', string ...$args): ?array
			{
				
				if (trim($table) === ''){
					throw new \Exception ('Table argument empty string.');
				}
				
				$sql = 'SELECT '. $this->getFieldInjSelect().' FROM `'.$table.'` '.$this->getWhereInj().' '.$this->getGroupByInj().' '.$this->getOrderByInj().' '.$this->getLimitInj();
				
				if (in_array(self::SQL_ECHO, $args)){
					echo $sql;
				} 
				
				return $this->runSelect($sql, $table, in_array(self::SINGLE, $args), in_array(self::STRICT, $args), in_array(self::TABLED, $args), $this->_whereArray);
				
			}
			
		/**
		* Lax Select function
		* @param string $query the table to select data from
		* @param string $args array of args for single/multi, strict, tabled prefix return array and echo.
		* @return array
		*/
			public function selLax(string $query, array $boundArray = null, string ...$args): ?array
			{
				
				if (in_array(self::SQL_ECHO, $args)){
					echo $query;
				}
				
				if(mb_stripos($query, 'select') === false){
					throw new \Exception('"SELECT" not part of the query in selLax();');
				}
				
				return $this->runSelect($query, 'table', in_array(self::SINGLE, $args), in_array(self::STRICT, $args), in_array(self::TABLED, $args), $boundArray);
				
			}
			
		/**
		* Strict Update function
		* @param string $table the table to update the data into
		* @param string $args array of args
		* @return bool
		*/
			public function updateStrict(string $table, ...$args): void
			{
				
				$sql = 'UPDATE `'.$table.'` SET '.$this->getUpdateFieldInj().' '.$this->getWhereInj('w_').'';
				
				if (in_array(self::SQL_ECHO, $args)){
					echo $sql;
				}
				
				if (is_array($this->_whereArray)){
					$boundArray = array_merge ($this->_updateFields, $this->_whereArray);
				} else {
					$boundArray = $this->_updateFields;
				}
				
				$this->queryLax($sql, $boundArray);
				
				return;
					
			}
			
		/**
		* Strict Insert function
		* Function uses defined clauses as set before this function call to insert that data as key=>value pairs into database table
		* @param string $args array of args
		* @return int
		*/
			public function insertStrict(string $table, ...$args): ?int
			{
			
				$inj = $this->getFieldToValueInsert();
				$sql = 'INSERT INTO `'.$table.'` ('.$inj['fields'].') VALUE ('.$inj['values'].')';
				
				if (in_array(self::SQL_ECHO, $args)){
					echo $sql;
				}
				
				$this->queryLax($sql, $this->_insertFields);
				
				return $this->DBH->lastInsertId();
			}
			
		/**
		* Strict Delete function
		* @param string $table the table to delete data from
		* @param string $args array of args
		* @return void
		*/
			public function deleteStrict(string $table, ...$args): void
			{
					
				$sql = 'DELETE FROM `'.$table.'` '.$this->getWhereInj().'';
				
				if (in_array(self::SQL_ECHO, $args)){
					echo $sql;
				}
				
				$this->queryLax($sql, $this->_whereArray);
				
				return;
			}
		
		/**
		* Straight Query function for passed query strings, none return
		* @param string $query the query to run
		* @param array $boundArray array of bound parameters
		* @return void
		*/
			public function queryLax(string $query, array $boundArray = null): void
			{
				
				try {
					$STH = $this->DBH->prepare($query);
					if ($boundArray === null){
						$STH->execute();
					} else {
						$STH->execute($boundArray);
					}
					$this->clearProperties();
				} catch (\PDOException $e){
					throw new SQLException($e->getMessage());
				} finally {
					return;
				}
			}
			
	//End Query Functions
	
	//Utility Functions
	
		/**
		* Run the select that's set up by selStrict and selLax functions
		* @param string $query the query to run
		* @param bool $singleResult single row or multi row return
		* @param bool $strict throw exception on empty data or not
		* @param bool $arrayType prefix return array with a ['table'] element in each row
		* @param array $boundArray array of bound paramters for sql
		* @return array
		*/
			private function runSelect(string $query, string $table, bool $singleResult = false, bool $strict = false, bool $tabled = false, array $boundArray = null): ?array
			{

				//Try to prepare the SQL statement, throw exception if this fails, for example without any where fields + ops?
				try {
					
					$STH = $this->DBH->prepare($query);
					
					if ($boundArray === null){
						$STH->execute();
					} else {
						$STH->execute($boundArray);
					}
					
				} catch (\PDOException $e) {
					throw new \Exception($e->getMessage());
				} finally {
					$this->clearProperties();
				}

				$STH->setFetchMode(\PDO::FETCH_ASSOC);
					
				if ($tabled === true){
					
					$returnedResult = [];
					$tempResults = [];
					
					if ($singleResult === true){
						
						$fetched = $STH->fetch();
						
						if ($fetched === false){
						
							//no rows
							if($strict === true){
								throw new \Exception('No Results');
							}
							
							return null;
							
						}
						
						return [$table => $fetched];
						
					} else {
						
						$fetched = $STH->fetchAll();
						
						if (empty($fetched)){
							
							//no rows
							if($strict === 'STRICT'){
								throw new \Exception('No Results');
							}
							
							return null;
							
						} else {
							
							foreach($fetched as $row){
								foreach ($row as $key=>$value){
										$tempResults[$table][$key] = $value;
								}
								$returnedResult[] = $tempResults;
							}
							
							return $returnedResult;
							
						}
						
					}
					
				} else {
					
					if ($singleResult === true){
						
						$fetched = $STH->fetch();
						
						if ($fetched === false){
							
							//no rows
							if($strict === 'STRICT'){
								throw new \Exception('No Results');
							}
								
							return null;
							
						} else {
							return $fetched;
						}
						
					} else {
						
						$fetched = $STH->fetchAll();
						
						if (empty($fetched)){
							
							//no rows
							if($strict === 'STRICT'){
								throw new \Exception('No Results');
							}
							
							return null;
							
						} else {
							return $fetched;
						}
						
					}
				}
			
			}
			
		/**
		* Check the sql function passed is ok
		* @param string $value a sql function
		* @return bool
		*/
			private function checkFunction(string $value = ''): ?bool
			{
				$value = trim($value);
				
				//protect against injection by semi-colon
					if(mb_stripos($value, ';') !== false){
						return false;
					}
				
				//none argument functions
					switch ($value){
						case 'NOW()':
							return true;
						break;
					}
				
				//argument functions
					$lastChar = (strlen($value)-1);
					if((
						//string
							mb_stripos($value, 'CONCAT(') === 0 ||
							mb_stripos($value, 'CHAR_LENGTH(') === 0 ||
							mb_stripos($value, 'FORMAT(') === 0 ||
							mb_stripos($value, 'LOWER(') === 0 ||
							mb_stripos($value, 'UPPER(') === 0 ||
							mb_stripos($value, 'TRIM(') === 0 ||
						//number
							mb_stripos($value, 'ABS(') === 0 ||
							mb_stripos($value, 'AVG(') === 0 ||
							mb_stripos($value, 'CEIL(') === 0 ||
							mb_stripos($value, 'COUNT(') === 0 ||
							mb_stripos($value, 'FORMAT(') === 0 ||
							mb_stripos($value, 'FLOOR(') === 0 ||
							mb_stripos($value, 'MAX(') === 0 ||
							mb_stripos($value, 'MIN(') === 0 ||
							mb_stripos($value, 'ROUND(') === 0 ||
							mb_stripos($value, 'RAND(') === 0 ||
							mb_stripos($value, 'SIGN(') === 0 ||
							mb_stripos($value, 'SUM(') === 0 ||
						//date
							mb_stripos($value, 'DATE(') === 0 ||
							mb_stripos($value, 'DATE_FORMAT(') === 0 ||
							mb_stripos($value, 'DAY(') === 0 ||
							mb_stripos($value, 'HOUR(') === 0 ||
							mb_stripos($value, 'MINUTE(') === 0 ||
							mb_stripos($value, 'MONTH(') === 0 ||
							mb_stripos($value, 'QUARTER(') === 0 ||
							mb_stripos($value, 'SECOND(') === 0 ||
							mb_stripos($value, 'TIME(') === 0 ||
							mb_stripos($value, 'WEEK(') === 0 ||
							mb_stripos($value, 'WEEKDAY(') === 0 ||
							mb_stripos($value, 'YEAR(') === 0
						) &&
						(
							mb_strripos($value, ')') === $lastChar || 	//close of function 
							mb_strripos($value, '"') === $lastChar		//where use of AS "something"
						)
					){
						return true;
					} else {
						return false;
					}
			}
			
		/**
		* Clear out the members for next transaction
		* @return void
		*/
			public function clearProperties(): void
			{
				$this->_selectFields = '*';
				$this->_insertFields = null;
				$this->_updateFields = null;
				$this->_whereArray = null;
				$this->_whereOps = null;
				$this->_orderByArray = null;
				$this->_groupBy = null;
				$this->_limit =  null;
			}
		
	//End Utility Functions
	
	//Get Functions
		
		/**
		* Build the whole where injection.
		* @return string
		*/
			private function getWhereInj(string $prefix = ''): ?string
			{
			
				/* This function takes all of the values (and arrays) in the Where Array and turns them into a string with bound parametres. */
				/* Note all the trimming, as this can catch whether or not the supplied is with '`' or not. */
			
				if (is_array($this->_whereArray)){
					
					//check the necessary members
						foreach ($this->_whereArray as $key => $value){
							if (trim($key) === ''){
								throw new \Exception ('Where array key empty.');
							}
						}
						
						if ($this->_whereOps === ''){
							throw new \Exception ('Where Ops empty in '.$function);
						}
						
						if (strlen($this->_whereOps) !== count($this->_whereArray)){
							throw new \Exception ('Ops does not match Where array count in '.$function);
						}
				
					//Set up the counting variable and initial string
					$i = 1;
					$whereInj = 'WHERE ';
					$indexCount = count($this->_whereArray);
					
					foreach ($this->_whereArray as $key=>$value){
					
						// If not the first loop, and AND to string
							if ($i <= $indexCount && $i > 1){
								$whereInj .= ' AND ';
							}
					
						$op = $this->getOP( $i);
						
						
						//don't need this now, as will cause mis-matched number of bound array elements error
							$keyUse = $key;
							unset ($this->_whereArray[$keyUse]);
						
						//If the OP is in, then $value is an array, but the key is also set (not assigned int index)
							if ($op === 'IN'){
								
								if(!is_array($value)){
									$valBits = explode(',',$value);
									$valBits = array_filter( $valBits, function($valBit) { return $valBit !== ''; });
								}
								
								if(empty($value)){
									throw new \exception('IN operation in Where Fields array need to be a valid array or explodable string.');
								}
								
								$valueIdentifiers = [];
								$i = 1;
								foreach($valBits as $valBit){
									$valueIdentifiers[] = ':'.$prefix.$keyUse.'_'.$i;
									$this->_whereArray[$prefix.$keyUse.'_'.$i] = $valBit;
									$i++;
								}
								
								//don't need this now, as will cause mis-matched number of bound array elements error
								
								$whereInj .= ' `'.$keyUse.'` IN ('.implode(', ',$valueIdentifiers).') ';
						
						//otherwise, then if $value is an array, that means $keyUse has been auto assigned as an int, and it's part of multiple field where (eg range `date` > X AND `date < Y)
							} elseif (is_array($value)){
							
								foreach ($value as $subKey => $subValue){
									
									//set up bound parameter as appending the current loop number to it to make it unique in the bound array.
									$whereInj .= ' `'.$subKey.'` '.$op.' :'.$prefix.$subKey.$i.' ';
									$this->_whereArray[$prefix.$subKey.$i] = $subValue;
									
								}
								
						//if op is not IN and $value not an array, then just a flat where clause (possibly like)
							} else {
								
								$value = ($op === 'LIKE') ? '%'.$value.'%' : $value;
								$this->_whereArray[$prefix.$keyUse] = $value;
								$whereInj .= ' `'.$keyUse.'` '.$op.' :'.$prefix.$keyUse.' ';

							}
						
						$i++;
						
					}
				} else {
					$whereInj = '';
				}
			
				return $whereInj;
			}
			
		/**
		* Get SQL operator as opposed to framework operator
		* @return string
		*/
			private function getOp(&$i): ?string
			{
				$op = substr($this->_whereOps, ($i-1), 1);
				switch ($op){
					case '=':
						return '=';
					break;
					case 'N':
						return '!=';
					break;
					case '<':
						return '<';
					break;
					case '>':
						return '>';
					break;
					case 'L':
						return '<=';
					break;
					case 'G':
						return '>=';
					break;
					case 'X':
						return 'LIKE';
					break;
					case 'I':
						return 'IN';
					break;
					default:
						throw new \Exception ('Invalid Ops Parameter at index '.$i);
					break;
				}
				
			}
			
		/**
		* Build field injection string for select
		* @return string
		*/
			private function getFieldInjSelect(): ?string{
				
				if(is_array($this->_selectFields)){
					
					foreach ($this->_selectFields as $value){
						if ($value === ''){
							throw new \Exception ('Blank Select Fields array element found.');
						}
					}
			
					$fieldInj = '';
				
					$i = 1;
					$indexCount = count($this->_selectFields);
					
					foreach ($this->_selectFields as $value){
						
						if ($i <= $indexCount && $i > 1){
							$fieldInj .= ',';
						}
						
						if($this->checkFunction($value) === true){
							$fieldInj .= ' '.$value.' ';
						} else {
							$fieldInj .= ' `'.$value.'` ';
						}
						
						$i++;
					}
					
					return $fieldInj;
				
				} else {
					return '*';
				}
			
			}
			
		/**
		* Build order by injection
		* @return string
		*/
			private function getOrderByInj(): ?string
			{
				
				//check the order by array
				if(is_array($this->_orderByArray)){
					foreach ($this->_orderByArray as $key => $value){
						if (trim($key) === ''){
							throw new \Exception ('Order By array key empty.');
						}
						
						if(strtolower($value) !== 'asc' && strtolower($value) !== 'desc'){
							throw new \Exception ('Order By array value must be "ASC" or "DESC".');
						}
					}
					
					$i = 1;
					$orderbyInj =  ' ORDER BY ';
					$indexCount = count($this->_orderByArray);
					foreach ($this->_orderByArray as $key => $value){
						if ($i <= $indexCount && $i > 1){
							$orderbyInj .= ' , ';
						}
										
						$orderbyInj .= ' `'.$key.'` '.$value.' ';						
						$i++;
					}
					return $orderbyInj;
					
				} else {
					return '';
				}
			}
		
		/**
		* Build group by injection
		* @return string
		*/
			private function getGroupByInj(): ?string
			{
				
				//check the group by array
				if(is_array($this->_groupBy)){
					foreach ($this->_groupBy as $value){
						if ($value === ''){
							throw new \Exception ('Blank Group By array element found.');
						}
					}
					
					$groupByInj = ' GROUP BY ';
					$i = 1;
					$groupCount = count($this->_groupBy);
					foreach ($this->_groupBy as $value){
					
						if ($i <= $groupCount && $i > 1){
							$groupByInj .= ' , ';
						}
						
						$groupByInj .= ' `'.$value.'` ';
						
						$i++;
					
					}
					
					return $groupByInj;
					
				} elseif (trim($this->_groupBy) !== '' ){
					return ' GROUP BY `'.trim($this->_groupBy).'` ';
				} else {
					return '';
				}
				
			}
			
		/**
		* Build limit injection
		* @return string
		*/
			private function getLimitInj(): ?string
			{
				
				//check limit array
				if(is_array($this->_limit)){
					foreach ($this->_limit as $value){
						if (!is_int((int)$value)){
							throw new \Exception ('All Limit array elements must be of type integer.');
						}
					}
					
					return ' LIMIT '.$this->_limit[0].','.$this->_limit[1];
					
				} elseif(trim($this->_limit) !== ''){
					return ' LIMIT 0,'.(int)$this->_limit;
				} else {
					return '';
				}
			}
			
		/**
		* Build field injection string 
		* @return string
		*/
			private function getUpdateFieldInj(): ?string
			{
			
				if (!is_array($this->_updateFields)){
					throw new \Exception ('Update Fields not set.');
				}
			
				$fieldInj = '';
			
				$i = 1;
				$indexCount = count($this->_updateFields);
				
				foreach ($this->_updateFields as $key=>$value){
					if ($i <= $indexCount && $i > 1){
						$fieldInj .= ',';
					}
					
					//check passed function
					if ($this->checkFunction($value)){
						$fieldInj .= ' `'.$key.'` = '.$value.'';
						unset($this->_updateFields[$key]);
					} else {
						$fieldInj .= ' `'.$key.'` = :'.$key.'';
					}
					$i++;
				}
				
				return $fieldInj;
			
			}
			
		/**
		* Build field => value array for insert and update
		* @return array
		*/
			private function getFieldToValueInsert(): ?array
			{
				
				//check insert array
				if(!is_array($this->_insertFields)){
					throw new \Exception ('Insert Fields array not set.');
				}
				
				$inj = [
					'fields' => '', 'values' => ''
				];
				
				$i = 1;
				$indexCount = count($this->_insertFields);
				
				foreach ($this->_insertFields as $key=>$value){
					
					$value = ($value == null) ? '' : $value; // so checkFunction works if trying to insert null data type
					
					if ($i <= $indexCount && $i > 1){
						$inj['fields'] .= ',';
						$inj['values'] .= ',';
					}
					
					$inj['fields'] .= ' `'.$key.'` ';
					
					//check if passed function
					if ($this->checkFunction($value) === true){
						$inj['values'] .= ' '.$value.' ';
						unset($this->_insertFields[$key]);
					} else {
						$inj['values'] .= ' :'.$key.' ';
					}
					
					$i++;
					
				}
				
				return $inj;
			}
			
	//End Get Functions
	
	//Set Functions
			
		/**
		* Set the select where fields member
		*
		*	If a field is needed more than once in the where array, then do the following:
		*		
		*	instead of the usual:
		*			
		*	$this-_whereArray = array(
		*		'someKey' => $someValue,
		*		'keySame' => $value1,
		*		'keySame' => $value2
		*	);
		*			
		*	YOU MUST use:
		*		
		*	$this-_whereArray = array(
		*		'someKey' => $someValue,
		*		array( 'keySame' => $value1) ,
		*		array('keySame' => $value2)
		*	);
		*		
		* this will get picked up in the get where injection function and dealt with accordingly.
		*
		* @return void
		*/
			public function setWhereFields(array $array): void
			{		
				$this->_whereArray = $array;
			}
			
		/**
		* Set the where ops member
		* @return void
		*/
			public function setWhereOps(string $ops): void
			{
				$this->_whereOps = $ops;
				return;
			}
			
		/**
		* Set the select fields member
		* @param array $array The Select Fields Array
		* @return void
		*/
			public function setSelectFields(array $array): void
			{
				$this->_selectFields = $array;
				return;
			}
		
		/**
		* Set the order by member
		* @param array $array The Order By Array
		* @return void
		*/
			public function setOrderBy(array $array): void
			{
				$this->_orderByArray = $array;
				return;
			}
			
		/**
		* Set the group by member
		* @param mixed $groupBy The Group By Array
		* @return void
		*/
			public function setGroupBy($groupBy): void
			{
				$this->_groupBy = $groupBy;
				return;
			}
			
		/**
		* Set the limit by member
		* @param mixed $limit The Limit Array
		* @return void
		*/
			public function setLimit($limit): void
			{
				$this->_limit = $limit;
				return;
			}
			
		/**
		* Set the select insert fields member
		* @return void
		*/
			public function setUpdateFields(array $array): void
			{
				$this->_updateFields = $array;
				return;
			}
			
		/**
		* Set the select insert fields member
		* @return void
		*/
			public function setInsertFields(array $array): void
			{
				$this->_insertFields = $array;
				return;
			}
}

This article series is focusing on how to build a framework, but whilst we're not going to be digging into every function in this class we will at least see how it's used and explore some important points.

Standardisation

The main purpose of this class calls back to the definition of a framework, where we're looking to provide a standardised way to build applications. We've already dealt with building a standardised way to parse URL's, and so this class provides a standardised way to work with a MySQL (MariaDB or similar) database, whilst ensuring security through sanitisation and prepared statements.

Sanitisation

All of the 'strict' functions within this class feature means to sanitise query input variables through the use of prepared statements. Note, this sanitisation only concerns the SQL query itself, it is not making passed inputs safe for use in PHP scripts thereafter. Make sure anything going into the database that may get output to the browser later on is properly escaped/sanitised using htmlentities() etc!

Exception Handling

The lecPDO class will throw standard \Exception's when it encounters any error, allowing you to surround any call to it's functionality with try{}catch(){} control statements and do something about those errors gracefully. Exact usage will be explored in the next article.

Code Walkthrough

Class Properties

	public const SINGLE = 1;
	public const MULTI = 2;
	public const STRICT = 3;
	public const TABLED = 4;
	public const SQL_ECHO = 5;
	
	private $_selectFields = '*';
	private $_insertFields = null;
	private $_updateFields = null;
	private $_whereArray = null;
	private $_whereOps = null;
	private $_orderByArray = null;
	private $_limit = null;
	private $_groupBy = null;

The first block of properties are used as class constant indentifiers for the query functions further down. They act as configuration flags to determine how those functions behave, and are passed to them as variadic arguments.

The second block of private properties are used to store query settings ready for building a SQL string query in the query functions. These are initialised as null apart from the $_selectFields property, which will be seen further down.

Construct and Database Handle

	function __construct(&$DBH)
	{
		$this->DBH = $DBH;
	}

This class will most likely be doing a lot of work, and have perhaps many instantiations. It should be evident therefor, why we've been strict in keeping the $DBH database handle only passed by reference all the way through. This will save a lot of memory in larger applications.

Select Query Methods

		/**
		* Strict Select function
		* @param string $table the table to select data from
		* @param string $args array of args for single/multi, strict, tabled prefix return array and echo.
		* @return array
		*/
			public function selStrict(string $table = '', string ...$args): ?array
			{
				
				if (trim($table) === ''){
					throw new \Exception ('Table argument empty string.');
				}
				
				$sql = 'SELECT '. $this->getFieldInjSelect().' FROM `'.trim($table,'`').'` '.$this->getWhereInj().' '.$this->getGroupByInj().' '.$this->getOrderByInj().' '.$this->getLimitInj();
				
				if (in_array(self::SQL_ECHO, $args)){
					echo $sql;
				} 
				
				return $this->runSelect($sql, $table, in_array(self::SINGLE, $args), in_array(self::STRICT, $args), in_array(self::TABLED, $args), $this->_whereArray);
				
			}
			
		/**
		* Lax Select function
		* @param string $query the table to select data from
		* @param string $args array of args for single/multi, strict, tabled prefix return array and echo.
		* @return array
		*/
			public function selLax(string $query, array $boundArray = null, string ...$args): ?array
			{
				
				if (in_array(self::SQL_ECHO, $args)){
					echo $query;
				}
				
				if(mb_stripos($query, 'select') === false){
					throw new \Exception('"SELECT" not part of the query in selLax();');
				}
				
				return $this->runSelect($query, 'table', in_array(self::SINGLE, $args), in_array(self::STRICT, $args), in_array(self::TABLED, $args), $boundArray);
				
			}

Strict Selection

As mentioned above, the intention for the use of this class is to standardise database access to try and prevent security issues. The selStrict() function is strict in so much as it will only accept a number of paramters and settings to build a SQL query, thereby ensuring all inputs are prepared and passed seperately as a bound array. The variadic array argument $args is used in the call to runSelect(), which we'll examine later in the article, and is made up of a selection from the class constants defined above. The SQL string is built using calls to getWhereInj(), getGroupByInj(), getOrderByInj() and getLimitByInj(). We'll explore these functions in more detail later, but basically they use values set in the private class properties to build valid SQL to inject into the main string.

Naturally, the strict selection method only really allows SQL where clauses that contain AND logic, not OR. In most cases in a web application, you're only ever concerned with using AND to narrow down the selection criteria for some expected rows. If you require anything more complicated in your selection query, then you could use the selLax() function.

Lax Selection

The selLax() function is used in cases where you may want to include more complicated selection logic in your query, such as joinging tables, the use of OR in where clauses, field concatenation and temporary fields / tables etc. It's called lax, because it cannot ensure all inputs are prepared through a bound array, and relies on the developer ensuring so. For example, you could pass the following two strings as your $sql argument:

'SELECT * FROM `users` WHERE `name` = '.$_POST['name'];

'SELECT * FROM `users` WHERE `name` = :name';

The first query is unbelievably stupid, as I hope most reading this will concur. The second would then require the $boundArray argument to be ['name'=>$_POST['name']] and would be sanitised and prepared properly in the runSelect() call.

Update Query Method

		/**
		* Strict Update function
		* @param string $table the table to update the data into
		* @param string $args array of args
		* @return bool
		*/
			public function updateStrict(string $table, ...$args): void
			{
				
				$sql = 'UPDATE '.$table.' SET '.$this->getUpdateFieldInj().' '.$this->getWhereInj('w_').'';
				
				if (in_array(self::SQL_ECHO, $args)){
					echo $sql;
				}
				
				if (is_array($this->_whereArray)){
					$boundArray = array_merge ($this->_updateFields, $this->_whereArray);
				} else {
					$boundArray = $this->_updateFields;
				}
				
				$this->queryLax($sql, $boundArray);
				
				return;
					
			}

Much like the selStrict() function, the updateStrict() function takes in a table and varidic array of integers as arguments, and uses the return from the get...() functions to build a valid, safe SQL query.

You'll note we're passing 'w_' to getWhereInj(). This will act as a prefix ensuring we don't get conflicts when preparing the SQL statement, as the identifiers for a given value cannot be used twice. For example:

'UPDATE `users` SET `name` = :name WHERE `name` = :name;' //invalid
'UPDATE `users` SET `name` = :name WHERE `name` = :w_name;' //valid

In the event that the query requires a where clause, the _whereArray is merged into the _updateFields property, so it's key => value pairs (having been updated to be prefixed by 'w_') can be used in the statement preparation.

The call to queryLax() actually prepares and executes the statement.

Insert Query Method

		/**
		* Strict Insert function
		* Function uses defined clauses as set before this function call to insert that data as key=>value pairs into database table
		* @param string $args array of args
		* @return int
		*/
			public function insertStrict(string $table, ...$args): ?int
			{
			
				$inj = $this->getFieldToValueInsert();
				$sql = 'INSERT INTO `'.$table.'` ('.$inj['fields'].') VALUE ('.$inj['values'].')';
				
				if (in_array(self::SQL_ECHO, $args)){
					echo $sql;
				}
				
				$this->queryLax($sql, $this->_insertFields);
				
				return $this->DBH->lastInsertId();
			}

Following the recurring pattern, the insertStrict() function takes in a table and varidic array of integers as arguments, and uses the return from the get...() functions to build a valid, safe SQL query. Unlike the update function however, the insertStrict() function returns the table `id` of the row just inserted, useful to then go on and use that id later in your code.

Delete Query Method

		/**
		* Strict Delete function
		* @param string $table the table to delete data from
		* @param string $args array of args
		* @return void
		*/
			public function deleteStrict(string $table, ...$args): void
			{
					
				$sql = 'DELETE FROM `'.$table.'` '.$this->getWhereInj().'';
				
				if (in_array(self::SQL_ECHO, $args)){
					echo $sql;
				}
				
				$this->queryLax($sql, $this->_whereArray);
				
				return;
			}

The final strict function, deleteStrict() takes in a table and varidic array of integers as arguments, and uses the return from the get...() functions to build a valid, safe SQL query.

Lax Query Method

		/**
		* Straight Query function for passed query strings, none return
		* @param string $query the query to run
		* @param array $boundArray array of bound parameters
		* @return void
		*/
			public function queryLax(string $query, array $boundArray = null): void
			{
				
				try {
					$STH = $this->DBH->prepare($query);
					if ($boundArray === null){
						$STH->execute();
					} else {
						$STH->execute($boundArray);
					}
					$this->clearProperties();
				} catch (\PDOException $e){
					throw new SQLException($e->getMessage());
				} finally {
					return;
				}
			}

The final lax query function, queryLax() is used for any complex query that does not require a return of rows from the database, but will still throw an exception if the query is invalid. This function is used in the updateStrict() and insertStrict() functions to run their queries.

Run Select

/**
* Run the select that's set up by selStrict and selLax functions
* @param string $query the query to run
* @param bool $singleResult single row or multi row return
* @param bool $strict throw exception on empty data or not
* @param bool $arrayType prefix return array with a ['table'] element in each row
* @param array $boundArray array of bound paramters for sql
* @return array
*/
	private function runSelect(string $query, string $table, bool $singleResult = false, bool $strict = false, bool $tabled = false, array $boundArray = null): ?array
	{

		//Try to prepare the SQL statement, throw exception if this fails, for example without any where fields + ops?
		try {

			$STH = $this->DBH->prepare($query);

			if ($boundArray === null){
				$STH->execute();
			} else {
				$STH->execute($boundArray);
			}

		} catch (\PDOException $e) {
			throw new \Exception($e->getMessage());
		} finally {
			$this->clearProperties();
		}

		$STH->setFetchMode(\PDO::FETCH_ASSOC);

		if ($tabled === true){

			$returnedResult = [];
			$tempResults = [];

			if ($singleResult === true){

				$fetched = $STH->fetch();

				if ($fetched === false || empty($fetched)){

					//no rows
					if($strict === true){
						throw new \Exception('No Results');
					}

					return null;

				}

				return [$table => $fetched];

			} else {

				$fetched = $STH->fetchAll();

				if (empty($fetched)){

					//no rows
					if($strict === true){
						throw new \Exception('No Results');
					}

					return null;

				} else {

					foreach($fetched as $row){
						foreach ($row as $key=>$value){
								$tempResults[$table][$key] = $value;
						}
						$returnedResult[] = $tempResults;
					}

					return $returnedResult;

				}

			}

		} else {

			if ($singleResult === true){

				$fetched = $STH->fetch();

				if ($fetched === false || empty($fetched)){

					//no rows
					if($strict === true){
						throw new \Exception('No Results');
					}

					return null;

				} else {
					return $fetched;
				}

			} else {

				$fetched = $STH->fetchAll();

				if (empty($fetched)){

					//no rows
					if($strict === true){
						throw new \Exception('No Results');
					}

					return null;

				} else {
					return $fetched;
				}

			}
		}

	}

runSelect()'s job is to return an array of results. Not the setting of $STH->setFetchMode(\PDO::FETCH_ASSOC); which makes the return from the database be an associative array itself, so we can foreach() over it. The exact make-up of the array is determined by the $single and $tabled arguments. The $boundArray that was built or passed to earlier functions is also passed through to the $STH->execute() function.

The $strict argument tells this function to throw an exception if nothing was returned from the table, which is useful when writing fundamental code, such as retreiving configuration settings, or a webpage view where failure to do so means something has gone quite wrong!

Other Utility Functions

		/**
		* Check the sql function passed is ok
		* @param string $value a sql function
		* @return bool
		*/
			private function checkFunction(string $value = ''): ?bool
			{
				$value = trim($value);
				
				//protect against injection by semi-colon
					if(mb_stripos($value, ';') !== false){
						return false;
					}
				
				//none argument functions
					switch ($value){
						case 'NOW()':
							return true;
						break;
					}
				
				//argument functions
					$lastChar = (strlen($value)-1);
					if((
						//string
							mb_stripos($value, 'CONCAT(') === 0 ||
							mb_stripos($value, 'CHAR_LENGTH(') === 0 ||
							mb_stripos($value, 'FORMAT(') === 0 ||
							mb_stripos($value, 'LOWER(') === 0 ||
							mb_stripos($value, 'UPPER(') === 0 ||
							mb_stripos($value, 'TRIM(') === 0 ||
						//number
							mb_stripos($value, 'ABS(') === 0 ||
							mb_stripos($value, 'AVG(') === 0 ||
							mb_stripos($value, 'CEIL(') === 0 ||
							mb_stripos($value, 'COUNT(') === 0 ||
							mb_stripos($value, 'FORMAT(') === 0 ||
							mb_stripos($value, 'FLOOR(') === 0 ||
							mb_stripos($value, 'MAX(') === 0 ||
							mb_stripos($value, 'MIN(') === 0 ||
							mb_stripos($value, 'ROUND(') === 0 ||
							mb_stripos($value, 'RAND(') === 0 ||
							mb_stripos($value, 'SIGN(') === 0 ||
							mb_stripos($value, 'SUM(') === 0 ||
						//date
							mb_stripos($value, 'DATE(') === 0 ||
							mb_stripos($value, 'DATE_FORMAT(') === 0 ||
							mb_stripos($value, 'DAY(') === 0 ||
							mb_stripos($value, 'HOUR(') === 0 ||
							mb_stripos($value, 'MINUTE(') === 0 ||
							mb_stripos($value, 'MONTH(') === 0 ||
							mb_stripos($value, 'QUARTER(') === 0 ||
							mb_stripos($value, 'SECOND(') === 0 ||
							mb_stripos($value, 'TIME(') === 0 ||
							mb_stripos($value, 'WEEK(') === 0 ||
							mb_stripos($value, 'WEEKDAY(') === 0 ||
							mb_stripos($value, 'YEAR(') === 0
						) &&
						(
							mb_strripos($value, ')') === $lastChar || 	//close of function 
							mb_strripos($value, '"') === $lastChar		//where use of AS "something"
						)
					){
						return true;
					} else {
						return false;
					}
			}
			
		/**
		* Clear out the members for next transaction
		* @return void
		*/
			public function clearProperties(): void
			{
				$this->_selectFields = '*';
				$this->_insertFields = null;
				$this->_updateFields = null;
				$this->_whereArray = null;
				$this->_whereOps = null;
				$this->_orderByArray = null;
				$this->_groupBy = null;
				$this->_limit =  null;
			}

These small utility functions are used at various points in the class. The checkFunction() function see whether the user has put any of a selection of common functions into their setSelectFields() call. If so, they have to adhere to a strict format, excluding the ';' character (as a bit of protection vs injection!)

The most important is clearProperties() which resets the class for use after the execution of every query by resetting the values of the private class properties.

The Injection Getter Methods

The job of these functions, getWhereInj(), getFieldInjSelect()getOrderByInj()getGroupByInj()getLimitInj()getUpdateFieldInj()getFieldToValueInsert(), is to build valid SQL that can be used as part of a larger string to make a valid query. We don't need to go into the specifics of each as the functionality is roughly similar; loop through an array (one of the private class properties) and use it's key => value pairs to build and return a string. Usually this is an empty string if the property in question is still null, or in the case of getFieldInjSelect() it will return '*'.

Get Op Method

		/**
		* Get SQL operator as opposed to framework operator
		* @return string
		*/
			private function getOp(&$i): ?string
			{
				$op = substr($this->_whereOps, ($i-1), 1);
				switch ($op){
					case '=':
						return '=';
					break;
					case 'N':
						return '!=';
					break;
					case '<':
						return '<';
					break;
					case '>':
						return '>';
					break;
					case 'L':
						return '<=';
					break;
					case 'G':
						return '>=';
					break;
					case 'X':
						return 'LIKE';
					break;
					case 'I':
						return 'IN';
					break;
					default:
						throw new \Exception ('Invalid Ops Parameter at index '.$i);
					break;
				}
				
			}

The getOp() function is parhaps the most interesting, as it uses character based identifiers to return the appropriate operator for use in the SQL query. We'll explore how this get's used in the next article.

Property Setter Methods

The job of these functions, setWhereFields()setWhereOps()setSelectFields()setOrderBy()setGroupBy()setLimit()setUpdateFields()setInsertFields(), is to set the values of the private class properties for use in the injection getter methods. setGroupBy() and setLimit() do not have type hinted arguments, as they can accept both strings and arrays as arguments.

Conclusion

The /library/Lectric/lecPDO.class.php file will be the basis for many classes you may wish to create using the Lectric framework. In the next article, we'll explore how to use it.

Continue to Part 6 by clicking here.




Hide Sidebar >



Archive



Hide Sidebar >


This website uses cookies. Privacy Policy