Home » MySql » MySQL Prepared Statement

PHP MySQL Prepared Statement

Summary: In this tutorial, you will learn how to use MySQL prepared statement, and queries execute faster and more secure.

We had studied how to insert multiple records with the help of insert query by separating the values set with a comma but this method is not very efficient. Each time for each set of values the whole query gets parsed by the server and it creates no difference in the execution efficiency of the query but there is a better alternate. The solution is a mysqL prepared statement, more efficient, more secure than the regular insert query execution. Let's see what prepared statements are and how to use it.

What is a Prepared Statement?

A prepared statement (also known as a parameterized statement) is a template of a query with dummy values(placeholders), These dummy values or placeholders will be replaced by actual values at the time of execution and our records will successfully be inserted in the database.

Working of a prepared statement:

A prepared statement has 3 stages:

  • Prepare: A template of a query is created with dummy values (?) and is sent to the database. Example: INSERT INTO student VALUES(?, ?, ?).
  • Parsing: The template received by the database will get parsed, compiled by the database and then will get stored without execution for future use.
  • Execute: When the user sends real values to the database then values get bind to the query at the time of execution. These values will replace the placeholders(?) at the time of execution for multiple times with different values.

Syntax in MySqli and PDO

The use of placeholders in MySQLi and PDO differs a bit, let's see the difference:

MySQLi Syntax:

INSERT INTO students (first_name, last_name, email) VALUES (?, ?, ?);

In the place of values, '?' is used which will get replaced later.

PDO Syntax:

PDO supports two types of placeholders, the (?) character and also named placeholders. The named placeholder although start with a colon (:) followed by an identifier.

Example:

INSERT INTO students (first_name, last_name, email)
VALUES (:first_name, :last_name, :email);

Advantages of a prepared statement:

  • It saves the parsing time for each insert query, as it gets parsed only once but can be executed multiple times with different values.
  • As we only have to send the parameters to the database server and not the whole query each time, it saves a lot of bandwidth.
  • Prepared statements are secure than the usual insert query execution because it can save the database from SQL injections. As we have told you that the values are sent separately to the database and only gets used at the time of execution, it makes the SQL injections useless because the values are not directly embedded in the query but used separately.

The following example will show you how prepared statements actually work:

Prepared Statement using Procedural Method

Let's see how to perform a simple prepared statement in PHP. (Replace HOST, USERNAME, PASSWORD, DATABASE with your database credentials.)

Example

<?php /* MySQL server connection */ $link = mysqli_connect("localhost", "root", "", "demo"); // Check connection if($link === false){ die("ERROR: Could not connect. " . mysqli_connect_error()); } // Prepare an insert statement $sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)"; if($stmt = mysqli_prepare($link, $sql)){ // Bind variables to the prepared statement as parameters mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email); /* Set the parameters values and execute the statement again to insert another row */ $first_name = "Alvaro"; $last_name = "Morte"; $email = "alvaromorte@mail.com"; mysqli_stmt_execute($stmt); /* Set the parameters values and execute the statement to insert a row */ $first_name = "María"; $last_name = "Pedraza"; $email = "maríapedraza@mail.com"; mysqli_stmt_execute($stmt); echo "Records inserted successfully."; } else{ echo "ERROR: Could not prepare query: $sql. " . mysqli_error($link); } // Close statement mysqli_stmt_close($stmt); // Close connection mysqli_close($link); ?>

As shown in the example above, you can insert multiple sets of values but the insert statement is prepared only once and it will also be parsed only once.


Explanation of Code :

The "mysqli_stmt_bind_param()" function is used to bind the placeholders to the variables which will provide values to them. At the time of execution, the values present in these variables will replace the placeholder '?'. The 'sss' string in the function is used to specify the type of values contained by these variables.

In this example, the 'sss' describes the data type 'string', one 's' for each variable sequentially.

The mysqli_stmt_execute() function is used after assigning values to the variables to execute the query with newly defined values.

The different types of data types are described by four characters:

  • b: binary (for the image, PDF file, etc.)
  • d: double (floating-point number)
  • i: integer (integer numbers)
  • s: string (text)

The number of type definition characters and the bind variables should be equal and in the proper sequence.


Prepared Statement Using Object-Oriented Method

Example

<?php /* MySQL server connection */ $mysqli = new mysqli("localhost", "root", "", "demo"); // Check connection if($mysqli === false){ die("ERROR: Could not connect. " . $mysqli->connect_error); } // Prepare an insert statement $sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)"; if($stmt = $mysqli->prepare($sql)){ // Bind variables to the prepared statement as parameters $stmt->bind_param("sss", $first_name, $last_name, $email); /* Set the parameters values and execute the statement again to insert another row */ $first_name = "Alvaro"; $last_name = "Morte"; $email = "alvaromorte@mail.com"; $stmt->execute(); /* Set the parameters values and execute the statement to insert a row */ $first_name = "María"; $last_name = "Pedraza"; $email = "maríapedraza@mail.com"; $stmt->execute(); echo "Records inserted successfully."; } else{ echo "ERROR: Could not prepare query: $sql. " . $mysqli->error; } // Close statement $stmt->close(); // Close connection $mysqli->close(); ?>

Explanation:

In MySQLi object-oriented method, the bind_param() method is used to bind the variables to the placeholders. The values get executed using execute() method in MySqli Object-oriented method.


Prepared Statement Using PDO Method

Example

<?php /* MySQL server connection */ try{ $pdo = new PDO("mysql:host=localhost;dbname=demo", "root", ""); // Set the PDO error mode to exception $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch(PDOException $e){ die("ERROR: Could not connect. " . $e->getMessage()); } // Attempt insert query execution try{ // Prepare an insert statement $sql = "INSERT INTO persons (first_name, last_name, email) VALUES (:first_name, :last_name, :email)"; $stmt = $pdo->prepare($sql); // Bind parameters to statement $stmt->bindParam(':first_name', $first_name, PDO::PARAM_STR); $stmt->bindParam(':last_name', $last_name, PDO::PARAM_STR); $stmt->bindParam(':email', $email, PDO::PARAM_STR); /* Set the parameters values and execute the statement again to insert another row */ $first_name = "Alvaro"; $last_name = "Morte"; $email = "alvaromorte@mail.com"; $stmt->execute(); /* Set the parameters values and execute the statement to insert a row */ $first_name = "María"; $last_name = "Pedraza"; $email = "maríapedraza@mail.com"; $stmt->execute(); echo "Records inserted successfully."; } catch(PDOException $e){ die("ERROR: Could not prepare/execute query: $sql. " . $e->getMessage()); } // Close statement unset($stmt); // Close connection unset($pdo); ?>

In the PDO method, bindParam() function is used to bind the parameters and also notice that it is used separately to bind each variable one by one.












Follow Us: