User's Manual

Writing Queries with Bind Variables
Beta Draft Querying Data 4-5
FROM departments
WHERE department_id = :did';
By using bind variables to parameterize SQL statements:
Q The statement is reusable with different input values without needing to change
the code.
Q The query performance is improved through a reduction of the query parse time
in the server, since the Oracle database can reuse parse information from the
previous invocations of the identical query string.
Q There is protection against "SQL Injection" security problems.
Q There is no need to specially handle quotes 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 execute. This process is
known as run-time binding.
To enable you PHP application to use bind variables in the query perform the
following changes to your PHP application code:
1. Edit anyco.php. Modify the query to use a bind variable, create an array to store
the value to be associated with the bind variable, and pass $bindargs to
db_do_query():
<?php // File: anyco.php
...
$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 as the OCI8 layer can determine the length. This is not the case for
bind variables accepting output results from a query.
2. Edit anyco_db.inc. 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:
<?php // File: anyco_db.inc
...
function db_do_query($conn, $statement, $bindvars = array())
{
$stid = oci_parse($conn, $statement);