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
– M. Eriksson, Commented Jan 30, 2020 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
– deceze ♦, Commented Jan 30, 2020 at 11:10
@deceze thanks for these insights. What about this case? Does it matter in this case? — binoculars
– binoculars, Commented Jan 30, 2020 at 11:13

M. Eriksson · Accepted Answer · 2020-01-30 11:15:15Z

1

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.

answered Jan 30, 2020 at 11:15

M. Eriksson

13.7k4 gold badges31 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Your Common Sense · Accepted Answer · 2020-01-30 11:40:00Z

0

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.

answered Jan 30, 2020 at 11:40

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

2 Comments

Dharman Over a year ago

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 stackoverflow.com/a/58773685/1839439

Your Common Sense Over a year ago

Your order by example is fair, i didn't think of it

Collectives™ on Stack Overflow

Using s for int in bind_param

2 Answers 2

Example 1

Example 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Example 1

Example 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related