4
\$\begingroup\$

Last time I asked a question here, it was for my select function. In that question, another user stated the way I did it, might not have been safe. So here I am with the entire class.

The class I'm about to show you is going to be used for a client's Homepage. My main concern is the safety of the PDO Class; I want to make sure that it's safe to use. Furthermore, if there are any coding improvements I'd really like to hear them, because every comment about my code makes me better.

class DB {

    private static $_instance = null,
                   $_vowels = array("a", "e", "i", "o", "u", "y", "A", "E", "I", "O", "U", "Y");

    private $_pdo,
            $_query,
            $_results,
            $_count = 0;

    private function __construct() {
        try {
            $this->_pdo = new PDO('mysql:dbname='. Config::get('pdo/db') .';host='. Config::get('pdo/host'), Config::get('pdo/username'), Config::get('pdo/password') );
            $this->_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->_pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
            $this->_pdo->exec("SET NAMES 'utf8';");
        }
        catch(PDOException $e) {
            die($this->ExceptionLog($e->getMessage(),$type="Database Connection"));
        }
    }

    public static function getInstance() {
        if(!isset($this->_instance)){
            $this->_instance = new DB();
        }
        return $this->_instance;
    }

    private static function removeVowelsWhere(array $array, $table) {
        $removed = array();
        foreach($array as $key => $value)
        {
            foreach($value as $operator => $attribute)
            {
                $removed[] = str_replace($this->_vowels, "", $table) . "." . $key . " " . $operator . " '" . $attribute . "'";
            }
        }
        return $removed;
    }

    private static function removeVowels(array $array) {
        $removed = array();
        foreach($array as $key => $value)
            $removed[] = str_replace($this->_vowels, '', $key) . '.' . $value;
        return $removed;
    }

    public function query($sql, $params = array()) {
        $this->_error = false;
        if($this->_query = $this->_pdo->prepare($sql)){
            $x = 1;
            if(count($params)) {
                foreach($params as $param) {
                    $this->_query->bindValue($x, $param);
                    $x++;
                }
            }
            if($this->_query->execute()) {
                $this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ);
                $this->_count = $this->_query->rowCount();
            } else{
                $this->_error = true;
            }
        }
        return $this;
    }

    public function error() {
        return $this->_error;
    }

    private function action($action){

                $sql = "{$action}";
                if(!$this->query($sql, array($sql))->error()) {
                    return $this;
                }
                return $sql;
    }

    public function select($table, $where = array(), $Innerjoin = array(), $group = array(), $selects = array()) {
        $sql = 'SELECT ' . (empty($selects) ? '*' : implode(', ', $this->removeVowels($selects))) .
            ' FROM `' . $table . '` AS ' . str_replace(self::$_vowels, '', $table);

        if(!empty($Innerjoin)){
            $join = "";
            foreach ($Innerjoin as $joinKey => $joinValue) {
                foreach ($joinValue as $key => $value) {
                    $join .= " INNER JOIN `" . $joinKey . "` AS " . str_replace($this->_vowels, '', $joinKey) . " ON ";
                    if(is_array($value)){
                        foreach ($value as $ids => $id) {
                            $join .= str_replace($this->_vowels, '', $ids) . "." . $key . " = " . str_replace($this->_vowels, '', $joinKey) . "." . $id;
                        }
                    } else {
                        $join .= str_replace($this->_vowels, '', $table) . "." . $key . " = " . str_replace($this->_vowels, '', $joinKey) . "." . $key;
                    }
                }
            }
            $sql .= $join;
        }

        if(!empty($where))
        {
            $sql .= " WHERE " . implode(' AND ', $this->removeVowelsWhere($where, $table));
        }

        if(!empty($group))
        {
            $sql .= " GROUP BY (" . implode(', ', $this->removeVowels($group)) . ')';
        }

        return $this->action($sql);
    }

    public function delete($table, $where) {
        $sql = 'DELETE FROM `' . $table . '`';
        if(!empty($where))
        {
            foreach($where as $key => $value)
            {
                foreach($value as $operator => $attribute)
                {

                    $sql .= "WHERE " .$key . " " . $operator . " '" . $attribute . "'";
                }
            }
        }

        return $this->action($sql);
    }

    public function update($table, $id, $column, $fields) {
        $set = '';
        $x = 1;

        foreach ($fields as $name => $value) {
            $set .= "{$name} = ?";
            if($x < count($fields)){
                $set .= ', ';
            }
            $x++;
        }
        $sql = "UPDATE {$table} SET {$set}  WHERE {$column} = {$id}";
        print $sql;
        if($this->query($sql, $fields)->error()){
            return true;
        }
    }

    public function insert($table, $fields) {
        $keys = array_keys($fields);
        $values = '';
        $x = 1;
        foreach($fields as $field){
            $values .= "?";
            if($x < count($fields)){
                    $values .= ', ';
                }
                $x++;
            }

            $sql = "INSERT INTO {$table} (`" . implode('`,`', $keys) . "`) VALUES ({$values})";
            if($this->query($sql, $fields)->error()){
                return true;
            }
    }

    public function results() {
        return $this->_results;
    }

    public function first(){
        return $this->results()[0];
    }

    public function count(){
        return $this->_count;
    }

    private function ExceptionLog($message, $type, $sql = "") {

        if(!empty($sql)) {
            $message .= "\r\nRaw SQL : "  . $sql;
        }
            Log::getInstance()->write($message, $type);
    }
}
\$\endgroup\$
2
\$\begingroup\$

There's a few changes I'd suggest to insert, firstly as the second parameter has to be an array, you can use type hinting to indicate this:

public function insert($table, array $fields)

You can also generate the $values without using a foreach loop, like so:

$values = substr(str_repeat('?, ', count($fields)), 0, -2);

The only other comment I have is that the removeVowels and removeVowelsWhere methods are static, so you should be calling them as self::removeVowels and self::removeVowelsWhere rather than $this-> which is for instance methods.

\$\endgroup\$
  • \$\begingroup\$ If these mistakes were fixed, Would it the Class then be ok and useable(slighty injecton free(i know That it's Not the Right plage about it)) \$\endgroup\$ – Nquit Jun 6 '14 at 19:00
  • \$\begingroup\$ @Nquit I believe so but I wasn't certain enough to put it in my post, let's see what others have to say about it. \$\endgroup\$ – MrLore Jun 6 '14 at 19:55

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.