mysqli_stmt::bind_param() - specify another data type than "s" for each parameter

Question

A mysqli_stmt does not have a query_params() function, I had to write my own. The parameter arry is bound to the statement with bind_param(). I need to specify the variable types dynamically. I could do that with something like:

$sType = '';
foreach ($aParameters as $iIndex => $mParameter) {
  if     (is_string($mParameter)) {$sType .= 's';}
  elseif (   is_int($mParameter)) {$sType .= 'i';}
  elseif ( is_float($mParameter)) {$sType .= 'd';}
  elseif (  is_null($mParameter)) {$sType .= 's';}
  elseif (  is_bool($mParameter)) {
    $sType .= 'i';
    $aParameters[$iIndex] = boolval($mParameter);}
  else {
    // trow new Exception(...);
  }
}

But as it turns out, mysql/mariadb will send booleans, integers and floats fine as strings, where the database server will happily cast them to the corresponding data type of the column. It seems like I could just skip this step and send every parameter as a string by default.

Are there any reaons to specify another data type than "s" for each parameter?

EDIT: I just found this SO topic which shows how to use the "b" type and mysqli_stmt::send_long_data when the binary packet would exceed the max_allowed_packet setting. I also have read that it will improve performance over solutions that employ bin2hex() to turn send a byte string as text.

Answer 1

It seems like I could just skip this step and send every parameter as a string by default.

Yes, exactly.

Are there any reasons to specify another data type than "s" for each parameter?

Extremely rare and vague. So far I was able to find as much as

• bigint values are better to be bound as integers rather than strings
• some report that casting could cause the wrong execution plan but I was unable to find a proof in the wild
• the binary type you already found yourself, though I would question the idea itself of storing BLOBs in the database
• that odd order by number case mentioned by Dharman.

However odd the case could be, I would propose to keep the typed binding but avoid that type sniffing, which does no good but could destroy your database.

Instead, just make types explicit but optional, like I did in my mysqli helper function:

function prepared_query($mysqli, $sql, $params, $types = "")
{
    $types = $types ?: str_repeat("s", count($params));
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    return $stmt;
}

when you don't need them (most of time), just leave types out:

$sql = "SELECT * FROM tmp_mysqli_helper_test LIMIT ?";
$res = prepared_query($conn, $sql, [10])->get_result();

but every time you will need it, it's already here and explicit, you could set the exact type you want:

$sql = "INSERT INTO table (id, blobfield) VALUES (?, ?)";
prepared_query($conn, $sql, [$id, $file], "ib");

Clean, simple and elegant!

Answer 2

The only time I have found it's important to use an integer parameter is in a LIMIT clause.

SELECT
...
LIMIT ?, ?

MySQL does not accept quoted string literals in this context, and does not accept parameters with string type. You have to use an integer.

See Parametrized PDO query and `LIMIT` clause - not working for my tests on this. That was a question about PDO, and I didn't test mysqli, but I believe it's a server-side MySQL requirement to use integer parameters in this case. So it should apply to mysqli too.

In all other cases (AFAIK), MySQL is able to convert strings into integers by reading the leading digits in the string, and ignoring any following characters.

---

@Dharman in a comment below makes reference to MySQL's support for integers in ORDER BY:

SELECT
...
ORDER BY ?

An integer in ORDER BY means to sort by the column in that position, not by the constant value of the number:

SELECT
...
ORDER BY 1 -- sorts by the 1st column

But an equivalent string value containing that number doesn't act the same. It sorts by the constant value of the string, which means every row is tied, and the sort order will be arbitrary.

SELECT
...
ORDER BY '1' -- sorts by a constant value, so all rows are tied

Therefore this is another case where the data type for a query parameter is important.

On the other hand, using ordinal numbers to sort by the column in that position in ORDER BY or GROUP BY is deprecated, and we shouldn't rely on that usage of SQL.