The Oracle + PHP Cookbook

Binding Variables in Oracle and PHP
by Larry Ullman

Improve the speed and security of your Oracle-driven PHP applications by binding variables.

Downloads for this article:
 Oracle Database 10g
 Oracle Instant Client
 Oracle JDeveloper PHP Extension
 Zend Core for Oracle

As you are certainly aware, the majority of today's Web sites rely upon databases in one way or another. Whether the site you are building requires a forum, an e-commerce component, contains tons of articles and information, or just takes feedback from visitors, you'll probably incorporate a database in some manner. While databases are great and often imperative, using them affects (and normally impairs) two common Web application problems: performance and security. Learning when and how to bind variables in PHP will go a long way towards improving both areas.

If you've done any benchmarking on your Web projects, you know that the database interactions are normally the most demanding processes. When you run any query on a database, Oracle must first parse the query to ensure that it's syntactically correct, prior to actually executing the query. This is true, even if you run several similar queries:

SELECT * FROM movies WHERE movie_id=1
SELECT * FROM movies WHERE movie_id=26
SELECT * FROM movies WHERE movie_id=5689
Although the only difference between these three queries is which exact record is being fetched, Oracle will treat them separately, parsing each before execution. The first benefit of binding variables is that Oracle will only need to parse the query once, regardless of how many times it is executed with different values. This change in a script's methodology can be a tremendous performance booster.

The second issue you constantly face as a Web developer is site security. This issue manifests in numerous ways and is nothing short of a never-ending, but critical, battle. In a database-driven site, many queries rely upon external values, such as those submitted by a user from a form, passed to the page in the URL, and so forth. Such queries are vulnerable to SQL injection attacks. (A "SQL injection attack" is when a malicious user supplies invalid data to a PHP script in an attempt to break a query.) If database queries are not handled properly, the malicious user could learn something about the script, the database, or the server from the resulting error messages. Take, for example, a query like this:

SELECT * FROM movies WHERE movie_id=$_GET['id']
Hopefully you are cringing at the sight of that query, considering how insecure it is. All a user has to do is change the URL from, say, http://www.example.com/movie.php?id=23 to http://www.example.com/movie.php?id=HaHa! in order to wreak havoc. Of course all data used in a query should be validated, but any time a variable is used in a query, you introduce the potential for errors should the variable's value be other than what was expected. Because a bound variable is separate from the actual query, the possibility of an SQL injection attack is greatly reduced.

In this "Oracle+PHP Cookbook" HowTo, you will learn how to bind variables when executing Oracle queries from a PHP script. By applying the following techniques and code examples to your own Web applications, you can easily improve their performance and security.

Background/Overview

To demonstrate using bound variables, a real-world example is best. The application in question, which I worked on some years ago, allowed golf professionals to establish what tee times were available at their golf course and at what price. For example, they might say that on a particular Saturday, the eligible tee times went from 7 a.m. to 4 p.m., in 10-minute intervals, with a rate of $50 before 2 p.m. and $40 afterward. These values were derived from an HTML form; the handling PHP script then created one record in a database table for each tee time (golfers could then select a time online from the list of records). To represent just one day, this process could require 50 or more extremely similar INSERT queries, making this an ideal use of bound variables.

The simplified table structure for this example could be created with the following SQL statement (without the presence of the other tables, I've done away with identifying keys and such):

CREATE TABLE teetimes (
        teetime DATE,
        rate NUMBER(5,2)
)
Obviously this could be expanded upon in many ways. For now, though, the important thing is that the code in this HowTo assumes you have established such a table and can connect to and populate it from a PHP script.

The following steps will show you exactly want you need to do to implement bound variables. The final code will be built up in a series of steps, with each process being analyzed so that you understand its purpose. Broken down into its basic elements, incorporating bound variables in a PHP script goes like this:

  1. Establish the query being used.
  2. Rewrite the query for bound variables.
  3. Parse the basic query in Oracle.
  4. Assign values to variables in PHP.
  5. Execute the query.
Step 1: Define the Query

Listing 1 is the outline of a general PHP script that would insert a number of records into the teetimes table. The Oracle-related code assumes that you are using PHP 5, where the OCI functions have a slightly different but more consistent naming scheme and syntax than those in PHP 4. If you are using an older version of PHP, see the PHP Manual for the correct functions and syntax, if needed. Also, because getting PHP to communicate with Oracle can be tricky, you may want to read this troubleshooting guide on alternative ways to handle the environmental variables.

Listing 1

                               
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
        <meta http-equiv="content-type" CONTENT="text/html;charset=utf-8" />
        <title>Binding Variables with PHP</title>

</head>
<body>
<h3>Entering Tee Times</h3>
<?php // bind1.php - listing 1

// Establish the environmental variables.
$sid = 'test10g';
$home = '/Users/oracle/10gEAR2/orahome';

putenv("ORACLE_HOME=$home");
putenv("ORACLE_SID=$sid");
putenv("TNS_ADMIN=$home/network/admin");


// Create the array of data to be inserted.
// This data represents what __should__ come from an HTML form.
$teetimes = array();
$date = '2005-08-20';

// Loop through each available hour in the day.
for ($hour = 7; $hour <! 16; $hour++) {

    // Loop through each hour in 10 minute increments.
    for ($minute = 0; $minute <! 60; $minute += 10) {
    
        // Create the date and time value.
        $this_time = "$date $hour:$minute";
        
        // Add a 0 if necessary.
        if ($minute <! 10) $this_time .= '0';
        
        // Determine the rate to use.
        $rate = ($hour <! 14) ? 50.00 : 40.00;
        
        // Add this teetime and rate to the array.
        $teetimes[$this_time] = $rate;
        
    }

}

//echo '<!pre>' . print_r ($teetimes, 1) . '<!/pre>'; // For debugging


// Connect to Oracle.

$c = oci_pconnect ('scott', 'tiger', $sid) OR die 
  ('Unable to connect to the database. Error: <!pre>' . print_r(oci_error(),1) . '<!/pre><!/body><!/html>');


// Insert each record into the table.
foreach ($teetimes as $time => $rate) {


    // Make the query, for example:
    /* INSERT INTO teetimes (teetime, rate) VALUES (TO_DATE('2005-08-21 15:00', 'yyyy-mm-dd hh24:mi'), 40.00); */
    $q = "INSERT INTO teetimes (teetime, rate) VALUES (TO_DATE('$time', 'yyyy-mm-dd hh24:mi'), $rate)";


    // Run the query.
        $s = oci_parse($c, $q);
     oci_execute ($s);
      
}


// Close the connection.

oci_close($c);


// Query to confirm the results:
/* SELECT TO_CHAR(teetime, 'MONTH DD, YYYY HH:MI AM') AS "Tee Time", rate FROM teetimes ORDER BY teetime ASC */
?>
</body>
</html>

                            
The purpose of this script simple: Presumably it will receive a range of dates, times, increments, and rates from a form. This data needs to be assembled in the proper syntax, then inserted into the database. Since the HTML form has not been created, this script automatically generates an array of representative data. Each individual tee time is then inserted into Oracle within a loop. This script is functional and effective, but could be vastly improved by taking advantage of bound variables.

Step 2: Redefine the Query Using Markers

In Listing 1, you can see that the query is original defined as:

INSERT INTO teetimes (teetime, rate) VALUES (TO_DATE('$time', 'yyyy-mm-dd hh24:mi'), $rate)
where $time and $rate are pulled from the manufactured array of data. This query must now be translated so that placeholders instead of variables represent the changing data. The syntax to use is :marker, where marker can be any identifier. In this particular case, the query should be turned into
INSERT INTO teetimes (teetime, rate) VALUES (TO_DATE(:t, 'yyyy-mm-dd hh24:mi'), :r)
In Listing 2, below, which is the end result, this query has been moved from its location in Listing 1. Now it is defined outside of the loop, as it only needs to be defined once (as opposed to once for each record being inserted). Notice as well that when you are binding variables, you can even drop the quotation marks that would normally be required (i.e., the first argument in the TO_DATE() function is just :t, not '$time'.) This is because the variables are essentially separate from the syntax of the query.

Listing 2

                               
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
        <meta http-equiv="content-type" CONTENT="text/html;charset=utf-8" />
        <title>Binding Variables with PHP</title>
</head>
<body>
<h3>Entering Tee Times</h3>
<?php

// Establish the environmental variables.
$sid = 'test10g';
$home = '/Users/oracle/10gEAR2/orahome';

putenv("ORACLE_HOME=$home");
putenv("ORACLE_SID=$sid");
putenv("TNS_ADMIN=$home/network/admin");


// Create the array of data to be inserted.
// This data represents what __should__ come from an HTML form.
$teetimes = array();
$date = '2005-08-21';

// Loop through each available hour in the day.
for ($hour = 7; $hour < 16; $hour++) {

    // Loop through each hour in 10 minute increments.
    for ($minute = 0; $minute < 60; $minute += 10) {
    
        // Create the date and time value.
        $this_time = "$date $hour:$minute";
        
        // Add a 0 if necessary.
        if ($minute < 10) $this_time .= '0';
        
        // Determine the rate to use.
        $rate = ($hour < 14) ? 50.00 : 40.00;
        
        // Add this teetime and rate to the array.
        $teetimes[$this_time] = $rate;
        
    }

}

//echo '<pre>' . print_r ($teetimes, 1) . '</pre>'; // For debugging


// Connect to Oracle.

$c = oci_pconnect ('scott', 'tiger', $sid) OR die 
  ('Unable to connect to the database. Error: <pre>' . print_r(oci_error(),1) . '</pre></body></html>');


// Define the query.
$q = "INSERT INTO teetimes (teetime, rate) VALUES (TO_DATE(:t, 'yyyy-mm-dd hh24:mi'), :r)";


// Parse the query.

$s = oci_parse($c, $q);
      
      
// Bind the values.

oci_bind_by_name($s, ':t', $time, 16);
oci_bind_by_name($s, ':r', $rate, 5);
      

// Insert each record into the table.
foreach ($teetimes as $time => $rate) {

    // Execute the query.
    oci_execute ($s);
      
}


// Close the connection.

oci_close($c);

// Query to confirm  the results:
/* SELECT TO_CHAR(teetime, 'MONTH DD, YYYY HH:MI AM') AS "Tee Time", rate FROM teetimes ORDER BY teetime ASC */

?>
</body>
</html>

                            
Step 3: Parse the Query in Oracle

Once you've defined the query with its markers, you should have Oracle parse it. This is the same thing Oracle does with every query, confirming that the syntax is correct. In your PHP script, use the oci_parse() function (or OCIParse(), in PHP 4), providing this function the database connection and the query as its arguments:

$s = oci_parse ($c, $q);
The result of the parsing is still assigned to a variable representing the statement ( $s), which is exactly how you would handle queries with non-bound variables.

Step 4: Associate PHP Variables with the Markers

If you're paying attention and are of the inquisitive sort, you're now wondering how the values from PHP then get run as part of the query. You do so using the oci_bind_by_name() function ( OCIBindByName() in PHP 4). This function takes the statement resource as its first argument, the name of a marker as its second, and the name of a PHP variable (or, alternatively, a literal value) as its third. For example:

oci_bind_by_name($s, ':t, $time);
oci_bind_by_name($s, ':r, $rate);
For security purposes and to minimize the potential for Oracle errors, it's best to also use the fourth, optional argument: the maximum length of the data to be inserted. The final binding lines are:
oci_bind_by_name($s, ':t, $time, 16);
oci_bind_by_name($s, ':r, $rate, 5);
The numbers used above correspond to what the reasonable maximum length should be of $time and $rate, accordingly. (Alternatively, if you use -1 for this fourth argument, PHP will use the current length of the variable as the maximum length.) Again, you should notice that in the example script (see Listing 2), these two lines come before the foreach loop. This may seem odd, as $time and $rate do not have values at this point. Here is why this works: these lines tell Oracle to use the value stored in $time for :t, and the value stored in $rate for :r, when the query is actually run. So long as those two variables have values when you execute the query, all will be fine.

Step 5: Execute the Bound Query

The final step is to execute the query for each set of values to be inserted. Within a loop, which will access every array element, you only need to assign the right value to $time and the right value to $rate, then execute the query. The same oci_execute() (or OCIExecute() in PHP 4) function performs the execution, using the exact same syntax as the unbound version:

oci_execute ($s);
Again, this takes place within the foreach loop, wherein $time and $rate have their proper values. See the complete Listing 2 for the final code.

I should point out a couple of other benefits of using this method. As a minor convenience, any trailing white space will be removed from the inserted values. More important, you do not need to use addslashes() or Magic Quotes to escape problematic characters (in fact, you shouldn't use either) as the variable values aren't actually part of the query.

Conclusion

In this HowTo you've seen how you can easily improve the security and performance of your database-driven Web applications by using bound variables. The actual incorporation of this technique involves only a few extra lines of code, and no special PHP extensions or libraries.

It is, however, important that you remember two things about binding variables:

  1. The speed performance only pays off on queries that are routinely run and syntactically the same although with different values (like this particular example). You'll see little to no performance benefit in esoteric, one-off queries. In order to make the final decision as to whether you should use bound variables in a particular Web application, perform some benchmarks to test the net effect.
  2. The added security is in no way a replacement for your own, standard security measures. You should always validate data used in queries—particularly that coming from $_POST, $_GET, or $_COOKIE—no matter what.
You should also know that there are actually two ways in which you can use bound variables between PHP and Oracle. The method discussed here is called bound parameters, meaning that the parameters of a query are bound to variables (you'll also see this referred to as a prepared statement). The other method is bound results, which is a different way of retrieving values from a database (after running a SELECT query).

For more information as to how you can improve the performance of your Oracle and PHP Web applications, look for articles about stored procedures, a different way of automating processes. When it comes to performance, you should also understand and utilize indexes on your tables. Finally, for tidiness purposes, you could consider calling the oci_free_statement() function ( OCIFreeStatement() in PHP 4) to free up the resources associated with a statement in a PHP script.


Larry Ullman is the Director of Digital Media Technology and Lead Web Developer at DMC Insights Inc., a company specializing in information technology. Larry lives outside of Washington, D.C., and is also the author of several books on PHP, SQL, Web development, and other computer technologies.

Send us your comments