Location: PHPKode > scripts > Secured mySQL Layer > mysql.class.php
<?php
/////////////////////////////////////////////////////////////////
/////////////////// Garry`s MySQL Class /////////////////////////
/////////////////////////////////////////////////////////////////
//////////////////      14/12/07        /////////////////////////
// THIS CLASS WRITTEN BY GARRY LACHMAN -> hide@address.com    //
/////////////////////////////////////////////////////////////////

require_once('./Settings.class.php');
class MySQL {
    // MySQL VARS
    var $mysql_link;
    var $mysql_host;
    var $mysql_username;
    var $mysql_password;
    var $mysql_database;    
    var $mysql_link_status = false;
    var $mysql_prefix;
    
    // VARS FOR LAST OPERATIONS
    var $query_result = Array();
    var $records_number;
    
    // CLASS VARS
    var $script_log;
    var $Settings;
    var $inited = false;
        
    function MySQL() {
        // LOADING SINGLETON INSTANCE OF SETTINGS
        $this->Settings =& Settings::getInstance();
        
        // LOADING SETTINGS FROM SETTINGS CLASS
        $this->mysql_host = $this->Settings->getMySQLSettings('hostname');
        $this->mysql_username = $this->Settings->getMySQLSettings('username');
        $this->mysql_password = $this->Settings->getMySQLSettings('password');
        $this->mysql_database = $this->Settings->getMySQLSettings('database');
        $this->mysql_prefix = $this->Settings->getMySQLSettings('prefix');
        
            
        $this->script_log .= "Constractor set settings\n";
        $this->inited = true;
        $this->script_log .= "Inited set to: " . $this->inited . "\n";
    }
    
    // PUBLIC FUNCTIONS
    
    function CustomQuerty($query)
    {
        $this->CheckConnection();
        
        $this->script_log .= "Query: " . $query . "\n";
        
        $result = mysql_query($query) or die($this->mysql_error_report());
        
        $this->script_log .= "Query executed\n";
        
        return $result;
    }
    
    function InsertQuerymassive($table, $insert_fields, $insert_values) {
        $this->CheckConnection();
        
        // BUILD SECURED QUERY - FOR SQL INJECTIONS PROTECTION
        $query = sprintf("INSERT INTO `%s` (", mysql_escape_string($table));
        
        $i=0;
        
        foreach($insert_fields as $arr) {
            if ($i > 0) {
                $query .= sprintf(", `%s`", mysql_escape_string($insert_fields[$i]));
            } else {
                $query .= sprintf("`%s`", mysql_escape_string($insert_fields[$i]));
            }
            $i++;
        }
        
        
        
        $i=0;
        foreach($insert_values as $arr) {
            $j=0;
            if ($i == 0)
            {
                $query .= ") VALUES ("; 
            }
            else
            {
                $query .= ", (";            
            }
            foreach($arr as $arr2)
            {
                if ($j > 0) {
                    $query .= sprintf(", '%s'", mysql_escape_string($arr2));
                } else {
                    $query .= sprintf("'%s'", mysql_escape_string($arr2));
                }
                $i++;
                $j++;
            }
            $query .= ")";
        }
    
        $this->script_log .= "Query: " . $query . "\n";
        
        $result = mysql_query($query) or die($this->mysql_error_report());
        
        $this->script_log .= "Query executed\n";
        
        return mysql_insert_id();
        
    }
    
    
    function InsertQuery($table, $insert_fields, $insert_values) {
        $this->CheckConnection();
        mysql_query("set character set 'utf8'");
        // BUILD SECURED QUERY - FOR SQL INJECTIONS PROTECTION
        $query = sprintf("INSERT INTO `%s` (", mysql_escape_string($table));
        
        $i=0;
        
        foreach($insert_fields as $arr) {
            if ($i > 0) {
                $query .= sprintf(", `%s`", mysql_escape_string($insert_fields[$i]));
            } else {
                $query .= sprintf("`%s`", mysql_escape_string($insert_fields[$i]));
            }
            $i++;
        }
        $query .= ") VALUES (";
        
        
        $i=0;
        foreach($insert_values as $arr) {
            if ($i > 0) {
                $query .= sprintf(", '%s'", mysql_escape_string($insert_values[$i]));
            } else {
                $query .= sprintf("'%s'", mysql_escape_string($insert_values[$i]));
            }
            $i++;
        }
        $query .= ")";
    
    
        $this->script_log .= "Query: " . $query . "\n";
        
        $result = mysql_query($query) or die($this->mysql_error_report());
        
        $this->script_log .= "Query executed\n";
        
        return mysql_insert_id();
        
    }
    
    function DeleteQuery($table, $where_field, $where_value, $opr=NULL)
    {
        $this->CheckConnection();
        
        $query = sprintf("DELETE FROM %s", mysql_escape_string($table));
                
        if (isset($where_field) && isset($where_value)) {
            $query .= " WHERE ";
            $i=0;
            foreach($where_field as $arr) {
                if ($i>0) {
                    $query .= sprintf(" %s `%s`='%s'",mysql_escape_string($opr[$i-1]),
                                                mysql_escape_string($where_field[$i]),
                                                mysql_escape_string($where_value[$i]));
                } else {
                    $query .= sprintf("`%s`='%s'", mysql_escape_string($where_field[$i]),
                                                mysql_escape_string($where_value[$i]));
                }
                $i++;
            }
        }
        
        $this->script_log .= "Query: " . $query . "\n";
        
        
        $result = mysql_query($query) or die($this->mysql_error_report());
        $this->script_log .= "Query executed\n";
    }
        
    function UpdateQuery($table, $update_fields, $update_values, 
                            $where_field=NULL, $where_value=NULL, $opr=NULL) {
        $this->CheckConnection();
        
        // BUILD SECURED QUERY - FOR SQL INJECTIONS PROTECTION
        $query = sprintf("UPDATE `%s` SET ", mysql_escape_string($table));
        
        $i=0;
        foreach($update_fields as $arr) {
            /*if ($i > 0) {
                $query .= sprintf(", %s `%s`='%s'", mysql_escape_string($opr[$i-1]),
                                                    mysql_escape_string($update_fields[$i]),
                                                    mysql_escape_string($update_values[$i]));               
            } */
            if ($i > 0) {
                $query .= sprintf(", `%s`='%s'",    mysql_escape_string($update_fields[$i]),
                                                    mysql_escape_string($update_values[$i]));               
            } else {
                $query .= sprintf("`%s`='%s'", mysql_escape_string($update_fields[$i]),
                                                mysql_escape_string($update_values[$i]));
            }
            $i++;
        }
        
        if (isset($where_field) && isset($where_value)) {
            $query .= " WHERE ";
            $i=0;
            foreach($where_field as $arr) {
                if ($i>0) {
                    $query .= sprintf(" %s `%s`='%s'",mysql_escape_string($opr[$i-1]),
                                                mysql_escape_string($where_field[$i]),
                                                mysql_escape_string($where_value[$i]));
                } else {
                    $query .= sprintf("`%s`='%s'", mysql_escape_string($where_field[$i]),
                                                mysql_escape_string($where_value[$i]));
                }
                $i++;
            }
        }
        
        $this->script_log .= "Query: " . $query . "\n";
                
        $result = mysql_query($query) or die($this->mysql_error_report());
        $this->script_log .= "Query executed\n";
    }
    
    function testCall()
    {
        $this->query_result = Array();
        $this->CheckConnection();
        mysql_select_db('listy');
        mysql_query('CALL `listy_lastClubs`()')  or die($this->mysql_error_report());
    }
    
    function CallQuery($name){
        $this->query_result = Array();
        $this->CheckConnection();
        
        //mysql_query('USE listy');
        $query = sprintf("CALL `%s`()",mysql_escape_string($name));
        //$query = "CALL `listy_lastClubs`()";
        
        $result = mysql_query($query) or die($this->mysql_error_report());
        // or die("Cannot Query")
        $this->script_log .= "Query executed\n";
        
        //$this->query_result = mysql_fetch_array($result) or die("Cannot Featch Array.");

        
        $i=0;
        while($row = mysql_fetch_assoc($result)) {
            $this->query_result[$i] = $row;
            $i++;
        }
        
        $this->script_log .= "Array fetched \n";
        
        if ($i > 0) {
            $this->records_number = mysql_num_rows($result) or die($this->mysql_error_report());
            $this->script_log .= "Records Number setted to:" .$this->records_number . "\n";
        } else {
            $this->records_number = 0 ;
            $this->script_log .= "Records Number setted to:" .$this->records_number . "\n";
        }
        
        mysql_free_result($result);
        
        return $this->query_result;
    }
    
    function SelectQuery($table, $where_field=NULL, $where_value=NULL,
                            $opr=NULL, $DESC=true, $ORDER_BY="id") {
        $this->query_result = Array();
        // Example for useing class:
        // SelectQuery('links',Array('id','name'),Array(1,'garry'),Array('OR'));
        // Return: Array
        //
        // TODO IN THIS FUNCTION:
        // * ADD LIMIT AND START
        $this->CheckConnection();

        
        // BUILD SECURED QUERY - FOR SQL INJECTIONS PROTECTION
        $query = sprintf("SELECT * FROM `%s`",mysql_escape_string($table));
        if (isset($where_field[0])) {
            $query .= " WHERE ";
            $i=0;
            foreach($where_field as $arr) {
                if ($i>0) {
                    $query .= sprintf(" %s `%s`='%s'",mysql_escape_string($opr[$i-1]),
                                                mysql_escape_string($where_field[$i]),
                                                mysql_escape_string($where_value[$i]));
                } else {
                    $query .= sprintf("`%s`='%s'", mysql_escape_string($where_field[$i]),
                                                mysql_escape_string($where_value[$i]));
                }
                $i++;
            }
        }
            
        if ($DESC == 'true') { 
            //$addon = " ORDER BY `id` DESC";
            $addon = sprintf(" ORDER BY `%s` DESC", $ORDER_BY);
        } else {
            //$addon = " ORDER BY `id`";
            $addon = sprintf(" ORDER BY `%s`", $ORDER_BY);
        }
        $query .= $addon;
        $this->script_log .= "Query: " . $query . "\n";
    
        $result = mysql_query($query) or die($this->mysql_error_report());
        // or die("Cannot Query")
        $this->script_log .= "Query executed\n";
        
        //$this->query_result = mysql_fetch_array($result) or die("Cannot Featch Array.");
        
        $i=0;
        while($row = mysql_fetch_assoc($result)) {
            $this->query_result[$i] = $row;
            $i++;
        }
        
        
        //$this->query_result = mysql_fetch_array($result);
        /*
        $i=0;
        foreach (mysql_fetch_assoc($result) as $row)
        {
            $this->query_result[$i] = $row;
            //array_push($this->query_result, $row);
            $i++;
        }
        */
        
        $this->script_log .= "Array fetched \n";
        
        if ($i > 0) {
            $this->records_number = mysql_num_rows($result) or die($this->mysql_error_report());
            $this->script_log .= "Records Number setted to:" .$this->records_number . "\n";
        } else {
            $this->records_number = 0 ;
            $this->script_log .= "Records Number setted to:" .$this->records_number . "\n";
        }
        
        mysql_free_result($result);
        
        return $this->query_result;
    }
    
    function CloseLink() {
        if ($this->mysql_link_status == true) {
            mysql_close($this->mysql_link) or die($this->mysql_error_report());
            $this->script_log .= "Mysql Link Closed\n";
            $this->mysql_link_status = false;
        } else {
            $this->script_log .= "Cannot close MySQL Link, the link dead\n";
        }
    }
    
    
    
    // SETTERS & GETTERS
    
    function getScriptLog() {
        return $this->script_log;
    }
    
    function getLastResult() {
        return $this->query_result;
    }
    
    function getLastRecordsNumber() {
        return $this->records_number;
    }
    
    function getDB() {
        return $this->mysql_database;
    }
    
    function setDB($database) {
        $this->mysql_database = $database;
        $this->script_log .= "New DB Setted: " . $this->mysql_database . "\n";
    }
    
    // PRIVATE FUNCTIONS
    function Connect() {
        $this->mysql_link = mysql_connect($this->mysql_host,
                                            $this->mysql_username,
                                            $this->mysql_password, 0, 65536) or die($this->mysql_error_report());
        $this->script_log .= "Conncted\n";
        
        mysql_select_db($this->mysql_database) or die($this->mysql_error_report());
        $this->script_log .= "Selected db: " . $this->mysql_database . "\n";
        $this->mysql_link_status=true;
    }
    
    function CheckConnection() {
        if ($this->mysql_link == NULL || $this->mysql_link_status == false) {
            $this->Connect();
        }   
        mysql_query("set character set 'utf8'");
    }

    function mysql_error_report() {
        echo mysql_error();
    }
    
        
}
?>
Return current item: Secured mySQL Layer