SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    In a house in the USA
    Posts
    293
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL wrapper class question

    So I have been working on a way to create a MySQL wrapper class so that if I ever decide to switch to some other DB than I do not have to switch out all of my functions again. Plus I am looking for a really easy way to manage SQL insert and update queries. I am mainly looking for comments on if this approach is good and if any code changes should be made. My code is below:

    PHP Code:
    <?php
    class mysqlWrapper {
            
        private 
    $_link// Link constructor
        
    private $_db;
        public 
    $_QueryCount = array();
        public 
    $_LastQuery// Store the last query ran in memory
        
    public $_AllQueries = array(); // An array of all queries ran
        
    public $_RowCount// Store the last count of records
        
    private $_Result;
        public 
    $_Errors = array();
        public 
    $_InsertID// Store last mysql_insert_id value
        
    private $_MagicQuotes// Check if magic quotes GPC is enabled or disabled
        
    public $_InsertNulls false// Insert NULL instead of blank value when value is empty
        
    public $_StripTags true// Default stip out all html tags
      
    public $_AllowedTags// All allowed html tags Example = <a><br><p>
      
    public $changedValues;
      private 
    $_Debug true;
      
      
          
        public function 
    Debug($OutputType 'html'$FileName '') {
         
    ob_start();
       
    var_dump($this);
       
    $dump ob_get_contents();
       
    ob_end_clean();
         switch(
    $OutputType) {
           case 
    'string':
             return 
    $dump;
             break;
           case 
    'file':
             
    //if(is_writable($FileName)) {
               
    $fp fopen($FileName'a');
               
    $ToWrite "\n-----------------------------\n".$dump;
               
    $handle fwrite($fp$ToWrite);
             
    fclose($fp);
            
    //}
            
    break;
        case 
    'html':
        default:
          echo 
    '<pre>';
          echo 
    htmlspecialchars($dump);
          echo 
    '</pre>';
          break;
        }
      }
      
        function 
    __construct($server ''$username ''$password ''$database '') {
            if(empty(
    $server) || empty($username) || empty($password) || empty($database)) {
                die(
    'Could not connect to server');
            }
            
            
    $this->_AllowedTags '<br><p><hr>'// Set the default values for allowed tags
            
    $this->_MagicQuotes = (get_magic_quotes_gpc() ? true false);
            
            
    $this->_link = @mysql_connect($server$username$password);
            if(
    mysql_error()) {
                die(
    'Could not connect to server');
            }
            unset(
    $password); // Remove password for security reasons
            
    $this->_db mysql_select_db($database$this->_link);
            if(
    mysql_error()) {
                die(
    'Could not select database');
            }
            
    $this->_QueryCount['Total'] = 0;
            
    $this->_QueryCount['Error'] = 0;
        }
            
        public function    
    Query($queryString$queryCount 0) {
            if(empty(
    $queryString)) {
                
    $this->_Errors[] = 'Query string was empty';
                return;
            }
            if(isset(
    $this->_Result[$queryCount])) {
              
    $this->_Result[$queryCount] = '';
            }
            
            
    $this->_QueryCount['Total']    += 1;
            
    $this->_Result[$queryCount] = @mysql_query($queryString$this->_link);
            
    //$this->_RowCount = @mysql_num_rows($this->_Result);
            
    $this->_LastQuery $queryString;
            
    $this->_AllQueries[] = $queryString;
            
            if(
    mysql_error()) {
                
    $this->_Errors[] = array(mysql_error(), $queryString);
                
    $this->_QueryCount['Error'] += 1;
                return 
    false;
            }        
            return 
    true;
        }
        
        public function 
    RowCount($queryCount 0) {
            return @
    mysql_num_rows($this->_Result[$queryCount]);
        }
        
        public function 
    GetObject($queryCount 0) {
            return 
    mysql_fetch_object($this->_Result[$queryCount]);
        }
        
        private function 
    BuildFields($values) {
            return 
    '(`'.join('`,`'$values).'`)';
        }
        
        private function 
    BuildValues($OriginalValues) {
            foreach(
    $OriginalValues as $key => $value) {
                  
    /*if(is_numeric($value)) {
                    $values[] = $value;
                  } else*/
              
    if($value=='NULL') {
                    
    $values[] = 'NULL';
                  } elseif(empty(
    $value) && $this->_InsertNulls==true) {
                      
    $values[] = 'NULL';
                  } else {
                    if(
    $this->_StripTags==true) {
                          
    $value strip_tags($value$this->_AllowedTags);
                    }
                    
    $values[] = '\''.$this->EscapeValue(trim($value)).'\'';
                  } 
            }
            return 
    '('.join(','$values).')';
        }
        
        
    // We can use use the InsertID function or _InsertID variable
        
    public function InsertID() {
            if(
    mysql_insert_id($this->_link)) {
                
    $this->_InsertID mysql_insert_id($this->_link);
                return 
    $this->_InsertID;
            }
            
    $this->_InsertID '';
            return 
    false;
        }
        
        public function 
    InsertQuery($table$values) {
            
    $s 'INSERT INTO '.$table.' '.$this->BuildFields(array_keys($values)).' VALUES '.$this->BuildValues($values);
              
    $this->Query($s);
              
    $this->InsertID();
              return 
    mysql_affected_rows($this->_link);
        }
          
        public function 
    UpdateQuery($table$values$where$RecordChanges false) {
            
    $sql 'UPDATE `'.$table.'` SET ';
            foreach(
    $values as $key=>$value) { 
                  
    /*if(is_numeric($value)) {
                    $s[] = '`'.$key.'` = '.$value;
                  } else*/
              
    if($value=='NULL') {
                    
    $s[] = '`'.$key.'` = NULL';
                } elseif(empty(
    $value) && $this->_InsertNulls==true) {
                      
    $s[] = '`'.$key.'` = NULL';
                  } else {
                    if(
    $this->_StripTags==true) {
                      
    $value strip_tags($value$this->_AllowedTags);
                    }
                    
    $s[] = '`'.$key.'` = \''.$this->EscapeValue(trim($value)).'\'';
                }
            }
            
            
    $sql .= join(', '$s);
            if(
    $RecordChanges == true) {
              
    $this->GetPreviousValues($table$values$where);
              if(
    $this->_Debug == true) {
               echo 
    '<pre>';
               
    print_r($this->changedValues);
               echo 
    '</pre>';
              }
          }
            
    $this->Query($sql.(isset($where) ? ' WHERE '.$where ''));
            return 
    mysql_affected_rows($this->_link);
        }  
        
        public function 
    EscapeValue($value) {
            if(
    $this->_MagicQuotes) {
                
    $value stripslashes($value);
            }
            
            if(
    function_exists('mysql_real_escape_string')) { 
                return 
    mysql_real_escape_string($value);
            } else {
                return 
    mysql_escape_string($value);
            }
        }
        
      public function 
    CheckError() {
        return 
    mysql_error();
      }
      
      
    // If we are recording changes to a database use the following function
      // This is automatically called if last value of insert or update query is set to true
      
    private function GetPreviousValues($table$values$where) {
        
        if(
    count($this->changedValues) > 0) { // Reset all changed values in case checking multiple items
          
    $this->changedValues '';
        }
        
        
    $WhereParts explode('='$where);
        if(!empty(
    $_POST['Comments'])) {
          
    $values2 = array('LogID'=>'NULL',
                           
    'Timestamp'=>time(),
                           
    'UpdatedBy'=>$_SESSION['_EmployeeID'],
                           
    'UpdateID'=>trim($WhereParts[1]),
                           
    'UpdateTable'=>$table,
                           
    'UpdateField'=>'Comments',
                           
    'OldValue'=>'',
                           
    'NewValue'=>'',
                           
    'Comments'=>$_POST['Comments']);
          
    $this->InsertQuery('log'$values2); 
          if(
    $this->CheckError()) {
            echo 
    'Error:'.mysql_error().'<p />Last Query: '.$this->_LastQuery;
            die;
          }
        }
              
        
    $this->Query('SELECT * FROM '.$table.' WHERE '.$where);
        if(
    $this->CheckError()) {
          return 
    false;
        }
        if(
    $this->RowCount()) {
          
    $info $this->GetObject();
          foreach(
    $values as $key=>$value) {
            if(empty(
    $value) && $this->_InsertNulls==true) {
                  
    $value 'NULL';
              } else {
                   if(
    $this->_StripTags==true) {
                         
    $value strip_tags($value$this->_AllowedTags);
                   }
                   
    $value $this->EscapeValue(trim($value));
              }
              
              
            if(
    $info->$key != $value) {
              
    $this->changedValues[$key] = array('old'=>$info->$key'new'=>$value);
              
              
    $values2 '';
              
    $values2 = array('LogID'=>'NULL',
                           
    'Timestamp'=>time(),
                           
    'UpdatedBy'=>$_SESSION['_EmployeeID'],
                           
    'UpdateID'=>trim($WhereParts[1]),
                           
    'UpdateTable'=>$table,
                           
    'UpdateField'=>$key,
                           
    'OldValue'=>$info->$key,
                           
    'NewValue'=>$values[$key],
                           
    'UserID'=>'NULL',
                           
    'RoomID'=>'NULL',
                           
    'LocationID'=>'NULL',
                           
    'Comments'=>'');
              
    $this->InsertQuery('log'$values2); 
              if(
    $this->CheckError()) {
                echo 
    'Error:'.mysql_error().'<p />Last Query: '.$this->_LastQuery;
                die;
              }
            }
          }
        }
      }      
        
    }
    ?>
    The way it is used:
    PHP Code:
    $values = array('LaptopID'=>'NULL',
                        
    'LaptopName'=>$_POST['name'],
                        
    'servicetag'=>strtoupper($_POST['servTag']),
                        
    'expresstag'=>$_POST['expressService'],
                        
    'model'=>$_POST['model'],
                        
    'serial'=>$_POST['serial'],                    'productKey'=>$this->parse->parseProductKey($_POST['productKey']),
                        
    'os'=>$_POST['os'],
                        
    'dead'=>(isset($_POST['dead']) ? $_POST['dead'] : ''));
        
    // Below, laptop is the table name and the $values are the database field names as the key, then my form value as the value of the keys
        
    $db->InsertQuery('laptop'$values); 
    Is this a good way to handle database calls? Any feedback would be appreciated.

    *I also have a table in the DB that I record all field changes and insert a new record whenever a value is updated. This option can be enabled/disabled for any query.
    Daniel
    http://www.wlscripting.com - PHP Tutorials and code snippets
    Notepad++ Function List plugin tip - for PHP developers

  2. #2
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This will work, at least in MySQL, but you have a problem with your BuildValues function - it's going to quote numerical data. While this works just fine in MySQL, other databases may not be as forgiving as technically it violates the SQL standard.

    It's not sufficient to simply add a is_numeric check on the data. Trust me, I wish it was that simple (this has been the big hurdle I've yet to satisfactorily overcome in my own database abstraction class). The problem is that the data's type is dependent upon the column, not the data itself. For example, if your productKey column is e.g. an INTEGER, then you should not be quoting the data sent to it, but you should be forcing numerical data (i.e. you should not be able to send 'foobar' to an INTEGER column); however, if productKey is a VARCHAR column (a common case if you use e.g. a hash as a product key), then even if the data happens to be '123456' you should be quoting it, but unfortunately a is_numeric check on the data will tell you it is numerical when in fact it is string data.

    The best way around this would be to use prepared statements, as then the database itself takes care of appropriate quoting for you, but unfortunately not all databases support prepared statements.

    Incidentally, as I'm using PHP5, I've abandoned my own database abstraction class in favor of using PDO. If this is an option for you, I highly recommend it.
    PHP questions? RTFM
    MySQL questions? RTFM

  3. #3
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    Yes it is good to use abstraction to handle databases, helps to migrate to different servers.

    To me your class is over complicated and trying to do too many things. How I would do it (work in progress)

    I would move a lot of the other features (recording, building SQL statements) to there own class. Because those functions don't match up with this class's purpose.

    PHP Code:
    <?php

    class DB_MySql {

        const 
    FETCH_ARRAY  1;
        const 
    FETCH_ASSOC  2;
        const 
    FETCH_OBJECT 3;

        static private 
    $_con;
        static private 
    $_res;

        
    //--------------------------------------------------------------------------

        
    function __construct ($host$username$password$database) {

            
    self::$_con mysql_connect($host$username$password);

            if (
    self::$_con === false) {
                throw new 
    Exception('Failed to connect to the selected server. (' mysql_error() . ')'0);
                return 
    false;
            }

            if (
    mysql_select_db($databaseself::$_con) === false) {
                throw new 
    Exception('Unable to select desired database. (' mysql_error() . ')'0);
                return 
    false;
            }

            
    # Note: unsetting the password in this function provides no security reasons.
            # It is not needed once the password as been passed to the function it is
            # only viewable by the function. (Requires memory hacking.) The weak point
            # is the function declearation itself.
            # unset($password); // Remove password for security reasons

        
    }

        
    //--------------------------------------------------------------------------

        
    public function query ($query) {

            
    $query trim($query);

            if (empty(
    $query)) {
                throw new 
    Exception('A valid SQL query must be supplied');
                return 
    false;
            }

            
    self::$_res mysql_query($queryself::$_con);

            if (
    self::$_res === false) {
                throw new 
    Exception('The SQL query to the database failed. (Response: ' mysql_error() . ')');
                return 
    false;
            }

            return 
    true;

        }

        
    //--------------------------------------------------------------------------

        
    public function fetch ($type self::FETCH_ARRAY) {

            switch (
    $type) {

                case 
    self::FETCH_ARRAY:
                    
    $t 'Array';
                    break;

                case 
    self::FETCH_ASSOC:
                    
    $t 'Assoc';
                    break;

                case 
    self::FETCH_OBJECT:
                    
    $t 'Object';
                    break;

            }

            return 
    $this->{'_fetch' $t}();

        }

        public function 
    fetchAll () {

            
    $ret = array();

            while (
    $row $this->_fetchArray()) {
                
    $ret[] = $row;
            }

            return 
    $ret;

        }

        public function 
    fetchCount () {

            return 
    mysql_num_rows(self::$_res);

        }

        
    //--------------------------------------------------------------------------

        
    private function _fetchArray () {

            return 
    mysql_fetch_array(self::$_res);

        }

        private function 
    _fetchAssoc () {

            return 
    mysql_fetch_assoc(self::$_res);

        }

        private function 
    _fetchObject () {

            return 
    mysql_fetch_object(self::$_res);

        }

        
    //--------------------------------------------------------------------------

        
    public function escape ($value) {

            if (
    get_magic_quotes_gpc()) {
                
    $value stripslashes($value);
            }

            return (
    function_exists('mysql_real_escape_string')) ? mysql_real_escape_string($value) : mysql_escape_string($value);

        }

        
    //--------------------------------------------------------------------------

        # For recording number of queries and last query what have you. I would use
        # a seperate class for that and leave this one to barebones. I would also
        # leave of the SQL constructing to a sub class make this one its parent. That
        # way this class is much more easily managed and moved to different databases.

    }
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  4. #4
    SitePoint Wizard Hammer65's Avatar
    Join Date
    Nov 2004
    Location
    Lincoln Nebraska
    Posts
    1,161
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would agree with the advice above. Some things to add.

    In my database layer, I have a basic abstraction class that supports MySQL, Postgres and SQL Server. There are plenty of others, but as this is only for own use, I cover the products I most commonly use. The base class does nothing more than route the same function calls (ie. fetch_assoc) to the appropriate function for the database product I have choosen to use. That's the "abstraction" part.

    This is then extended by a "dataAccess" class that provides more specialized functions using the base functions where it can. Like you I have an insert and update function a several other "macro" like functions, including one for pagination. This class can be used to access the database or be extended by classes that take on the identity of particular tables in a specific project. All the logic to deal with the data in that table is contained in that class, which has the full capabilities of the entire database code system.

    I also have an alternate check that can be run to determine if the names of fields that are being placed in the query, are actually in that database table. This precludes the need to check if data from a form has had additional values added to it that are not in the table. They are either silently filtered out or the submission can be rejected, whichever I choose.

    Good to see a good escaping function in your code.

    You can do prepared statements in PHP code. Prepared statements basically use SQL templates in much the same way that simple HTML template systems work.

    You have a string that is a template of a SQL query, with placeholders for the values you want to insert. You input those values, escape them if necessary, and then use str_replace, preg_replace or a similar method to replace the placeholders with the data.

    The function mysql_real_escape_string is actually contained in the MySQL client libraries, rather than being in the PHP extension code. When you do a prepared statement with newer versions of MySQL and the MySQL PHP extension, it is actually using that function behind the scenes to escape data values when needed.

    There are functions available, when using MySQL and Postgres in PHP to get the type of a field. You could conceivably use that to intellegently control if you quote a value or not, otherwise put the quotes in the proper place in your SQL template.

  5. #5
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not to be too pedantic, but if you're using prepared statements correctly there is no need to use str_replace or preg_replace at all:
    Doing it via MySQL commands (you could use PHP's mysql_* functions to issue each query in sequence):
    Code:
    mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
    mysql> SET @a = 3;
    mysql> SET @b = 4;
    mysql> EXECUTE stmt1 USING @a, @b;
    +------------+
    | hypotenuse |
    +------------+
    |          5 |
    +------------+
    mysql> DEALLOCATE PREPARE stmt1;
    Source: MySQL manual

    Using PHP's mysqli_* functions:
    (Long example, so I'll just provide the link instead of copy/pasting here)
    http://www.php.net/manual/en/functio...li-prepare.php

    Using PDO:
    PHP Code:
    <?php
    $stmt 
    $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
    $stmt->bindParam(':name'$name);
    $stmt->bindParam(':value'$value);

    // insert one row
    $name 'one';
    $value 1;
    $stmt->execute();

    // insert another row with different values
    $name 'two';
    $value 2;
    $stmt->execute();
    ?>
    Only if you are emulating prepared statements in your abstraction layer (completely unnecessary with MySQL, MS SQL Server, and I believe PostgreSQL as well, as they support prepared statements) would you ever need to use the procedure outlined by Hammer65 above.

    It's probably worth noting the PDO will emulate prepared statements for database drivers that do not support them (most likely using methods similar to what Hammer65 suggested), but when the underlying support is there then PDO will use that instead.
    PHP questions? RTFM
    MySQL questions? RTFM

  6. #6
    SitePoint Wizard Hammer65's Avatar
    Join Date
    Nov 2004
    Location
    Lincoln Nebraska
    Posts
    1,161
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are correct but many of the servers I work on don't have the mysqli extension only the older extension, even if they have MySQL 5. I know it doesn't make sense, however my abstraction layer has to compensate for that.

    The mssql extension doesn't have that feature in it. regardless if the version of MS SQL server being accessed supports it.

  7. #7
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To be perfectly honest, in that case I would then implement your abstraction of prepared statements using the series of queries I showed in my first post. Although on the other side of honesty, this would probably be more work than it's worth as it would result in essentially 3 levels of abstraction of this one feature (native to the PHP extension e.g. mysqli, native to the database but unsupported by the PHP extension e.g. mssql, and unsupported in the database). So I guess I'm not really sure how I'd go about it in your case; maybe your solution is best after all.
    PHP questions? RTFM
    MySQL questions? RTFM

  8. #8
    SitePoint Wizard Hammer65's Avatar
    Join Date
    Nov 2004
    Location
    Lincoln Nebraska
    Posts
    1,161
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To tell you the truth, I don't use that feature very often, I use the "macro" functions much more. The problem is, the company I work for has a server with a few clients on it, but for the most part, I am jumping from server to server, ISP to Webhost, VPS to shared hosting, so I can't count on certain things to be there. I've learned to build it in just in case.

  9. #9
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    In a house in the USA
    Posts
    293
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for all of your replies. That PDO class looks rather interesting. Unfortunately I do not think that I will be able to use it.

    Would doing a query on the table prior to creating the sql statement to gather field types, then build the query depending on the field types be a viable solution to quoting? Granted that would introduce yet another query into the picture but the quotes would be correct.

    I will see if I can come up with a way to separate the class into more specific classes instead of one "global do all" class.
    Daniel
    http://www.wlscripting.com - PHP Tutorials and code snippets
    Notepad++ Function List plugin tip - for PHP developers

  10. #10
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You should be able to query the table structure and parse it (you really only need to know which fields are numeric - all others should be quoted) in under 0.002 seconds. So that shouldn't add too much overhead to your script. Of course, you'd have to do that for each table, and then I recommend caching the results in memory (to accelerate multiple updates to the same table). The trick would be writing the actual code to do this, although that shouldn't be that big a deal either.

    PHP Code:
    function findNumericFields($tablename)
    {
        
    $query "SHOW CREATE TABLE $tablename";
        
    $res mysql_query($query);
        list(
    $table) = mysql_fetch_row($query);
        
    //begin pseudo code
        
    for each column in $table
            extract field type
            
    if field is numeric don't quote
            else quote
        //end pseudo code

    That pseudo code shouldn't be too difficult to write, really. I believe the column name is always first and the type always second, and there is a finite list of valid types. The column name should never contain spaces, although MySQL will allow such if the name is wrapped in backticks (`). In fact, it appears that MySQL will always use backticks around table and column names in the results of a SHOW CREATE TABLE, however other databases will not (and do not allow the backtick syntax at all - it's a MySQLism); this will likely be the biggest hitch to overcome if you want a database-agnostic abstraction class, but a clever regular expression could solve your problem. I recommend if you want to go this route to do what you can turning the above pseudo code into real PHP code, then come back and ask questions if you get stuck later.

    @Hammer65
    The more I think about your situation, the more I'm convinced that you've implemented the best solution. The only other way would be the 3-level abstraction of prepared statements I mentioned earlier, and that really just seems like it would be far more work than it is worth.
    PHP questions? RTFM
    MySQL questions? RTFM

  11. #11
    SitePoint Wizard Hammer65's Avatar
    Join Date
    Nov 2004
    Location
    Lincoln Nebraska
    Posts
    1,161
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It occured to me, that if you added a setType() method to your database class, that handled those cases where you did have a varchar or text field but just happened to have a numeric input, that you could specify that it be quoted for just that field. All other fields would use is_numeric except for those that you explicitly specify as a particular type. This would likely happen infrequently enough, that you wouldn't be doing that many calls to setType() in order to set the class instance up for use. In some situations you wouldn't be using it at all, especially with proper validation.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •