Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

Re: Recalcitrant placeholders

by shmem (Chancellor)
on Jul 05, 2021 at 19:00 UTC ( [id://11134678]=note: print w/replies, xml ) Need Help??


in reply to Recalcitrant placeholders

What happens if you do this?

my $crid = $crm->db->selectrow_array("SELECT idPerson FROM Person WHER +E email = ? OR altEmail = ?", undef, quotemeta $data{'email'}, quotem +eta $data{'email'});
perl -le'print map{pack c,($-++?1:13)+ord}split//,ESEL'

Replies are listed 'Best First'.
Re^2: Recalcitrant placeholders
by afoken (Chancellor) on Jul 06, 2021 at 20:43 UTC
    my $crid = $crm->db->selectrow_array("SELECT idPerson FROM Person WHER +E email = ? OR altEmail = ?", undef, quotemeta $data{'email'}, quotem +eta $data{'email'});

    That looks just wrong. There should be absolutely no need to quote bind values in DBI. Adding any kind of extra quoting just makes things worse. DBI and the DBD take care of everything needed to pass bind parameters to the database.

    Alexander

    --
    Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
Re^2: Recalcitrant placeholders
by Bod (Parson) on Jul 05, 2021 at 20:47 UTC

    $crid is undef

      my $crid = $crm->db->selectrow_array
      $crid is undef

      Why am I not surprised? Let's RTFM:

      selectrow_array
      @row_ary = $dbh->selectrow_array($statement); @row_ary = $dbh->selectrow_array($statement, \%attr); @row_ary = $dbh->selectrow_array($statement, \%attr, @bind_values);

      This utility method combines "prepare", "execute" and "fetchrow_array" into a single call. If called in a list context, it returns the first row of data from the statement. The $statement parameter can be a previously prepared statement handle, in which case the prepare is skipped.

      If any method fails, and "RaiseError" is not set, selectrow_array will return an empty list.

      If called in a scalar context for a statement handle that has more than one column, it is undefined whether the driver will return the value of the first column or the last. So don't do that. Also, in a scalar context, an undef is returned if there are no more rows or if an error occurred. That undef can't be distinguished from an undef returned because the first field value was NULL. For these reasons you should exercise some caution if you use selectrow_array in a scalar context, or just don't do that.

      Let me shorten that a little bit for weary eyes:

      • selectrow_array() is designed to be called in LIST context
      • selectrow_array() returns an empty LIST on error
      • Behaviour in scalar context is not well-defined
      • In scalar context, you can't tell the difference between an error and NULL returned
      • DBI warns not just once, but twice twice to AVOID SCALAR CONTEXT for call selectrow_array()

      Now, error handling. DBI offers a tedious, error-prone and highly annoying way of error handling, and a smart and lazy way of error handling.

      The tedious way is to add an extra error check to almost any DBI method call. Just like you would have to do in assembler or in C. You need to study the DBI documentation to find out how each method behaves on error. And yes, they do behave differently.

      Or, you could have DBI and perl do all of that grunt work for you, by just adding RaiseError => 1 to the attribute hash of connect(). That automatically adds error checks to all methods, and all methods will handle errors in the same way: They die instead of returning special error indicator values. And that's almost always exactly what you want. It's also extremely useful for doing transactions, see "Transactions" in the DBI documentation. This is copied right out of that section:

      use Try::Tiny; $dbh->{AutoCommit} = 0; # enable transactions, if possible $dbh->{RaiseError} = 1; try { foo(...) # do lots of work here bar(...) # including inserts baz(...) # and updates $dbh->commit; # commit the changes if we get this far } catch { warn "Transaction aborted because $_"; # Try::Tiny copies $@ into +$_ # now rollback to undo the incomplete changes # but do it in an eval{} as it may also fail eval { $dbh->rollback }; # add other application on-error-clean-up code here };

      (You could also do without Try::Tiny, by using an eval BLOCK that returns a true value:)

      $dbh->{AutoCommit} = 0; # enable transactions, if possible $dbh->{RaiseError} = 1; eval { foo(...) # do lots of work here bar(...) # including inserts baz(...) # and updates $dbh->commit; # commit the changes if we get this far 1; } or do { warn "Transaction aborted because $@"; # now rollback to undo the incomplete changes # but do it in an eval{} as it may also fail eval { $dbh->rollback }; # add other application on-error-clean-up code here };

      There are rare cases when you expect an SQL statement to fail (like testing if a table exists by simply using it in an SQL statement). Wrap them into eval BLOCK or try/catch and you are done.

      And finally, doing database stuff in a web context with RaiseError set. Yes, an uncaught (i.e. unhandled) database error will kill your program, resulting in an ugly "500 Internal Server Error" or something similar. That's a good thing. It tells you that your code still has errors. As I wrote: If you expect database errors, wrap them in eval BLOCK or try/catch. For a simple-minded CGI, wrapping all of the database stuff in a big eval BLOCK and returning a "database oopsie" page on error is often acceptable.

      Alexander

      --
      Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://11134678]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others wandering the Monastery: (5)
As of 2024-03-28 11:36 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found