Need advices on OOP PHP MySQL connection

Question

So I wrote a simple MySQLi class (scratched from different tutorials + my own knowledge) and I'd like you to point out any errors and/or what should I add/change/remove. Big thanks. Here's the code:

dbclass.php

<?php
    class Database
    {
        protected static $connection;

        public function __construct()
        {
            self::$connection = $this->connect();
        }

        public function __destruct()
        {
            self::$connection->close();
        }

        private function connect()
        {
            if (!isset(self::$connection))
            {
                $config = parse_ini_file("dbsettings.ini");
                self::$connection = new mysqli($config["host"], $config["username"], $config["password"], $config["database"]);
            }

            if (!self::$connection)
                return false;

            return self::$connection;
        }   

        public function query($query)
        {
            $result = self::$connection->query($query);
            return $result;
        }  

        public function select($query)
        {
            $rows = array();
            $result = $this->query($query);

            if (!$result)
                return false;

            while ($row = $result->fetch_assoc())
            {
                $rows[] = $row;
            }

            return $rows;
        } 

        public function escape($value)
        {
            $value = htmlspecialchars($value, ENT_QUOTES, "UTF-8");
            return "'".self::$connection->real_escape_string($value)."'";
        }
    };
?>

Example usage

<?php
    require("php/require/dbclass.php");

    if (isset($_POST["login"]))
    {
        $conn = new Database;

        $email = $conn->escape($_POST["email"]);
        $password = $conn->escape($_POST["password"]);

        $rows = $conn->select("SELECT * FROM users WHERE email = $email");

        if ($rows)
        {
            foreach ($rows as $row)
            {
                echo $row["username"]."<br />";
            }
        }
    } 
?>

Stack Overflow is not a code review site, but one feedback is essential: anything that doesn't have a support for **prepared statements** should never be used. — Your Common Sense, Dec 01 '16 at 13:22
Why wrap a perfectly usable class in another class. All you are doing is obfiscating well know methods and property names with unknow method and property names! — RiggsFolly, Dec 01 '16 at 13:25
@chris85 why should it cause an error? email is a field in the table **users**, $email is a value from `$_POST` — , Dec 01 '16 at 13:26
@RiggsFolly to stop writing x lines of code on each page if I want to make a query, the class makes it simpler and less code — , Dec 01 '16 at 13:28
@daavid245 as you can learn from my answer, your class is doing quite the opposite ;) — Your Common Sense, Dec 01 '16 at 13:28
But my **escape** function returns **' + variable + '** already, look closely :) — , Dec 01 '16 at 13:28
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Dec 01 '16 at 13:29

score -1 · Answer 1 · answered Dec 01 '16 at 13:26

-1

Stack Overflow is not a code review site but still. Isn't it better to make your code to do escaping for you? Are you sure you want write that escape call by hand for every single variable (with a risk of forgetting that), instead of just a simple line like this

    $conn = new Database;
    $rows = $conn->select("SELECT * FROM users WHERE email = ?", [$_POST["email"]]);

answered Dec 01 '16 at 13:26

Your Common Sense

156,878
40
214
345

Didn't know about that, thanks :) – Dec 01 '16 at 13:30

Need advices on OOP PHP MySQL connection

1 Answers1