What happens when you filter input in PDO prepared statement and the STR is in the form of an interger?

Question

This may be a little elementary for some, but in something like the following statement what would happen if the string was an integer (e.x 007 as in the movie):

$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);

1. If colour was '007' would PDO::PARAM_STR, still work?
2. What is int 12 for? does it refer to the length of (colour & $colour)?
3. Is it's purpose to maximize the filter? (only strings of 12 get through?)

Thanks guys, still working on deciphering manual (new to PHP) but so far don't see specifics on this.

Complete statement here.

/* Execute a prepared statement by binding PHP variables */

$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
FROM fruit
WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();

Answer 1

Most of time nothing wrong happens. As far as I know, SQL, just like PHP, is a loosely-typed language that allows you to represent a number as a string, so you can always format integers as strings. There are only few exceptional cases:

• LIMIT clause in emulation mode or any other SQL clause that just cannot accept a string operand.
• complex queries with non-trivial query plan that can be affected by a wrong operand type
• peculiar column types, like BIGINT or BOOLEAN that require an operand of exact type to be bound (note that in order to bind a BIGINT value with PDO::PARAM_INT you need a mysqlnd-based installation).

Now to your example. 007 is actually a string, not integer. You can't get an integer with leading zeros in PHP. But in case there is a real integer, you still can bind it as a string.

What is int 12 for?

This number has a very specific purpose used with stored procedures only and doesn't have any effect on the regular queries.

So you can tell that the example is rather misleading.