PHP Prepared SQL Statements

When you are collecting user input on a HTML form that will be used in an SQL statement, a user can enter in SQL language that modifies your statement, which can do real damage to your database. This is called a SQL injection attack and it can be prevented by using prepared statements.

PHP Prepared SQL Statements, often referred to as prepared statements or parameterized queries, are used to execute the same (or similar) SQL statements repeatedly with high efficiency. They work by preparing (or compiling) an SQL statement template and then binding parameters to it.

Here's an example of a regular SQL statement:

INSERT INTO table_name (column1, column2, column3) VALUES ($value1, $value2, $value3)

Here's an example of a prepared SQL statement:

prepare("INSERT INTO table_name (column1, column2, column3) VALUES (?, ?, ?)");
bind_param("sss", $value1, $value2, $value3);

Both of these examples use variables to store the values used in the SQL statements. In example 1, no work is done to verify that the variable data has been sanitized or validated. In example 2, the SQL statement has been created and stored, then the variable data is bound to the placeholder parameters. The "sss" argument is the data type for each of the variables specified, so in this instance, each of the varaibles are of string type. There are four different data types: i-integer, d-double, s-string, and b-BLOB. This process limits the ability of a user to perform a SQL injection.

The prepared statement can be broken down into three parts: prepare, bind, and execute.

Prepare: The SQL statement template is created and sent to the database. The database parses, compiles, and performs query optimization on it.

Bind: Any parameters (values) you want to include in the query are sent to the database. These parameters are treated as placeholders in the SQL statement.

Execute: The database executes the statement. The parameter values you've provided are then passed separately. This allows the database to distinguish the SQL code from the data being sent.

Using prepared statements helps to prevent SQL injection attacks because parameter values are never concatenated into the SQL string.

Insert, Update, Select, Delete, And Like Prepared Statements

Now that we are aware of prepared statements, how do we use them? Here are some examples:

SETUP DB CONNECTION

//DB CONNECTION VARIABLES
  $servername = '127.0.0.1';
  $username = 'enteryourDBusrernamehere';
  $password = 'enteryourDBpasswordhere';
  $dbname = 'TestDB';
  $SQLAdmin   = 'mr.jeff.m.doyle@gmail.com';
//Connect to SQL
  $conn = new mysqli($servername, $username, $password, $dbname);
//Check for Errors
  if ($conn->connect_error) {
    die("Error Connecting to DB");
  }

Insert

$sql = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
  $sql->bind_param("si", $name, $age);
  $sql->execute();
  $sql->close();

Update

$sql = $mysqli->prepare("UPDATE WEBSERVER.SysMessages SET MessText = ?, AlertEn = ? WHERE MessType= ?");
  $sql->bind_param("sis", $MessText, $AlertEn, $AlertTyp);
  $sql->execute();
  $sql->close();

Select

$sql = $mysqli->prepare("SELECT * FROM myTable WHERE name = ?");
  $sql->bind_param("s", $name);
  $sql->execute();
  $sql->close();

Delete

$sql = $mysqli->prepare("DELETE FROM myTable WHERE id = ?");
  $sql->bind_param("i", $id);
  $sql->execute();
  $sql->close();

Like

$sql = $mysqli->prepare("SELECT * FROM CustomerInfo WHERE $Column LIKE ?");
  $sql->bind_param('s', $Search);
  $sql->execute();
  $sql->close();