PHP MySQL Prepared Statements

In this article, we will demonstrate how PHP Mysql prepared statements can be used for developing web applications in order to make them secure and efficient.

During this lecture we will cover the PHP MySQL prepared statements, how to create them, how to execute them, and how to pass parameters to the prepared statements.

Furthermore, we will be discussing the best practices to be followed when using prepared statements for web development.

A prepared statement is a way of executing SQL statements that contain parameters that can be bound to values later on in the execution process.

By using this approach, we are able to protect against SQL injection attacks, which is a common security problem found in web applications.

Table Of Content-

PHP MySQL Prepared Statements:

PHP MySQL Prepared Statements and Bound Parameters

Prepared statements are a type of SQL statement that can be used to repeat the same (or similar) statements in a highly efficient manner.

This is basically how prepared statements work:

Prepare: The first step is to create an SQL statement template that will be sent to the database. There are some values that are left out of the equation, referred to as parameters (labeled “?”). Example: INSERT INTO Users VALUES(?, ?, ?)
Databases parse, compile, and optimize the SQL statement template in order to perform query optimization, and then store the result of each of these without executing it on the server.
Execute: After binding the parameters to the values, the application executes the statement in the database. The application may run the statement as many times as it wants with different values

The main advantages of using prepared statements over executing SQL statements directly are as follows:

Using prepared statements reduces the time taken to parse the query because the preparation for the query is only done once (even though the statement has to be run multiple times).
A bound parameter minimizes the bandwidth that is sent to the server as only the parameters are sent every time, and not the whole query.
There is a great advantage of using prepared statements when it comes to preventing SQL injections, as parameter values that will be transmitted later using a different protocol do not need to be properly escaped. A SQL injection attack cannot occur if the original template for the statement is not derived from an external source of input.

Prepared Statements in MySQLi

Using MySQLi, the following example shows how to use prepared statements and bound parameters:

Example (MySQLi with Prepared Statements):

<?php $servername = "localhost"; $username = "my_user"; $password = "my_pass"; $dbname = "mrx_test_db"; // Creating connection $conn= new mysqli($servername, $username, $password, $dbname); // Checking connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // prepare and bind $stmt = $conn->prepare("INSERT INTO Users (firstname, lastname, email, age, gender) VALUES (?, ?, ?, ?, ?)"); $stmt->bind_param("sssis", $firstname, $lastname, $email, $age, $gender); // set parameters and execute // inserting data 1 $firstname = "Matt"; $lastname = "LeBlanc"; $email = "[email protected]"; $age = 55; $gender = "Male"; $stmt->execute(); // inserting data 2 $firstname = "Matthew"; $lastname = "Perry"; $email = "[email protected]"; $age = 53; $gender = "Male"; $stmt->execute(); // inserting data 3 $firstname = "Jennifer"; $lastname = "Anniston"; $email = "[email protected]"; $age = 54; $gender = "Female"; $stmt->execute();echo "New records added successfully"; $stmt->close(); $conn->close(); ?>

Using the example above, here are some lines of code to explain:

"INSERT INTO Users (firstname, lastname, email, age, gender) VALUES (?, ?, ?, ?, ?)"

If we want to substitute an integer, string, double, or blob value in our SQL, we insert a question mark (?) in the place where we want to insert the value.

Let’s take a look at the bind_param() function now:

$stmt->bind_param("sssis", $firstname, $lastname, $email, $age, $gender);

The purpose of this function is to bind the parameters to the SQL query, so that the database will know what the parameters are.

Listed in the “sssis” argument are the types of data that are contained between parameters. There is an s character in the parameter, which indicates that it is a string.

There are four types of arguments that can be made:

i – integer
d – double
s – string
b – BLOB

For each of the parameters, we need one of these.

This method minimizes the risk of SQL injections by specifying to MySQL what type of data to expect.

Note: In order to insert any data from outside sources (such as user input), it is very important that it needs to be sanitized and validated before it is inserted.

Prepared Statements in PDO

A PDO prepared statement and bound parameters are used in the following example:

Example (PDO with Prepared Statements):

<?php $servername = "localhost"; $username = "my_user"; $password = "my_pass"; $dbname = "mrx_test_db"; try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // Specify the exception error mode in the PDO $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // prepare sql and bind parameters $stmt = $conn->prepare("INSERT INTO Users (firstname, lastname, email, age, gender) VALUES (:firstname, :lastname, :email, :age, :gender)"); $stmt->bindParam(':firstname', $firstname); $stmt->bindParam(':lastname', $lastname); $stmt->bindParam(':email', $email); $stmt->bindParam(':age', $age); $stmt->bindParam(':gender', $gender); // inserting data 1 $firstname = "Matt"; $lastname = "LeBlanc"; $email = "[email protected]"; $age = 55; $gender = "Male"; $stmt->execute(); // inserting data 2 $firstname = "Matthew"; $lastname = "Perry"; $email = "[email protected]"; $age = 53; $gender = "Male"; $stmt->execute(); // inserting data 2 $firstname = "Jennifer"; $lastname = "Anniston"; $email = "[email protected]"; $age = 54; $gender = "Female"; $stmt->execute(); echo "New records added successfully"; } catch (PDOException $e) { echo "Error: " . $e->getMessage(); } $conn = null; ?>

Example Explanation

This is PHP code that displays examples of the use of Php Mysql prepared statements using PDO (PHP Data Objects).

We first establishes a connection to a MySQL database using the PDO class, specifying the server name, username, password, and database name. The code then sets the error mode to ERRMODE_EXCEPTION so that any errors will throw an exception.
Next, a prepared statement is created using the prepare() method of the PDO class. The SQL statement is specified with placeholders for the values that will be inserted into the table. The placeholders are named using a colon followed by a name (:firstname, :lastname, etc.).
After the prepared statement is created, the bindParam() method is used to bind the named placeholders to PHP variables ($firstname, $lastname, etc.) that will hold the values to be inserted into the table.
The code then sets the values of the PHP variables to the data that is to be inserted into the table and calls the execute() method of the prepared statement to insert the data into the table. This process is repeated three times to insert three sets of data into the Users table.
Finally, the code catches any exceptions that occur during the execution of the Php Mysql prepared statement and displays an error message if necessary.

We value your feedback.