Parameter Binding in PHP – Examples and Discussion

In this article, we are going to look into four basic examples of Parameter Binding in PHP using both MySQLi and PDO. If Parameter Binding does not tell you anything, you may want to check out my previous article where I explained how Parameter Binding can be used for deterring most SQL Injection attacks. Let’s get started!

Creating the MySQLi and PDO Objects

This section explores the creation of MySQLi and PDO objects.

Database Credentials

The following are the dummy credentials we are going to use.

$servername = "localhost";
$username = "user";
$password = "password";
$db = "database";

Creating MySQLi Object

$mysqli = new mysqli($servername, $username, $password, $db);
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

Creating PDO Object

try {
  $pdo = new PDO("mysql:host=$servername;dbname=$db", $username, $password);
} catch(PDOException $e) {
  echo "Error: " . $e->getMessage() . "<br>";
  die();
}

Example 1: Single Parameter Binding

Let’s begin with a simple example of binding a single parameter. Imagine a situation where we fetch a blog post depending on the postID that we receive via HTTP Request.

Using MySQLi

<?php
$post_id = $_REQUEST["id"];
$stmt = $mysqli->prepare("SELECT title, body FROM posts WHERE post_id = ?");
$stmt->bind_param("s", $post_id); // 's' sets datatype as string
$stmt->execute();
$result = $stmt->get_result();
$post = $result->fetch_assoc();
?>

Explanation:

  • The prepare() method accepts a SQL statement as a string. We use question marks to denote a parameter that will be bound later.
  • In the bind_param() method, the first argument is a string indicating the data types of the parameters being bound. The second argument is the variable we want to bind to the parameter. Check the documentation for examples.
  • The get_result() method fetches the rows from the result set.
  • We can use fetch_assoc() to fetch a single row from the result set.

Preventing SQL Injection in PHP with Parameter Binding

Using PDO

<?php
$post_id = $_REQUEST["id"];
$stmt = $pdo->prepare("SELECT title, body FROM posts WHERE post_id = :post_id");
$stmt->bindParam(":post_id", $post_id);
$stmt->execute();
$post = $stmt->fetch(PDO::FETCH_ASSOC);
?>

Explanation:

  • The prepare() method is used to prepare the SQL statement with parameters that are required to be bound.
  • Parameters are bound using a colon. The name of the parameter following the colon is up to you.
  • The bindParam() method treats the parameters as a string by default, but you can mention the datatype also. Check out the documentation.
  • The fetch( PDO::FETCH_ASSOC ) method fetches a single row from the result set in an associative array format.
  • You can also use fetchAll(PDO::FETCH_ASSOC) method to fetch all rows from the result set.

Example 2: Multiple Parameter Binding

For this example, assume that the user can update his/her username and age using a form. The form is submitted using method POST.

Using MySQLi

<?php
$username = $_POST["username"];
$age = $_POST["age"];
$user_id = $_SESSION["user_id"];
$stmt = $mysqli->prepare("UPDATE users SET username = ?, age = ? WHERE user_id = '$user_id'");
$stmt->bind_param("si", $username, $age);
$stmt->execute();
?>

Explanation:

  • Notice that we did not use a parameter for the user_id field since it is coming from the session variable and not the user. Only bind parameters which are dynamic, meaning that they are dynamically provided by the user.
  • Notice how we assigned the data types of username and age variables. An integer is indicated by the letter “i”. More details here.

What is Cross-Site Scripting?

Using PDO

$username = $_POST["username"];
$age = $_POST["age"];
$user_id = $_SESSION["user_id"];
$stmt = $pdo->prepare("UPDATE users SET username = :username, age = :age WHERE user_id = '$user_id'");
$stmt->execute(array(
  "username" => $username,
  "age" => $age
));

Explanation:

  • The prepare() method is used to prepare the dynamic parameters.
  • In PDO, you can send execute() an array of the parameters mapped to their respective variables. Notice we do not use the colons when mapping to the variables in the array.

Example 3: Reusing Query With Parameter Binding

In this example, I will show you how to reuse a single SQL statement to optimize your PHP application. Assume we have a ‘products’ table where the user can insert multiple products’ name and price at once.

Using MySQLi

<?php

$data = array(
  0 => array(
    "name" => "product1",
    "price" => "25"
  ),
  1 => array(
    "name" => "product2",
    "price" => "30"
  ),
);

$stmt = $mysqli->prepare("INSERT INTO products(name, price) VALUES(?, ?)");
$stmt->bind_param("sd", $name, $price);

foreach($data as $product) {
  $name = $product["name"];
  $price = $product["price"];
  $stmt->execute();
}

?>

Explanation:

  • We prepare and bind parameters just like before.
  • Then we use a for each loop to pick each product one by one, bind them to the SQL statement we prepared earlier and execute.

Generating Random Numbers in C++ | Java | Python | JS | PHP

Using PDO

<?php

$data = array(
  0 => array(
    "name" => "product1",
    "price" => "25"
  ),
  1 => array(
    "name" => "product2",
    "price" => "30"
  ),
);

$stmt = $mysqli->prepare("INSERT INTO products(name, price) VALUES(:name, :price)");

foreach($data as $product) {
  $name = $product["name"];
  $price = $product["price"];
  $stmt->execute(array(
    "name" => $name,
    "price" => $price
  ));
}

?>

Example 4: Implementing Basic Search With SQL LIKE

In this last but not the least example, we are going to take a look at how we can use SQL LIKE operator with bound parameters.

Using MySQLi

<?php
$keyword = $_GET['keyword'];
$stmt = $mysqli->prepare('SELECT * FROM products WHERE name LIKE ?');
$keyword = "%".$keyword."%";
$stmt->bind_param('s', $keyword);
$stmt->execute();
$result = $stmt->get_result();

while($res = $result->fetch_assoc()) {
  // use results
}
?>

Explanation:

  • The important thing to note here is that we cannot use %% in the query itself like %?%. We have to modify the keyword by prepending and appending % as and when required.
  • After modifying the keyword we will bind it to the prepared statement.

What are Progressive Web Apps?

Using PDO

<?php
$keyword = $_GET['keyword'];
$stmt = $pdo->prepare('SELECT * FROM products WHERE name LIKE :name');
$keyword = "%" . $keyword . "%";
$stmt->execute(array("name" => $keyword));
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach($result as $res) {
  // use results
}
?>

That’s all folks! We hope you enjoyed our PHP Parameter Binding tutorial. Happy coding! 😎