PDO Preprocessing Statements and Stored Procedures of PHP

PHP PDO Preprocessing Statements and Stored Procedures

Many more mature databases support the concept of preprocessing statements.

What is a preprocessing statement? Think of it as a compiled template for the SQL you want to run, and it can be customized using variable parameters. Preprocessing statements can bring two major benefits:

Queries only need to be parsed (or preprocessed) once, but can be executed multiple times with the same or different parameters. When the query is ready, the database will analyze, compile and optimize the plan for executing the query. For complex queries, this process takes a long time, and if you need to repeat the same query many times with different parameters, this process will greatly slow down the application. By using preprocessing statements, repeated analysis/compilation/optimization cycles can be avoided. In short, preprocessing statements take up fewer resources and therefore run faster. Parameters supplied to preprocessed statements do not need to be enclosed in quotation marks, and the driver will handle them automatically. If your application uses only preprocessing statements, you can ensure that SQL injection does not occur. (However, there is still a risk of SQL injection if the rest of the query is built with unescaped input).

Preprocessing statements are so useful that their only feature of 1 is that PDO simulates processing when the driver does not support it. This ensures that the application can use the same data access pattern regardless of whether the database has such functionality.

Repeated insertion with preprocessing statements

The following example executes an insert query by replacing the corresponding named placeholders with name and value

<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);
//  Insert 1 Row 
$name = 'one';
$value = 1;
$stmt->execute();
//  Insert another with a different value 1 Row 
$name = 'two';
$value = 2;
$stmt->execute();
?>

Repeated insertion with preprocessing statements

The following example is replaced by name and value? Placeholder to execute an insert query.

<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);
//  Insert 1 Row 
$name = 'one';
$value = 1;
$stmt->execute();
//  Insert another with a different value 1 Row 
$name = 'two';
$value = 2;
$stmt->execute();
?>

Getting data using preprocessing statements

The following example gets the data based on the provided form of the key value. User input is automatically enclosed in quotation marks, so there is no risk of SQL injection attacks.

<?php
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?");
if ($stmt->execute(array($_GET['name']))) {
 while ($row = $stmt->fetch()) {
  print_r($row);
 }
}
?>

Applications can also bind output and input parameters if supported by database drivers. Output parameters are usually used to get values from stored procedures. Output parameters are slightly more complicated to use than input parameters, because when you bind an output parameter, you must know the length of the given parameter. If the value bound for the parameter is larger than the recommended length, an error will be generated.

Calling Stored Procedures with Output Parameters

<?php
$stmt = $dbh->prepare("CALL sp_returns_string(?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000);
//  Calling stored procedures 
$stmt->execute();
print "procedure returned $return_value\n";
?>

You can also specify parameters that have both input and output values, and their syntax is similar to that of 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.

Invoking stored procedures with input/output parameters

<?php
$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
$value = 'hello';
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);
//  Calling stored procedures 
$stmt->execute();
print "procedure returned $value\n";
?>

Invalid use of placeholders

<?php
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE '%?%'");
$stmt->execute(array($_GET['name']));
//  Placeholders must be used in the position of the whole value 
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE ?");
$stmt->execute(array("%$_GET[name]%"));
?>

Summarize