The code below is my attempt to create a mysqli abstraction layer in an effort to remove as much code away from the presentation page and also to improve security against sql injection, but, being a complete newb to oop I’m not sure I’m even taking the right approach
I’m looking for criticism of my code, documentation and any other tips anyone can give me to get me on the right track. (or even “why on earth are you trying to do this? There’s a much better way” type comments) – basically, feel free to critique what you like , It’s all a learning experience for me… apologies for this being a long post, but as I want comments regarding my use of documentation I though best to leave it in.
The 1st block of code below is the class itself and the 2nd demonstrates usage. I’m particularly unsure about my use of call_user_func_array … does this negate the advantage of using prepared statements?
Any way here goes:-
The class:-
Code: Select all
<?php
/**
* mysqliDbFunctions
*
* TODO: Document fully
* TODO: test parameters passed to preppedQuery are correct data types and AddMsg if not
* TODO: test for sql injection vulns
*/
class mysqliDbFunctions
{
public $debug="false";
protected $dbserver;
protected $dbuser;
protected $dbpass;
protected $dbname;
protected $mysqliconn;
protected $qtype;
protected $lastnumrows=null;
/**
* mysqliDbFunctions::__construct()
*
* @return
* constructor
*/
function __construct()
{
$this->dbserver='my.se.rv.er';defaults to a slave server rather than master
$this->dbuser='myuser';
$this->dbpass='mypass';
$this->dbname='mydbname';
$this->qtype='read';
}
/**
* mysqliDbFunctions::mysqliDbConnect()
*
* @param mixed $dbserver
* @param mixed $dbuser
* @param mixed $dbpass
* @param mixed $dbname
* @return
* sets up connection to database as specified in parameters - allows read only queries to be directed to a slave server and write queries to a master if in a replication set up
* TODO: switch user method for read/write queries?
*
*/
function mysqliDbConnect($dbserver, $dbuser, $dbpass, $dbname)
{
if ($this->qtype == "write")
{
$this->dbserver='myr.ead.ser.ver';
}else {
$this->dbserver='myw.rit.ese.rver';
}
$this->mysqliconn = new mysqli($this->dbserver, $dbuser, $dbpass, $dbname);
if ($this->debug)
{
if (mysqli_connect_errno())
{
$this->addMsg("mysqliDbConnect","mysqliDbConnect could not connect : MySqli error: ".mysqli_connect_error());
} else
{
$this->addMsg("mysqliDbConnect","mysqliDbConnect connected succesfully!");
}
}
}
/**
* mysqliDbFunctions::setQueryType()
*
* @param mixed $type
* @return
* set query type to "read" or "write" - sets appropriate server for read or write queries i.e slave or master
*
* TODO: add handlers for other query types eg sprocs, multiple queries?
*
*/
function setQueryType($type)
{
switch ($type)
{
case ("write"):
$this->qtype = "write";
break;
case ("read"):
$this->qtype = "read";
break;
default:
if ($this->debug)
{
$this->addMsg("setQueryType","setQueryType : unknown query type:".$qtype);
}
$this->qtype = "read";
}
}
/**
* mysqliDbFunctions::setDbName()
*
* DESCRIPTION: sets the default
*
* @param mixed $dbname
* @return
* sets the database name for any subsequent queries
*/
function setDbName($dbname)
{
$this->dbname = $dbname;
}
/**
* mysqliDbFunctions::debug()
*
* @param mixed $bool (public property)
* @return
*
* turns debugging messages to on or off - set to false once app is fully debugged!
*/
public function debug($bool)
{
if (is_bool($bool))
{
$this->debug = $bool;
return true;
}
else
{
if ($this->debug)
{
$this->addMsg("debug Error","debug: parameter must be present and boolean");
}
$this->addMsg("debug","debug: debug set flase");
echo "debug: debug set false";
return false;
}
}
/**
* mysqliDbFunctions::addMsg()
*
* DESCRIPTION: adds a debugging message - use public method printErrors() from calling page to display all messages
*
* @param mixed $type
* @param mixed $message
* @return
*
*/
protected function addMsg($type, $message)
{
if (!empty($type) && !empty($message))
{
$index = count($this->msg);
$this->msg[$index]['type'] = $type;
$this->msg[$index]['msg'] = $message;
return 1;
}
else
{
if ($this->debug)
{
$this->addMsg("addMsg Error","addMsg: type and message must not be empty");
}
return 0;
}
}
/**
* mysqliDbFunctions::checkParameterType()
*
* DESCRIPTION: not yet developed - function to check parameters passed to preppedQuery are indeed the datatype that is expected in the vartype parameter - log the userip,query & parameters passed if not so can check if injection attempt
*
* @param mixed $param - array of parameters
* @param mixed $type - parameter type string that was passed to multiquery eg ssd for 2 strings and a decimal
* @return - multidim array of parameters with sub array of pass/fails
*
*
*/
protected function checkParameterType($param,$type)
{
if (!$param)
{
if ($this->debug)
{
$this->addMsg("checkParameterType error","no parameter passed");
}
return null;
}
if (!$type)
{
if ($this->debug)
{
$this->addMsg("checkParameterType error","no type passed");
}
return null;
}
}
/**
* mysqliDbFunctions::preppedQuery()
*
* @param mixed $querysql
* @param mixed $params (array)
* @return mixed
*
* DESCRIPTION: Prepares and executes a parameterised mysqli query - returns an array as results NB 1st param passed must be a string specifying the datatype (as below) of each of the subsequent parameters eg if passing two strings and a float 1st parameter should be "ssd".
* BIND TYPE COLUMN TYPE
* i All INT types
* d DOUBLE and FLOAT
* b BLOBs
* s All other types EG STRINGS
*
* Returns an associative array with the results
*
* TODO: test that parameters passed match the datatype string.
*
*
*/
public function preppedQuery($querysql,$params)
{
if (!is_array($params))
{
if ($this->debug)
{
$this->AddMsg("preppedQuery error","params is not an array");
}
return null;
}
$nparams = sizeof($params);
$typestring = $params[0];
// echo "<pre>";
// print_r($params);
// echo "</pre>";
$this->mysqliDbConnect($this->dbserver,$this->dbuser,$this->dbpass,$this->dbname);
if ($stmt = $this->mysqliconn->prepare($querysql))
{
call_user_func_array(array($stmt, 'bind_param'), $params);
$stmt->execute();
$meta = $stmt->result_metadata();
if (substr_count(strtolower($querysql),"select"))
{
while ($field = $meta->fetch_field())
{
$fieldparams[] = &$row[$field->name];
}
}
if (substr_count(strtolower($querysql),"select"))
{
call_user_func_array(array($stmt, 'bind_result'), $fieldparams);
$ctr = 0;
while ($stmt->fetch())
{
foreach($row as $key => $val)
{
$c[$key] = $val;
}
$result[] = $c;
$ctr++;
}
}
$this->lastnumrows = $ctr;
$stmt->close();//substr_count(strtolower($querysql),"select")
if ($this->checkQueryType($querysql) == "select")
{
if (!is_array($result))
{
if ($this->debug)
{
$this->AddMsg("preppedQuery error","result is not an array i.e. likely no rows may be an error");
}
return null;
}
return $result;
}
else{
$this->AddMsg("preppedQuery info",$this->checkQueryType($querysql)." type query");
}
}
else {
$this->addMsg("preppedQuery Error","preppedQuery: problem executing statement: ".$this->mysqliconn->error);
}
}
/**
* mysqliDbFunctions::preppedMultiQuery()
*
* DESCRIPTION: takes arrays of queries, input variables , and variable types and submits them to preppedQuery, returns array of resultsets (which can be fired into simpleRawTableDisplay for simple tabular output)
*
* @param mixed $queries
* @param mixed $vars
* @param mixed $vartypes
* @return
*
*
*/
public function preppedMultiQuery($queries,$vars,$vartypes)
{
if (!is_array($queries))
{
if ($this->debug)
{
$this->addMsg("preppedMultiQuery error:","passed parameter is not an array!");
}
return null;
}
$numqueries = sizeof($queries);
if(strlen($queries[$numqueries-1]) < 1)
{
// delete last query - original querystring has been terminated with a ; causing array to add an extra blank element
array_pop($queries);
$numqueries--;
}
$resultsets = array();
$rowsarray = array();
$qtypesarray = array();
for ($qctr = 0;$qctr < $numqueries;$qctr++)// cycles through each of the queries and builds params to throw at preppedQuery
{
$params = array($vartypes[$qctr]);
if(substr(rtrim($queries[$qctr]),-1,1) !== ";")// checks for semi-colon as rightmost character in query
{
$queries[$qctr] .= ";";
}
foreach($vars[$qctr] as $var)
{
array_push($params,$var);
}
//echo $queries[$qctr]."<br/>";
$result = $this->preppedQuery($queries[$qctr],$params);
array_push($rowsarray,$this->lastnumrows);
array_push($qtypesarray,$this->checkQueryType($queries[$qctr]));
array_push($resultsets,$result);
$resultsets['rows'] = $rowsarray;
$resultsets['qtypes'] = $qtypesarray;
}
return $resultsets;
}
/**
* mysqliDbFunctions::checkQueryType()
*
* DESCRIPTION: returns the type of sql query passed i.e select , update etc.
*
* @param mixed $query
* @return
*/
protected function checkQueryType($query)
{
$query = strtolower($query);
if (strlen($query) == 0)
{
$this->addMsg("checkQueryType:","string of no length passed!<br/>");
return "no query passed";
}
$qtypes = array(
"select",
"insert",
"update",
"delete",
"call",
"alter"
);
foreach($qtypes as $q)
{
$stmtcount = substr_count(strtolower($query),$q);
if ($stmtcount > 0)
{
//echo strtoupper($q)." query found!<br/>";
$qtype = $q;
}
}
return $qtype;
}
/**
* mysqliDbFunctions::doQuery()
*
* DESCRIPTION: performs a mysqli query against the current database and returns an associative array of the results NB - not sql injection safe - any parameters in the sql query passed to this should be escaped in the calling code before passing the query to this function NB the resultarray returned is in actual fact a db resultset object so can be operated on by such things as mysqli->fetch_assoc() - dangerous when utilising user input? so probably really not a good idea to use this method unless query is fixed!
*
* @param mixed $querysql
* @return
*
* TODO: cope with no results to return.
*/
public function doQuery($querysql)
{
$this->mysqliDbConnect($this->dbserver,$this->dbuser,$this->dbpass,$this->dbname);
$resultarray = $this->mysqliconn->query($querysql);
$this->mysqliconn->close();
return $resultarray;
}
/**
* mysqliDbFunctions::simpleRawTableDisplay()
* DESCRIPTION: when passed an associative resultset array returns a center aligned table containing the raw data and headers
*
* @param mixed $inputarray
* @return void
*
* TODO: deal with nulls
*
*/
public function simpleRawTableDisplay($inputarray)
{
echo "<table cellspacing=\"0\" border = \"1\">";
echo "<tr>";
foreach(array_keys($inputarray[0]) as $title)
{
echo "<th><strong>".$title."</strong></th>";
}
echo "</tr>";
$rowctr = 0;
foreach ($inputarray as $row)
{
echo "<tr>";
foreach ($row as $value)
{
if(!$value)
{
echo "<td style=\"text-align:center; color:#C0C0C0\">NULL</td>";
}else{
echo "<td >".$value."</td>";
}
}
echo "</tr>";
$rowctr++;
}
echo "</table>";
}
/**
* mysqliDbFunctions::simpleCheckAndDisplay()
*
* DESCRIPTION: takes an array of resultsets and displays in simple tables (calls simpleRawTableDisplay)
*
* @param mixed $resultsets - PASSED BY REFERENCE SO THAT $resulsets['rows'] can be modified
* @return void
*/
public function simpleCheckAndDisplay(&$resultsets)
{
if (array_key_exists('rows',$resultsets))
{
$rctr = 0;
foreach($resultsets['rows'] as $r)
{
$resultsrows[] = $r;
if ($r>0)
{
unset($resultsets['rows'][$rctr]);
$this->simpleRawTableDisplay($resultsets[$rctr]);
}else
{
if ($this->debug)
{
$this->addMsg("simpleCheckAndDisplay error:","<strong>Query $queries[$rctr] returned no rows!</strong><br/>");// if debug add into addMsg
}
}
echo "<strong>".$r." rows returned</strong><br/>";
$rctr++;
}
}else
{
if ($this->debug)
{
$this->addMsg("simpleCheckAndDisplay error: ","[rows] key doesn't exist! no resultsets returned!<br/>");// if debug add into addMsg
}
}
unset($resultsets['rows']);
}
/**
* mysqliDbFunctions::printErrors()
*
* DESCRIPTION: displays all debugging messages added by addMsg
*
* @return
*/
public function printErrors()
{
if (count($this->msg) > 0)
{
foreach ($this->msg as $msg)
{
echo ("<br/><strong>" . $msg['type'] . "</strong>: " . $msg['msg'] . "<br/>");
}
}
}
/**
* mysqliDbFunctions::__destruct()
*
* @return
*
* destructor
*/
function __destruct()
{
//destroy this object
}
}
?>Code: Select all
<?php
require_once('class.dbmysqli.php');
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
<link rel="stylesheet" href="newstyles.css"/>
<title>MySqli Test page</title>
</head>
<body>
<?php
// world test database from http://dev.mysql.com/doc Copyright Statistics Finland, http://www.stat.fi/worldinfigures
$db = new mysqliDbFunctions();
$db->debug(true);
// test update & insert queries:-
$db->setQueryType("write");
$updatesql = "update City set Population = ? where Name = ?;";
$updatesql .= "insert into City (Name,CountryCode,District,Population) values (?,?,?,?);";
$updatesql .= "update Country set HeadOfState = ? where HeadOfState = ?;";
$updatesql .= "update Country set HeadOfState = ? where HeadOfState = ?;";
$queries = explode(";",$updatesql);
$vars = array(
array(500000,"Leeds"),
array("ShangriLa","XXX","Xanadu",1),
array("Barack Obama","George W. Bush"),
array("Elizabeth II","Elisabeth II")
);
$vartypes = array(
"ds",
"sssd",
"ss",
"ss"
);
$resultsets = $db->preppedMultiQuery($queries,$vars,$vartypes);
unset($updatesql,$queries,$vars,$vartypes);// garbage cleanup
// test some simple select queries:-
$db->setQueryType("read");
$querysql = "SELECT * FROM City where left(Name,1) = ? and LEFT(CountryCode,1) = ?;";
$querysql .= "SELECT Name,Continent,population,GNP FROM Country where Continent = ? and GovernmentForm = ? and Population > ? order by ? desc;";
$querysql .= "SELECT *
FROM CountryLanguage cl
inner join Country c
on cl.CountryCode = c.Code
where `Language` like ? and isOfficial = ?;";
// could stick the queries straight into an array as per below rather than building from concatenated string if wanted
$queries = explode(";",$querysql);
$vars = array(
array("S","A"),
array("Asia","Republic",1000000,"Name"),
array("%Eng%","T")
);
$vartypes = array(
"ss",
"ssds",
"ss"
);
$resultsets = $db->preppedMultiQuery($queries,$vars,$vartypes);
$db->simpleCheckAndDisplay($resultsets);
$db->printErrors();
unset($db,$queries,$querysql,$resultsets,$vars,$vartypes);// garbage cleanup
?>
</body>
</html>