new to oop - mysqli class

Coding Critique is the place to post source code for peer review by other members of DevNetwork. Any kind of code can be posted. Code posted does not have to be limited to PHP. All members are invited to contribute constructive criticism with the goal of improving the code. Posted code should include some background information about it and what areas you specifically would like help with.

Popular code excerpts may be moved to "Code Snippets" by the moderators.

Moderator: General Moderators

Post Reply
TheMadProfessor
Forum Newbie
Posts: 2
Joined: Wed Jan 21, 2009 10:34 am

new to oop - mysqli class

Post by TheMadProfessor »

Hi, I’ve just recently started trying to improve my PHP skills and shift over to the OOP paradigm.

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
        }
}
?>
Usage:-

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>
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: new to oop - mysqli class

Post by Christopher »

It certainly looks workable, but pretty non-standard both in naming and implementation. It does a few extra things, but you would probably be better sticking to the PDO interface for example. Or take a look at the Table Data Gateway pattern.
(#10850)
TheMadProfessor
Forum Newbie
Posts: 2
Joined: Wed Jan 21, 2009 10:34 am

Re: new to oop - mysqli class

Post by TheMadProfessor »

arborint wrote:It certainly looks workable, but pretty non-standard both in naming and implementation. It does a few extra things, but you would probably be better sticking to the PDO interface for example. Or take a look at the Table Data Gateway pattern.
Hi, I suspected it would be a non-standard approach, I'm writing it more as an exercise in building a db abstraction class and learning exercise than anything else. Can you highlight an area where the naming and implementation is off the beaten track? what I'm trying to do is not get any bad habits to start with so that would help. PDO I've not yet looked at (there seems to be a plethora!) . I also wanted something for a replication environment in which update/insert queries are sent to the master server (the master is in turn a data mirror from an external db source and consequently is usually heavily loaded) and select queries from one of the slaves hence the setQueryType method. (although really the class ought to switch this as appropriate) in the checkQueryType method)

What started me off down this track was the wish to chuck in a bunch of queries a la mysqli->multi_query but have them prepared so safe from sql injection (or so I believe?!) - have I missed something here?

Patterns are something that I've kind of put on a back burner for now... which is probably not a great move, but I've had a hard enough time getting the "object" approach to begin with, having been self/web taught and procedural all the way until now. I'm still waiting for the eureka moment with patterns (I suspect part and parcel of OOP approach though and have PHP Objects Patterns & practice book on the still to read pile at the moment alas)
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: new to oop - mysqli class

Post by Christopher »

I think your intention to do prepared statements is probably a good one. However I think the code would be a lot cleaner if you were building strings, splitting them, and maintaining parallel arrays of related data. I would encapsulate each one -- maybe even create a standard Model object that had methods for each that you could just pass the data to.
(#10850)
Post Reply