Its for helpful.....Writing Queries with Bind Variables

Using queries with values included in the WHERE clause may be useful for some situations. However, if the conditional values in the query are likely to change it is not appropriate to encode a value into the query. Oracle recommends that you use bind variables.

A bind variable is a symbolic name preceded by a colon in the query that acts as a placeholder for literal values. For example, the query string created in the anyco.php file could be rewritten with the bind variable :did as follows:

$query =

 'SELECT   department_id, department_name, manager_id, location_id

  FROM     departments

  WHERE    department_id = :did';

By using bind variables to parameterize SQL statements:

• The statement is reusable with different input values without needing to change the code.

• The query performance is improved through a reduction of the query parse time in the server, because the Oracle database can reuse parse information from the previous invocations of the identical query string.

• There is protection against "SQL Injection" security problems.

• There is no need to specially handle quotation marks in user input.

When a query uses a bind variable, the PHP code must associate an actual value with each bind variable (placeholder) used in the query before it is executed. This process is known as run-time binding.

To enable your PHP application to use bind variables in the query, perform the following changes to your PHP application code:

1. Edit the anyco.php file. Modify the query to use a bind variable, create an array to store the value to be associated with the bind variable, and pass the $bindargs array to the db_do_query() function:

   ...
   
   
   
   $query =
   
   'SELECT   department_id, department_name, manager_id, location_id
   
   FROM     departments
   
   WHERE    department_id = :did';
   
   
   
   $bindargs = array();
   
   // In the $bindargs array add an array containing
   
   // the bind variable name used in the query, its value, a length
   
   array_push($bindargs, array('DID', 80, -1));
   
   
   
   $conn = db_connect();
   
   $dept = db_do_query($conn, $query, $bindargs);
   
   
   
   ...
   
   ?>
   
   
   
   

   In this example, the bind variable, called DID, is an input argument in the parameterized query, and it is associated with the value 80. Later, the value of the bind variable will be dynamically determined. In addition, the length component is passed as -1 so that the OCI8 layer can determine the length. If the bind variable was used to return output from the database an explicit size would be required.

2. Edit the anyco_db.inc file. Modify the db_do_query() function to accept a $bindvars array variable as a third parameter. Call the oci_bind_by_name() OCI8 call to associate the PHP values supplied in $bindvars parameter with bind variables in the query. The function becomes:

   function db_do_query($conn, $statement, $bindvars = array())
   
   {
   
    $stid = oci_parse($conn, $statement);
   
    if (!$stid) {
   
      db_error($conn, __FILE__, __LINE__);
   
    }
   
   
   
     // Bind the PHP values to query bind parameters
   
     foreach ($bindvars as $b) {
   
       // create local variable with caller specified bind value
   
       $$b[0] = $b[1];  
   
       // oci_bind_by_name(resource, bv_name, php_variable, length)
   
       $r = oci_bind_by_name($stid, ":$b[0]", $$b[0], $b[2]); 
   
       if (!$r) {
   
         db_error($stid, __FILE__, __LINE__);
   
       }
   
     }
   
    $r = oci_execute($stid, OCI_DEFAULT);
   
    if (!$r) {
   
          db_error($stid, __FILE__, __LINE__);
   
    }
   
    $r = oci_fetch_all($stid, $results, null, null,
   
                           OCI_FETCHSTATEMENT_BY_ROW);
   
    return($results);
   
   }
   
   
   
   

   The binding is performed in the foreach loop before the oci_execute() is done.

   For each entry in $bindvars array, the first element contains the query bind variable name that is used to create a PHP variable of the same name; that is, $$b[0] takes the value DID in $b[0] and forms a PHP variable called $DID whose value is assigned from the second element in the entry.

   The oci_bind_by_name() function accepts four parameters: the $stid as the resource, a string representing the bind variable name in the query derived from the first element in the array entry, the PHP variable containing the value to be associated with the bind variable, and the length of the input value.

3. To test the results of the preceding modifications, save the anyco.php and anyco_db.inc files and enter the following URL:

   On Windows:

   http://localhost/chap4/anyco.php
   
   
   
   

   On Linux:

   http://localhost/~/chap4/anyco.php
   
   
   
   

   The page returned in the browser window should resemble the following page:

   
   Description of the illustration chap4_db_connect_003.gif