RSevrinsky has asked for the wisdom of the Perl Monks concerning the following question:

In the process of testing various scripts for an upcoming upgrade from perl 5.004 to 5.005_03 and from DBI 0.91 to 1.13, I've run across a glitch in using DBI->quote() on tied elements of hashes.

Specifically, I'm trying to use DBI to automatically quote an element of a hash tied via CGI->Vars: 

use DBI;
use CGI;

$q = new CGI("test=foo");
$p = $q->Vars;
$dbh = DBI->connect("dbi:mysql:test");

print "element is $p->{test}, quoted element is " .
  $dbh->quote($p->{test}) . "\n";

$test = $p->{test};
print "element is $test, quoted element is " .
  $dbh->quote($test) . "\n";

[download]

For some reason, it seems that DBI thinks that its parameter is not defined() and returns NULL. I've tried this with NDBM_File as well and experienced the same behavior.

Strangely enough, if you try tracing the DBI handle at level 2 or higher, it works just fine: 

DBI->trace(2, 'dbitrace.log');

[download]

Is there something I can do to use the tied hash elements without constant copying them out, and without slowing down for full tracing?

Replies are listed 'Best First'.
Re: DBI-quote doesn't like tied hashes?
by chromatic (Archbishop) on May 17, 2000 at 01:00 UTC
    I prefer to use placeholders: 
    my $sth = $dbh->prepare("SELECT * FROM ?");
$sth->execute($p->{test});

    [download]
    Quoting is taken care of automatically. Not only is there one less thing to worry about, it allows for more generic code.
[reply]
[d/l]
      chromatic wrote: 
          > I prefer to use placeholders:
    >
    > my $sth = $dbh->prepare("SELECT * FROM ?");

      [download]
      I also prefer to use placeholders, but only when I can actually use them. :) In the databases I've used, you can't use a placeholder for the table name, because the database driver doesn't have enough information to create an execution plan if it doesn't know the table name.

      In general, though, I heartily endorse the use of placeholders. :)

[reply]
[d/l]
        Yeah, and in those cases (ie; with table names) I do it both ways...
        $sth = $dbh->prepare(qq{
select $ouch,$this from $hurts
where $another = ?
}) or DoSomethingElse();

        [download]
        I know this looks stupid has hell, and it has bothered me in the past too! But, I don't use it that often so I never really bothered looking for a solution. At least keeping the values within the placeholders I get DBI to quote them for me. I haven't had the need to quote a table name yet...
[reply]
[d/l]
Re: DBI-quote doesn't like tied hashes?
by btrott (Parson) on May 17, 2000 at 07:09 UTC
    You're right (just tested it). That's pretty weird behavior, and unfortunately, I'm not sure exactly whose fault it is. :)

    Here's another (better?) way to fix it: put it in double quotes to force it into string context: 

        print $dbh->quote("$p->{test}");

    [download]
    This fixes it for me. If you do use this, I'd investigate it more to figure out *why* this fixes it, so as not to be burned by any future changes that could screw this up.
[reply]
[d/l]

Back to Seekers of Perl Wisdom