A prepared statement is a feature designed to execute the same or similar SQL statements efficiently multiple times.
Here’s how prepared statements work:
Prepare: An SQL statement template with unspecified values (parameters labeled as ?) is created and sent to the database. For example: INSERT INTO MyGuests VALUES(?, ?, ?). The database parses, compiles, and optimizes the SQL statement template but does not execute it yet.
Execute: Later, the application binds specific values to the parameters, and the database executes the statement. The application can execute the statement multiple times with different values.
Reduced Parsing Time: The SQL statement is parsed, compiled, and optimized only once, even if the statement is executed multiple times.
Minimized Bandwidth: Only the parameter values are sent to the server each time, rather than the entire query.
Improved Security: Prepared statements protect against SQL injection because parameter values are handled separately and do not need to be escaped if the statement template is not derived from external input.
The following example demonstrates the use of prepared statements and bound parameters in MySQLi:
| <?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 = “[email protected]”; $stmt->execute(); $firstname = “Mary”; $lastname = “Moe”; $email = “[email protected]”; $stmt->execute(); $firstname = “Julie”; $lastname = “Dooley”; $email = “[email protected]”; $stmt->execute(); echo “New records created successfully”; $stmt->close(); $conn->close(); ?> |
Code lines explained from the example above:
| “INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)” |
In our SQL statement, we use a question mark (?) as a placeholder for where we want to substitute integer, string, double, or blob values.
Next, let’s examine the bind_param() function:
| $stmt->bind_param(“sss”, $firstname, $lastname, $email); |
This function binds parameters to the SQL query and specifies their types. The "sss" argument indicates the data types of the parameters, where each s denotes a string.
The possible data types are:
Each parameter must have a corresponding type. By specifying the data types, we reduce the risk of SQL injection attacks.
| Note: When inserting data from external sources (such as user input), it is crucial to sanitize and validate the data. |
The following example demonstrates the use of prepared statements and bound parameters in PDO:
| <?php $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); // 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 a row $firstname = “John”; $lastname = “Doe”; $email = “[email protected]”; $stmt->execute(); // insert another row $firstname = “Mary”; $lastname = “Moe”; $email = “[email protected]”; $stmt->execute(); // insert another row $firstname = “Julie”; $lastname = “Dooley”; $email = “[email protected]”; $stmt->execute(); echo “New records created successfully”; } catch(PDOException $e) { echo “Error: “ . $e->getMessage(); } $conn = null; ?> |