❮ Func Cal Cal Info Func Xml Get Current Line Number ❯

PHP `MySQL Prepared Statements`

Prepared statements are very useful for preventing MySQL injection.

Prepared Statements and Bound Parameters

Prepared statements are used to execute the same SQL statements multiple times efficiently.

The working mechanism of prepared statements is as follows:

Preparation: Create an SQL statement template and send it to the database. Reserved values are marked with parameters "?". For example:

INSERT INTO MyGuests (firstname, lastname, email) VALUES(?, ?, ?)

The database parses, compiles, and performs query optimization on the SQL statement template and stores the result without outputting it.

Execution: Finally, pass the bound values to the parameters ("?" marks), and the database executes the statement. The application can execute the statement multiple times if the parameter values are different.

Compared to directly executing SQL statements, prepared statements have two main advantages:

Prepared statements significantly reduce parsing time, as the query is done only once (though the statement is executed multiple times).

Binding parameters reduces server bandwidth, as you only need to send the query parameters, not the entire statement.

Prepared statements are very useful for SQL injection because the parameter values, sent after the query, are used with a different protocol, ensuring data validity.

MySQLi Prepared Statements

The following example uses prepared statements in MySQLi and binds the corresponding parameters:

Example (MySQLi Using Prepared Statements)

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check 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
$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();
?>

Analyzing each line of the example:

"INSERT INTO MyGuests (firstname, lastname, email) VALUES(?, ?, ?)"

In the SQL statement, we use question marks (?) where we can replace them with integers, strings, double precision floating point numbers, and booleans.

Next, let's look at the bind_param() function:

$stmt->bind_param("sss", $firstname, $lastname, $email);

This function binds the SQL parameters and tells the database the values of the parameters. The "sss" parameter lists the data types of the remaining parameters. The 's' character tells the database that the parameter is a string.

There are four types of parameters:

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

Each parameter must be specified with a type.

By telling the database the data types of the parameters, the risk of SQL injection is reduced.

| | Note: Validating the data is very important if you want to insert other data (user input). | | --- | --- |

Prepared Statements in PDO

The following example uses prepared statements and binds parameters in PDO:

Example (PDO Using Prepared Statements)

<?php
$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 exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Prepare 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 a 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 inserted successfully";
}
catch(PDOException $e)
{
echo "Error: " . $e->getMessage();
}
$conn = null;
?>