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.

Absolutely no reason if you're doing it this way. The reason would be if you always wanted to have a specific parameter bound as an integer or float. Determining what type you bind based on the data type of the variable you're binding is pointless with MySQL. — Devon Bessemer, Jul 02 '18 at 14:34
Treat em all as `s` string, unless you are piping in some `b` binary on a null reference. — IncredibleHat, Jul 02 '18 at 15:14
@IncredibleHat Yes, so far that seems to be the bottom line. Thanks! — Code4R7, Jul 03 '18 at 08:34

Bill Karwin · Accepted Answer · 2019-11-09T16:27:41.177

3

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.

edited Nov 09 '19 at 16:27

answered Jul 02 '18 at 15:16

Bill Karwin

538,548
86
673
828

1

Thanks for digging into my question. I tested the LIMIT clause, but I can not reproduce it with PHP 7.0, MySQLi client connected to MariaDB 10.0. When I issue a `call_user_func_array([$rStmt, 'bind_param'], $aCall);` (The $aCall array is a workaround for PHP bug #44139.) then the `LIMIT` clause accepts strings as a type. – Code4R7 Jul 02 '18 at 17:31
1

Okay maybe it's just a problem when using PDO with emulated prepares, as I wrote in the other answer I linked to. – Bill Karwin Jul 02 '18 at 17:34
I think you're right about PDO, because of [this](https://stackoverflow.com/questions/2269840/how-to-apply-bindvalue-method-in-limit-clause) question. – Code4R7 Jul 02 '18 at 17:37
Yeah, `LIMIT '10', '20'` is not valid MySQL syntax. The wonky behavior of emulated prepares by PDO can result in that syntax. Best not to use emulated prepares. – Bill Karwin Jul 02 '18 at 17:39
Good call on ints in LIMIT. I guess I never thought of that since I've never used user input directly in a LIMIT statement. I always derive it programmatically from whitelisted choices/paging. – IncredibleHat Jul 03 '18 at 13:42
This must be the reason why I prefer mysqli over PDO =] – Code4R7 Jul 09 '18 at 19:02
1

@Code4R7, I prefer PDO, as long as emulated prepares are disabled. I dislike the way mysqli does parameter binding. PDO has nicer usage, you can pass an array of values to `execute()`. – Bill Karwin Jul 09 '18 at 19:05
@BillKarwin I was hinting towards [this video](https://www.youtube.com/watch?v=eKy9fV_zX_o), but you're absolutely right with the array of values, which is why I had to write my own `query_params()`. I think PDO is a general connector for generic situations. You can use any database. In my case the framework is tied to MariaDB, so mysqli would be a better choice. Although I would like to move to PostGres whenever my technology stack permits it. – Code4R7 Jul 10 '18 at 07:01
@Code4R7, It makes sense to have one API for all SQL databases. Like the model of JDBC, ODBC, Perl's DBI, or Go's db. The fact that PHP chose to design a completely unique extension and API for each brand of database was a bad decision (like many in PHP). – Bill Karwin Jul 10 '18 at 15:40
@BillKarwin Let's agree to disagree here. Please allow me to extrapolate what you're saying. It's like that it's a bad design choice to have multiple webserver side languages like PHP, Ruby, Java, et cetera. and that a language that combines them all like [HAXE](https://haxe.org) does make sense. Therefore unique languages are a bad decision? I agree that PDO makes sense in many cases. But when you want to drive a Formula 1 car a generic driver won't do. Also availability is another (huge) quality and there was a time that PDO was not available. A pragmatic approach, PHP says it on their site. – Code4R7 Jul 10 '18 at 19:22
It's true, PDO was still a PECL project when PHP 5.0 was released, but it became a standard PHP extension in PHP 5.1. If you want to choose technology based on what was available before 2005, be my guest. – Bill Karwin Jul 10 '18 at 19:38
We all know PDO uses MySQLi in the background. And PDO is not exactly a holy grail, for example it does not (yet) convert the actual SQL queries to the flavours of various databases. And PDO is measured a tiny bit slower than MySQLi. Luckily, the most important differences are listed [here](http://php.net/manual/en/mysqlinfo.api.choosing.php), on which a decision should be based. – Code4R7 Jul 10 '18 at 20:18
I found your answer when I was writing [mine here](https://stackoverflow.com/a/58773685/1839439). I was thinking about `ORDER BY` clause which acts differently based on the type, but your post got me confused with `LIMIT` which will cast the value to a number. Could I ask you to update the answer with your finding from the comment section, to be less confusing for the next person that comes along, please? – Dharman Nov 09 '19 at 15:17
`LIMIT` will not cast the parameters to a number. But it does *require* that the parameter is an integer, not a string. If you pass the wrong type of parameter, like a string, then the query will act as if you had done `LIMIT '0', '1'` which is an error. – Bill Karwin Nov 09 '19 at 16:20
You are confusing emulated and native prepared statements. The query will act as if you had done LIMIT '0', '1' only with PDO with emulation turned on. But the question is about mysqli. Although it is not limit but mysqli who casts, the outcome is all the same - the query works – Your Common Sense Nov 09 '19 at 16:41

Your Common Sense · Answer 2 · 2022-09-17T07:48:20.340

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!

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

2 Answers2

Linked

Related