PHP MySQL Prepared Statements


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


Prepared Statements and Bound Parameters

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.
You can also search for these topics, php mysql prepared and bound statement, php mysql prepared statement, php mysql bound parametrs, php mysql prepared statement execute, prepared statement select using php mysql.

Prepared Statements in MySQLi

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

Look at the table structure:

Column Name Datatype
id int
name text
city text

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:-

2 new records created successfully;

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

ID Name City
1 ABC Mumbai
2 XY Tokyo

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 in PDO

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;
?>
You can also search for these topics, php mysql prepared statement mysqli procedural, php mysql prepared statement last insert id, prepared statement in mysqli example, php mysql prepared statement pdo example, php mysqli PDO with Prepared Statements, php MySQLi with Prepared Statements.