Skip to content

Instantly share code, notes, and snippets.

@machavity
Last active November 21, 2019 15:41
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save machavity/c84dad59bbc4d7d37b2d6e6bfd654df3 to your computer and use it in GitHub Desktop.
Mysqli bind single parameter

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 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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment