Using s for int in bind_param

Question

Let's take this mysqli query:

$query = "SELECT * FROM table WHERE (".$id_type."=?)";

$id_type is hard-coded and can be "userID" (int(11) in the mysql table) or "sessionID" (char(36) in the mysql table).

Can I just bind the parameters like this?

$stmt->bind_param('s', $id);

Is there a performance penalty or something else I should consider when using s in bind_param, even though $id might be an int? If not, why would I even bother in the future using the correct letters (i, s, d, ...)?

Or you could just do: `$type = $id_type == 'userID' ? 'i' : 's';`. Then you only need to do: `$stmt->bind_param($type, $id);`. — M. Eriksson, Jan 30 '20 at 11:10
It matters in that the value will be sent to MySQL as a different type. Most of the time MySQL does its own type casting to the context-appropriate type, so often it doesn't matter. But *often* isn't *always*. — deceze, Jan 30 '20 at 11:10
@deceze thanks for these insights. What about this case? Does it matter in this case? — binoculars, Jan 30 '20 at 11:13
[This should be helpful](https://stackoverflow.com/questions/21762075/mysql-automatically-cast-convert-a-string-to-a-number). — nice_dev, Jan 30 '20 at 11:20

score 1 · Accepted Answer · answered Jan 30 '20 at 11:15

It's always good to be as accurate as possible to reduce the risk of side effects/issues.

In your case, it should be pretty easy to do this.

Example 1

$type = $id_type == 'userID' ? 'i' : 's';
$stmt->bind_param($type, $id);

That would work well if you only have two options.

If you have more, the below method would work.

Example 2

$types = [
    'userID'    => 'i',
    'sessionID' => 's',
];

$stmt->bind_param($types[$id_type], $id);

Then you can keep adding to your $types array if you need more cols.

score 0 · Answer 2 · answered Jan 30 '20 at 11:40

Yes, you can.

Edge cases are rare and most likely you will never need to use a specific type.

That trick offered in the other answer would work in this particular case. But for the application at whole, when you are about some automation, setting particular types will be pain in below the back.

There are millions questions on Stack Overflow, about such weird cases you'd never imagine. None of them about use cases when you used s and it caused any problem.

In your old mysql_query code you were using '$id' all the time without a single problem.

PDO is using this approach with s all the time. No reports either.

So you can perform some tricks if you wish, but in practice they are unnecessary.

Could you explain what you mean by "There are millions questions on Stack Overflow, about such weird cases you'd never imagine." Do you mean an example I described in https://stackoverflow.com/a/58773685/1839439 — Dharman, Jan 30 '20 at 11:53

Using s for int in bind_param

2 Answers2

Example 1

Example 2

Linked