PHP MySQL Prepared Statements

Prepared statements are extremely useful in the fight against SQL injections.

A prepared statement is a feature that allows you to quickly execute the same (or similar) SQL commands again and over again.

The basic operation of prepared statements is as follows :

1.prepare:The database receives a SQL statement template that is constructed and transmitted to it. Parameters (labeled "?") are values for which no value is supplied. Example : INSERT INTO MyGuests VALUES(?, ?, ?).

2. The database parses, compiles, and optimizes the SQL statement template before storing the results without running it.

3. Execute: The program binds to the arguments at a later point, and the database executes the declaration. The application can execute the statement as often as it wishes with various values In comparison with the direct implementation of SQL statements, the prepared statements have three key advantages :

• Prepared statements speed up parsing because the query is only prepared once (Despite the fact that the statement is repeated).
• Bound parameters save server traffic by requiring only the parameters to be sent each time rather than the entire query.
• Because parameter values that are transferred later using a separate protocol do not need to be adequately encoded, prepared statements are extremely beneficial against SQL injections. SQL injection isn't possible if the initial statement template isn't obtained from outside data.

We established an empty table called "Employee" with three columns : "id", "name", and "city".

Look at the table structure:

In MySQLi, the following example shows how to use prepared statements with bound parameters.

In this example, we are going to add two new records into "Employee" table:

Example (MySQLi Object Oriented with Prepared Statements)

<?php
// Create connection
$conn = new mysqli("localhost", "username", "password", "myDB");
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
// prepare and bind
$stmt = $conn->prepare("INSERT INTO Employee (id, name, city) VALUES (?, ?, ?)");
$stmt->bind_param("iss", $id, $name, $city);
// set parameters and execute
// First record
$id = "1";
$name = "ABC";
$city = "Mumbai";
$stmt->execute();
// Second record
$id = "2";
$name = "XY";
$city = "Tokyo";
$stmt->execute();
echo "2 new records created successfully";
$stmt->close();
$conn->close();
?>

Output:-

After executing the above program the "Employee" table look like:

Code lines to explain from the example above :

"INSERT INTO Employee (id, name, city) VALUES (?, ?, ?)"

We use a question mark (?) in SQL to replace an integer, text, double, or blob value.

Then, have a look at the bind_param() function :

$stmt->bind_param("iss", $id, $name, $city);

This function connects the SQL question mark parameters to notify the database what their parameters are. The "iss" display lists data types which are the parameters. Mysql says the parameter is a string, according to a character.

The argument may be one of four types :

• i - integer
• d - double
• s - string
• b - BLOB

For each parameter, we have to have one of them.

By notifying MySQL what kind of data we may expect, the possibility of SQL injections is minimized.

Note :- It's highly vital to sanitize and validate the data if we wish to insert any data from external sources (such as input from the user).

Prepared statements and bound parameters are used in the following example in PDO :

Example (PDO with Prepared Statements)

<?php
try {
  $conn = new PDO("mysql:host=localhost;dbname=myDB", "root", "");
  // prepare sql and bind parameters
  $stmt = $conn->prepare("INSERT INTO Employee (name, city) VALUES (:name, :city)");
  $stmt->bindParam(':name', $name);
  $stmt->bindParam(':city', $city);
  // set parameters and execute
  // First record
  $name = "ABC";
  $city = "Mumbai";
  $stmt->execute();
  // Second record
  $name = "XY";
  $city = "Tokyo";
  $stmt->execute();
  echo "2 new records created successfully";
} catch(PDOException $e) {
  echo "Error: " . $e->getMessage();
}
$conn = null;
?>