Using bind variables (sometimes known as parameterization) is an important way to prevent SQL Injection attacks, and should be used as a matter of course on any internet-facing web site.

In addition, there may be performance improvements in statements executed multiple times.

Most methods that accept an SQL statement as a parameter, also accept an array of bind variables to insert into the statement.

/*
* Using a MySQL database
*
* Statement without Binding
*/
$sql = "SELECT * FROM some_table WHERE col1='A' AND col2='B' AND col3='C'";
$result = $db->execute($sql);
 
/*
* Same statement with binding
*/
$bindVars = array('A','B','C');
$sql = "SELECT * FROM some_table WHERE col1=? AND col2=? AND col3=?";
$result = $db->execute($sql,$bindVars);

Note that the number of variable in the $bindVars array must match the bind placeholders (?)

Bind placeholders vary from one database to another, so ADOdb provides a method param() that returns a database specific placeholder. Using the above example, we can write it as a portable statement:

$bindVars = array('A','B','C');
 
$col1Ph = $db->param('col1');
$col2Ph = $db->param('col2');
$col2Ph = $db->param('col3');
 
$sql = "SELECT * FROM some_table WHERE col1=$col1Ph AND col2=$col2ph AND col3=$colPh";
$result = $db->execute($sql,$bindVars);

In the above example, you can see that it is necessary to obtain a unique placeholder for each bind variable. This is because in some databases, the placeholder is uniquely associated with the column. So in the above example would appear as follows:

SELECT * FROM some_table WHERE col1=? AND col2=? AND col3=?

SELECT * FROM some_table WHERE col1=:col1 AND col2=:col2 AND col3=:col3

SELECT * FROM some_table WHERE col1=$1 AND col2=$2 AND col3=$3

You can still use the bind form of execute in ADOdb if the database does not support binding. In this case ADOdb simply rewrites the query statement for you back into the non-bind form.

Some databases, Oracle in particular, offer massive performance improvements in insertion performance when used with 2 dimensional bind arrays. This technique is known as Bulk Binding.

Using our previous example, let us assume that we need to insert multiple some_table records, and that we can determine the values to be inserted prior to the execution of all the statements

Traditionally, we might write our insert statement like this:

$sql = "INSERT INTO some_table(col1,col2,col3) values ('A','B','C')";
$db->execute($sql);
$sql = "INSERT INTO some_table(col1,col2,col3) values ('D','E','F')";
$db->execute($sql);
$sql = "INSERT INTO some_table(col1,col2,col3) values ('G','H','I')";
$db->execute($sql);
$sql = "INSERT INTO some_table(col1,col2,col3) values ('J','K','L')";
$db->execute($sql);
etc......

We can also use bind variables like this, in this case we may obtain speed improvements over the previous execution:

$bindVars = array('A','B','C');
$sql = "INSERT INTO some_table(col1,col2,col3) values (?,?,?)";
$db->execute($sql,$bindVars);
$bindVars = array('D','E','F');
$sql = "INSERT INTO some_table(col1,col2,col3) values (?,?,?)";
$db->execute($sql,$bindVars);
etc......

If we now turn our $bindVars into a 2 dimensional array, we can create multiple records using a single SQL statement:

$bindVars = array(array('A','B','C'),
                  array('D','E','F'),
                  array('G','H','I'),
                  array('J','K','L'));
$sql = "INSERT INTO some_table(col1,col2,col3) values (?,?,?)";
$db->execute($sql,$bindVars)

In databases without Bulk Binding support, ADOdb simply iterates through the $bindVars array, and executes them individually.

Bulk Binding using 2D arrays is disabled by default in ADOdb. It must be deliberately enabled in the driver using the following syntax:

include 'adodb/adodb.inc.php';
 
$db = newAdoConnection('drivername');
 
$db->connect('parameters');
 
/*
* Enable bulk binding
*/
$db->bulkBind = true;