DBI confusion

[PerlRob]

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()?

[moritz]

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 '' = '?

[choroba]

selectrow_array is a method of the database handle, the method of the statement handle is called fetchrow_array. See DBI.

[mje]

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.

[Zen]

Read the DBI docs on placeholders. Your code is easily exploited.

[toke]

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)