PHP PDO prepared statements and stored procedures

PHP PDO Reference Manual

Many more mature databases support the concept of prepared statements.

What is prepared statements? Think of it as a compiled template you want to run SQL, which can be customized using variable parameters. Prepared statements offer two major benefits:

• Query only resolves (or pretreatment) once, but can be executed multiple times with the same or different parameters. When the query is ready, the database will analyze, compile and execute the query optimization plan. For complex queries, this process takes a long time, if you need to be repeated many times with different parameters the same query, then the process will greatly reduce the speed of the application. By using the prepared statement avoid repeating analyze / compile / optimize cycle. In short, prepared statements use less resources and thus run faster.
• Provide parameters to prepared statements do not need to use quotation marks, the driver will be handled automatically. If the application only uses prepared statements, we can ensure that no SQL injection. (However, if other parts of the query is not escaped from the input to build, then there is still the risk of SQL injection).

Prepared statements so useful that they only feature is the analog processing when the driver does not support the PDO. This ensures that regardless of whether the database has a function, you can ensure that applications can use the same data access patterns.

PreparedStatements repeated insertion

The following example by substituting the appropriate name placeholder with name and value to execute an insert query

<?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();
?>

PreparedStatements repeated insertion

The following example by substituting name and value? Placeholders to execute an insert query.

<?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();
?>

Get data using prepared statements

The following example in the form of key-based access to data already provided. 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);
  }
}
?>

If the database driver support, application can also bind input and output parameters Output parameters are typically used to obtain values ​​from the stored procedure. Output parameters to use than input parameters to be slightly more complicated, because when binding an output parameter, you must know the parameters of a given length. If the value for the parameter is greater than the binding recommendations of the length, it will generate an error.

Calling a stored procedure with an output parameter

<?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 both input and output values ​​of the parameters, the syntax is similar to output parameters. In the next example, the string "hello" is passed to the stored procedure when the stored procedure returns, hello is replaced with the return value of the stored procedure.

With input / output parameters to call a stored procedure

<?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