Preparing and executing SQL statements with variable input in PHP (ibm_db2)

To prepare and execute an SQL statement that includes variable input, use the db2_prepare, db2_bind_param, and db2_execute functions. Preparing a statement improves performance because the database server creates an optimized access plan for data retrieval that it can reuse if the statement is executed again.

To prepare and execute an SQL statement that includes parameter markers:

1. Call the db2_prepare function, passing the listed arguments:

   connection

   A valid database connection resource returned from the db2_connect or db2_pconnect function.

   statement

   A string that contains the SQL statement, including question marks (?) as parameter markers for any column or predicate values that require variable input. This string can include an XQuery expression that is called the XMLQUERY function. You can only use parameter markers as a place holder for column or predicate values. The SQL compiler is unable to create an access plan for a statement that uses parameter markers in place of column names, table names, or other SQL identifiers.

   options

   Optional: An associative array that specifies statement options:

   DB2_ATTR_CASE

   For compatibility with database systems that do not follow the SQL standard, this option sets the case in which column names will be returned to the application. By default, the case is set to DB2_CASE_NATURAL, which returns column names as they are returned by the database. You can set this parameter to DB2_CASE_LOWER to force column names to lowercase, or to DB2_CASE_UPPER to force column names to upper case.

   DB2_ATTR_CURSOR

   This option sets the type of cursor that ibm_db2 returns for result sets. By default, ibm_db2 returns a forward-only cursor (DB2_FORWARD_ONLY) which returns the next row in a result set for every call to db2_fetch_array, db2_fetch_assoc, db2_fetch_both, db2_fetch_object, or db2_fetch_row. You can set this parameter to DB2_SCROLLABLE to request a scrollable cursor so that the ibm_db2 fetch functions accept a second argument specifying the absolute position of the row that you want to access within the result set.

   If the function call succeeds, it returns a statement handle resource that you can use in subsequent function calls that are related to this query.

   If the function call fails (returns False), you can use the db2_stmt_error or db2_stmt_errormsg function to retrieve diagnostic information about the error.

2. Optional: For each parameter marker in the SQL string, call the db2_bind_param function, passing the listed arguments. Binding input values to parameter markers ensures that each input value is treated as a single parameter, which prevents SQL injection attacks against your application.

   stmt

   A prepared statement returned by the call to the db2_prepare function.

   parameter-number

   An integer that represents the position of the parameter marker in the SQL statement.

   variable-name

   A string that specifies the name of the PHP variable to bind to the parameter specified by parameter-number.

3. Call the db2_execute function, passing the listed arguments:

   stmt

   A prepared statement returned by the db2_prepare function.

   parameters

   Optional: An array that contains the values to use in place of the parameter markers, in order.

   For more information about the ibm_db2 API, see http://www.php.net/docs.php.

$sql = "SELECT firstnme, lastname FROM employee WHERE bonus > ? AND bonus < ?";
$stmt = db2_prepare($conn, $sql);
if (!$stmt) {
  // Handle errors
}

// Explicitly bind parameters
db2_bind_param($stmt, 1, $_POST['lower']);
db2_bind_param($stmt, 2, $_POST['upper']);

db2_execute($stmt);
// Process results

// Invoke prepared statement again using dynamically bound parameters
db2_execute($stmt, array($_POST['lower'], $_POST['upper']));