PDO (PHP Data Object) পর্ব ২: Prepared Statement

Prepared Statement কি?

Database Management System এ Prepared Statement হচ্ছে অনেকটা SQL Query Template এর মত। যা SQL query এর মাধ্যমে বিভিন্ন Parameter এর actual Value এর পরিবর্তে ভিন্ন ভিন্ন Placeholder ( “?” or :placeHolderName ) দিয়ে Database এ পাঠায়। পরবর্তীতে Database Management System তখন SQL Query Template টির উপর Parse, Compile, Syntax Check এবং Query Optimization করে এবং Execute ব্যতীত ফলাফলটি পরবর্তীতে ব্যবহারের জন্য সংরক্ষণ করে। পরবর্তীতে application টি value গুলোকে Parameter এ Bind করে, এবং DBMS স্টেটমেন্টটিকে Execute করে। Application টি statement কে প্রত্যেকবার ভিন্ন ভিন্ন value দ্বারা যতবার ইচ্ছে execute করতে পারে।

Prepared Statement ব্যবহার করলে কি সুবিধা পাওয়া যাবে?

• Prepared statement পার্সিং টাইমকে কমিয়ে দেয় কারন এটি query একবারই execute করে (যদিও স্টেটমেন্টটি অসংখ্যবার execute করা যায়)। ব্যাপারটা অনেকটা প্রতিদিন বাজার করার জন্য ঘর থেকে বাহির হওয়ার দরকার নাই, ঘরে বসে online এ অর্ডার দিলেই সেই পণ্যটি ঘরেই চলে আসবে , আর এতে সময় এবং শ্রম দুইটাই বাঁচবে।
• Bind parameter গুলো সার্ভারের Bandwidth load কমিয়ে দেয়, যেহেতু আপনাকে শুধু Parameter গুলোকেই বার বার পাঠাতে হয়, সম্পূর্ণ query পাঠাতে হয়না।
• Prepared statement গুলো SQL Injection এর বিরুদ্ধে খুবই কার্যকর ভূমিকা রাখে। SQL Injection হলো এক ধরণের Database Injection কোড যা Database এ আক্রমণ ও তথ্য চুরির কাজে ব্যবহার করা হয়। আর Prepared Statement এর প্যারামিটারের value গুলি যেহেতু সরাসরি বহিরাগত input থেকে আসেনা যা পরবর্তীতে একটি ভিন্ন প্রোটোকল ব্যবহার করে প্রেরন করা হয়। যেহেতু মূল SQL এর value গুলো সরাসরি বহিরাগত input থেকে পাওয়া যায় না। তাই SQL Injection ঘটতে পারে না।

https://blog.w3programmers.com/php-course/

কি ধরনের Database Operation এ Prepared Statement ব্যবহৃত হয়?

মোটামুটি Database এ Data Insert, Read, Update, Delete Operation সহ সব ধরনের অপারেশন এ আপনি Prepared Statement ব্যবহার করতে পারেন। চলুন একটার পর একটা করে দেখা যাক :

Prepared Statement এর সব গুলো ব্যবহার বুঝার জন্য আমরা একটা Database তৈরী করব । আর এর জন্য আপনাকে নিচের কোডটিতে আপনার Database Username এবং Password টি দিয়ে execute করলেই হবে।

<?php
$dsn="mysql:host=localhost";
$user="root";
$pass="";
try {
    $dbh = new PDO($dsn,$user,$pass);
    $dbh->query('CREATE DATABASE IF NOT EXISTS testDB') or die("Database Create Fail!");
    echo "Database Create Success";
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}
?>

Result

Database Create Success

এবার আপনি phpmyadmin এ গিয়ে চেক করে দেখতে পারেন, আপনার database তৈরী হয়েছে কিনা ?

আপনার Database তৈরী হয়ে থাকলে, এবার আমরা একটা Table তৈরী করব এবং Table এ কিছু Data Insert করব। আর এর জন্য আপনাকে নিচের কোডটিতে আপনার Database Username, Password এবং dsn এ localhost এর পর সেমিকোলন (;) দিয়ে Database এর নামটি সঠিকভাবে দিয়ে execute করলেই হবে। নিচের কোডটি দেখুন :

<?php
$dsn="mysql:host=localhost;dbname=testDB";
$user="root";
$pass="";
$sql=<<<"table"
CREATE TABLE IF NOT EXISTS `students` ( 
	`id` INT NOT NULL AUTO_INCREMENT , 
	`name` VARCHAR(20) NOT NULL , 
	`email` VARCHAR(100) NOT NULL , 
	`mobile` VARCHAR(20) NOT NULL , 
	PRIMARY KEY (`id`(4))) 
ENGINE = InnoDB;
INSERT INTO `students` (`id`, `name`, `email`, `mobile`) VALUES 
		(NULL, 'Noman Khan', 'noman@mail.com', '01899009988'), 
		(NULL, 'Sahin Khan', 'sahin@mail.com', '01900887766'), 
		(NULL, 'Firoj Khan', 'firon@mail.com', '01900998877'), 
		(NULL, 'Rashed Khan', 'rashed@test.com', '01566778899'), 
		(NULL, 'Sohag Khan', 'sohag@mail.com0', '1788993344')
table;
try {
    $dbh = new PDO($dsn,$user,$pass);
    $dbh->query($sql) or die("fail");
    echo "Table Create Success";
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}
?>

PHP PDO Prepared Statement এ bindParam,bindValue এবং bindColumn function এর কাজ কি?

bindColumn function

PHP Prepared Statement এ SQL statement এর মধ্যে Column name অথবা Column Number এর বিপরীতে যেকোনো variable কে bind করার জন্য bindColumn() function ব্যবহৃত হয়।

<?php
function readData($dbh) {
  $sql = 'SELECT name, colour, calories FROM fruit';
  try {
    $stmt = $dbh->prepare($sql);
    $stmt->execute();

    /* Bind by column number */
    $stmt->bindColumn(1, $name);
    $stmt->bindColumn(2, $colour);
    
    /* Bind by column name */
    $stmt->bindColumn('calories', $cals);

    while ($row = $stmt->fetch(PDO::FETCH_BOUND)) {
      $data = $name . "\t" . $colour . "\t" . $cals . "\n";
      print $data;
    }
  }
  catch (PDOException $e) {
    print $e->getMessage();
  }
}
readData($dbh);
?>

উপরের কোডটির Output আসবে অনেকটা নিচের মতো করে :

apple	red	150
banana	yellow	175
kiwi	green	75
orange	orange	150
mango	red	200
strawberry	red	25

bindParam() Function

PHP Prepared Statement এ SQL statement এর মধ্যে Name placeholder অথবা Question Mark (“?”) Placeholder এর বিপরীতে যেকোনো variable কে bind করার জন্য bindParam() function ব্যবহৃত হয়।

bindValue() Function

PHP Prepared Statement এ SQL statement এর মধ্যে Name placeholder অথবা Question Mark (“?”) Placeholder এর বিপরীতে যেকোনো value কে bind করার জন্য bindValue() function ব্যবহৃত হয়।

PHP PDO তে bindParam এবং bindValue এর মধ্যে কোনো পার্থক্য আছে?

যদিও bindValue এবং bindParam function দুটির কাজ হচ্ছে SQL statement এর মধ্যে Name placeholder অথবা Question Mark (“?”) Placeholder এর বিপরীতে যেকোনো variable কে bind করা , কিন্তু খুবই গুরুত্তপূর্ণ একটা পার্থক্য আছে, আর তা হচ্ছে, bindValue শুধু variable এর value কে bind করে, অনেকটা hard copy বা static এর মতো, অর্থাৎ , একবার যদি PDO Prepared Statement এ value bind হয়ে যায় , তাহলে variable এর মান পরিবর্তন হলেও SQL Statement এর আউটপুট এ কোনো পরিবর্তন আসবেনা।
অন্যদিকে bindParam function টি Variable এর reference কে bind করে। অর্থাৎ ,PDO Prepared Statement এ Variable এর value bind হয়ে যাওয়ার পর যদি আবার variable এর মানে কোনো পরিবর্তন আসে , তাহলে SQL Statement এর ফলাফলেও পরিবর্তন আসবে।

bindParam function দিয়ে উদাহরণ:

$sex = 'male';
$s = $dbh->prepare('SELECT name FROM students WHERE sex = :sex');
$s->bindParam(':sex', $sex); // use bindParam to bind the variable
$sex = 'female';
$s->execute(); // executed with WHERE sex = 'female'

bindValue function দিয়ে উদাহরণ:

$sex = 'male';
$s = $dbh->prepare('SELECT name FROM students WHERE sex = :sex');
$s->bindValue(':sex', $sex); // use bindValue to bind the variable's value
$sex = 'female';
$s->execute(); // executed with WHERE sex = 'male'

Insert Operation এ placeholder name দিয়ে Prepared statement এর ব্যবহার

INSERT Operation এ placeholder name দিয়ে Prepared Statement ব্যবহার করতে চাইলে, আপনাকে PDO Class এর prepare mehtod এর মধ্যে INSERT Query টি লিখতে হবে এবং INSERT Query এর মধ্যে Value গুলোর পরিবর্তে placeholder name দিতে হবে। তারপর আবার PDOStatement class এর bindParam Method দিয়ে প্রত্যেকটি placeholder এর বিপরীতে value গুলোকে bind করে দিতে হবে। নিচের উদাহরণটি লক্ষ্য করুন :

নোট: PDO Class এর query,prepare method call করার পর pdoStatement Object তৈরী হয়।

<?php
$dsn="mysql:host=localhost;dbname=testDB";
$user="root";
$pass="";
try {
    $dbh = new PDO($dsn,$user,$pass);
    $stmt = $dbh->prepare("INSERT INTO students (name, email, mobile) VALUES (:name, :email, :mobile)");
	$stmt->bindParam(':name', $name);
	$stmt->bindParam(':email', $email);
	$stmt->bindParam(':mobile', $mobile);
    // insert one row
	$name = 'Habibur Rahman';
	$email = "habib@mail.com";
	$mobile = "01899887766";
	$stmt->execute();
	echo "Insert First Row Success<br>";
// insert another row with different values
	$name = 'Tareq Rahman';
	$email = "tareq@mail.com";
	$mobile = "01659887766";
	$stmt->execute();
	echo "Insert Second Row Scuccess";
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}
?>

Result:

Insert First Row Success
Insert Second Row Scuccess

https://blog.w3programmers.com/php-course/

ব্যাখ্যা:লাইন নম্বর ৭ এ লক্ষ্য করুন আমরা INSERT Query এর মধ্যে সরাসরি value না লিখে placeholder Name দিয়ে Query টি কে Prepare করে নিয়েছি, তারপর লাইন নম্বর ৮,৯,১০ এ placeholder গুলোতে variable গুলোকে bind করে দিয়েছি। তারপর আমরা ১৫ এবং ২১ নম্বর লাইনে আমরা নতুন নতুন value assign করে execute করি। আর এতে সুবিধা হচ্ছে আমাদের প্রত্যেক বার SQL টি call করা লাগছেনা , শুধু value পাঠিয়ে দিলেই হচ্ছে।

Insert Operation এ (“?“) দিয়ে Prepared statement এর ব্যবহার

INSERT Operation এ (“?“) দিয়ে Prepared Statement ব্যবহার করতে চাইলে, আপনাকে PDO Class এর prepare mehtod এর মধ্যে INSERT SQL টি লিখতে হবে এবং INSERT SQL এর মধ্যে Value গুলোর পরিবর্তে (“?“) দিতে হবে। তারপর আবার PDOStatement class এর bindParam Method দিয়ে প্রত্যেকটি placeholder Position Number এর বিপরীতে value গুলোকে bind করে দিতে হবে। নিচের উদাহরণটি লক্ষ্য করুন :

<?php
$dsn="mysql:host=localhost;dbname=testDB";
$user="root";
$pass="";
try {
    $dbh = new PDO($dsn,$user,$pass);
    $stmt = $dbh->prepare("INSERT INTO students (name, email, mobile) VALUES (?,?,?)");
	$stmt->bindParam(1, $name);
	$stmt->bindParam(2, $email);
	$stmt->bindParam(3, $mobile);
    // insert one row
	$name = 'Habibur Rahman';
	$email = "habib@mail.com";
	$mobile = "01899887766";
	$stmt->execute();
	echo "Insert First Row Success<br>";
// insert another row with different values
	$name = 'Tareq Rahman';
	$email = "tareq@mail.com";
	$mobile = "01659887766";
	$stmt->execute();
	echo "Insert Second Row Scuccess";
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}
?>

Result:

Insert First Row Success
Insert Second Row Scuccess

ব্যাখ্যা:লাইন নম্বর ৭ এ লক্ষ্য করুন আমরা INSERT Query এর মধ্যে সরাসরি value না লিখে (“?“) দিয়ে Query টি কে Prepare করে নিয়েছি, তারপর লাইন নম্বর ৮,৯,১০ এ placeholder গুলোতে variable গুলোকে bind করে দিয়েছি। তারপর আমরা ১৫ এবং ২১ নম্বর লাইনে আমরা নতুন নতুন value assign করে execute করি। আর এতে সুবিধা হচ্ছে আমাদের প্রত্যেক বার SQL টি call করা লাগছেনা , শুধু value পাঠিয়ে দিলেই হচ্ছে।

SELECT Operation এ placeholder name দিয়ে Prepared statement এর ব্যবহার

SELECT Operation এ placeholder name দিয়ে Prepared Statement ব্যবহার করতে চাইলে, আপনাকে PDO Class এর prepare method এর মধ্যে SELECT Query টি লিখতে হবে এবং SELECT Query এর মধ্যে WHERE condition এর Value গুলোর পরিবর্তে placeholder name দিতে হবে। তারপর আবার PDOStatement class এর bindParam Method দিয়ে প্রত্যেকটি placeholder এর বিপরীতে value গুলোকে bind করে দিতে হবে। নিচের উদাহরণটি লক্ষ্য করুন :

<?php
$dsn="mysql:host=localhost;dbname=testDB";
$user="root";
$pass="";
try {
    $dbh = new PDO($dsn,$user,$pass);
    $stmt = $dbh->prepare("SELECT * FROM students WHERE id=:id");
	$stmt->bindParam("id",$id);
    // insert one row
	$id = 2;
	$stmt->execute();
	echo "<pre>";
	print_r($stmt->fetch(PDO::FETCH_ASSOC));
	echo "</pre>";
	} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}
?>

Result

Array
(
    [id] => 2
    [name] => Sahin Khan
    [email] => sahin@mail.com
    [mobile] => 01900887766
)

Select Operation এ (“?“) দিয়ে Prepared statement এর ব্যবহার

SELECT Operation এ (“?“) দিয়ে Prepared Statement ব্যবহার করতে চাইলে, আপনাকে PDO Class এর prepare mehtod এর মধ্যে QUERY টি লিখতে হবে এবং SELECT QUERY এর মধ্যে Value গুলোর পরিবর্তে (“?“) দিতে হবে। তারপর আবার PDOStatement class এর bindParam Method দিয়ে প্রত্যেকটি placeholder Position Number এর বিপরীতে value গুলোকে bind করে দিতে হবে। নিচের উদাহরণটি লক্ষ্য করুন :

<?php
$dsn="mysql:host=localhost;dbname=testDB";
$user="root";
$pass="";
try {
    $dbh = new PDO($dsn,$user,$pass);
    $stmt = $dbh->prepare("SELECT * FROM students WHERE id=?");
	$stmt->bindParam(1,$id);
    // insert one row
	$id = 2;
	$stmt->execute();
	echo "<pre>";
	print_r($stmt->fetch(PDO::FETCH_ASSOC));
	echo "</pre>";
	} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}
?>

Result

Array
(
    [id] => 2
    [name] => Sahin Khan
    [email] => sahin@mail.com
    [mobile] => 01900887766
)