PHP MySQL Prepared Statements

Prepared statements are very useful for preventing MySQL injection.

Prepared Statements and Bound Parameters

Prepared statements are used to execute multiple identical SQL statements, and they execute more efficiently.

The working principle of prepared statements is as follows:

  1. Preparation: Create an SQL statement template and send it to the database. Reserved values are marked with the "?" parameter. For example:

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

  2. The database parses, compiles, and performs query optimization on the SQL statement template, and stores the result without outputting it.

  3. Execution: Finally, the application bound values are passed to the parameters ("?" marks), and the database executes the statement. The application can execute the statement multiple times if the parameter values are different.

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

  • Prepared statements greatly reduce analysis time, as the query is only done once (although the statement is executed multiple times).

  • Bound parameters reduce server bandwidth; you only need to send the parameters of the query, not the entire statement.

  • Prepared statements are very useful against SQL injection, because parameter values are sent using a different protocol, ensuring the legitimacy of the data.

MySQLi Prepared Statements

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

Example (MySQLi using prepared statements)

<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; //Create connection $conn = new mysqli($servername, $username, $password, $dbname); //Check connection if ($conn->connect_error) { die("Connection failed:" . $conn->connect_error); } //Prepare and bind $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(); ?>

Analyze each line of code in the following example:

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

In the SQL statement, we used question marks (?), where we can replace the question marks with integers, strings, double-precision floating-point numbers, and boolean values.

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

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

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

The parameters have the following four types:

  • i - integer
  • d - double
  • s - string
  • b - BLOB (binary large object)

Each parameter needs to specify a type.

By telling the database the data type of the parameters, the risk of SQL injection can be reduced.

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

Prepared Statements in PDO

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

Example (PDO using 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 exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //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); //Insert row $firstname = "John"; $lastname = "Doe"; $email = "john@example.com"; $stmt->execute(); //Insert another row $firstname = "Mary"; $lastname = "Moe"; $email = "mary@example.com"; $stmt->execute(); //Insert another row $firstname = "Julie"; $lastname = "Dooley"; $email = "julie@example.com"; $stmt->execute(); echo "New record inserted successfully"; } catch(PDOException $e) { echo "Error: " . $e->getMessage(); } $conn = null; ?>

Other Extensions