PHP PDO Prepared Statements with Example
Updated on
...
22nd August 2023
A layer of database abstraction known as PHP Data Objects (PDO) offers a standardized way to interact with databases. By separating the query from the data being provided to it, prepared statements in PDO are a security feature that helps prevent SQL injection attacks by separating the query from the data being passed to it.
Key Features of PDO Prepared Statements in PHP:
- SQL Injection Prevention: Prepared statements offer a significant layer of protection against SQL injection attacks. The parameters passed to the prepared statement are automatically sanitized and escaped by the database driver, making it much harder for malicious users to manipulate the query.
- Improved Performance: PDO-prepared statements can be cached by the database server, which means that if you execute the same or similar queries multiple times with different parameter values, the database can reuse the execution plan, leading to improved performance.
- Parameter Binding: With prepared statements, you can easily bind parameters to placeholders in your SQL queries. This allows you to separate the data from the query itself, making the code cleaner and easier to manage.
- Reusability: Once you prepare a statement, you can reuse it with different parameter values without needing to re-parse and re-optimize the query. This can be especially beneficial in applications where the same query is executed frequently with varying parameters.
- Security and Validation: Prepared statements help enforce proper data types for the bound parameters, preventing unexpected type conversions. This can be useful for validating data before it's sent to the database.
- Readable Queries: Since the SQL query and the data are kept separate, the actual query becomes more readable and less cluttered with variable substitutions and concatenations.
- Database Abstraction: PDO provides a consistent interface to various databases, which means you can use the same prepared statement syntax regardless of the underlying database system. This makes your code more portable.
- Error Handling: PDO provides detailed error messages and exceptions that can help you diagnose and troubleshoot issues in your queries more effectively.
- Placeholder Flexibility: Prepared statements support both named and positional placeholders, giving you the flexibility to choose the style that fits your coding preferences.
- Database Driver Independence: PDO allows you to switch between different database drivers without changing your code significantly, as long as you stick to standard SQL and PDO methods.
Overall, PDO prepared statements offer a secure and efficient way to interact with databases in PHP applications while providing a level of abstraction that promotes good coding practices and helps prevent common pitfalls like SQL injection.
Database Connection:
First, establish a connection to your database using PDO:
$server = 'localhost';
$user = 'root';
$pass = '';
$db = 'php_pdo';
try {
$dbconn = new PDO("mysql:host=$server; dbname=$db", $user, $pass);
$dbconn ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
Select and run the foreach loop to display data
<?php
try {
$server = 'localhost';
$user = 'root';
$pass = '';
$db = 'php_pdo';
$dbconn = new PDO("mysql:host=$server; dbname=$db", $user, $pass);
$select_query = "SELECT * FROM students";
$stmt = $dbconn->prepare($select_query);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_OBJ);
// echo "<pre>";
// print_r($result);
foreach ($result as $row) {
echo 'name :' . $row->name . "<br>";
echo 'email :' . $row->email . "<br>";
}
} catch (PDOException $e) {
echo 'Error: ' . $e->getMessage();
}
Select and display the data using while loop
<?php
try {
$server = 'localhost';
$user = 'root';
$pass = '';
$db = 'php_pdo';
$dbconn = new PDO("mysql:host=$server; dbname=$db", $user, $pass);
$select_query = "SELECT * FROM students";
$stmt = $dbconn->prepare($select_query);
$stmt->execute();
//using while loop
while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {
echo 'Name: ' . $row->name . "<br>";
echo 'Email: ' . $row->email . "<br>";
echo "<br>";
}
} catch (PDOException $e) {
echo 'Error: ' . $e->getMessage();
}
Named Placeholder(Parameter) vs Positional parameter:
Named Placeholder(Parameter):
Named parameters are placeholders in SQL queries that are identified by a name instead of a question mark (?). They allow you to bind values to placeholders using associative arrays, making your code more readable and self-explanatory. Named parameters are particularly useful when dealing with complex queries with multiple parameters.
<?php
try {
$server = 'localhost';
$user = 'root';
$pass = '';
$db = 'php_pdo';
$dbconn = new PDO("mysql:host=$server; dbname=$db", $user, $pass);
// set the PDO error mode to exception
$dbconn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $dbconn->prepare("SELECT * FROM students WHERE name =:name AND gender =:gender");
$stmt->execute(['name' => 'abdul', 'gender' => 'male']);
$result = $stmt->fetchAll();
echo "<pre>";
print_r($result);
} catch (PDOException $e) {
echo 'Error:' . $e->getMessage();
}
using bindParam()
<?php
try {
$server = 'localhost';
$user = 'root';
$pass = '';
$db = 'php_pdo';
$dbconn = new PDO("mysql:host=$server; dbname=$db", $user, $pass);
// Set the PDO error mode to exception
$dbconn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $dbconn->prepare("SELECT * FROM students WHERE name = :name AND gender = :gender");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':gender', $gender);
$name = 'abdul';
$gender = 'male';
$stmt->execute();
$result = $stmt->fetchAll();
echo "<pre>";
print_r($result);
} catch (PDOException $e) {
echo 'Error: ' . $e->getMessage();
}
Positional parameter:
$stmt = $dbconn->prepare("SELECT * FROM students WHERE name = ? AND gender = ?");
$stmt->execute(['abdul', 'male']);
$result = $stmt->fetchAll();
echo "<pre>";
print_r($result);
Using bindParam()
$stmt = $dbconn->prepare("SELECT * FROM students WHERE name = ? AND gender = ?");
$name = 'abdul';
$gender = 'male';
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $gender);
$stmt->execute();
$result = $stmt->fetchAll();
echo "<pre>";
print_r($result);
Insert values using a prepared statement
$insert_query = "insert into students(name, email,phone,gender) values (:name, :email, :phone, :gender)";
$stmt = $dbconn->prepare($insert_query);
$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':phone', $phone);
$stmt->bindParam(':gender', $gender);
$name = "Rani";
$email = "[email protected]";
$phone = "8585858585";
$gender = "male";
$stmt->execute();
Insert Using Associative array
$insert_query = "insert into students(name, email,phone,gender) values (:name, :email, :phone, :gender)";
$stmt = $dbconn->prepare($insert_query);
$name = "Jon";
$email = "[email protected]";
$phone = "8585858585";
$gender = "male";
$stmt->execute([':name' => $name, ':email' => $email, ':phone' => $phone, ':gender' => $gender]);
Update values using a prepared statement
$update_query = "update students set name =:name, email=:email where id=:id";
$stmt = $dbconn->prepare($update_query);
$name = "Jon";
$email = "[email protected]";
$id = 4;
$stmt->execute([':name' => $name, ':email' => $email, ':id' => $id]);
Delete row using a prepared statement
<?php
try {
$server = 'localhost';
$user = 'root';
$pass = '';
$db = 'php_pdo';
$dbconn = new PDO("mysql:host=$server; dbname=$db", $user, $pass);
$update_query = "delete from students where id=:id";
$stmt = $dbconn->prepare($update_query);
$id = 4;
$stmt->execute([':id' => $id]);
} catch (PDOException $e) {
echo 'Error: ' . $e->getMessage();
}
