PHP MySQL preprocessing statement
May 11, 2021 PHP
Table of contents
1. PHP MySQL preprocessing statement
2. Preprocess statements and binding parameters
3. MySQLi preprocessing statement
4. Instance (MySQLi uses preprocessed statements)
PHP MySQL preprocessing statement
Preprocessed statements are useful for preventing MySQL injection.
Preprocess statements and binding parameters
Preprocessed statements are used to execute multiple identical SQL statements and are more efficient.
Preprocessed statements work as follows:
-
Preprocessing: Create a SQL statement template and send it to the database. T he reserved value is marked with the parameter "?" . For example: INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)
-
Database analysis, compilation, query optimization of SQL statement templates, and storage results are not output
-
Execution: Finally, the value of the applied binding is passed to the parameter (the "?" tag) and the database executes the statement. The app can execute statements multiple times if the value of the argument is different.
Preprocessed statements have two main advantages over executing SQL statements directly:
-
Preprocessed statements significantly reduce analysis time and make only one query (although statements are executed multiple times)
-
Binding parameters reduce server bandwidth, and you only need to send the parameters of the query, not the entire statement
-
Preprocessed statements are useful for SQL injection because parameter values are sent using different protocols to ensure the legitimacy of the data.
MySQLi preprocessing statement
The following example uses a preprocessed statement in MySQLi and binds the appropriate parameters:
Instance (MySQLi uses preprocessed statements)
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
Create a connection
$conn = new mysqli($servername, $username, $password, $dbname);
Detect the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
prepare and bind
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES(?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);
Set parameters and execute them
$firstname = "John";
$lastname = "Doe";
$email = "[email protected]";
$stmt->execute();
$firstname = "Mary";
$lastname = "Moe";
$email = "[email protected]";
$stmt->execute();
$firstname = "Julie";
$lastname = "Dooley";
$email = "[email protected]";
$stmt->execute();
echo "New records created successfully";
$stmt->close();
$conn->close();
?>
Resolve each line of code for the following instances:
In sql statements, we use question marks (?), where we can replace them with integers, strings, double floats, and Boolean values.
Next, let's look at bind_param() function:
The function binds the parameters of SQL and tells the value of the database parameters. T he "sss" parameter column handles the data types of the remaining parameters. The s character tells the database that the argument is a string.
There are four types of parameters:
-
i - integer (integer)
-
d - double (double floating-point type)
-
s - string (string)
-
b - BLOB (Boolean value)
Each parameter needs to specify a type.
You can reduce the risk of SQL injection by telling the data types of database parameters.
|
Note: If you want to insert additional data (user input), validation of the data is very important. |
|---|
Preprocessed statements in the PDO
The following examples we use in PDO to preprocess statements and bind parameters:
Instance (PDO uses preprocessed statements)
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";
try {
$conn = new PDO("mysql:host=$servername; dbname=$dbname", $username, $password);
Set the PDO error mode to be an exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
Preprocess SQL and bind parameters
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email)
VALUES (:firstname, :lastname, :email)");
$stmt->bindParam(':firstname', $firstname);
$stmt->bindParam(':lastname', $lastname);
$stmt->bindParam(':email', $email);
Insert the row
$firstname = "John";
$lastname = "Doe";
$email = "[email protected]";
$stmt->execute();
Insert another row
$firstname = "Mary";
$lastname = "Moe";
$email = "[email protected]";
$stmt->execute();
Insert another row
$firstname = "Julie";
$lastname = "Dooley";
$email = "[email protected]";
$stmt->execute();
echo "New records created successfully";
}
catch(PDOException $e)
{
echo $sql . " <br>" . $e->getMessage();
}
$conn = null;
?>