DBI::SQL_INTEGER
DBI::SQL_SMALLINT
DBI::SQL_DECIMAL
DBI::SQL_FLOAT
DBI::SQL_REAL
DBI::SQL_DOUBLE
DBI::SQL_NUMERIC
[download]

That's also done if there's no type provided.

I've always found this to be more valuable because it helps avoid some malicious statements -- a user will have more trouble trying to drop one of my tables if his '; DROP TABLE users;' statement is quoted and treated as a literal string.

But the DBI book hedges on this topic... it actually says that it uses a heuristic to determine quotability when using placeholders, which is why I did the following instead:

 # As ubiquitous as hashes are in Perl, the DBI API does not
 # offer a way to commit hashes to disk.
 # @::order_data consists of the field names of my database
 # in order
 sub fiddle_with_my_data_to_get_it_to_work_with_the_DBI_API {

   my @output_data;
   for (@::order_data) {
     push @output_data, $dbh->quote
       (
        defined($::uregister->{$_}) 
        ? $::uregister->{$_} 
        : $::profile{$_}
       );
   }

   $::INSERT_TERM=join ',', @output_data;
 }
[download]

princepawn, but tye calls me "PrincePawn"

I am assuming that you are referring to whether or not DBI appropriately quotes data when we have code like the following (this is production code that I wrote for Special Olympics):

    my $sql = 'INSERT INTO ' . $table . '(title,       author, lesson_
+summary,
                                          recommended, grades, rationa
+le,
                                          standards) 
                                  VALUES (?, ?, ?,
                                          ?, ?, ?,
                                          ?)';
    my $sth = $dbh->prepare($sql);

    $sth->execute(
      $template->param('Title'), 
      $ENV{'REMOTE_USER'},
      $template->param('Summary'),
      $template->param('Recommended'), 
      $grades, 
      $template->param('Rationale'),  
      $standards
    );
[download]

If we stick a number into a char field in the database, no problem. The question is: what happens if it's the other way around. If you, as a programmer, try to stick "foo" into a numeric "ID" field, that's your own fault and isn't an issue with DBI. After considering that, the only other question that I could come up with is what happens if I try to stick an string which could be interpreted as an integer into a numeric database field. I tried this with mysql. I issued the following command:

update lesson_temp set id= '4' where id = 5;
[download]

select * from lesson_temp where id = 4;
[download]

If you have a more specific problem, I'd like to see it and maybe comment on it. However, remember that while something may be "foolproof", the universe is building bigger and better fools. Therefore, it falls upon you as a programmer to ensure that data validation is as iron-clad as you can make it. Trust yourself, not the database (or DBI).

Hope this helps.

Cheers,
Ovid

For those who are wondering, yes, the data in the CGI parameters were untainted earlier. It looks a bit odd as they have been left in the same variable.

Update: princepawn: you think Oracle would choke on something so basic? You may be right as I have not used it extensively, but I'm willing to bet my lunch money that one of the world's most expensive and robust databases can figure out a simple string to numeric conversion.