PHP--PDO preprocessing statements and stored procedures

Many more mature databases support the concept of preprocessing statements. What is a preprocessing statement? It can be thought of as a compiled template of the SQL you want to run, and it can be customized using variable parameters. Preprocessing statements can provide two major benefits:

Queries need to be parsed (or preprocessed) only once, but can be executed multiple times with the same or different parameters. When the query is ready, the database parses, compiles, and optimizes the plan for executing the query. For complex queries, this process takes a long time, and if you need to repeat the same query multiple times with different parameters, the process will greatly reduce the speed of your application. By using preprocessing statements, you can avoid repeating the parse/compile/optimize cycle. In short, preprocessing statements consume less resources and thus run faster.

The parameters provided to the preprocessing 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 other parts of the query are built from an escaped input).

Preprocessing statements are so useful that their only feature is that PDO will simulate processing when the driver is not supported. This ensures that the application is able to use the same data access pattern regardless of whether the database has such functionality.

Example #1 repeated insertions with preprocessing statements

The following example executes an insert query by replacing the corresponding named placeholder 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 a line    $name = ' one ';    $value = 1;    $stmt->execute ();    Insert another line with a different value    $name = ' two ';    $value = 2;    $stmt->execute ();? >

Example #2 repeated insertions with preprocessing statements

The following example is replaced by the 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 a line    $name = ' one ';    $value = 1;    $stmt->execute ();    Insert another line with a different value    $name = ' two ';    $value = 2;    $stmt->execute ();? >

Example #3 fetching data using preprocessing statements

The following example gets the data based on the form provided by the key value. The user's 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 database-driven support is supported. Output parameters are typically used to get values from stored procedures. The output parameters are slightly more complicated to use than the input parameters, because when you bind an output parameter, you must know the length of the given parameter. If the value that is bound for the parameter is greater than the recommended length, an error is generated.

Example #4 with output parameters call a stored procedure

<?php    $stmt = $dbh->prepare ("Call sp_returns_string (?)");    $stmt->bindparam (1, $return _value, PDO::P aram_str, 4000);    Call the stored procedure    $stmt->execute ();    Print "procedure returned $return _value\n";? >

You can also specify parameters that have both input and output values, which are similar in syntax 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 Call stored procedure with input/output parameters

<?php    $stmt = $dbh->prepare ("Call sp_takes_string_returns_string (?)");    $value = ' Hello ';    $stmt->bindparam (1, $value, PDO::P aram_str| PDO::P aram_input_output, 4000);    Call the stored procedure    $stmt->execute ();    Print "procedure returned $value \ n";? >

Invalid use of Example #6 placeholders

<?php    $stmt = $dbh->prepare ("select * from REGISTRY where name like '%?% '");    $stmt->execute (Array ($_get[' name '));    The placeholder must be used in the position of the entire value    $stmt = $dbh->prepare ("select * from REGISTRY where name is like?");    $stmt->execute (Array ("%$_get[name]%"));? >

