0

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;
    }
}
2
Contributors
1
Reply
16
Views
1 Year
Discussion Span
Last Post by cereal
0

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);
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.