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

Re: Recalcitrant placeholders

by kcott (Archbishop)
on Jul 05, 2021 at 06:39 UTC ( [id://11134654]=note: print w/replies, xml ) Need Help??


in reply to Recalcitrant placeholders

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

Replies are listed 'Best First'.
Re^2: Recalcitrant placeholders
by Bod (Parson) on Jul 05, 2021 at 20:39 UTC
    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

        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

        Those unrelated things have to be there.
        The problem goes away if I turn off taint mode...

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others surveying the Monastery: (3)
As of 2024-03-29 02:22 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found