http://www.developer.com/

Back to article

PHP 5 OOP: Protecting Data With Visibility


May 10, 2006

This article continues the discussion of using PHP to create a database abstraction layer by using the OOP features of PHP. In the prior article, PHP 5 OOP: Interfaces Abstract Classes and the Adapter Pattern, I presented an interface for abstracting database access from the actual database. The article also presented code for accessing a database.

In this article you will learn to expand upon the functionality you built from the first article in order to show how to protect your data using visibility modifiers.

Using Accessibility Modifiers

You may have noticed in the previous article the use of the $link variable of the MySqlDB class. This variable is used to store the link resource generated when a connection is made to the MySQL database. You may also have noticed the word private before its declaration in contrast to the PHP 4 method of declaring the variable using var. The word private refers to the visibility (also known as accessibility) of the variable within the class.

Visibility is similar to variable scope, however, offers finer control. There are three types of visibility.

  • Public (default) - the variable can be accessed and changed globally by anything.
  • Protected - the variable can be accessed and changed only by direct descendants (those who inherit it using the extends statement) of the class and class its self
  • Private - the variable can be accessed and changed only from within the class.

Similar to the implementation of interfaces, attempting to violate these rules in your program will generate a fatal error and, again like interfaces, their existence is purely for the convenience of the programmer. This does not mean however they should be ignored. Specifying the visibility of your class member variables enables you to protect data within your objects from outside influence.

The $link variable in the MySqlDB class is declared as private - this means that the variable can only be accessed from within the object using the $this variable. This preventsit from being overwritten accidentally by any other object or function outside the class. We will use the visibility tools to help us create a query object for our database abstraction layer.

You can think of a query as an individual entity with text that is executed and a result acquired after its execution. It would be useful to keep these two attributes of a query in the same place-an object perhaps. Some database systems also have a facility that provides stored procedures. Stored procedures are similar to functions. They are stored queries that can take parameters and can be invoked at anytime. Versions of MySQL before 5.1 and some of database management systems do not however provide this feature.

You will incorporate both of these features into the query object in the examples in this article. The example will emulate a basic stored procedure and store the result pointer internally. The query main part of the query object will be built in the next part of the article to tie it in properly with the database abstraction layer. For now the focus will be on just the query execution from the object, which will simply be calling query() function the MySqlDB object.

Define the following public functions in the query object:

  • __construct() - the constructor will take an argument containing the a reference to an instance of an object which implements the DB interface.
  • prepare() - the prepare function initializes the stored procedure functionality for the query. It may contain one or more numbered place holders which are then passed as arguments to the execute() function. The placeholders are defined using a colon followed by an integer pertaining to the argument number and a letter pertaining to the data type of the argument.

    A simple query containing a place holder may look like this:

    
    SELECT col1,col2 FROM table_name WHERE col1=:1I
    
  • execute() - the execute function executes the query. If it was initialized previously as a stored procedure using the prepare() function, any parameters are passed as arguments to the stored procedure. Otherwise the first argument only is used as the query text. The execute function returns the result of the executed query.
  • compile() - the compile function is similar to the execute function, however, the query is not actually executed. Instead it takes the arguments of the stored procedure and returns the compiled version of the query with all the placeholders substituted and santitized in the query string.

Protected Members

As mentioned above you can use the concept of visibility is to hide the internal workings of an object and protect the integrity of data required for its internal workings. I have already explained that the result pointer returned by the query will be stored as a protected property. Protected members are used because a database specific query object that extends from the query object may need to override the core functionality.

Digging Into the Code

Enough theory, lets get coding. First create a template for the object as shown in listing 1. You will then build from that.

Listing 1: A Template for our Database query class

class DBQuery  
{  
    /**  
     * Holds a reference to an object which implements the DB interface.  
     */  
    protected $db;  


    /**  
     * Set to true if this is a stored procedure.  
     */  
    protected $stored_procedure = false;  


    /**  
     * Holds a query with all the strings removed.  
     */  
    private $query;  


    /**  
     * Used to match quotes in SQL  
     */  
    private static $QUOTE_MATCH = "/(\".*(?db = $db;  
    }  


    public function prepare($query)  
    {  
        $this->stored_procedure = true;  
    }  


    public function compile($args)  
    {  
    }  


    public function execute($query)  
    {  


    }  
} 


The Prepare Function

Using the template in Listing 1, the first thing you will do is to build the prepare() function. To ensure that no characters within quotes are accidentally parsed as placeholders, this method should remove all strings inside the query and store them temporarily in an array. The strings themselves are also replaced with placeholders identified by a sequence of characters that should never appear in an SQL statement. During compilation of the query, the procedure placeholders are first substituted, then the strings of put back into the query. This is accomplished with the help of the preg_replace() function and a helper function used as a callback to the preg_replace() function. Listing 2 contains the new prepare function.

Listing 2: The prepare() function

    /** 
     * Prepares the query as a stored procedure. 
     * @param string $query Prepared query text 
     * @return void 
     */ 
    public function prepare($query) 
    { 
        $this->stored_procedure = true; 
        $this->quote_store = array(); // clear the quote store 
        $this->query = preg_replace(self::$QUOTE_MATCH, '$this->sql_quote_replace("1"?"1":'2')', $query); 
    } 

    private function sql_quote_replace($match) 
    {    
        $number = count($this->query_strings); 
        $this->query_strings[] = $match;         
        return "$||$$number"; 
    } 

Notice the use of the private visibility for the static QUOTE_MATCH property, the quote_store property and the sql_quote_replace() function. You want these to be private rather than protected to ensure that any subclass which overrides the query class prepare() method uses its own mechanisms to exclude quotes.

The Compile Function

The next task is to build the compile() and execute() functions.

The compile() function is shown in Listing 3. It will:

  • Take a variable number of arguments; which match the placeholders in the query.
  • Check that the placeholder is of the correct data type and substitute it for the value in the argument.
  • Return the query as a string but does not execute it.
  • Throw an exception if the query object has not been initialized as a stored procedure using the prepare() function.

Listing 3: The compile() function

/** 
     * Returns the compiled query without executing it. 
     * @param mixed $args,... Query Parameters 
     * @return string Compiled Query 
     */ 
    public function compile($params) 
    { 
        if (! $this->stored_procedure) { 
            throw new Exception("Stored procedure has not been initialized."); 
        } 

        /* substitute parameters */ 
        $params = func_get_args(); // get function arguments 
        $query = preg_replace("/(?compile_callback($params, 1, "2")', $this->query); 

        return $this->add_strings($query); // put the strings back into the query 
    } 
     
    /** 
     * Re-inserts strings removed by the prepare() function. 
     */ 
    private function add_strings($string) 
    { 
        $numbers = array_keys($this->query_strings); 
        $count = count($numbers); 

        $searches = array(); 

        for($x = 0; $x < $count; $x++) { 
            $searches[$x] = "$||${$numbers[$x]}"; 
        } 

        return str_replace($searches, $this->query_strings, $string); 
    } 

    /** 
     * Executed each time a place holder is substituted in the stored procedure. 
     */ 
    protected function compile_callback($params, $index, $type) 
    {         
        --$index; 

        /* throw an exception */ 
        if (! isset($params[$index])) { 
            throw new Exception("Required number of arguments not sent to stored procedure."); 
        } 
         
        /* you may want to add additional types such as dates and times here */ 
        switch ($type) { 
            case 'S': 
                return '"' . $this->db->escape_string($params[$index]) . '"'; 
                break; 
            case 'I': 
                return (int) $params[$index]; 
                break; 
            case 'N': 
                return (float) $params[$index]; 
            default: 
                throw new Exception("Unrecognized data type '$type' specified in stored procedure."); 
        } 
    } 

The compile() function uses two additional functions. The compile_callback() function is used as a callback in the preg_replace() function call. It is executed each time a place holder is found in the query replacing the place holder with the value in passed to the compile function.

The Execute Function

Finally, you need to build the execute() function. The execute() function compiles the query and then executes it using the DB object which was used to initialize the DBQuery object, notice in Listing 4 how the call_user_func_array() function is used to get the compiled query, this is used because the number of arguments passed to the execute function cannot be determined until runtime.

Listing 4: The execute() function


/** 
     * Executes the current Query 
     * 
     * Executes the current query replacing any place holders with the supplied 
     * parameters. 
     * 
     * @param mixed $queryParams,... Query parameter 
     * @return resource A reference to the resource representing the executed query. 
     */ 
    public function execute($queryParams = '') 
    { 
        //example: SELECT * FROM table WHERE name=:1S AND type=:2I AND level=:3N 
        $args = func_get_args(); 

        if ($this->stored_procedure) { 
            /* call the compile function to get the query */ 
            $query = call_user_func_array(array($this, 'compile'), $args); 
        } else { 
            /* a stored procedure was not initialized, so execute this as a standard query */ 
            $query = $queryParams; 
        } 
             
        $this->result = $this->db->query($query); 

        return $this->result; 
    } 

The complete source for the query object may be found as an attachment at the end of this article. Remember that you are reinventing a very simplistic version of the wheel here, as stored procedures and database abstraction has already been addressed by the PearDB PHP extension.

Pulling it all Together - A short Example

To demonstrate how the query object may be used, I have constructed a small example that uses the DBQuery object as a stored procedure in checking for a correct user name and password. This is shown in listing 5.

Listing 5: A short example

require 'mysql_db.php5'; 
    require_once 'query2.php5'; 


    $db = new MySqlDb; 
    $db->connect('host', 'username', 'pass'); 
    $db->query('use content_management_system'); 

    $query = new DBQuery($db); 
     
    $query->prepare('SELECT fname,sname FROM users WHERE username=:1S AND pword=:2S AND expire_time<:3I'); 

    if ($result = $query->execute("visualad", "apron", time())) { 
        if ($db->num_rows($result) == 1) { 
            echo('Correct Credentials'); 
        } else { 
            echo('InCorrect Credentials / Session Expired'); 
        } 
    } else { 
        echo('Error executing query: ' . $db->error()); 
    } 

Conclusion

In this article you have seen how to protect and restrict the visibility of object data using the accessibility modifiers private, protected and public when declaring class variables. These modifiers are used in the MysqlDB and DBQuery classes to protect data important to the internal workings of the objects.

In our next article I'll present another new feature in PHP 5, type hinting. You will also get to extend the DBQuery to allow for support of all the functions in the DB interface that operate on the result of an executed query. This will be accomplished using delegation.

If you have any comments regarding this tutorial please post them here.

Downloads

You can download the query.zip file here.

About the Author

Adam Delves is a university student and web programmer from the UK who is studying computing. He has been a PHP programmer for over 3 years and now runs two small websites and writes articles for PHP builder.com.

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date