WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

PHP Prepared Statements


These point helps arranged for maintain a strategic distance from sql injection.


Prepared Statements and Bound Parameters

A arranged proclamation is an element used to execute the equivalent (or comparable) SQL explanations more than once with high efficiency.

Prepared explanations essentially work like this:

  1. Prepare: A SQL explanation format is made and sent to the database. Certain qualities are left unspecified, called parameters (marked "?"). Model: INSERT INTO MyGuests VALUES(?, ?, ?)
  2. The database parses, arranges, and performs question advancement on the SQL explanation format, and stores the outcome without executing it
  3. Execute: sometime in the not too distant future, the application ties the qualities to the parameters, and the database executes the announcement. The application may execute the announcement the same number of times as it needs with various values

Compared to executing SQL articulations specifically, readied explanations have two primary advantages:

  • Prepared proclamations decreases parsing time as the planning on the question is done as it were once (in spite of the fact that the announcement is executed various times)
  • Bound parameters limit data transmission to the server as you need send as it were the parameters each time, and not the entire query
  • Prepared proclamations are exceptionally valuable against SQL infusions, in light of the fact that parameter esteems, which are transmitted later utilizing an alternate convention, need not be effectively gotten away. In the event that the first proclamation layout isn't gotten from outer information, SQL infusion can't occur.

Need for Statements in MySQLi

The following precedent uses arranged proclamations and bound parameters in MySQLi:

Example (MySQLi with Prepared Statements)

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

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

//Check connection
if ($conn->connect_error) {
    die("Connection fizzled: " . $conn->connect_error);
}

//get ready and bind
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) Qualities (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);

//set parameters and execute
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";
$stmt->execute();

$firstname = "Mary";
$lastname = "Moe";
$email = "mary@example.com";
$stmt->execute();

$firstname = "Julie";
$lastname = "Dooley";
$email = "julie@example.com";
$stmt->execute();

echo "New records made successfully";

$stmt->close();
$conn->close();
?>

Code lines to clarify from the model above:

"INSERT INTO MyGuests (name,hometown,school) VALUES (?, ?, ?)"

In our SQL, we embed a question mark (?) where we need to substitute in a whole number, string, twofold or mass value.

Then, examine the bind_param() function:

$stmt->bind_param("sss", $name, $hometown, $school);

This work ties the parameters to the SQL inquiry and tells the database what the parameters are. The "sss" contention records the kinds of information that the parameters are. The s character tells mysql that the parameter is a string.

The contention might be one of four types:

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

We mush utilization of these parameter.

Mysql information can be expcted we limit the danger of SQL injections.


Prepared Statements in PDO

Exaample of PDO:

Example (PDO with 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 mistake mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

   /plan sql and tie parameters
    $stmt = $conn->prepare("INSERT INTO Biograph (name, main residence, school)
    VALUES (:name,:hometown,:school)");
    $stmt->bindParam(':firstname', $firstname);
    $stmt->bindParam(':lastname', $lastname);
    $stmt->bindParam(':email', $email);

   /embed a row
    $firstname = "John";
    $lastname = "Doe";
    $email = "john@example.com";
    $stmt->execute();

    /embed another row
    $firstname = "Mary";
    $lastname = "Moe";
    $email = "mary@example.com";
    $stmt->execute();

   /embed another row
    $firstname = "Julie";
    $lastname = "Dooley";
    $email = "julie@example.com";
    $stmt->execute();

    reverberation "New records made successfully";
    }
catch(PDOException $e)
    {
    reverberation "Error: " . $e->getMessage();
    }
$conn = null;
?>