Syrius Watch

PHP class for database operations using mysqli object

2015-03-19T09:30:00.001+01:00

<?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";
   }
?>

stopPropagation vs. cancelBubble

2012-06-11T14:16:00.001+02:00

There are many occasions when you have to override the default action of an event. The browser has to go through every single ancestor element of the event target to see if it has an event handler.

For this reason we very often must stop the event propagation. Here is my function that I use to stop event propagation in every kind of web browser.

Google Maps v.3 - routeplanner bug

2012-05-28T10:31:00.003+02:00

I noticed a little bug in class google.maps.DirectionsService.
In Google Maps Javascript API V3 Reference we can read this about destination (and about origin) property:

"Location of destination. This can be specified as either a string to be geocoded or a LatLng. Required."

But be careful! If you give coordinates in string the finally route will contains some differences from expected route. My suggestion is next to avoid this problem:

aMatches = sDestination.match(/[a-z]*/i);
if(!aMatches.length || (aMatches.length==1 && aMatches[0]=="")) { // no letters in sDestination => this contains only coordinates!
var a = sDestination.split(",");
var oDestination = new google.maps.LatLng(1.0*a[0], 1.0*a[1]);
}

var request = {
...
destination: oDestination ? oDestination : sDestination,
...
};

Szívdobbanás... A kardiológia múltja és jelene

2012-05-25T15:20:00.001+02:00

Budapesten, a Semmelweis Orvostörténeti Múzeumban nemrég (május 14-én) nyílt meg ez a kiállítás, amely ez év október 31-ig várja a látogatókat.

A kiállítás nem titkolt szándéka az is, hogy még több emberben tudatosuljon: a technika és a tudomány fejlődésének nagyon sokat köszönhetünk, de mindez nem elég, ha mi magunk nem érzünk felelősséget saját egészségünk iránt. A tárlat felhívja a figyelmünket a szív- és érrendszeri betegségek minden aspektusára.

How can I add event handler to a html object?

2012-05-23T10:09:00.002+02:00

Each HTML object has many event handler (onclick, onkeyup, onfocus, etc.) Sometimes we need to execute own routines when an event occurs. How can we assign our function to the event handler?

1. The simplest case is that when we can directly assign our function to the handler of an element:

2. But when our function is bigger and moreover, we must assign it to more event hadlers the solution above is not effective. Then we must define our function normally and assign its pointer to the event hadlers.
In this case I use MyAddEvent() function defined below. With this function I can attach my special event handler (attribute: pFunctionPointer) to any DHTML element (attribute: oElement).

How can I get the client's browser height?

2012-05-22T09:11:00.001+02:00

If you are a javascript programmer surely you've met the problem about calculating client's browser height (clientHeight) in different browser types.

1. What tells you the HTML specification (DHTML Object Model, HTML DOM)? The solution seems to be simple:

2. But sometimes this solution can give you inappropriate result. The best way (what I use always) is the next:

3. Using jQuery height() style property (or method):

How can I detect client's browser type with JavaScript?

2012-05-21T14:57:00.003+02:00

Many times for JavaScript developers is necessary to detect the type and the version of the client's (in other words: user's or visitor's) browser.

1. Usually it's enough to detect is the browser MS IExplorer or not. In this case, you can use the next code:

2. If you like to use the most simplest way try this code (The Navigator object contains all information about the visitor's browser):

3. If you want to get more detailed information you can use the code below:

In code above the name of the browser is defined with variable sBrowserName. The browser's version number is defined with variable sBrowserVersionMajor.

4. And the end let me to introduce how to detect client's browser type and version with jQuery. For this action you must use the Global jQuery Object jQuery.browser.
I put here a little sample but - attention - this code is not at all reliable!

Take care to notice that last code detects that your browser is "mozilla" - even if your browser is Firefox in fact! And it detects "safari" even if your browser is Google Chrome. :)

I like to use solution number 3 (above) because all things are "in my hand".

Javascript programing

2012-05-17T20:35:00.001+02:00

JavaScript is an ECMAScript language standard. It is primarily used in the web pages in order to provide enhanced user interfaces and dynamic websites. Does not require a compiler for client-side implementations. JavaScript uses syntax influenced by that of C. JavaScript copies many names and naming conventions from Java.

Today the JavaScript is the most popular scripting language on the internet, and works in all major browsers, such as Internet Explorer, Firefox, Chrome, Opera, and Safari. It is a scripting language with a very simple syntax! Almost anyone can put small "snippets" of code into their HTML pages. I can recommended for beginners to visit next site:

w3schools.com - JavaScript How To

Welcome to Syrius Watch

2012-05-17T09:35:00.010+02:00

15 years in business and here is my first post. I'm Syrius, programmer, designer and... (to some degree) philosopher. Thank you so much for your time and the privilege of having a meeting with you at my blog.

What about the blog? Let me see... I will try to describe my trip across the cyberspace best I can. And I also want to give you all my experience that I got in past 15 years:

about HTML coding
about Javascript programing
about PHP
about using MySQL directives
about Google MAP
and about website optimization - or in other words, about Search engine optimization (SEO)