March 19, 2015

PHP class for database operations using mysqli object

.
<?php

  class DataBaseHandler {
 
   private $hostname;
   private $username;
   private $password;
   private $database;
   private $charset;
   private $mysqli;
   private $USE_OF_information_schema_ENABLED = false;
   private $result = null;
  
 
   // = CONSTRUCTOR = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
   public function __construct($database, $username, $password, $host='localhost', $port=3306, $charset="utf8") {
   
    $this->database = $database;
    $this->username = $username;
    $this->password = $password;
    $this->hostname = $host.':'.$port;
    $this->charset = $charset;
   
    $this->connect();
   }
   // = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
   public function execute($sql) {
    if(!($this->result=$this->mysqli->query($this->safeSql($sql)))) {
     $this->error($this->mysqli->errno, $this->mysqli->error."<br>\nSQL = $sql");
    }
    return $this->result;
   }
   // = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
   public function getRecord($sql) {
    $this->execute($sql);
    return $this->result->getNext();
   }
   // = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
   public function getNext() {
    $i = error_reporting(0);
    $a = $this->result->fetch_assoc();
    if(mysqli_connect_error()) $this->error(mysqli_connect_errno(), mysqli_connect_error());
    error_reporting($i);
    return $a;
   }
   // = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
   public function seek($offset) {
    $i = error_reporting(0);
    $this->result->data_seek($offset);
    if(mysqli_connect_error()) $this->error(mysqli_connect_errno(), mysqli_connect_error());
    error_reporting($i);
   }
   // = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
   public function newId() {
    $i = error_reporting(0);
    $insert_id = $this->mysqli->insert_id;
    if(mysqli_connect_error()) $this->error(mysqli_connect_errno(), mysqli_connect_error());
    error_reporting($i);
    return $insert_id;
   }
   // = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
   public function enableInformationSchema() {
    $this->USE_OF_information_schema_ENABLED = true;
   }
   // = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
  
  
  
   // = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
   private function connect() {
  
    $i = error_reporting(0);
    $this->mysqli = new mysqli($this->hostname, $this->username, $this->password, $this->database);
    if(mysqli_connect_error()) $this->error(mysqli_connect_errno(), mysqli_connect_error());
    error_reporting($i);
   
    if(!$this->mysqli->set_charset($this->charset)) {
     $this->error($this->mysqli->errno, $this->mysqli->error);
    }
   
    return true;
   }
   // = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
   private function error($code, $message) {
    die("Error in DataBaseHandler<br>Error code = $code<br>\nMessage = $message");
   }
   // = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
   private function safeSql($sql) {
    $malicious = 0;
  
    if($this->USE_OF_information_schema_ENABLED) {
     // Use of table 'information_schema' is allowed only for OCMS supervisors
     // and only when $sys["USE_OF_information_schema_ENABLED"] is set to TRUE!
    }
    else {
     if(preg_match("/(INFORMATION\_SCHEMA)/i",$sql,$m)) $malicious = 1;
    }
   
    // In 'newsletters' module sometimes somebody puts comments into the css codes.
    // These css codes system save in MySQL tables.
    // Because the /* */ characters may be detected as malicious codes we must eliminate these comments!
    $sql = preg_replace("/\/\*[^\*\n\r]*\*\//", "", $sql);
   
    if(preg_match("/(\/\*[^\*]*\*\/)/i",$sql,$m)) $malicious = 2;
    elseif(preg_match("/([\s]+SLEEP[\s]*\([\s]*[0-9]+[\s]*\))/i",$sql,$m)) $malicious = 3;
    elseif(strpos($sql,";")!==false) {
     // so, SQL has ';' character!
     if(preg_match("/(DROP[\s]+TABLE)/i",$sql,$m)) $malicious = 4;
     elseif(preg_match("/(DROP[\s]+DATABASE)/i",$sql,$m)) $malicious = 5;
     elseif(preg_match("/(SHOW[\s]+TABLES)/i",$sql,$m)) $malicious = 6;
    
     elseif(preg_match("/(ALTER[\s]+TABLE)/i",$sql,$m)) $malicious = 7;
    
     elseif(preg_match("/(RENAME[\s]+TABLE)/i",$sql,$m)) $malicious = 8;
    
     elseif(preg_match("/(DELETE[\s]+FROM)/i",$sql,$m)) $malicious = 9;
     elseif(preg_match("/(DELETE[\s]+LOW_PRIORITY[\s]+FROM)/i",$sql,$m)) $malicious = 10;
     elseif(preg_match("/(DELETE[\s]+QUICK[\s]+FROM)/i",$sql,$m)) $malicious = 11;
     elseif(preg_match("/(DELETE[\s]+IGNORE[\s]+FROM)/i",$sql,$m)) $malicious = 12;
    
     elseif(preg_match("/(SHOW[\s]+VARIABLES)/i",$sql,$m)) $malicious = 13;
     elseif(preg_match("/(SHOW[\s]+GLOBAL[\s]+VARIABLES)/i",$sql,$m)) $malicious = 14;
     elseif(preg_match("/(SHOW[\s]+SESSION[\s]+VARIABLES)/i",$sql,$m)) $malicious = 15;
    
     elseif(preg_match("/(CREATE[\s]+TABLE)/i",$sql,$m)) $malicious = 16;
     elseif(preg_match("/(CREATE[\s]+TEMPORARY[\s]+TABLE)/i",$sql,$m)) $malicious = 17;
    
     elseif(preg_match("/(\;[\s]INSERT[\s]+INTO[\s]+)/i",$sql,$m)) $malicious = 18;
     elseif(preg_match("/(\;[\s]UPDATE[\s]+)/i",$sql,$m)) $malicious = 19;
     elseif(preg_match("/(\;[\s]SELECT[\s]+)/i",$sql,$m)) $malicious = 20;
    
     elseif(preg_match("/(\*\/[\s]INSERT[\s]+INTO[\s]+)/i",$sql,$m)) $malicious = 21;
     elseif(preg_match("/(\*\/[\s]UPDATE[\s]+)/i",$sql,$m)) $malicious = 22;
     elseif(preg_match("/(\*\/[\s]SELECT[\s]+)/i",$sql,$m)) $malicious = 23;
    }
   
    if($malicious>0) {
     $this->error("0", "The progress has stopped by DataBaseHandler Security.</b><br>\n"
      ."Malicious code = $malicious<br>Malicius part = ".$m[1]."<br>\n"
      ."Malicius part (htmlspecialchars) = ".htmlspecialchars($m[1])
     );
    }
    return $sql;
   }
   // = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
  }
?>
Example:
<?php
    require_once("DataBaseHandler.php");
   
    $db = new DataBaseHandler(my_database, my_username, my_password);
    $db->execute("SELECT * FROM my_table");
    while($row=$db->getNext()) {
        echo $row["my_field"]."<br>\n";
    }
?>