Making better access to the database file and its functions

[Marco_4]

Hello, simple information. I was creating the connection to the database files with various functions, such as: "Get One, getAll".

I wondered if their syntax was safe against sql attacks, though in fact could be improved with the inclusion of the bind, but do not know how to make them better.

However, the code is this:

class db {
    private $conn;
    private $host;
    private $user;
    private $password;
    private $baseName;
    private $port;
    private $Debug;

    function __construct($params=array()) {
        $this->conn = false;
        $this->host = 'localhost'; //hostname
        $this->user = 'gallery'; //username
        $this->password = 'Macchinona321654987'; //password
        $this->baseName = 'gallery'; //name of your database
        $this->port = '3306';
        $this->debug = true;
        $this->connect();
    }

    function __destruct() {
        $this->disconnect();
    }

    function connect() {
        if (!$this->conn) {
            try {
                $this->conn = new PDO('mysql:host='.$this->host.';dbname='.$this->baseName.'', $this->user, $this->password, array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));  
            }
            catch (Exception $e) {
                die('Erreur : ' . $e->getMessage());
            }

            if (!$this->conn) {
                $this->status_fatal = true;
                echo 'Connection BDD failed';
                die();
            } 
            else {
                $this->status_fatal = false;
            }
        }

        return $this->conn;
    }

    function disconnect() {
        if ($this->conn) {
            $this->conn = null;
        }
    }

    function getOne($query) {
        $result = $this->conn->prepare($query);
        $ret = $result->execute();
        if (!$ret) {
           echo 'PDO::errorInfo():';
           echo '<br />';
           echo 'error SQL: '.$query;
           die();
        }
        $result->setFetchMode(PDO::FETCH_ASSOC);
        $reponse = $result->fetch();

        return $reponse;
    }

    function getAll($query) {
        $result = $this->conn->prepare($query);
        $ret = $result->execute();
        if (!$ret) {
           echo 'PDO::errorInfo():';
           echo '<br />';
           echo 'error SQL: '.$query;
           die();
        }
        $result->setFetchMode(PDO::FETCH_ASSOC);
        $reponse = $result->fetchAll();

        return $reponse;
    }

        function Count($query) {
                $result = $this->conn->prepare($query);
                $ret = $result->execute();
        if (!$ret) {
           echo 'PDO::errorInfo():';
           echo '<br />';
           echo 'error SQL: '.$query;
           die();
        }
        $result->fetchAll(PDO::FETCH_ASSOC);
        $reponse = $result->rowCount();

        return $reponse;
    }


    function execute($query) {
        if (!$response = $this->conn->exec($query)) {
            echo 'PDO::errorInfo():';
           echo '<br />';
           echo 'error SQL: '.$query;
           die();
        }
        return $response;
    }
}

[cereal]

I wondered if their syntax was safe against sql attacks, though in fact could be improved with the inclusion of the bind, but do not know how to make them better.

Use prepared statements, add a second parameter to the getOne() and getAll() methods and send an array, for example:

function getOne($query, $input) {
    $result = $this->conn->prepare($query);
    $ret = $result->execute($input);
    if (!$ret) {
       echo 'PDO::errorInfo():';
       echo '<br />';
       echo 'error SQL: '.$query;
       die();
    }
    $result->setFetchMode(PDO::FETCH_ASSOC);
    $reponse = $result->fetch();

    return $reponse;
}

function getAll($query, $input) {
    $result = $this->conn->prepare($query);
    $ret = $result->execute($input);
    if (!$ret) {
       echo 'PDO::errorInfo():';
       echo '<br />';
       echo 'error SQL: '.$query;
       die();
    }
    $result->setFetchMode(PDO::FETCH_ASSOC);
    $reponse = $result->fetchAll();

    return $reponse;
}

Then when you execute the query you do:

$input = [1, 2, 3];

$db = new db;
$result = $db->getAll("select * from users where id IN(?, ?, ?)", $input);