What makes PDO safer than normal mysql?

Question

Time and time again I've read on Stackoverflow that I should be using PDO to access MySQL because it is safer. I recently changed some of my select and insert statements to PDO using some online tutorial and found them to be very similar to my original code. This makes me think that perhaps I've missed something.

So, my question is what makes PDO safer than normal mysql? Is there anything that would make those examples safer?

EDIT: I've pasted my insert code below. If you can see some ways of making it safer please let me know.

include 'dataB3S3.php';

try {
        $dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
        /*** connect to DB ***/


        /*** INSERT data ***/
        $count = $dbh->exec("INSERT INTO $table(`instance` ,`uid`,`teid`) VALUES (NULL,'$userID','$teid')");

         /*** display the id of the last Auto INSERT ***/
        $lastInsertValue=$dbh->lastInsertId();

        /*** close the database connection ***/
        $dbh = null;  
}

The tutorial does have a section on [prepared statements](http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html#10) however it gets most of the descriptions and explanations completely wrong. For starters, `PDO::quote()` is **not** called internally. It's articles like this that are the root cause of PHP's bad reputation — Phil, Mar 21 '12 at 04:32
Is `PDO::quote()` like the equivalent of `mysql_real_escape_string()` ? — TryHarder, Mar 21 '12 at 04:55
Yes, only it also wraps the value in quotes. Parameter binding is *far* superior so please **never** use this method. See http://php.net/manual/en/pdo.quote.php for more information — Phil, Mar 21 '12 at 04:59
It's also very important to note that some drivers do not implement quote(). The first one that comes to mind is MSSQL. quote() does nothing... :) — Corbin, Mar 21 '12 at 05:03
another duplicate of [MySQL / PDO / Prepared Statements - All a big jump, a bit overwhelming and a little confusing?](http://stackoverflow.com/questions/8061185/mysql-pdo-prepared-statements-all-a-big-jump-a-bit-overwhelming-and-a-lit) — Your Common Sense, Mar 21 '12 at 05:10
Thanks for the comments and the links. They are all very useful. — TryHarder, Mar 21 '12 at 05:21

score 5 · Accepted Answer · answered Mar 21 '12 at 04:24

5

You've missed one of the main benefits, prepared statements. Using them instead of directly embedding variables in your query like your sample code protects you better against accidental SQL injection vulnerabilities.

answered Mar 21 '12 at 04:24

ceejayoz

165,698
38
268
341

score 1 · Answer 2 · edited May 23 '17 at 12:22

Yes. ceejayoz has a good point, but to me, aside from filtering and sanitizing the data, PDO can actually inject the resulting datasets in full fledged objects.

I learned this a while back from this awesome SO post.

For the sake of having a code example, I will paste e-satis code in here as I have no postable code example of this on hand but trust me, I have borrowed the concept many times:

class Student {

    public $id;
    public $first_name;
    public $last_name

    public function getFullName() {
        return $this->first_name.' '.$this->last_name
    }
}

try 
{
    $dbh = new PDO("mysql:host=$hostname;dbname=school", $username, $password)

    $stmt = $dbh->query("SELECT * FROM students");

    /* MAGIC HAPPENS HERE */

    $stmt->setFetchMode(PDO::FETCH_INTO, new Student);


    foreach($stmt as $student)
    {
        echo $student->getFullName().'<br />';
    } 

    $dbh = null;
}
catch(PDOException $e)
{
    echo $e->getMessage();
}

Not that I am a ZF lover, but Zend Framework actually makes a pretty awesome usage of PDO in their abstraction. Look into the ZEND_DB_ related classes to see how powerful PDO can be when used in conjunction with a worthy library.

As a bonus, most if not all of this framework's classes run stand-alone or as modular components. I have used many of these in small projects myself, their libs actually are pretty simple to use and well-documented, and very much tried and tested in many environnements.

Happy coding!

Yes, there are many (valid) alternatives to ZF. Thanks for pointing that out. — stefgosselin, Mar 21 '12 at 18:05

What makes PDO safer than normal mysql?

2 Answers2