Beefy Boxes and Bandwidth Generously Provided by pair Networks Frank
Problems? Is your data what you think it is?
 
PerlMonks  

DBI confusion

by PerlRob (Sexton)
on Jul 23, 2008 at 07:11 UTC ( #699522=perlquestion: print w/ replies, xml ) Need Help??
PerlRob has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

How can I tell the difference between an empty result set and an error after calling the selectrow_arrayref() method of DBI? When a user types the wrong username, my script always dies at this point--but the errstr() method returns nothing:
$arrayRef = $dbh->selectrow_arrayref("SELECT password FROM table WHERE + username = '$username'") or die "Error: " . $dbh->errstr;

Whereas, if I do the following, my script never dies on a bad username, and the execute() method returns 'E0E', meaning the query succeeded but the result set was simply empty (i.e. the username doesn't exist):
$sth = $dbh->prepare("SELECT password FROM table WHERE userName = '$us +erName'") or die $dbh->errstr; $result = $sth->execute(); print "There were no rows returned\n" if $result == 0;

What am I doing wrong with selectrow_arrayref()?

Comment on DBI confusion
Select or Download Code
Re: DBI confusion
by moritz (Cardinal) on Jul 23, 2008 at 07:37 UTC
    What I do in my scripts is setting the RaiseError option to 1, and let it take care of all errors.

    Then you can do something along these lines:

    my $sth = $dbh->prepare("SELECT password FROM table WHERE userName = ? +"); $sth->execute($username); if ($sth->selectrow_array){ print "logged in\n"; }

    Please don't ever interpolate data into your SQL queries, use placeholders like I did in the example above. If you interpolate data, you're not safe against SQL injection - what if the user name actually is evil ' or '' = '?

Re: DBI confusion
by mje (Deacon) on Jul 23, 2008 at 08:30 UTC

    selectrow_arrayref can obviously find no rows to return in which case it returns undef:

    perl -e 'use DBI;$h = DBI->connect("dbi:Oracle:","xxx","yyy");my $r = +$h->selectrow_arrayref("select 1 from dual where 1=0");use Data::Dump +er;print Dumper($r);' $VAR1 = undef;

    Unfortunately, selectrow_arrayref also returns undef if there is an error:

    perl -e 'use DBI;$h = DBI->connect("dbi:Oracle:","xxx","xxx",{RaiseErr +or => 0,PrintError => 0});my $r = $h->selectrow_arrayref("select 1 fr +om dualxx");use Data::Dumper;print Dumper($r);' $VAR1 = undef;

    the error being "ORA-00942: table or view does not exist". Note I had to turn off RaiseError and PrintError to a) avoid DBI calling die and b) avoid DBI printing the error. To tell the difference between the two you need to set RaiseError and catch the die:

    perl -e 'use DBI;$h = DBI->connect("dbi:Oracle:","xxx","xxx",{RaiseErr +or => 1,PrintError => 0});my $r; eval{$r = $h->selectrow_arrayref("se +lect 1 from dualxx")};if ($@) { print "Error $@ and DBI err is $DBI:: +err";} else {use Data::Dumper;print Dumper($r);}'

    which outputs:

    Error DBD::Oracle::db selectrow_arrayref failed: ORA-00942: table or view does not exist (DBD ERROR: error possibly near <*>; indicator at char 14 in 'select 1 from <*>dualxx') for Statement "select 1 from dualxx" at -e line 1.
Re: DBI confusion
by Zen (Deacon) on Jul 23, 2008 at 12:14 UTC
    Read the DBI docs on placeholders. Your code is easily exploited.
Re: DBI confusion
by toke (Initiate) on Nov 06, 2012 at 16:14 UTC

    I know this is entry very old but I have to correct. DBI execute() never returns 'E0E' but '0E0' which evaluates to 0 in int context and true in boolean context. DBI selectrow_arrayref() returns an reference to an array or undef.

    In your 1st case:

    $arrayRef is undef when an error occours => false
    $arrayRef points to an empty list when no results are fetched => false
    $arrayRef points to array with the first row of data. => true
    
    You can use the // operator to check for undef. Like $arrayRef = $dbh->selectrow_arrayref($QUERY) // die "Error: " . $dbh->errstr;

    In your 2nd case:

    $result > 0 you got results. =>  true
    $result == "0E0" No results - no error => true
    $result == undef Error. => false
    
    Some recommendations:
    • use RaiseError and eval{} and error checking
    • Never to store passwords but salted hashes
    • As the fellow Monks suggested also use Placeholders (if fully supported by driver) or at last $dbh->quote($string)

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://699522]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (16)
As of 2014-04-16 14:38 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (430 votes), past polls