10

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.

Vladislav Rastrusny
  • 29,378
  • 23
  • 95
  • 156

2 Answers2

7

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.

Pekka
  • 442,112
  • 142
  • 972
  • 1,088
  • +1 from me, excellent example. It's not just SQL injection that should be avoided, displaying raw MySQL error messages to user isn't really appealing nor professional so the developer **should** always enforce type safety. – N.B. Apr 26 '11 at 17:21
  • @N.B. to me it's too boring. I'd prefer concise code over repeating this binding again and again – Your Common Sense Apr 26 '11 at 17:23
  • 1
    What PHP version did you test your code on? I have 5.3.1 on Windows here and no exception is thrown either with PDO::ATTR_EMULATE_PREPARES or without on parametrized LIMIT clauses as @Col. suggested. And what about other databases? Do they do type conversion as well? – Vladislav Rastrusny Apr 27 '11 at 06:31
5

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.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345