PHP--PDO prepared statements and stored procedures

Many more mature databases support the concept of prepared statements. What are prepared statements? Think of it as a compiled template of the SQL you want to run, which can be customized using variable parameters. Prepared 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 a query is ready, the database analyzes, compiles, and optimizes the plan for executing the query. This process takes longer for complex queries and can significantly slow down your application if the same query needs to be repeated multiple times with different parameters. By using prepared statements, you can avoid repeated analysis/compile/optimization cycles. Simply put, prepared statements use fewer resources and therefore run faster.

Parameters provided to prepared statements do not need to be enclosed in quotes, the driver will handle them automatically. If your application uses only prepared statements, you can be sure that SQL injection will not occur. (However, if other parts of the query are constructed from unescaped input, there is still a risk of SQL injection).

Prepared statements are so useful that their only feature is that PDO will simulate processing when the driver does not support it. This ensures that applications can use the same data access pattern regardless of whether the database has such capabilities.

Example #1 Use prepared statements for repeated inserts

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(&#39;:name&#39;, $name);
    $stmt->bindParam(&#39;:value&#39;, $value);
    // 插入一行
    $name = &#39;one&#39;;
    $value = 1;
    $stmt->execute();
    // 用不同的值插入另一行
    $name = &#39;two&#39;;
    $value = 2;
    $stmt->execute();
?>

Example #2 Use prepared statements for repeated inserts

Example below Execute an insert query by replacing the ? placeholder with name and value.

<?php
    $stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
    $stmt->bindParam(1, $name);
    $stmt->bindParam(2, $value);
    // 插入一行
    $name = &#39;one&#39;;
    $value = 1;
    $stmt->execute();
    // 用不同的值插入另一行
    $name = &#39;two&#39;;
    $value = 2;
    $stmt->execute();
?>

Example #3 Use prepared statements to obtain data

The following example obtains data based on the provided key value. User input is automatically quoted, so there is no danger of SQL injection attacks.

<?php
    $stmt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?");
    if ($stmt->execute(array($_GET[&#39;name&#39;]))) {
        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 usually used to get values ​​from stored procedures. Output parameters are slightly more complicated 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 a parameter is greater than the recommended length, an error is generated.

Example #4 Calling a stored procedure 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 with both input and output values, and the syntax is 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.

Example #5 Calling a stored procedure with input/output parameters

<?php
    $stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
    $value = &#39;hello&#39;;
    $stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);
    // 调用存储过程
    $stmt->execute();
    print "procedure returned $value\n";
?>

Example #6 Invalid use of placeholders

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