PHP MySQL preprocessing statements

PhpMySQL preprocessing Statements

Preprocessing statements are useful for preventing MySQL injection.

preprocessing statements and binding parameters

Preprocessing statements are used to execute multiple identical SQL statements and perform more efficiently.

The preprocessing statement works as follows:

1. Preprocessing: Creates a SQL statement template and sends it to the database. The reserved value uses the parameter "?" tag. Example: INSERT into Myguests (firstname, LastName, email) VALUES (?,?,?)
2. Database parsing, compiling, query optimization for SQL statement templates, and storing results without output
3. Execution: Finally, the value of the applied binding is passed to the parameter ("?" tag), and the database executes the statement. The application can execute the statement multiple times if the value of the parameter is different.

Preprocessing statements have two main advantages over direct execution of SQL statements:

• Preprocessing statements greatly reduce the parsing time and only one query (although the statements are executed multiple times)
• Binding parameters Reduce server bandwidth, you only need to send the parameters of the query, not the entire statement
• Preprocessing statements are useful for SQL injection, since parameter values are sent with different protocols to ensure the legitimacy of the data.

MYSQLI preprocessing statements

The following example uses a preprocessing statement in mysqli and binds the corresponding parameters:

Instance (mysqli using a preprocessing statement) <?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "MyDB";

Create a connection
$conn = new Mysqli ($servername, $username, $password, $dbname);

detecting connections
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 perform
$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 ();
?>

Parse each line of code for the following instance:

"INSERT into Myguests (firstname, LastName, email) VALUES (?,?,?)"

In the SQL statement, we used the question mark (?), where we can replace the question mark with an integer, a string, a double-precision float, and a Boolean value.

Next, let's take a look at the Bind_param () function:

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

The function binds the arguments to the SQL and tells the values of the database parameters. The SSS parameter column handles the data type of the remaining parameters. The S character tells the database that the parameter is a string.

The following four types of parameters are available:

• I-integer (integral type)
• D-double (double-precision floating-point type)
• S-string (String)
• B-blob (Boolean)

Each parameter requires a specified type.

By telling the data type of a database parameter, you can reduce the risk of SQL injection.

	Note: If you want to insert additional data (user input), validation of the data is very important.

Pre-processing statements in PDO

The following example we used a preprocessing statement in PDO and bound parameters:

Instance (PDO uses preprocessing statements) <?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "Mydbpdo";

try {
$conn = new PDO ("mysql:host= $servername;d bname= $dbname", $username, $password);
Set PDO error mode to exception
$conn->setattribute (Pdo::attr_errmode, pdo::errmode_exception);

preprocessing SQL and binding 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 Row
$firstname = "John";
$lastname = "Doe";
$email = "[email protected]";
$stmt->execute ();

inserting additional rows
$firstname = "Mary";
$lastname = "Moe";
$email = "[email protected]";
$stmt->execute ();

inserting additional rows
$firstname = "Julie";
$lastname = "Dooley";
$email = "[email protected]";
$stmt->execute ();

echo "New Records created successfully";
}
catch (Pdoexception $e)
{
Echo $sql. "<br>". $e->getmessage ();
}
$conn = null;
?>

PHP MySQL preprocessing statements