How to use PDO and Parameter Binding

Parameter binding is essential for protecting your web application from SQL-injection. Pretty much all data which is going to be used in SQL statement needs binding. Binding simply saying is just a way to tell engine that a particular piece of data is a string, number, character and so on. By doing this special characters like quotes and double quotes, semi-colons, etc. won’t be interpreted as commands by the database.

Example:

public function dbSelect($table, $fieldname=null, $id=null)        {
$this->conn();
$sql = "SELECT * FROM `$table` WHERE `$fieldname`=:id";
$stmt = $this->db->prepare($sql);
$stmt->bindParam(':id', $id);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

This simple function returns database record by table name, primary field name and its value. bindParam is used after prepare and before execute (this is important). Question marks also might be used instead of names.

More complex example can include data type:

        public function dbUpdate($table, $fieldname, $value, $pk, $id)    {
            $this->conn();
			$sql = "UPDATE `$table` SET `$fieldname`=:value WHERE `$pk` = :id";
            $stmt = $this->db->prepare($sql);
            $stmt->bindParam(':id', $id, PDO::PARAM_STR);
			$stmt->bindParam(':value', $value, PDO::PARAM_STR);
            $stmt->execute();
        }

Or insertion of associative array with field names and values:

        public function dbInsert($table, $values)   {
            $this->conn();
            $fieldnames = array_keys($values[0]);
            $size = sizeof($fieldnames);
            $i = 1;
            $sql = "INSERT INTO $table";
            $fields = '( ' . implode(' ,', $fieldnames) . ' )';
            $bound = '(:' . implode(', :', $fieldnames) . ' )';
            $sql .= $fields.' VALUES '.$bound;
            $stmt = $this->db->prepare($sql);
            foreach($values as $vals)
            {
                $stmt->execute($vals);
            }
        }

Also there is a wonderful CRUD (Create-Retrieve-Update-Delete) module to start with http://www.phpro.org/classes/PDO-CRUD.html. And a full tutorial on PDO could be found at http://phpro.org/tutorials/Easy-Access-With-PDO-CRUD.html

--
Best Regards,
Azat Mardan
Microsoft MVP | Book and Course Author | Software Engineering Leader
Azat Mardan avatar
https://www.linkedin.com/in/azatm
To contact Azat, the main author of this blog, submit the contact form or schedule a call at clarity.fm/azat and we can go over your bugs, questions and career.

3 thoughts on “How to use PDO and Parameter Binding

  1. Samire

    So I’m doing the exact same thing, and it’s NOT working. I still get a zero. So obulovsiy something is different!function get_tabs_sub($tabID) { global $db; $query = SELECT * FROM tabs_sub WHERE tabID = $tabID ORDER BY subName ; $tab_subs = $db->query($query); return $tab_subs;} .$tab_subs_count = $tab_subs->num_rows; .If I do a foreach loop, I can count’ the rows but that just isn’t the right way to do it!Any ideas?

  2. Shobana

    Excellent little artlcie for the beginner with PDO. One constructive comment is that you may also want to educate users on catching PDOExceptions.In some instances where a connection fails because of a server overload (or other error), the PDO schema is exposed with the username and password intact.try { $db = new PDO(“mysql:host=localhost;dbname=pdo-demo”, $user, $pass);} catch {PDOException $e) { $e->getMessage();}

Leave a Reply

Your email address will not be published. Required fields are marked *