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.

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.

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:

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.

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:

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.

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.

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.

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!

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 '*'.

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.