I'm trying to wrap my brain around your problem and I'm not quite sure what the problem is. Because Perl does not enforce types down to the int/string level but instead switches between necessary types when needed, there is indeed an question of what's going on with this data. However, I'm not sure that this is a Perl question.

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]
You are wondering, if I understand you correctly, whether or not DBI always properly quotes variables that get stuffed into the ? placeholders. However, I feel that this is really a non-issue.

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]
Because I wrapped the 4 in single quotes, it can be interpreted as a string. Mysql had no problem with it. Then I issued this command: 
select * from lesson_temp where id = 4;

[download]
It returned the row with no problem. Clearly, Mysql is smart enough to avoid minor pitfalls like that. I would presume that other databases are similarly able to deal with this. As a result, I think this question is not an issue.

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.

In reply to (Ovid) Re: Reliability of quoting via DBI placeholders by Ovid
in thread Reliability of quoting via DBI placeholders by princepawn

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.