Everything
php
Learn Community Releases The PHP Project
Home » Project » Newsgroups » php.internals » Making mysqli easier to use with parameters »

Making mysqli easier to use with parameters

php.internals

Craig Francis

6 years ago
Hi, Considering many new developers don't use a database abstraction, and instead copy/paste mysqli_query() examples. I'm just wondering, is there a way we can make it easier to use parameterised queries? Some initial thoughts below. Craig Take this semi-complex case, in that it's looking for an undefined number of users: <?php $sql = 'SELECT id, name FROM user WHERE id IN (' . implode(', ', $ids) . ')'; $result = mysqli_query($db, $sql); while ($row = mysqli_fetch_assoc($result)) { print_r($row); } ?> This is flawed, especially if those $ids come from untrusted data, but it's not obvious. I'm only aware of mysqli_stmt_bind_param() being able to do parameterised queries via mysqli. I abstracted that function away many years ago, and a recent question reminded me of its, well, difficult way of working: <?php $in_sql = implode(',', array_fill(0, count($ids), '?')); $sql = 'SELECT id, name FROM user WHERE id IN (' . $in_sql . ')'; if ($statement = $db->prepare($sql)) { $params = [str_repeat('i', count($ids))]; foreach ($ids as $key => $value) { $params[] = &$ids[$key]; // Must be a reference, not ideal. } call_user_func_array(array($statement, 'bind_param'), $params); $statement->execute(); $result = $statement->get_result(); while ($row = mysqli_fetch_assoc($result)) { print_r($row); } } ?> I'm wondering, could mysqli_stmt_execute() be updated to take the parameters in a developer friendly way? <?php $in_sql = implode(',', array_fill(0, count($ids), '?')); // $in_sql = substr(str_repeat('?,', count($ids)), 0, -1); // Any better ideas? $sql = 'SELECT id, name FROM user WHERE id IN (' . $in_sql . ')'; if ($statement = $db->prepare($sql)) { $statement->execute($ids); while ($row = mysqli_fetch_assoc($statement->result)) { print_r($row); } } ?> This approach doesn't take types, which might be possible to make optional ('s' kind of works for most things); or maybe... <?php $statement->execute($ids, str_repeat('i', count($ids))); ?> Personally I'm not sold on that idea. So maybe the parameters could work with an array that includes types: <?php $statement->execute([ ['i', 1], ['s', 'example'], ['b', $blob], ]); ?> It is a slightly odd array structure, but it's fairly easy to work with, especially if your SQL string is being built up dynamically... <?php $parameters = []; $parameters[] = ['i', 1]; $parameters[] = ['s', 'example']; if (true) { $parameters[] = ['b', $blob]; } ?> And I wonder if $statement->get_result() could be provided via a `$statement->result` property, so it could be used directly by mysqli_fetch_assoc(). Thoughts?

Unnamed Person

6 years ago
> <?php > > $in_sql = implode(',', array_fill(0, count($ids), '?')); > > $sql = 'SELECT id, name FROM user WHERE id IN (' . $in_sql . ')'; > > if ($statement = $db->prepare($sql)) { > > $params = [str_repeat('i', count($ids))]; > foreach ($ids as $key => $value) { > $params[] = &$ids[$key]; // Must be a reference, not ideal. > } > call_user_func_array(array($statement, 'bind_param'), $params); > > $statement->execute(); > > $result = $statement->get_result(); > > while ($row = mysqli_fetch_assoc($result)) { > print_r($row); > } > > } > > ?>
Written in my email client without error handling code, so apologies if it's not quite correct: $in = join(',', array_fill(0, count($ids), '?')); $select = "SELECT id, name FROM user WHERE id IN ({$in});"; $statement = $mysqli->prepare($select); $statement->bind_param(str_repeat('i', count($ids)), ...$ids); $statement->execute(); This is part of my [highest score answer on StackOverflow][1]. Critically, applying the ids array as `...` will handle the by-reference passing. I don't think we need to improve the ergonmics of mysqli for this case specifically, as it's always going to require some dynamic SQL generation because of the variable number of parameters to bind. [1]: https://stackoverflow.com/a/23641033/538216

Craig Francis

6 years ago
On 22 Mar 2020, at 15:28, Levi Morrison <levi.morrison@datadoghq.com> wrote:
> applying the ids array as `...` will handle the by-reference passing.
That does help, thanks. But I still wonder if the ability to pass in a single array of parameters to `$statement->execute()` would remove a step, and be a bit easier to use for all queries. If this was for a search form, where it dynamically creates a SELECT with a variety of different parameters, it gets tricky again. And there was the thing I tacked onto the end, where I would like to use a `$statement->result` property to skip the use of `$statement->get_result()`: while ($row = mysqli_fetch_assoc($statement->result)) { } Craig

Joel Hutchinson

6 years ago
Hey Craig, I think this might be in the same vein as something I'd love to see done (PDO already does this). Proposed it a while back and have talked to a few internals folks about it off and on https://externals.io/message/107857#107857 Either way, mysqli lacks any proper way to do dynamic binds on a single basis. This seems like low hanging fruit to make mysqli easier to use -----Original Message----- From: Craig Francis <craig@craigfrancis.co.uk> Sent: Sunday, March 22, 2020 6:34 PM To: Levi Morrison <levi.morrison@datadoghq.com> Cc: PHP internals <internals@lists.php.net> Subject: Re: [PHP-DEV] Making mysqli easier to use with parameters On 22 Mar 2020, at 15:28, Levi Morrison <levi.morrison@datadoghq.com> wrote:
> applying the ids array as `...` will handle the by-reference passing.
That does help, thanks. But I still wonder if the ability to pass in a single array of parameters to `$statement->execute()` would remove a step, and be a bit easier to use for all queries. If this was for a search form, where it dynamically creates a SELECT with a variety of different parameters, it gets tricky again. And there was the thing I tacked onto the end, where I would like to use a `$statement->result` property to skip the use of `$statement->get_result()`: while ($row = mysqli_fetch_assoc($statement->result)) { } Craig
> On 22 Mar 2020, at 15:28, Levi Morrison <levi.morrison@datadoghq.com> wrote: > >> <?php >> >> $in_sql = implode(',', array_fill(0, count($ids), '?')); >> >> $sql = 'SELECT id, name FROM user WHERE id IN (' . $in_sql . ')'; >> >> if ($statement = $db->prepare($sql)) { >> >> $params = [str_repeat('i', count($ids))]; >> foreach ($ids as $key => $value) { >> $params[] = &$ids[$key]; // Must be a reference, not ideal. >> } >> call_user_func_array(array($statement, 'bind_param'), >> $params); >> >> $statement->execute(); >> >> $result = $statement->get_result(); >> >> while ($row = mysqli_fetch_assoc($result)) { >> print_r($row); >> } >> >> } >> >> ?> > > Written in my email client without error handling code, so apologies > if it's not quite correct: > > $in = join(',', array_fill(0, count($ids), '?')); > $select = "SELECT id, name FROM user WHERE id IN ({$in});"; > $statement = $mysqli->prepare($select); > $statement->bind_param(str_repeat('i', count($ids)), ...$ids); > $statement->execute(); > > This is part of my [highest score answer on StackOverflow][1]. > Critically, applying the ids array as `...` will handle the > by-reference passing. > > I don't think we need to improve the ergonmics of mysqli for this case > specifically, as it's always going to require some dynamic SQL > generation because of the variable number of parameters to bind. > > [1]: https://stackoverflow.com/a/23641033/538216
-- PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit: http://www.php.net/unsub.php

Craig Francis

6 years ago
Hi Joel, I assume you're under the handle Machavity in Room 11: https://chat.stackoverflow.com/transcript/message/48927577#48927577 https://gist.github.com/machavity/c84dad59bbc4d7d37b2d6e6bfd654df3 If not, I'd be up for your proposal as well, anything to make mysqli easier to use. Personally I'd still prefer being able to build up an array of parameters, as I'm creating the SQL string (same as PDO). But that's only because the parameter positions will change... which could be handled by $i++, or as the second link suggests, make the parameter argument optional (and presumably reset after execute, so the statement can be reused). Craig On Mon, 23 Mar 2020 at 12:19, Joel Hutchinson < Joel.Hutchinson@onlinecommercegroup.com> wrote: