0
\$\begingroup\$

source : https://github.com/Zakisu/QB_Mysql/blob/master/mysql/Mysql.php

please review mycode read the docs about the usage

<?php
/**
 * Mysql Class
 *
 * @category  Database Access
 * @package   Mysql
 * @author    Muhammad Zaki Sulistya <zaki.sulistya@gmail.com>
 * @copyright Copyright (c) 2016
 */
class QB_Mysql
{
    /**
     * PDO instance
     * @var PDO
     */
    protected $db;
     /**
     * Database credentials
     * @var string
     */
    private $host;
    private $user;
    private $passwd;
    private $dbname;
     /**
     * Table prefix
     * @var string
     */
    protected $prefix;
    /**
     * Table name (with prefix, if used)
     * @var string 
     */
    protected $table;
    /**
     * The parameter of the query
     * @var array
     */
    public $params = array();
    /**
     * Insert query
     * @var string
     */
    protected $insert;
    /**
     * Select query
     * @var string
     */
    protected $select;
    /**
     * Update query
     * @var string
     */
    protected $update;
    /**
     * Delete query
     * @var string
     */
    protected $delete;
    /**
     * An array that holds WHERE conditions
     * @var array
     */
    protected $where = array();
    /**
     * An array that holds OR WHERE conditions
     * @var array
     */
    protected $orWhere = array();
    /**
     * An array that holds HAVING WHERE conditions
     * @var array
     */
    protected $having = array();
    /**
     * An array that holds OR HAVING conditions
     * @var array
     */
    protected $orHaving = array();
    /**
     * Used to accommodate the value of ORDER BY condition
     * @var array
     */
    protected $orderBy = array();
    /**
     * Used to accommodate the value of GROUP BY condition
     * @var array
     */
    protected $groupBy = array();
    /**
     * Set a limit for a query
     * @var int
     */
    protected $limit;
    /**
     * To know if the query is a Subquery or not
     * @var string
     */
    protected $sub = "OFF";
    /**
     * Dynamic list of subquery
     * @var array
     */
    protected $subQuery = array();
    /**
     * The list of error
     * @var array
     */
    protected $errorLog = array();
    /**
     * @param string $host
     * @param string $user
     * @param string $passwd
     * @param string $dbname
     * @param string $prefix
     */
    public function __construct($host = null, $user = null, $passwd = null, $dbname = null, $prefix = null)
    {
        if(is_array($host))
        {
            foreach($host as $key => $value)
            {
                $$key = $value;
            }
        }
        $this->host = $host;
        $this->user = $user;
        $this->passwd = $passwd;
        $this->dbname = $dbname;
        $this->prefix = $prefix;
        $this->connect();
    }
    /**
     * A method to connect to the database
     *
     * @return void
     */
    private function connect()
    {
        try
        {
            $this->db = new PDO("mysql:host={$this->host};dbname={$this->dbname}", $this->user, $this->passwd);
        }
        catch(PDOException $e)
        {
            $this->catchError($e->getFile(), $e->getLine(), $e->getMessage());
        }
    }
    /**
     * Execute SQL query.
     *
     * @param string $query  User-provided query to execute.
     * @param array  $params Variables array to bind to the SQL statement.
     *
     * @return Mysql returned a PDOStatement object, or FALSE on failure
     */
    public function query($query, $params = null)
    {
        try
        {
            if($params == null)
            {
                $stmt = $this->db->query($query);
            }
            else
            {
                $stmt = $this->db->prepare($query);
                $stmt->execute($params);
            }
            return $stmt;
        }
        catch(PDOException $e)
        {
            $this->catchError($e->getFile(), $e->getLine(), $e->getMessage());
        }
    }
    /**
     * To make sure that the query as subquery
     *
     * @return Mysql
     */
    public function subQuery()
    {
        $this->sub = "ON";
        return $this;
    }
    /**
     * Set the table of the query
     *
     * @return Mysql
     */
    public function table($table)
    {
        $this->table = $table;
        return $this;
    }
    /**
     * Select columns
     *
     * @param array $cols Desired columns
     *
     * @return Mysql
     */
    public function select($cols = "*")
    {
        $table = $this->prefix.$this->table;
        if(is_array($cols))
        {
            $cols = implode(", ", $cols);
        }
        $this->select = "SELECT
                            {$cols}
                            FROM
                            {$table} ";
        return $this;
    }
    /**
     * Insert method to add new row
     *
     * @param array $data Data containing information for inserting into the DB.
     *
     * @return Mysql
     */
    public function insert($data)
    {
        $table = $this->prefix.$this->table;
        $value = array_values($data);
        for($i=0;$i<count($value);$i++)
        {
            $generateKey = uniqid(":");
            $this->params[$generateKey] = $value[$i];
            $value[$i] = $generateKey;
        }
        $cols = implode(", ", array_keys($data));
        $valueCols = implode(", ", $value);
        $this->insert = "INSERT INTO
                        {$table}
                        ({$cols})
                    VALUES
                        ({$valueCols})";
        return $this;
    }
    /**
     * Update query
     *
     * @param array  $data Array of data to update the desired row
     *
     * @return Mysql
     */
    public function update($data)
    {
        $table = $this->prefix.$this->table;
        $point = array();
        foreach ($data as $key => $value) {
            $generateKey = uniqid(":");
            $this->params[$generateKey] = $value;
            $value = $generateKey;
            array_push($point, ($key." = ".$value));
        }
        $update = implode(", ", $point);
        $this->update = "UPDATE
                        {$table}
                    SET
                        {$update} ";
        return $this;
    }
     /**
     * Delete query
     *
     * @return Mysql
     */
    public function delete()
    {
        $table = $this->prefix.$this->table;
        $this->delete = "DELETE
                        FROM
                        {$table} ";
        return $this;
    }
     /**
     * Just an ordinary method to filter for AND/OR statements for SQL queries
     *
     * @param string $option (AND/OR) statements
     * @param string the value of the statements
     *
     * @return void
     */
    protected function whereFilter($option, $value)
    {
        try 
        {
            if(strtoupper($option) == "AND")
            {   
                array_push($this->where, $value);
            }
            elseif(strtoupper($option) == "OR")
            {
                array_push($this->orWhere, $value);
            }
            else
            {
                throw new Exception("Error Processing Query");
            }       
        } 
        catch (Exception $e) 
        {
            $this->catchError($e->getFile(), $e->getLine(), $e->getMessage());
        }
    }
    /**
     * Just an ordinary method to filter for AND/OR statements for SQL queries
     *
     * @param string $option (AND/OR) statements
     * @param string the value of the statements
     *
     * @return void
     */
    protected function havingFilter($option, $value)
    {
        try 
        {
            if(strtoupper($option) == "AND")
            {   
                array_push($this->having, $value);
            }
            elseif(strtoupper($option) == "OR")
            {
                array_push($this->orHaving, $value);
            }
            else
            {
                throw new Exception("Error Processing Query");
            }       
        } 
        catch (Exception $e) 
        {
            $this->catchError($e->getFile(), $e->getLine(), $e->getMessage());
        }
    }
     /**
     * This method allows you to specify multiple (method chaining optional) AND/OR/HAVING WHERE statements for SQL queries.
     *
     * @param string $cols  The name of column.
     * @param string $operator Comparison operator. Default is =
     * @param string $value The value of the column
     * @param string $option Condition of where statement (OR, AND, HAVING)
     *
     * @return Mysql
     */
    public function where($cols, $operator = "=", $value, $option = "AND")
    {
        try
        {
            $listOperator = ["=", ">", "<", "<=", ">=", "!=", "BETWEEN", "NOT BETWEEN", "IN", "NOT IN"];
            $listOption = array("AND", "OR");
            if(in_array($operator, $listOperator) AND in_array($option, $listOption))
            {
                if(strtoupper($operator) == "BETWEEN" OR strtoupper($operator) == "NOT BETWEEN")
                {
                    if((is_array($value) AND (count($value) > 2)) OR empty($value))
                    {
                        throw new Exception("Error Processing [BETWEEN] or [NOT BETWEEN] Query");
                    }
                    else
                    {
                        for($i=0;$i<count($value);$i++)
                        {
                            if(!empty($this->subQuery) AND $this->sub != "ON")
                            {
                                if(in_array($value[$i], $this->subQuery))
                                {
                                    $value[$i] = "(".$value[$i].")";
                                }
                                else
                                {
                                    $generateKey = uniqid(":");
                                    $this->params[$generateKey] = $value[$i];
                                    $value[$i] = $generateKey;
                                }
                            }
                        }
                        $value = "{$cols} {$operator} ".implode(" AND ", $value);
                        $this->whereFilter($option, $value);
                    }
                }
                elseif(strtoupper($operator) == "IN" OR strtoupper($operator) == "NOT IN")
                {
                    for($i=0;$i<count($value);$i++)
                    {
                        if(!empty($this->subQuery) AND $this->sub != "ON")
                        {
                            if(in_array($value[$i], $this->subQuery))
                            {
                                $value[$i] = "(".$value[$i].")";
                            }
                            else
                            {
                                $generateKey = uniqid(":");
                                $this->params[$generateKey] = $value[$i];
                                $value[$i] = $generateKey;
                            }
                        }
                    }
                    $value = "{$cols} {$operator} (".implode(", ", $value).")";
                    $this->whereFilter($option, $value);
                }
                else
                {
                    if(!empty($this->subQuery) AND $this->sub != "ON")
                    {
                        if(in_array($value, $this->subQuery))
                        {
                            $value = "(".$value.")";
                        }
                        else
                        {
                            $generateKey = uniqid(":");
                            $this->params[$generateKey] = $value;
                            $value = $generateKey;
                        }
                        $value = "{$cols} {$operator} {$value}";
                    }
                    else
                    {
                        $generateKey = uniqid(":");
                        $this->params[$generateKey] = $value;
                        $value = $generateKey;
                        $value = "{$cols} {$operator} {$value}";
                    }
                    $this->whereFilter($option, $value);
                }
            }
            else
            {
                throw new Exception("Error Processing The Query");
            }
            return $this;
        }
        catch(Exception $e)
        {
            $this->catchError($e->getFile(), $e->getLine(), $e->getMessage());
        }
    }
     /**
    * This method allows you to specify multiple (method chaining optional) AND/OR statements for SQL queries.
    *
    * @param string $cols  The name of column.
    * @param string $operator Comparison operator. Default is =
    * @param string $value The value of the column
    * @param string $option Condition of having statement (OR/AND)
     *
    * @return Mysql
    */
    public function having($cols, $operator = "=", $value, $option = "AND")
    {
        try
        {
            $listOperator = ["=", ">", "<", "<=", ">=", "!="];
            $listOption = array("AND", "OR");
            if(in_array($operator, $listOperator) AND in_array($option, $listOption))
            {
                if(!empty($this->subQuery) AND $this->sub != "ON")
                {
                    if(in_array($value, $this->subQuery))
                    {
                        $value = "(".$value.")";
                    }
                    else
                    {
                        $generateKey = uniqid(":");
                        $this->params[$generateKey] = $value;
                        $value = $generateKey;
                    }
                    $value = "{$cols} {$operator} {$value}";
                }
                else
                {
                    $generateKey = uniqid(":");
                    $this->params[$generateKey] = $value;
                    $value = $generateKey;
                    $value = "{$cols} {$operator} {$value}";
                }
                $this->havingFilter($option, $value);
            }
            else
            {
                throw new Exception("Error Processing The Query");
                print_r($db->getErrorLog());    
            }
            return $this;
        }
        catch(Exception $e)
        {
            $this->catchError($e->getFile(), $e->getLine(), $e->getMessage());
        }
    }
     /**
     * This method allows you to specify multiple (method chaining optional) GROUP BY statements for SQL queries.
     *
     * @param string $cols The name of the database field.
     *
     * @return Mysql
     */
    public function groupBy($cols)
    {
        if(is_array($cols))
        {
            $cols = implode(", ", $cols);
        }
        array_push($this->groupBy, "{$cols}");
        return $this;
    }
    /**
     * This method allows you to specify multiple (method chaining optional) ORDER BY statements for SQL queries.
     *
     * @param string $cols The name of the database field.
     * @param string $operator ASC/DESC statement.
     * @param array $fields Fieldset for ORDER BY FIELD() ordering
     * 
     * @return Mysql
     */
    public function orderBy($cols, $field = null, $operator = "ASC")
    {
        $operator = strtoupper($operator);
        if(is_array($cols))
        {
            $cols = implode(", ", $cols);
        }
        if(($field == null) AND ($operator == "ASC" OR $operator == "DESC"))
        {
            array_push($this->orderBy, "{$cols} {$operator}");
        }
        elseif(!empty(is_array($field)))
        {
            for($i=0;$i<count($field);$i++)
            {
                $field[$i] = "'".$field[$i]."'";
            }
            $field = implode(", ", $field);
            array_push($this->orderBy, "FIELD({$cols}, {$field})");
        }
        return $this;
    }
    /**
     * This method allows you to set the limit for the SQL query.
     *
     * @param int $limit set the limit for the SQL query.
     * 
     * @throws Exception
     * @return Mysql
     */
    public function limit($limit)
    {
        try
        {
            if(is_int($limit) OR is_float($limit))
            {
                $this->limit = $limit;
            }
            else
            {
                throw new Exception("The value of limit parameter should be an integer");
            }
            return $this;
        }
        catch(Exception $e)
        {
            $this->catchError($e->getFile(), $e->getLine(), $e->getMessage());
        }
    }
    /**
     * This method allows you to merge all statements.
     *
     * @return string
     */
    protected function queryBuilder()
    {
        $query = null;
        if(!empty($this->insert))
        {
            $query = $this->insert;
        }
        if(!empty($this->select))
        {
            $query = $this->select;
        }
        if(!empty($this->update))
        {
            $query = $this->update;
        }
        if(!empty($this->delete))
        {
            $query = $this->delete;
        }
        if(!empty($this->where) OR !empty($this->orWhere))
        {
            $query .= " WHERE ";
        }
        if(!empty($this->where))
        {
            $query .= implode(" AND ", $this->where);
        }
        if(!empty($this->orWhere))
        {
            if(!empty($this->where))
            {
                $query .= " OR ";
            }
            $query .= implode(" OR ", $this->orWhere);
        }
        if(!empty($this->groupBy))
        {
            $query .= " GROUP BY ".implode(",", $this->groupBy);
        }
        if(!empty($this->having) OR !empty($this->orHaving))
        {
            $query .= " HAVING ";
        }
        if(!empty($this->having))
        {
            $query .= implode(" AND ", $this->having);
        }
        if(!empty($this->orHaving))
        {
            if(!empty($this->having))
            {
                $query .= " OR ";
            }
            $query .= implode(" OR ", $this->orHaving);
        }
        if(!empty($this->orderBy))
        {
            $query .= " ORDER BY ".implode(",", $this->orderBy);
        }
        if(!empty($this->limit))
        {
            $query .= " LIMIT {$this->limit}";
        }
        return $query;
    }
    /**
     * This method allows you to set a subquery.
     *
     * @return string
     */
    public function get()
    {
        try
        {
            $query = $this->queryBuilder();
            if($this->sub == "ON")
            {
                array_push($this->subQuery, $query);
                $this->sub = "OFF";
                $this->where = array();
                $this->orWhere = array();
                $this->having = array();
                return $query;
            }
            else
            {
                throw new Exception("Error Processing Request");

            }
        }
        catch(Exception $e)
        {
            $this->catchError($e->getFile(), $e->getLine(), $e->getMessage());
        }
    }
    /**
     * This method allows you to run the SQL query.
     *
     * @return Mysql
     */
    public function run()
    {
        $query = $this->queryBuilder();
        if(empty($this->params))
        {
            $run = $this->query($query);
        }
        else
        {
            $run = $this->query($query, $this->params);
        }
        $this->reset();
        return $run;
    }
    /**
     * This method is used to catch an error and log it.
     *
     * @return void
     */
    protected function catchError($file, $line, $message)
    {
        $error = array("file"=>$file, "line"=>$line, "message"=>$message);
        array_push($this->errorLog, $error);
    }
    /**
     * This method is used to dump all error info.
     *
     * @return array returned errorLog property
     */
    public function getErrorLog()
    {
        return $this->errorLog;
    }
    /**
     * Reset states after an execution.
     *
     * @return void
     */
    protected function reset()
    {
        $this->table = "";
        $this->params = array();
        $this->select = "";
        $this->where = array();
        $this->orWhere = array();
        $this->having = array();
        $this->orHaving = array();
        $this->orderBy = array();
        $this->groupBy = array();
        $this->limit = "";
        $this->subQuery = array();
    }
}
?>
\$\endgroup\$
  • \$\begingroup\$ What does it do and does it do so as intended? \$\endgroup\$ – Mast Mar 25 '16 at 13:04
  • 1
    \$\begingroup\$ For the review, check this article, Your first database wrapper's childhood diseases. Regarding the question about vulnerability, add charset in your PDO DSN and turn PDO emulation mode off \$\endgroup\$ – Your Common Sense Mar 31 '16 at 8:05
1
\$\begingroup\$

Sorry to say, but you are just making PDO worse there :(

  • restricting only to using mysql
  • your constructor is missing options, which means that:
    • it's probably not utf-8
    • you can't user row count
    • exceptions are NOT thrown
    • all prepared statements are emulates (and vulnerable to SQL injection)
  • what if I want to use socket to mysql?
  • storing values just to use as parts of DSN is pointless
  • constructors should not have logic
  • constructors should not have new statements
  • have you unit-tested QB_Mysql::where() or QB_Mysql::having()?

... anyway, I got bored around the line 400.

\$\endgroup\$
  • \$\begingroup\$ i think your word is amazing, i'm very proud of you. i'm not felt sad because your word. can you explain where is the vulnerable? and do you have social media like facebook maybe(not twitter)? i need to make conversation with you \$\endgroup\$ – Zaki Sulistya Mar 25 '16 at 13:27
  • 1
    \$\begingroup\$ There is a design flaw in PDO's driver for MySQL. The support for PREPARE queries in MySQL was added only in 5.0 versions (released in 2003). The adoption rate was slow, which mean that when PHP 5.1 was released (version that added PDO) there still was huge percentage of MySQL 4.x servers out there. \$\endgroup\$ – tereško Apr 2 '16 at 20:29
  • 1
    \$\begingroup\$ That's why by default PDO was set to emulate prepared statements for the MySQL driver. This emulation has to be turned off, because otherwise your code can becomes vulnerable to SQL injections. Especially when connection does not have a defined charset. For full details, please review this article: wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers (it will explain how to disable emulation, define charset and enable exception based error reporting) \$\endgroup\$ – tereško Apr 2 '16 at 20:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.