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
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?
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();}
You have a good Blog right here Mate. Love your content incredibly informative, Please keep up the very good work.