Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

Recalcitrant placeholders

by Bod (Curate)
on Jul 04, 2021 at 20:31 UTC ( #11134633=perlquestion: print w/replies, xml ) Need Help??

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

This has had me scratching my head for a while...

my $crid = $crm->db->selectrow_array("SELECT idPerson FROM Person WHER +E email = ? OR altEmail = ?", undef, $data{'email'}, $data{'email'}); print "ERROR: " . $crm->db->errstr if $crm->db->err; print "#$data{'email'}# - $crid";

The print statements are only there for debugging. The # is around the email to check no extraneous spaces had crept in which they haven't. $crm is my CRM module and the db method returns the database handle.

The output I get is #me@example.com# - 0
idPerson is an auto increment INT which starts at 1. There is no zero in the database!

If I manually query the database I get this:

SELECT idPerson FROM Person WHERE email = 'me@examle.com' OR altEmail += 'me@example.com' 90 102
Yet my code returns 0.

Things I have tried:

  • Pulling $crm->db into a separate variable first - no change
  • Replacing the placeholders with the variables WHERE email = '$data{'email'}' - no change.
  • Hardcoding the email WHERE email = 'me\@example.com' - that works!
  • Pulling the email into a separate variable my $email = $data{'email'} then putting that into the query WHERE email = '$email' - doesn't work
  • Forcing array context my ($crid) = $crm->db->selectrow_array - no change

I have found a workaround:

my $query = $crm->db->prepare("SELECT idPerson FROM Person WHERE email + = ? OR altEmail = ?"); $query->execute($data{'email'}, $data{'email'}); my $crid = $query->fetchrow_array; print "ERROR: " . $crm->db->errstr if $crm->db->err;
But I have selectrow_array working like this in other places. I don't understand what is going on here!

The DBI documentation says that selectrow_array is the equivalent of prepare, execute and fetchrow_array. It also says that selectrow_array returns undef if there is an error. Here it is returning zero which is not in the database!

Can you help me understand what is going on here?

Replies are listed 'Best First'.
Re: Recalcitrant placeholders
by kcott (Bishop) on Jul 05, 2021 at 06:39 UTC

    G'day Bod,

    I tried to reproduce your problem but couldn't.

    I created a test database:

    $ sqlite3 bod SQLite version 3.34.0 2020-12-01 16:14:00 Enter ".help" for usage hints. sqlite> create table Person (idPerson, email, altEmail); sqlite> insert into Person values (1, 'you@example.com', ''); sqlite> insert into Person values (2, '', 'me@example.com'); sqlite> insert into Person values (3, 'me@example.com', ''); sqlite> select * from Person; 1|you@example.com| 2||me@example.com 3|me@example.com| sqlite>

    Then a test script:

    #!/usr/bin/env perl use strict; use warnings; use DBI; my $dbh = DBI->connect("dbi:SQLite:dbname=bod"); print "Contents of Person:\n"; eval { print join('|', @$_), "\n" for $dbh->selectall_array('select * fro +m Person'); 1; } or do { print $dbh->errstr, "\n"; }; print "Head-scratching code:\n"; eval { my %data = (email => 'me@example.com'); my $crid = $dbh->selectrow_array("SELECT idPerson FROM Person WHER +E email = ? OR altEmail = ?", undef, $data{'email' }, $data{'email'}); print "$crid\n"; 1; } or do { print $dbh->errstr, "\n"; };

    Output:

    Contents of Person: 1|you@example.com| 2||me@example.com 3|me@example.com| Head-scratching code: 2

    As you can see, I had to make some guesses; however, I used a verbatim copy of your selectrow_array(). It correctly found the idPerson (i.e. 2) using your parameters.

    Instead of presenting us with bits of isolated code, please provide an SSCCE that reproduces your problem and is written in such a way that we can run it and help you towards a solution.

    What you could also do is create a temporary directory and, in there, create the test database exactly as I have and run exactly the same test script I presented; then compare the output.

    — Ken

      Instead of presenting us with bits of isolated code, please provide an SSCCE that reproduces your problem and is written in such a way that we can run it and help you towards a solution.

      I cannot reproduce the problem myself...

      To try and get to the bottom of the problem, I have written this script:

      #!/usr/bin/perl -T use CGI::Carp qw(fatalsToBrowser); use FindBin qw($RealBin); my $safepath; BEGIN { if ($RealBin =~ m!^(/home/username/uk/www)!) { $safepath = "$1/../lib"; } else { die "Illegal use of software - visit www.example.com to use th +is site"; } } use lib "$safepath"; use Site::HTML; use Site::Wayfinder; use Bod::CRM; use strict; use warnings; my $crm = Bod::CRM->new('test'); print "Content-type: text/plain\n\n"; my $db = '...'; my $un = '...'; my $pw = '...'; ##### Uncomment one line ##### #my $dbh = DBI->connect("dbi:mysql:$db:localhost:3306",$un,$pw) || die + "DB ERROR: " . $dbh->errstr; my $dbh = $crm->db; $dbh->do("CREATE TEMPORARY TABLE Temp_Test ( idTest INT NOT NULL AUTO_INCREMENT PRIMARY KEY, fname VARCHAR(40), nname VARCHAR(40), sname VARCHAR(60), email VARCHAR(100), altEmail VARCHAR(100) ) ENGINE MyISAM"); print "ERROR: " . $dbh->errstr if $dbh->err; $dbh->do("INSERT INTO Temp_Test (fname, nname, sname, email) VALUES (' +Ian', 'Bod', 'Boddison', 'me\@example.com')"); $dbh->do("INSERT INTO Temp_Test (fname, nname, sname, email) VALUES (' +Boomer', 'Boo', 'Dog', 'dog\@example.com')"); $dbh->do("INSERT INTO Temp_Test (fname, nname, sname, email) VALUES (' +Oi', '', 'You', 'you\@example.com')"); my $query = $dbh->prepare("SELECT * FROM Temp_Test"); $query->execute; $, = ' - '; while (my @row = $query->fetchrow_array) { print @row; print "\n"; } my %data; $data{'email'} = 'dog@example.com'; my $crid = $dbh->selectrow_array("SELECT idTest FROM Temp_Test WHERE e +mail = ? OR altEmail = ?", undef, $data{'email'}, $data{'email'}); print "ERROR: " . $dbh->errstr if $dbh->err; print "\nCRID: $crid\n";
      This correctly dumps the table data and then displays CRID: 2

      So what I have done is to go back to the code that is behaving strangely and double check it.
      It has been stripped back to bare minimum and the strange result still happens:

      #!/usr/bin/perl -T use CGI::Carp qw(fatalsToBrowser); use FindBin qw($RealBin); my $safepath; BEGIN { if ($RealBin =~ m!^(/home/username/uk/www)!) { $safepath = "$1/../lib"; } else { die "Illegal use of software - visit www.example.com to use th +is site"; } } use lib "$safepath"; use Site::HTML; use Site::Wayfinder; use Bod::CRM; use strict; use warnings; my $html = Site::HTML->new; my $wf = Site::Wayfinder->new; my $crm = Bod::CRM->new('test'); $html->head; my $logbox = 'log_login'; $logbox = 'log_fpass' if $data{'command'} eq 'fpass'; # Reset password if ($data{'command'} eq 'rpass') { my $test = $crm->db->selectrow_array("SELECT idPerson FROM Person +WHERE email = ? OR altEmail = ?", undef, $data{'email'}, $data{'email +'}); print "<p>TEST: $test</p>\n"; my $query = $crm->db->prepare("SELECT idPerson FROM Person WHERE e +mail = ? OR altEmail = ?"); $query->execute($data{'email'}, $data{'email'}); my $crid = $query->fetchrow_array; print "<p>CRID: $crid</p>\n"; exit; } __END__
      This has two ways of getting the same data from the database.

      The output is:

      TEST: 0 CRID: 1
      The selectrow_array function should not be able to return zero when zero does not exist anywhere in the idPerson field of the database.

      update:

      Just to prove it is not something that is happening in Site::HTML->head, I have changed:

      $html->head;
      for
      #$html->head; print "Content-type: text/plain\n\n";
      and the output now is:
      <p>TEST: 0</p> <p>CRID: 1</p>

        "I cannot reproduce the problem myself..."

        Why not? Please explain.

        "To try and get to the bottom of the problem, I have written this script:"

        But why post it here. It's littered with all sorts of things unrelated to the base problem which, until we know more, would seem to be database-related. There is taint-related code, web-related code, and three non-CPAN modules about which we basically know nothing. This is not an SSCCE.

        I wrote a succinct test and suggested you try it: that doesn't appear to have happened. At the time of writing, you hadn't told us what database you were using. I chose SQLite because it's common and easy to use. If you don't have it, you should be able to write something equally simple with "dbi:SQLite:..." replaced with "dbi:mysql:...".

        "The selectrow_array function should not be able to return zero when zero does not exist anywhere in the idPerson field of the database."

        What's the basis for that claim?

        In "DBI: selectrow_array", there are three examples with '@row_ary = ...'; none with '$some_scalar = ...'. It talks about how calling this in a scalar context can be handled differently depending on the driver being used. It ends with "... you should exercise some caution if you use selectrow_array in a scalar context, or just don't do that." [my emphasis]

        Perhaps what you're seeing is something akin to:

        $ perl -E 'my @x = (); my $y = @x; say $y' 0

        If, after writing an SSCCE and perhaps resolving issues, this still fails in your main application, I would suggest the next step would be to write a /path/to/Bod/CRM/NN-selectrow_array.t and compare '$dbh->selectrow_array(...)' with '$crm->db->selectrow_array(...)'.

        My code shown so far uses Perl(5.34.0), DBI(1.643) and DBD::SQLite(1.66). I don't have DBD::mysql installed and I haven't used MySQL for about 20 years, so I can't really help you with that.

        — Ken

Re: Recalcitrant placeholders
by GrandFather (Saint) on Jul 04, 2021 at 21:22 UTC

    Of error handling and calling selectrow_array in scalar context the DBI documentation says:

    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.

    Does any of that ring alarm bells? Maybe setting RaiseError will help if you haven't already?

    Optimising for fewest key strokes only makes sense transmitting to Pluto or beyond

      The query doesn't return more than one column.

      However, I checked it wasn't a scalar issue in one of the things I tried...
      - Forcing array context my ($crid) = $crm->db->selectrow_array - no change

      Raise Error is on by default and would be caught by the error trapping on the next line.

      edit - it also isn't returning undef, it is returning zero which is what is really confusing.

Re: Recalcitrant placeholders
by hippo (Bishop) on Jul 04, 2021 at 21:21 UTC
    SELECT idPerson FROM Person WHERE email = 'me@examle.com' OR altEmail = 'me@example.com' ... Hardcoding the email WHERE email = 'me\@example.com' - that works!

    Those email addresses are not the same.


    🦛

      WHERE email = 'me@examle.com' OR altEmail = 'me@example.com'

      That fails at compilation time saying that @example is not declared

        A copy and paste of "examle" is the correct thing to do, but it's not the same as copy and comprehend: "examle" ne "example".

        Update: ENOTENOUGHCOFFEE

        Optimising for fewest key strokes only makes sense transmitting to Pluto or beyond
Re: Recalcitrant placeholders
by Fletch (Chancellor) on Jul 04, 2021 at 21:48 UTC

    General tip: enabling trace on your handles can be elucidating to see how things are actually expanding and what's being passed into the DB engine. Before your execute do $query->trace(2) (or some variation).

    The cake is a lie.
    The cake is a lie.
    The cake is a lie.

      Thanks Fletch
      I shall try that tomorrow and it might help explain what is going on.

Re: Recalcitrant placeholders
by shmem (Chancellor) on Jul 05, 2021 at 19:00 UTC

    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'
      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". ;-)

      $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". ;-)
Re: Recalcitrant placeholders
by stevieb (Canon) on Jul 04, 2021 at 20:39 UTC

    Instead of WHERE email = 'me\@example.com', what happens if you use WHERE email = 'me@example.com'? Does it break the same way?

      Does it break the same way?

      Yes - it complains that I have not defined @example!

        Who complains? Perl or the database?

        map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]
Re: Recalcitrant placeholders
by Anonymous Monk on Jul 05, 2021 at 03:00 UTC
    Which database driver exactly? Tip: Use Data::Dump for debugging not plain print

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (2)
As of 2021-12-05 23:36 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    R or B?



    Results (31 votes). Check out past polls.

    Notices?