Making mysqli easier to use with parameters

4 years ago by Craig Francis — view source

unread

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?

4 years ago by Levi Morrison via internals — view source

unread

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

4 years ago by Craig Francis — view source

unread

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

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

4 years ago by Joel Hutchinson — view source

unread

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

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

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

4 years ago by Craig Francis — view source

unread

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 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.
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.
--
To unsubscribe,
visit: http://www.php.net/unsub.php