prepared parameterized query with PDO

Question

New to this new and secure way of handling SQL's in PHP and MySql driven web based application, to secure the code from SQL injections. I am planning to start using mysqli with PDO. Can anyone please outline how should i get started and proceed.

Any reference to any article will also be helpful.

Thanks in advance.

Answer 1

Take a look at this previous SO question:

http://stackoverflow.com/questions/1290975/how-to-create-a-secure-mysql-prepared-statement-in-php/

Though the accepted answer was a mysqli one, there are also answers for PDO.

Answer 2

To create the connection

try {
    $db = new PDO("mysql:dbname=".DB_NAME.";host=".DB_HOST,DB_USER,DB_PWD);
} catch (PDOException $e) {
    die("Database Connection Failed: " . $e->getMessage());
}

Then to prepare a statement

$prep = $db->prepare("SELECT * FROM `users` WHERE userid = ':id'");

As you can see, you label each parameter you'd like by prefixing any string with ':'. Then all you do is pass an array mapping the parameter (:id) to the value when you execute.

if (!$prep->execute(array(":id" => $userinput))) {
   $error = $prep->errorInfo();
   echo "Error: {$error[2]}"; // element 2 has the string text of the error
} else {
   while ($row = $prep->fetch(PDO::FETCH_ASSOC)) { // check the documentation for the other options here
        // do stuff, $row is an associative array, the keys are the field names
   }
}

Instead of PDO::FETCH_ASSOC with the "fetch" function, there are various other ways to get your data. You can use fetchAll to get an array of ALL the results at once instead of just going row by row. Or you can get the array of information as a 0-indexed array, or you can even fetch the results directly into a class instance (if the field names line up with the properties of the class.)

All the documentation of PDO can be found here: PHP.net PDO Manual