YouTip

# PHP PDO Prepared Statements and Stored Procedures [PHP PDO Reference](#) Many more mature databases support the concept of prepared statements. What is a prepared statement? Think of it as a compiled template for the SQL you want to run, which can be customized using variable parameters. Prepared statements offer two major benefits: * The query needs to be parsed (or prepared) only once, but can be executed multiple times with the same or different parameters. When the query is prepared, the database will analyze, compile, and optimize the plan for executing that query. For complex queries, this process can take a significant amount of time. If you need to repeat the same query multiple times with different parameters, this process would greatly slow down the application. By using prepared statements, you avoid the repeated analysis/compilation/optimization cycle. In short, prepared statements use fewer resources and therefore run faster. * The parameters provided to the prepared statement do not need to be quoted; the driver handles this automatically. If the application only uses prepared statements, you can be sure that SQL injection will not occur. (However, if other parts of the query are built from unescaped input, there is still a risk of SQL injection). Prepared statements are so useful that their only characteristic is that PDO will simulate their processing when the driver does not support them. This ensures that the application can use the same data access pattern regardless of whether the database has this feature. ### Repeated Insertion with Prepared Statements The following example executes an INSERT query by replacing the corresponding named placeholders with `name` and `value`. prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)"); $stmt->bindParam(':name', $name); $stmt->bindParam(':value', $value);// Insert a row $name = 'one'; $value = 1; $stmt->execute();// Insert another row with different values $name = 'two'; $value = 2; $stmt->execute();?> ### Repeated Insertion with Prepared Statements The following example executes an INSERT query by replacing the `?` placeholders with `name` and `value`. prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)"); $stmt->bindParam(1, $name); $stmt->bindParam(2, $value);// Insert a row $name = 'one'; $value = 1; $stmt->execute();// Insert another row with different values $name = 'two'; $value = 2; $stmt->execute();?> ### Fetching Data with Prepared Statements The following example fetches data based on a provided key value. User input is automatically quoted, so there is no risk of SQL injection attacks. prepare("SELECT * FROM REGISTRY where name = ?");if ($stmt->execute(array($_GET['name']))) { while ($row = $stmt->fetch()) { print_r($row); }}?> If the database driver supports it, the application can also bind output and input parameters. Output parameters are typically used to retrieve values from stored procedures. Output parameters are slightly more complex to use than input parameters because when binding an output parameter, you must know the length of the given parameter. If the value bound to the parameter is larger than the suggested length, an error will be generated. ### Calling a Stored Procedure with Output Parameters prepare("CALL sp_returns_string(?)"); $stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000); // Call the stored procedure $stmt->execute();print "procedure returned $return_valuen";?> You can also specify parameters that have both input and output values, using syntax similar to output parameters. In the next example, the string "hello" is passed to the stored procedure, and when the stored procedure returns, "hello" is replaced with the value returned by the stored procedure. ### Calling a Stored Procedure with Input/Output Parameters prepare("CALL sp_takes_string_returns_string(?)"); $value = 'hello'; $stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000); // Call the stored procedure $stmt->execute();print "procedure returned $valuen";?> ### Invalid Use of Placeholders prepare("SELECT * FROM REGISTRY where name LIKE '%?%'"); $stmt->execute(array($_GET['name']));// Placeholders must be used in the place of the entire value $stmt = $dbh->pr