The great strength of PHP was that it allowed for a multiparadigm approach to programming. The multiparadigm approach meant that users could mix and match programming styles to their needs.
The vast majority of PHP scripts are extremely simple scripts that can, and should be, handled by short procedures.
The most common PHP script grabs a piece of personalized information from a database and displays it on a web page.
To keep the multiparadigm nature of the language. The database connection must be presented as base level function. The functions must be global throughout the script.
In my last post I encapsulated the PDO object in a function. Having encapsulated the PDO object in a function, I can now extend it with my own functions and objects.
PHP has long had a problem with SQL injection scripts. I designed the base for the program sqlBlocks in the first PHP programs I designed.
This code viewer shows SQLblocks.php used in a program. Here is the file in plain text . NOTE: I just added a function that turns the date retrieved from the web into julian days.
A common PHP scenario is that one takes input through a HTML form and submits it to a PHP program.
The data from the form appears in the PHP $_POST variable (which is an array).
sqlBlock.php takes the data from the POST variable and binds it to a SQL statement.
In the constructor asks for the type of SQL operation: i=INSERT, d=DELETE, u=UPDATE and r=REPLACE and the name of the table affected.
The function defColumn() defines each of the columns in the statement. The first parameter is the name of the column. The second parameter asks if the value is required. IF it is, enter the message to give if the variable is null. The third parameter asks for the data type.
defColumn() binds the variable to the data in $_POST. Sometimes I want to add data from a different sources. The function addColumn() lets me define a variable and add its data in a single stroke.
I can write a program that looks like
$sb = new sqlBlock('u','MyTable');
// call defColumn() for each element in $_POST
$sb->defColumn('col_a','Column A is Required','int');
$sb->defColumn('col_b','Column B is required','str');
$sb->defColumn('col_c',false,'str');
// Add data from a source other than post
$sb->addColumn('update_ts',now(),'int');
$sb->defColumn('where'); // This separates the SET and WHERE section
$sb->defColumn('id','ID is required',int);
$sb->exec('Successfully Updated Table','Failed miserably');
The function getSQL() will produce a SQL statement from the defined columns. The function exec($successMsg,$failMsg) will create a PDD statment, bind the variables and execute the statement. It will use the msg object described earlier to generate a success of fail message.
Defining the columns line by line is tedious. Why not just define all of the columns for the SQL statement in a single block?
The function addBlock() lets me define the columns with a single delimited string of text.
$sb = new sqlBlock('u','MyTable');
$sb->addBlock('col_a,Column A is Required,int
col_b','Column B is required',str
col_c',f,now
where
id,ID is required,int');
$sb->exec('Successfully updated table','Failed miserably');
This is why I called the object SQLblock(). Just three lines of code produces a function that can validate data from complex HTML forms.
I once wrote a version of the object that used the CREATE TABLE call that created the table. I did not put that in production as it encourages sloppy updates to the database.
Anyway, the little block of code can validate data from most HTML forms. It will bind the data to the columns and execute the insert. Assuming that the PDO Statement bind function protects against SQL injection attacks, the function should be safe.
The function reports the messages with the message object described previously.
Object Oriented Programming is supposed to make life easier. The PDO Object by Zend makes life more complex. The little group of sql functions I created lets programmers validate data in just a few lines of code.