Making mysqli easier to use with parameters

  109202
March 22, 2020 12:45 craig@craigfrancis.co.uk (Craig Francis)
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:



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:

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?

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

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:

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



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?
  109203
March 22, 2020 15:28 internals@lists.php.net ("Levi Morrison via internals")
> > $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
  109217
March 22, 2020 23:33 craig@craigfrancis.co.uk (Craig Francis)
On 22 Mar 2020, at 15:28, Levi Morrison 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 morrison@datadoghq.com> wrote: > >> > >> $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
  109228
March 23, 2020 12:19 Joel.Hutchinson@onlinecommercegroup.com (Joel Hutchinson)
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 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 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 morrison@datadoghq.com> wrote: > >> > >> $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
  109248
March 23, 2020 19:29 craig@craigfrancis.co.uk (Craig Francis)
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:

> 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 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 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 morrison@datadoghq.com> > wrote: > > > >> >> > >> $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 > > -- > PHP Internals - PHP Runtime Development Mailing List > To unsubscribe, visit: http://www.php.net/unsub.php > >