Prepared Statements are a useful too for preventing SQL Injection. Instead of building an SQL string to be evaluated by the database, a database statement is prepared first. This statement contains the query string but with placeholders for any dynamic data. It is similar to defining a function with arguments for inputs. When the data is added to the statement, it must go into a pre-defined spot and must match the data type specified (string, integer, etc.) The effect is to maintain a strict separation between the SQL control and the SQL data. This makes it impossible for an attacker to get "control code" out of the data area into the statement.
An example using prepared statements in PHP.
<?php
// prepare the statement with ? placeholder
$sql = "SELECT * FROM products WHERE name=?";
$stmt = mysqli_prepare($connection, $sql);
// Bind the value to the placeholder
// The type declaration is "s" for string.
$name = 'Blue shirt';
mysqli_stmt_bind_param($stmt, "s", $name);
// Execute the statement
mysqli_stmt_execute($stmt);
// Bind the result so they can be accessed
mysqli_stmt_bind_result($stmt, $products);
// Retrieve and work with the results
$products = mysqli_stmt_fetch($stmt);
foreach($products as $product) {
// ...
}
?>An object-oriented example of prepared statements in PHP.
<?php // create the object $mysqli = new mysqli("localhost", "user", "pwd", "db");
// prepare the statement with ? placeholder $sql = "SELECT * FROM products WHERE name=?"; $stmt = $mysqli->prepare($sql);
// Bind the value to the placeholder // The type declaration is "s" for string. $name = 'Blue shirt'; $bind_result = $stmt->bind_param("s", $name);
// Execute the statement $execute_result = $stmt->execute();
// Bind the result so they can be accessed $stmt->bind_result($products);
// Retrieve and work with the results $products = $stmt->fetch(); foreach($products as $product) { // ... } ?>
