There’s one big flaw in our previous MySQL insert statement: it’s open to SQL Injection Attacks. Prepared statements virtually eliminate that concern. You tell the SQL server what you’re intending to do and then you give it the data. For example, you can tell the server that you’re planning on inserting data. If you send a delete statement disguised in your insert statement, that doesn’t make sense and will not have the same kind of effect that it would without prepared statements.

https://blog.devgenius.io/php-p85-mysql-insert-51a9e3bfedd3

https://medium.com/geekculture/cybersecurity-p9-sql-injections-471de647e8d7

Recap

Our Author class had an insert statement that accepted data and inserted it into the authors table.

<?php
namespace Authors;

require_once("DB.php");

use Database\DB;

class Author
{
    public function insert( array $data )
    {
        $sql = "INSERT INTO authors (first_name, last_name, email) 
                VALUES (
                        '" . $data['first_name'] . "', 
                        '" . $data['last_name'] . "', 
                        '" . $data['email'] .  "')";

        $db = new DB;
        $status = $db->insert($sql);
        $db->close();

        echo $status;
    }
}

Prepared Statement

Let’s see the anatomy of the prepared insert statement.

INSERT INTO authors (first_name, last_name, email) VALUES (?, ?, ?)

Those question marks aren’t accidental. This is the syntax for the prepared statement. It’s saying that we have three fields, first_name, last_name, and email, and each of those fields will receive some data. It’s important to have three question marks to signify that there will be data bound to those three fields.

The steps are easy:

• Connect to the database
• Prepare the query
• Bind variables (data) to the query
• Execute the query
• Close the connection

<?php
namespace Authors;

require_once("DB.php");

use Database\DB;

class Author
{
    public function insert( array $data )
    {
        $sql = "INSERT INTO authors (first_name, last_name, email) 
                VALUES (?, ?, ?)";

        $db = new DB;
        $mysqli = $db->_connection;

        $statement = $mysqli->prepare( $sql );
        $statement->bind_param("sss", $data['first_name'], $data['last_name'], $data['email']);
        $statement->execute();

        $statement->close();
        $db->close();
    }
}

Let’s digest it one line of code at a time.

• The $data array is passed as an argument. The $data array has the following three keys: first_name, $last_name, and $email.
• The $sql statement is created that will be prepared. It’s just a string at this point. It’s just for some clarity and separation from the code below so that it doesn’t become too busy.
• Instantiate the DB object. The DB object instantiates the mysqli object and stores it in the $_connection property. We’re going to grab that and store it locally in our $mysqli variable.
• The prepare method is part of the mysqli object. We’re going to pass it the SQL string so that it can prepare it. Once it’s prepared, it returns a new object, and we’re going to store that object as a statement.
• It is within this statement object that we can use the bind_param method. The bind_param method does not belong to the mysqli object. Be careful of that trap.
• The first argument that the bind_param method expects is the type of variable that will be bound. Since we’re binding 3 variables, it needs to know what are the types. sss indicates that there will be three strings that are bound.
• The next arguments are the variables that will be bound. This is the data that is sent through the $data array.
• Finally, we call the execute method that’s part of the statement object.
• We now have two objects that are “open.” We need to close the statement object and the $db object. We can do that through the $db object since we have a close() method that states $db->_connection->close();.

The Full Code

Below you can find the three files that interact with each other. The insert method in our DB class can be removed since we’re using prepared statements now. I’ll keep it in for now, but will probably remove it eventually.

<?php
require_once("Author.php");

use Authors\Author;

$data = [
    "first_name" => "Sam",
    "last_name"  => "Johnson",
    "email"      => "sam@example.com"
];

$author = new Author;
$author->insert($data);

<?php
namespace Authors;

require_once("DB.php");

use Database\DB;

class Author
{
    public function insert( array $data )
    {
        $sql = "INSERT INTO authors (first_name, last_name, email) 
                VALUES (?, ?, ?)";

        $db = new DB;
        $mysqli = $db->_connection;

        $statement = $mysqli->prepare( $sql );
        $statement->bind_param("sss", $data['first_name'], $data['last_name'], $data['email']);
        $statement->execute();

        $statement->close();
        $db->close();
    }
}