DBI confusion

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;
[download]

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;
[download]

What am I doing wrong with selectrow_arrayref()?

Comment on DBI confusion Select or Download Code

Replies are listed 'Best First'.
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"; }` [download] 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 '' = '`?	[reply] [d/l] [select]
Re^2: DBI confusion by choroba (Cardinal) on Jun 01, 2017 at 08:45 UTC
selectrow_array is a method of the database handle, the method of the statement handle is called fetchrow_array. See DBI. ($q=q:Sq=~/;[c](.)(.)/;chr(-\|\|-\|5+lengthSq)`"S\|oS2"`map{chr \|+ord }map{substrSq`S_+\|`\|}3E\|-\|`7**2-3:)=~y+S\|`+$1,++print+eval$q,q,a, [download]	[reply] [d/l]
Re: DBI confusion by mje (Curate) 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;` [download] 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;` [download] 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);}'` [download] 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.	[reply] [d/l] [select]
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.	[reply]
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)`	[reply] [d/l] [select]