right way of PDO queries

Question

this is my connection class.

class Database {
    private $host;
    private $port;
    private $dbname;
    private $username;
    private $password;

    function __construct($host, $port, $dbname, $username, $password) {
        $this->host = $host;
        $this->port = $port;
        $this->dbname = $dbname;
        $this->username = $username;
        $this->password = $password;

        try {
            $conn = new PDO("pgsql:host=$this->host port=$this->port dbname=$this->dbname", "$this->username", "$this->password");
            echo "PDO connection object created";
        }
        catch(PDOException $e) {
            echo $e->getMessage();
        }
    }

}

$db = new Database('host','5432','eu','eu','eu');

Can you help me with doing right QUERY class which will be safe from sql injections?

Thanks!

As long as you use prepared statements you are safe, and no string escaping needed. See http://www.php.net/manual/en/pdostatement.bindparam.php and the rest of the pdo manual for that matter. One of many very complete tutorials here: http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html — gview, Aug 13 '13 at 17:27
It is not the query class that needs to be safe from injection, it is your queries. Unless you use an ORM / automatic query builder (or for some reason build your own), you need to understand a *technique* for writing safe queries, such as parameterised queries or correctly escaped inputs. — IMSoP, Aug 13 '13 at 19:30
There are lots of good tips on this question: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 Don't just read the most highly-rated answer, read them all, get a feel for the problems and the solutions. — IMSoP, Aug 13 '13 at 19:33

score 1 · Answer 1 · answered Aug 13 '13 at 19:26

I took out all the useless stuff from your class and added desired query. It will provide as much protection as PDO itself.

class Database
{
    function __construct($host, $port, $dbname, $username, $password) {
        $dsn = "pgsql:host=$host;port=$port;dbname=$dbname";
        $this->conn = new PDO($dsn, $username, $password);
    }
    function query($query, $bind) {
        $stmt = $this->conn->prepare($query);
        $stmt->execute($bind);
        return $stmt;
    }
}
$db   = new Database('host','5432','eu','eu','eu');
$sql  = "SELECT * FROM users WHERE age > ? AND sex = ?";
$stmt = $db->query($sql, array(20,'F'));
$data = $stmt->fetchAll();
foreach ($data as $row) {
    echo $row['name'];
}

score -1 · Answer 2 · answered Aug 13 '13 at 17:27

-1

You might want to check out this excellent tutorial from Tutsplus. They cover what you need (prepared statements) and more there! I would also suggest extending PDO rather than making a wrapper class - it's generally more flexible.

answered Aug 13 '13 at 17:27

Connor Peet

6,065
3
22
32

right way of PDO queries

2 Answers2