Understanding prepared statements in PHP

Описание: A prepared statement is used to execute the same SQL statement repeatedly with high efficiency and protect against SQL injections. Prepared statement is also called parameterized statement.

Prepared statement consists of two stages, prepare and execute.

At prepare stage a statement template is sent to the database server. The server performs a syntax check and initializes server internal resources for later use.

At execute stage client binds parameter values and sends them to the server. The server executes the statement with the bound values using the previously created internal resources.

We are explaining with an example in PDO approach.

First of all, we need usual four parameters to establish connection with the database such as server name, database name, database user name, and database password.
$servername = "localhost";
$username = "root";
$password = "pass123";
$dbname = "memberdb";

Now, we are going to make the connection with the database, and then write the prepared statement in two stages, prepare and execute. The entire code goes under try and catch block.
try {
// Database connection & Prepared statement goes here.
} catch(PDOException $e) {
echo "Error: " . $e-gt;getMessage();
}

Now, we are connecting with the database set the PDO error mode to exception.
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn-gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Now, we are at the prepare stage. We are preparing SQL and bind parameters. This is a SQL template and this template can be used to repeatedly to execute prepared statement.

See the code lines now. At first line we are preparing a SQL template for adding new records. Look at the VALUES() part. We have used three labels with colon. In the next three lines we are binding these labels with variables.
$stmt = $conn-gt;prepare("INSERT INTO users (firstname, lastname, email) VALUES (:firstname, :lastname, :email)");
$stmt-gt;bindParam(':firstname', $firstname);
$stmt-gt;bindParam(':lastname', $lastname);
$stmt-gt;bindParam(':email', $email);

Now, we are at the execute stage. Here we are actually adding new record using the SQL template we have designed at the prepare stage earlier. We are using the same variable name we have bind with the labels and assign values to these. Finally, we call execute() function to execute the prepared statement. It adds a new record in the database.
$firstname = "John";
$lastname = "Doe";
$email = "[email protected]";
$stmt-gt;execute();

We can use the same bind parameters of SQL prepare template to execute prepared statement and new records multiple times.
$firstname = "Mary";
$lastname = "Moe";
$email = "[email protected]";
$stmt-gt;execute();

We come out of the try and catch block and close the database connection.
$conn = null;

This is the power of prepared statement. Once a SQL statement is prepared, we can use it multiple times to execute with different set of values.