Prepared statements have become more important of late. By breaking a query
into instructions and data, we avoid the problem of SQL injection. While MySQLi
supports prepared statements, it has a structural deficiency that PDO does not
share. MySQLi does not support binding parameters separately.
Consider the following query (which we will put into a variable called $sql)
INSERT INTO db.visits(page, ip) VALUES(?, ?)
Within PDO we can do the following
$stmt = $db->prepare($sql);
$stmt->bindParam(1, $page, PDO::PARAM_STR);
$stmt->bindParam(2, $ip, PDO::PARAM_STR);
MySQLi takes a different approach, by binding all the parameters at the same
time (and mandating that they be passed by reference)
$stmt = $db->prepare($sql);
$stmt->bind_param('ss', $page, $ip);
This is functional, but only for smaller queries. As the parameter list grows,
this methodology becomes unwieldy
$stmt->bind_param('ssssisssid', $name, $company, $billing_addr, $billing_city,
$billing_state, $billing_zip...);
Not only is hard to maintain (you have to manually count parameters if you get
an error), it also introduces a structural problem, in that if we want a
conditional query and parameters, we have to get even more unwieldy
$sql = 'SELECT name FROM db.customer WHERE record_id = ?';
$params = 'i';
$data = [&$_GET['record_id']];
if(isset($_GET['zip'])) {
$params .= 's';
$data[] = &$_GET['zip'];
$sql .= ' AND billing_zip = ?';
}
// The first argument for mysqli_stmt_bind_param must be the data type list
array_unshift($params, $data);
$stmt = $db->prepare($sql);
call_user_func_array([$stmt, 'bind_param'], $data);
$stmt->execute();
PDO manages to make this work much more elegantly
$sql = 'SELECT name FROM db.customer WHERE record_id = ?';
if(isset($_GET['zip'])) $sql .= ' AND billing_zip = ?';
$stmt = $db->prepare($sql);
$stmt->bindParam(1, $_GET['record_id'], PDO::PARAM_INT);
if(isset($_GET['zip'])) $stmt->bindParam(2, $_GET['zip'], PDO::PARAM_STR);
$stmt->execute();
It's clear the MySQLi interface lags behind PDO here, yet still remains a
staple in PHP. Many projects dutifully switched from the removed MySQL
interface, yet, due to these issues, they still write procedural code to
generate SQL because it's easier to simply escape the data and understand the
code, than it is to work these extra layers in to secure their queries with
prepared statements.
MySQLi desparately needs a single bind like PDO has. I would propose a new
function, named mysqli_stmt_bind_single. Reusing the PDO query above with
proposed syntax
$stmt = $db->prepare($sql);
$stmt->bind_single('i', $_GET['record_id'], 1);
if(isset($_GET['zip'])) $stmt->bind_single('s', $_GET['zip'], 2);
$stmt->execute();
This proposal would leave the previous mysqli_stmt_bind_param mostly untouched.
Instead, the two could be used in tandem
$sql = 'SELECT name FROM db.customer WHERE record_id = ? AND shipping_zip = ?';
if(isset($_GET['zip'])) $sql .= ' AND billing_zip = ?';
$stmt = $db->prepare($sql);
$stmt->bind_param('is', $_GET['record_id'], $_GET['shipping_zip']);
if(isset($_GET['zip'])) $stmt->bind_single('s', $_GET['billing_zip'], 3);
$stmt->execute();
This necessitates a small change to mysqli_stmt_bind_param, in that the current
function has a parameter check to ensure that the number of binds matches the
number of parameters in the query (or else it emits an E_WARNING). That check
would have to move to mysqli_stmt_execute, if it is still to be performed.
public mysqli_stmt::bind_single( mixed type, mixed $var [, int $position]) :
bool
$position can be omitted, and it will take the next spot in the internal data
array. Passing an invalid postion would emit an E_WARNING. $postion can
overwrite an existing value silently, with a final check coming on the call of
mysqli_stmt_execute, as described above. This is how PDO operates now.
