Reasons to strongly type parameters in PDO?

Question

When you bind parameters to SQL statement, you can provide parameter type like PDO::PARAM_STR. If you don't, type defaults to PDO::PARAM_STR. What can be the reasons to specifically set the type of each parameter? PDO::PARAM_STR works with any parameter as I know at least in MySQL. I think even with PDO::PARAM_STR can be used even with BLOB columns.

PDO::PARAM_STR does not introduce any SQL injection because you still have prepared queries.

Answer 1

Using PARAM_STR happens to always work in column values because mySQL implicitly converts values to the correct type where it can, but it will fail for example in this query:

$limit = 1;

$dbh->prepare("SELECT * FROM items LIMIT :limit");
$dbh->bindParam(":limit", $limit, PDO::PARAM_STR); 
     // Will throw "You have an error in your SQL syntax..."

one should absolutely use PARAM_INT where appropriate - for cases like the one above, and to prepare for database engines other than mySQL that may be more strict in what they expect.

Answer 2

personally I see no reason, as long as you have this attribute set:

$dbh->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );

so, it would detect LIMIT case automatically

anyway I'd prefer to define type in a placeholder, not in binding function.

However, my experience with PDO is not that strong. I just tried it out and decided to turn back to plain mysql.