What is the benefit of prepared statement in PDO in php?

By : Admin  |  Updated On : 27 Feb, 2021

What is the benefit of prepared statement in PDO in php?

Prepared Statement (also known as parameterized statement) is just a SQL query template which contains placeholder instead of the actual values. These placeholders will be replaced by the actual values at the time of execution of the statement.

Sometimes developer writing sql queries using pdo prepare function thinks they are using pdo prepared statement.Its not right always, understant prepared statement by below example.

$pdo = new PDO(...);

//No Prepare
$stmt = $pdo->prepare("SELECT * FROM USER WHERE id=2");
$stmt->execute();

//Prepare
$stmt = $pdo->prepare("SELECT * FROM USER WHERE id=?");
$stmt->execute([2]);

 

Benefits of Prepared statements :-

1. Repeated inserts in db using prepared statements.

Below code example prepared the query statment once in db server and execute again and again.

$stmt = $dbh->prepare("INSERT INTO User (name, email) VALUES (:name, :email)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);
$stmt->execute();

// insert another row with different values
$stmt->bindParam(':name', $name2);
$stmt->bindParam(':email', $email2);
$stmt->execute();

 

2. Fetching data from db using prepared statements

Below code fetches data based on a key value supplied by a user input. The user input is automatically quoted, so there is no risk of a SQL injection attack.

$stmt = $dbh->prepare("SELECT * FROM user where name = ?");
if ($stmt->execute(array($_GET['name']))) {
  while ($row = $stmt->fetch()) {
    print_r($row);
  }
}

 

Understand how hacker can inject in sql query, and also know how prepared query can prevent this sql injection.

Below link has get param with id.

Your URL : http://localhost/test.php?id=1

Hacker URL : http://localhost/test.php?id=1" OR 1="1

$sql = 'select * from user where id="' . $_GET['id'] . '"';
echo $sql;
$res = mysql_query($sql);
while ($row = mysql_fetch_assoc($res)) {
    print_r($row);
}

Above SQL injection can be prevent by the prepared statement like below.

$stmt = $dbh->prepare("SELECT * FROM user where id = ?");
if ($stmt->execute(array($_GET['id']))) {
  while ($row = $stmt->fetch()) {
    print_r($row);
  }
}