Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:107857
To: "internals@lists.php.net" <internals@lists.php.net>
Thread-Topic: mysqli needs a method to bind a single parameter
Thread-Index: AdWjuwjZuAEPM+p8S7ag150DgwV5aw==
Date: Mon, 25 Nov 2019 18:06:18 +0000
Message-ID:
 <MN2PR18MB25894DE93EF773D3CF05B28AED4A0@MN2PR18MB2589.namprd18.prod.outlook.com>
Accept-Language: en-US
Content-Language: en-US
received-spf: None (protection.outlook.com: onlinecommercegroup.com does not
 designate permitted sender hosts)
Content-Type: multipart/alternative;
	boundary="_000_MN2PR18MB25894DE93EF773D3CF05B28AED4A0MN2PR18MB2589namp_"
MIME-Version: 1.0
X-MS-Exchange-CrossTenant-Network-Message-Id: 2d96dd17-2c75-4f97-d3a9-08d771d22c68
X-MS-Exchange-CrossTenant-originalarrivaltime: 25 Nov 2019 18:06:18.9347
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 733bee1d-43aa-48a2-a713-34870bf1749b
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: VChAykFpPh5AaIZzfMxffLLE+cM3L7n8JiR3hWpMG8sAB0vvoF0angwptTzsqveh
X-MS-Exchange-Transport-CrossTenantHeadersStamped: MN2PR18MB2527
X-Envelope-From: <Joel.Hutchinson@onlinecommercegroup.com>
Subject: mysqli needs a method to bind a single parameter
From: Joel.Hutchinson@onlinecommercegroup.com (Joel Hutchinson)

--_000_MN2PR18MB25894DE93EF773D3CF05B28AED4A0MN2PR18MB2589namp_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Prepared statements have become more important of late. By breaking a query=
 into instructions and data, we avoid the problem of SQL injection. While M=
ySQLi 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 =3D $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 sam=
e time (and mandating that they be passed by reference)
$stmt =3D $db->prepare($sql);
$stmt->bind_param('ss', $page, $ip);
This is functional, but only for smaller queries. As the parameter list gro=
ws, this methodology becomes unwieldy
$stmt->bind_param('ssssisssid', $name, $company, $billing_addr, $billing_ci=
ty, $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 =3D 'SELECT name FROM db.customer WHERE record_id =3D ?';
$params =3D 'i';
$data =3D [&$_GET['record_id']];
if(isset($_GET['zip'])) {
  $params .=3D 's';
  $data[] =3D &$_GET['zip'];
  $sql .=3D ' AND billing_zip =3D ?';
}
// The first argument for mysqli_stmt_bind_param must be the data type list
array_unshift($params, $data);
$stmt =3D $db->prepare($sql);
call_user_func_array([$stmt, 'bind_param'], $data);
$stmt->execute();
PDO manages to make this work much more elegantly
$sql =3D 'SELECT name FROM db.customer WHERE record_id =3D ?';
if(isset($_GET['zip'])) $sql .=3D ' AND billing_zip =3D ?';

$stmt =3D $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 s=
taple in PHP. Many projects dutifully switched from the removed MySQL inter=
face, yet, due to these issues, they still write procedural code to generat=
e 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 pre=
pared 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 p=
roposed syntax
$stmt =3D $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 untouc=
hed. Instead, the two could be used in tandem
$sql =3D 'SELECT name FROM db.customer WHERE record_id =3D ? AND shipping_z=
ip =3D ?';
if(isset($_GET['zip'])) $sql .=3D ' AND billing_zip =3D ?';

$stmt =3D $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 cur=
rent function has a parameter check to ensure that the number of binds matc=
hes 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 da=
ta 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.

--_000_MN2PR18MB25894DE93EF773D3CF05B28AED4A0MN2PR18MB2589namp_--