PHP Prepared Statements

Preprocessed statements are very useful for preventing MySQL injection.

Prepared statements and binding parameters

Prepared statements are used to execute multiple identical SQL statements and are more efficient.

The prepared statement works as follows:

  1. Preprocessing: Create a SQL statement template and send it to the database. Reserved valuesb are marked with the parameter "?". For example:

      INSERT INTO   MyGuests     (  firstname  ,   lastname  ,   email  )  < span class = "pln"> VALUES   (?,    ?, < /span>   ?)  
  2. Database analysis, compilation, query optimization on SQL statement templates, and storage of results without output.

  3. Execute: Finally, pass the value of the application binding to the parameter ("?" tag), and the database executes the statement. The application can execute the statement multiple times if the values ​​of the parameters are different.

Compared to executing SQL statements directly, prepared statements have two main advantages:

  • Prepared statements greatly reduce analysis time, and only one query is made (although the statement is executed multiple times).

  • Binding parameters reduces server bandwidth, you only need to send the parameters of the query, not the entire statement.

  • Prepared statements are very useful for SQL injection, because the parameter values are sent using different protocols to ensure the legality of the data.

MySQLi prepared statements

The following example uses prepared statements in MySQLi and binds the corresponding parameters:

Example (MySQLi uses prepared statements)

<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Detect connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // Preprocessing and binding $stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)"); $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 record inserted successfully"; $stmt->close(); $conn->close(); ?>

Parse each line of code for the following examples:

"INSERT INTO MyGuests (firstname, lastname, email) VALUES (?,?,?)"

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

Next, let's look at the bind_param () function:

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

This function binds SQL parameters and tells the database parameter values. The "sss" parameter column handles the data types of the remaining parameters. The s character tells the database that the parameter is a string.

There are four types of parameters:

  • i-integer (integer)
  • d-double (double precision floating point)
  • s- string
  • b-BLOB (binary large object: binary large object)

Each parameter requires a type.

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

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

Preprocessed statements in PDO

In the following example, we use prepared statements and bind parameters in PDO:

Example (PDO uses prepared statements)

<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDBPDO"; try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // Set PDO error mode to abnormal $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Preprocess 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); // Insert row $firstname = "John"; $lastname = "Doe"; $email = "john@example.com"; $stmt->execute(); // Insert another line $firstname = "Mary"; $lastname = "Moe"; $email = "mary@example.com"; $stmt->execute(); // Insert another line $firstname = "Julie"; $lastname = "Dooley"; $email = "julie@example.com"; $stmt->execute(); echo "New record inserted successfully"; } catch(PDOException $e) { echo "Error: " . $e->getMessage(); } $conn = null; ?>