PDO::PARAM_INT is important in bindParam?

Question

Add PDO::PARAM_INT or PDO::PARAM_STR have any meaning in Mysql query?

$sql  = 'SELECT TagId FROM tagthread WHERE ThreadId = :ThreadId';  $stmt = $this->db->prepare($sql); $stmt->bindParam(':ThreadId', $threadid, PDO::PARAM_INT);  $stmt->execute(); 

Answer 1

Yes, use it.

I did a few tests (with PDO::ATTR_EMULATE_PREPARES false) and I found out that the quotes around the values will be different.

When you bind an integer value with PARAM_INT there will be no quotes in the query (A string value with PARAM_INT has quotes). If you bind an integer value with PDO::PARAM_STR there will be quotes and mysql has to cast to integer.

Examples:

$stmt->bindParam(':ThreadId', $threadid, PDO::PARAM_INT); $threadid = 123; // SELECT TagId FROM tagthread WHERE ThreadId = 123 $threadid = '123test'; // SELECT TagId FROM tagthread WHERE ThreadId = '123test' // mysql will cast 123test to 123 

EDIT:

I further tested and read on that topic. Conclusion: Implicit casting is dangerous and can lead to unexpected results. Read more on that here. Another disadvantage to always use PDO::PARAM_STR is the performance. Read more on performance Disadvantages of quoting integers in a Mysql query?

So if your column is of type [TINY|SMALL|MEDIUM|BIG]INT than use PARAM_INT. And in case it is a LIMIT clause than cast to integer if the variable type in PHP is not integer.

Answer 2

Edit: Depends! See Your Common Sense comment below.

If the value is a integer it should be treated as an integer. Apply this with as many datatypes as possible.

If you don't set the Attribute of PDO::ATTR_EMULATE_PREPARES to false, you will get a nasty error.

Solid example:

$stmt = $dbh->prepare("SELECT * FROM table123 WHERE raw_field = :field LIMIT 1 OFFSET :offset;"); $stmt->bindParam(':field',  $field); $stmt->bindParam(':offset', $offset);  if ($map_stmt->execute()) {     $data = stmt->fetch(PDO::FETCH_ASSOC); } else {     echo 'Error :';     echo '<pre>';     print_r($map_stmt->errorInfo());     print_r($map_stmt->debugDumpParams());     echo '</pre>'; } 

Will return back a nasty error containing:

Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0'' at line 1

Query: SELECT * FROM table123 WHERE raw_field = 'home' LIMIT 1 OFFSET '0'

Useless you treat it as an integer, and it will remove the string (e.g.: ' ').

$stmt->bindParam(':offset', $offset, PDO::PARAM_INT); 

In a nutshell:

You choose! Strict data or not..