<?php
class SqlMgr
{
    /**
     * Modern PHP constructor (instead of old PHP4-style function SqlMgr()).
     */
    public function __construct()
    {
        $this->link       = null;
        $this->res        = null;
        $this->curDB      = "";
        $this->slowQuerys = array();

        $this->init(); // Connect to DB on creation
    }

    /*
     * Connect to the MySQL database, select DB, set UTF-8
     */
    public function init()
    {
        // Attempt to connect to MySQL with DB name
        $this->link = mysqli_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASS, MYSQL_NAME);
        if (!$this->link) {
            die("SQL Error: " . mysqli_connect_error());
        }

        $this->curDB = MYSQL_NAME; // keep track of current DB

        // Set connection to UTF-8
        $this->exec("SET character_set_results = utf8");
        $this->exec("SET names utf8");
        $this->exec("SET character_set_client = utf8");
        $this->exec("SET character_set_connection = utf8");
        $this->exec("SET character_set_database = utf8");
        $this->exec("SET character_set_server = utf8");
    }

    /*
     * Close the MySQL link
     */
    public function destroy()
    {
        if ($this->link) {
            mysqli_close($this->link);
        }
    }

    /*
     * Return the internal MySQLi link
     */
    public function getLink()
    {
        return $this->link;
    }

    /*
     * Check Query String for possible injections (basic approach)
     */
    public function check_injections($qry)
    {
        // remove all escaped backslashes
        $withoutquotewords = str_replace("\\\\", "", $qry);
        // remove escaped chars, e.g. \n, \t
        $withoutquotewords = preg_replace("/\\\\[\W\w]/", "", $withoutquotewords);

        // remove all quoted strings
        $withoutquotewords = preg_replace("/'([^']*)'/", "", $withoutquotewords);
        $withoutquotewords = preg_replace('/"([^"]*)"/', "", $withoutquotewords);

        // scan the MySQL commands for blacklisted words
        foreach ($this->blacklist as $blacklisted) {
            if (stristr($withoutquotewords, $blacklisted)) {
                try {
                    global $sLog;
                    $sLog->logMessage(LOG_TYPE_SQL, "SQL_INJECTION: ".$qry);
                } catch(Exception $e) {
                    // ignoring
                }
                return true; // found blacklisted pattern
            }
        }
        return false; // no blacklisted pattern found
    }

    /*
     * Wrapper for queries against localization table
     */
    public function execLocalization($qry)
    {
        return $this->exec($qry, DATABASE_LOCALIZATION);
    }

    /*
     * Wrapper for queries against users table
     */
    public function execUsers($qry)
    {
        return $this->exec($qry, DATABASE_USERS);
    }

    /*
     * Execute MySQL query
     * @param $qry      SQL string
     * @param $database One of DATABASE_DEFAULT, DATABASE_USERS, etc.
     */
    public function exec($qry, $database = DATABASE_DEFAULT)
    {
        // Timer for performance checking
        $tm = new Timer();
        $tm->start();

        // If the query targets a "global" table, switch DB
        if (in_array($database, getGlobalTables())) {
            mysqli_select_db($this->link, MYSQL_NAME_GLOBAL);
        }

        // Check for possible SQL injection patterns
        if ($this->check_injections($qry)) {
            return null;
        }

        // Perform the query
        $this->res = mysqli_query($this->link, $qry);
        if ($this->res == null) {
            try {
                global $sLog;
                $sLog->logMessage(LOG_TYPE_SQL, "SQL_ERROR[".mysqli_error($this->link)."]: ".$qry);
            } catch(Exception $e) {
                // ignoring
            }
            return false;
        }

        // Switch back to local DB if needed
        if (in_array($database, getGlobalTables())) {
            mysqli_select_db($this->link, MYSQL_NAME);
        }

        // Check how long this query took
        $tm->stop();
        if ($tm->getTimeElapsedMs() > DEBUG_TIMING_SLOW_QUERY_TIME) {
            $slow_query = new SlowQuery($qry, $tm);
            array_push($this->slowQuerys, $slow_query);
        }

        return $this->res;
    }

    /*
     * select() query wrapper
     * @param $table     Table to query
     * @param $whichData array of fieldnames OR string
     * @param $whereData assoc array for WHERE clause
     * @param $orderData assoc array for ORDER BY (optional)
     * @param $offset    OFFSET for LIMIT
     * @param $limit     number of rows
     */
    public function select($table, $whichData, $whereData, $orderData = '', $offset = 0, $limit = 0)
    {
        if(!is_array($whereData)) {
            return null; // only arrays allowed for whereData
        }
        $which = '';
        $where = '';
        $order = '';

        // Build SELECT fields
        if (is_array($whichData)) {
            foreach ($whichData as $val) {
                $which .= '`'.$val.'`,';
            }
            $which = rtrim($which, ',');
        } else {
            $which = $whichData;
        }

        // Build WHERE
        foreach ($whereData as $key => $val) {
            $where .= ' `'.$key.'`='.(is_int($val) ? i($val) : "'".a($val)."'").' AND';
        }
        if (strlen($where) > 0) {
            $where = substr($where, 0, -3); // strip trailing 'AND'
        }

        // Build ORDER
        if (is_array($orderData)) {
            $order = ' ORDER BY ';
            foreach ($orderData as $key => $val) {
                $order .= '`'.$key.'` '.$val.',';
            }
            $order = rtrim($order, ',');
        } else if ($orderData != '') {
            $order = ' ORDER BY `'.$orderData.'`';
        }

        // If offset or limit are used, build the clause
        $limitClause = '';
        // if ($limit > 0) {
        //     $limitClause = " LIMIT ".(int)$offset.", ".(int)$limit;
        // }

        $qry = 'SELECT '.$which.' FROM `'.$table.'`'.
               (strlen($where) ? ' WHERE '.$where : '').
               $order.
               $limitClause;

        return $this->exec($qry);
    }

    /*
     * update() query wrapper
     * @param $table        table to be updated
     * @param $updateData   assoc array 'field' => 'value'
     * @param $conditions   string, e.g. "field1=2 AND field2>0"
     * @param $limit        numeric limit if needed
     */
    public function update($table, $updateData, $conditions = '', $limit = 0)
    {
        $upcmd = "UPDATE `".$table."` SET ";
        if (is_array($updateData)) {
            foreach ($updateData as $col => $val) {
                if (is_int($val)) {
                    $upcmd .= '`'.$col."`=".$val.",";
                } else {
                    $upcmd .= '`'.$col."`='".a($val)."',";
                }
            }
            $upcmd = rtrim($upcmd, ',');
        } else {
            // If $updateData is not array, we directly use it
            $upcmd .= $updateData;
        }

        if ($conditions != '') {
            $upcmd .= " WHERE ".$conditions;
        }
        // if ($limit > 0) {
        //     $upcmd .= " LIMIT ".(int)$limit;
        // }

        return $this->exec($upcmd);
    }

    /*
     * Return last MySQL query result
     */
    public function getLastResult()
    {
        return $this->res;
    }

    /*
     * Return reference to slow queries array
     */
    public function &getSlowQuerys()
    {
        return $this->slowQuerys;
    }

    private $link;         // MySQLi connection
    private $res;          // Last result
    private $curDB;        // Name of current DB
    private $blacklist = array('union', '/*', '#', '--', 'concat', 'drop', 'outfile', 'dumpfile', 'load_file');
    private $slowQuerys;
}

/**
 * Class SlowQuery
 * Holds info about queries that exceeded DEBUG_TIMING_SLOW_QUERY_TIME
 */
class SlowQuery
{
    public function __construct($qry, $tm)
    {
        $this->qry  = $qry;
        $this->tm   = $tm;
        $this->timeElapsedMs = $tm->getTimeElapsedMs();
    }

    public $qry;
    public $tm;
    public $timeElapsedMs;
}
?>