Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:109202
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_CF6316F5-07AC-4524-A230-B545133BF1CE"
Mime-Version: 1.0 (Mac OS X Mail 13.0 \(3608.60.0.2.5\))
Message-ID: <3CE6E742-33E9-4686-93C4-2B1DB1E405B7@craigfrancis.co.uk>
Date: Sun, 22 Mar 2020 12:45:22 +0000
To: PHP internals <internals@lists.php.net>
Subject: Making mysqli easier to use with parameters
From: craig@craigfrancis.co.uk (Craig Francis)

--Apple-Mail=_CF6316F5-07AC-4524-A230-B545133BF1CE
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii

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 =3D 'SELECT id, name FROM user WHERE id IN (' . implode(', ', =
$ids) . ')';

    $result =3D mysqli_query($db, $sql);

    while ($row =3D 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 =3D implode(',', array_fill(0, count($ids), '?'));

    $sql =3D 'SELECT id, name FROM user WHERE id IN (' . $in_sql . ')';

    if ($statement =3D $db->prepare($sql)) {

        $params =3D [str_repeat('i', count($ids))];
        foreach ($ids as $key =3D> $value) {
            $params[] =3D &$ids[$key]; // Must be a reference, not =
ideal.
        }
        call_user_func_array(array($statement, 'bind_param'), $params);

        $statement->execute();

        $result =3D $statement->get_result();

        while ($row =3D 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 =3D implode(',', array_fill(0, count($ids), '?'));

    // $in_sql =3D substr(str_repeat('?,', count($ids)), 0, -1); // Any =
better ideas?

    $sql =3D 'SELECT id, name FROM user WHERE id IN (' . $in_sql . ')';

    if ($statement =3D $db->prepare($sql)) {

        $statement->execute($ids);

        while ($row =3D 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 =3D [];
    $parameters[] =3D ['i', 1];
    $parameters[] =3D ['s', 'example'];

    if (true) {
        $parameters[] =3D ['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?


--Apple-Mail=_CF6316F5-07AC-4524-A230-B545133BF1CE--