PHP Prepared Statements read this for website protections

Posted at August 14, 2018 677 Views . 0 Saved .

Prepared Statements and Bound Parameters 

A prepared statement is a method, or feature used in performing the same work procedure php programming is doing, but on a more secure version.

Why Prepared Statement in Php


Ever since php built websites has been facing a lot of challenges from sql attacks, prepared statement has been the only feature you can use to avoid being hacked with sql injections.

So you need to know this very well.

How Does Prepared Statement Works

Php prepared statement work on parameters. to trick the statement not to execute immediately the function is called.

We want to insert a data inside our database, and we don't want a simple code to be triggered into our website database.

Now, let us take for example, we have an input field and we want to make a sign-up system where a users username and password will be stored inside our database.



Now, for example the user inserted a SQL statement to destroy our database. Like ' DROP table

Now that user try's to drop our table, so to prevent this we need to look for a way to make the input field have no effect on our website database.

Then that is where sql parameters comes in.

We'll  say from our php mysqli file. 

The prepared statement we are using will be in two ways, the procedure ways and the advanced way.

Prepared Statement The Procedure Ways

  • /* Attempt MySQL server connection. Assuming you are running MySQL
  • server with default setting (user 'root' with no password) */
  • $link = mysqli_connect("localhost", "root", "", "demo");
  • // Check connection
  • if($link === false){
  •     die("ERROR: Could not connect. " . mysqli_connect_error());
  • }
  • // Prepare an insert statement
  • $sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
  • if($stmt = mysqli_prepare($link, $sql)){
  •     // Bind variables to the prepared statement as parameters
  •     mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email);
  •     /* Set the parameters values and execute
  •     the statement again to insert another row */
  •     $first_name = "Hermione";
  •     $last_name = "Granger";
  •     $email = "";
  •     mysqli_stmt_execute($stmt);
  •     /* Set the parameters values and execute
  •     the statement to insert a row */
  •     $first_name = "Ron";
  •     $last_name = "Weasley";
  •     $email = "";
  •     mysqli_stmt_execute($stmt);
  •     echo "Records inserted successfully.";
  • } else{
  •     echo "ERROR: Could not prepare query: $sql. " . mysqli_error($link);
  • }
  • // Close statement
  • mysqli_stmt_close($stmt);
  • // Close connection
  • mysqli_close($link);
  • ?>

The Advanced Way

  • $name = 'Emmanuel';
  • $password = sha1('password');
  • $sql = "INSERT INTO users (name,password) VALUES (?,?);
  • $sql->bind_param("ss",$name,$password);
  • $sql->execute();

  • Prepared Statements in PDO

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

    Example (PDO with Prepared 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 exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        // insert a row
        $firstname = "John";
        $lastname = "Doe";
        $email = "";
        // 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);

    if($stmt) {
        echo "New records created successfully";
    catch(PDOException $e)
        echo "Error: " . $e->getMessage();
    $conn = null;
    //from w3schools 

    Read More:

    Rizwan Khan
    Aweome article bro
    Thanks bro