PHP PDO Prepared Statements and Stored Procedures

PHP PDO Reference Manual

Many more mature databases support the concept of prepared statements.

What is a prepared statement? You can think of it as a compiled template of the SQL you want to run, which can be customized with variable parameters. Prepared statements bring two major benefits:

• The query only needs to be parsed (or preprocessed) 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 the query. For complex queries, this process can take a long time. If the same query needs to be repeated many times with different parameters, this process will greatly slow down the application. By using prepared statements, you can avoid repeated analyze/compile/optimize cycles. In short, prepared statements consume fewer resources and therefore run faster.
• Parameters provided to prepared statements do not need to be enclosed in quotes; the driver handles this automatically. If an 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 feature is that PDO will emulate them when the driver does not support them. This ensures that regardless of whether the database has such functionality, the application can use the same data access pattern.

Performing repeated inserts using prepared statements

The following example executes an insert query by substituting 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);

// 插入一行
$name = 'one';
$value = 1;
$stmt->execute();

//  用不同的值插入另一行
$name = 'two';
$value = 2;
$stmt->execute();
?>

Performing repeated inserts using prepared statements

The following example executes an insert query by replacing the ? placeholders with name and value.

<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);

// 插入一行
$name = 'one';
$value = 1;
$stmt->execute();

// 用不同的值插入另一行
$name = 'two';
$value = 2;
$stmt->execute();
?>

Fetching data using prepared statements

The following example fetches data based on key values provided. The user's input is automatically enclosed in quotes, so there is no danger 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);
  }
}
?>

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 stored procedures with output parameters

<?php
$stmt = $dbh->prepare("CALL sp_returns_string(?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000); 

// 调用存储过程
$stmt->execute();

print "procedure returned $return_value\n";
?>

You can also specify parameters that have both input and output values, with 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 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); 

// 调用存储过程
$stmt->execute();

print "procedure returned $value\n";
?>

Invalid use of placeholder

<?php
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE '%?%'");
$stmt->execute(array($_GET['name']));

// 占位符必须被用在整个值的位置
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE ?");
$stmt->execute(array("%$_GET[name]%"));
?>

PHP PDO Reference Manual