CODEWITHSUNDEEP
mysqlpdosql-injection
TryHarder
TryHarder

Reputation: 2777

What makes PDO safer than normal mysql?

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;  
}

Upvotes: 1

Views: 442

Answers (2)

Stephane Gosselin
Stephane Gosselin

Reputation: 9148

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!

Upvotes: 1

ceejayoz
ceejayoz

Reputation: 180065

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.

Upvotes: 5

Related Questions