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?
