.
<?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:
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;
}
// = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
}
?>
<?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";
}
?>
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";
}
?>