PHP--PDO preprocessing statements and stored procedures

PDO at the PHP database abstraction layer-preprocessing statements and stored procedures many more mature databases support the concept of preprocessing statements. What is a pre-processing statement? You can think of it as a compiled template for the SQL statement you want to run. it can be customized using variable parameters. Preprocessing statements can bring two 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 ready, the database will analyze, compile, and optimize the plan for executing the query. For complex queries, this process takes a long time. if you need to repeat the same query multiple times with different parameters, this process will greatly reduce the speed of the application. By using preprocessing statements, you can avoid repeated analysis, compilation, and optimization cycles. In short, preprocessing statements consume less resources and therefore run faster.

Parameters provided to the pre-processing statement do not need to be enclosed in quotation marks, and the driver will automatically process them. If the application only uses preprocessing statements, it can ensure that SQL injection is not performed. (However, if the other part of the query is constructed by unescaped input, there is still a risk of SQL injection ).

Preprocessing statements are so useful that their only feature is that PDO will simulate processing when the driver does not support them. This ensures that the application can use the same data access mode regardless of whether the database has such a function.

Example #1 use a pre-processing statement for repeated inserts

The following example uses name and value to replace the corresponding name placeholder to execute an insert query.

 Prepare ("insert into registry (name, value) VALUES (: name,: value)"); $ stmt-> bindParam (': name', $ name ); $ stmt-> bindParam (': value', $ value); // Insert a row $ name = 'one'; $ value = 1; $ stmt-> execute (); // Insert another row with different values $ name = 'two'; $ value = 2; $ stmt-> execute ();?>

Example #2 use a pre-processing statement for repeated inserts

In the following example, we use name and value to replace? To execute an insert query.

 Prepare ("insert into registry (name, value) VALUES (?, ?) "); $ Stmt-> bindParam (1, $ name); $ stmt-> bindParam (2, $ value); // Insert a row $ name = 'one '; $ value = 1; $ stmt-> execute (); // Insert another row with different values $ name = 'two'; $ value = 2; $ stmt-> execute ();?>

Example #3 use a preprocessing statement to obtain data

The following example shows how to obtain the data in the form provided by the key value. User input is automatically enclosed in quotation marks, so there is no risk of SQL injection attacks.

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

If the database driver supports this, the application can also bind the output and input parameters. the output parameters are usually used to obtain values from the stored procedure. The use of output parameters is a little more complex than that of input parameters, because the length of a given parameter must be known when an output parameter is bound. If the value bound to a parameter is greater than the recommended length, an error is returned.

Example #4 call the stored procedure with output parameters

 Prepare ("CALL sp_returns_string (?) "); $ Stmt-> bindParam (1, $ return_value, PDO: PARAM_STR, 4000); // call the stored procedure $ stmt-> execute (); print "procedure returned $ return_value \ n";?>

You can also specify parameters with both input and output values. The syntax is similar to that of 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 value returned by the stored procedure.

Example #5 call the stored procedure with input/output parameters

 Prepare ("CALL sp_takes_string_returns_string (?) "); $ Value = 'hello'; $ stmt-> bindParam (1, $ value, PDO: PARAM_STR | PDO: PARAM_INPUT_OUTPUT, 4000 ); // call the stored procedure $ stmt-> execute (); print "procedure returned $ value \ n";?>

Example #6 The placeholder is invalid

 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 LIKE? "); $ Stmt-> execute (array (" % $ _ GET [name] % ");?>