Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

Re^5: Recalcitrant placeholders

by haukex (Bishop)
on Jul 09, 2021 at 19:51 UTC ( #11134866=note: print w/replies, xml ) Need Help??


in reply to Re^4: Recalcitrant placeholders
in thread Recalcitrant placeholders

Those unrelated things have to be there.

That logically doesn't make sense ;-)

The main issue is that your code relies on libraries that we don't have - the most important thing to keep in mind is that we need to be able to run the code and reproduce the issue ourselves.

The problem goes away if I turn off taint mode...

Well that's certainly interesting and might indicate a bug somewhere. Unfortunately I was unable to reproduce the issue you're seeing as well, which means the issue may be somewhere outside of the code you've shown here.

I would consider as a bare minimum that you inline the code of $crm->db, remove the other dependencies, and show the output of Data::Dumper (with $Data::Dumper::Useqq=1;) of %data.

Also, GrandFather, afoken, and kcott all commented that both the selectrow_array and fetchrow_array documentation state "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." really do mean you shouldn't be doing that*. Stated a little differently, you shouldn't be surprised that this undefined behavior is biting you. It may not even be worth trying to debug this and instead just using the API the way the documentation says you should...

* Update: Yes, even if you only have one column. afoken's point that the documentation clearly warns you away from scalar context is important.

Replies are listed 'Best First'.
Re^6: Recalcitrant placeholders
by Bod (Deacon) on Jul 10, 2021 at 13:45 UTC
    Also, GrandFather, afoken, and kcott all commented that both the selectrow_array and fetchrow_array documentation state "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." really do mean you shouldn't be doing that*. Stated a little differently, you shouldn't be surprised that this undefined behavior is biting you. It may not even be worth trying to debug this and instead just using the API the way the documentation says you should...

    I addressed this in the original question:
    Things I have tried...Forcing array context my ($crid) = $crm->db->selectrow_array - no change

    The problem goes away if I turn off taint mode...
    Well that's certainly interesting and might indicate a bug somewhere.

    I thought it was an isolated incident confined to this piece of code. But it has broken Bod::CRM which we discussed in [RFC] Review of module code and POD.
    The worrying thing is that it fails silently. Luckily I picked up the issue with the test instance of our CRM database.

    I *think* it is because the value passed to the placeholder originated from STDIN via the POST from a web form (or occasionally from the QUERY_STRING CGI environment variable). I suspect Perl considers this to be tainted but doesn't warn about it in any way. Perhaps this is a bug in DBI. Especially as the documentation for selectrow_array says "This utility method combines "prepare", "execute" and "fetchrow_array" into a single call"

    But this fails:

    my ($value) = $dbh->selectrow_array("SELECT value FROM Test WHERE idTe +st = ?", undef, $value_from_stdin);
    Yet this, which is supposed to be equivelent, works:
    my $query = $dbh->prepare("SELECT value FROM Test WHERE idTest = ?"); $query->execute($value_from_stdin); my ($value) = $query->fetchrow_array;
    As a consequence, I am going through code used under taint mode and changing selectrow_array for the above modification if there is any chance that the placeholder data might be considered tainted.

    update

    Some testing has proved that it is about tainted data being passed to the placeholders and only with selectrow_array, not execute and fetchrow_array. With taint mode on, if I pass a value to the placeholder that has come from STDIN then it fails silently. But if I pass the same value assigned to the variable in my script and pass that to the placeholder, it works.

    This behaviour makes sense but it would also make sense for it to throw an error when I try to do it rather than failing silently.

      Sorry for the late reply.

      I addressed this in the original question: Things I have tried...Forcing array context my ($crid) = $crm->db->selectrow_array - no change

      I missed that, sorry about that.

      Some testing has proved that it is about tainted data being passed to the placeholders and only with selectrow_array, not execute and fetchrow_array. With taint mode on, if I pass a value to the placeholder that has come from STDIN then it fails silently. But if I pass the same value assigned to the variable in my script and pass that to the placeholder, it works.

      Thanks for the further information, that does somewhat make sense. Unfortunately, I still haven't been able to reproduce it. What version of MySQL, DBI, DBD::mysql, etc. are you using? Does the code below produce the incorrect results for you?

      This behaviour makes sense but it would also make sense for it to throw an error when I try to do it rather than failing silently.

      The DBI docs say that as of v1.31, you can turn on the TaintIn option, which works for me in that it causes DBI to die with tainted arguments.

      #!/usr/bin/perl -T use warnings; use strict; use feature 'say'; use Scalar::Util qw/tainted/; use DBI; use DBI::Const::GetInfoType; my $dbh = DBI->connect( "DBI:mysql:database=testing;host=127.0.0.1", $ENV{USER}, 'barfoo', { RaiseError=>1, AutoCommit=>1, TaintIn=>0 }); say "Perl: $]"; say "Database: ", $dbh->get_info( $GetInfoType{SQL_DBMS_NAME} ), " ", $dbh->get_info( $GetInfoType{SQL_DBMS_VER} ); say "Driver: ", $dbh->{Driver}->{Name}; say "DBI Ver: ", $DBI::VERSION; say "DBD::mysql Ver: ", $DBD::mysql::VERSION; $dbh->do('DROP TABLE IF EXISTS Person'); $dbh->do(<<'ENDSQL'); CREATE TABLE Person ( idPerson VARCHAR(256), email VARCHAR(256), altEmail VARCHAR(256) ); ENDSQL $dbh->do('INSERT INTO Person (idPerson, email, altEmail) VALUES ("hell +o","foo@bar.com","foo@bar.com");'); die "run me with an empty string as the first argument" unless @ARGV && !length $ARGV[0]; my %data = ( email => 'foo@bar.com'.shift(@ARGV) ); die unless tainted($data{'email'}); my $query = $dbh->prepare("SELECT idPerson FROM Person WHERE email = ? + OR altEmail = ?"); $query->execute($data{'email'}, $data{'email'}); my ($crid) = $query->fetchrow_array; say "CRID: $crid"; my ($test) = $dbh->selectrow_array("SELECT idPerson FROM Person WHERE +email = ? OR altEmail = ?", undef, $data{'email'}, $data{'email'}); say "TEST: $test"; __END__ Perl: 5.032001 Database: MySQL 5.7.33 Driver: mysql DBI Ver: 1.643 DBD::mysql Ver: 4.050 CRID: hello TEST: hello

      I spun up the test database with Docker as I showed in this node.

        Sorry for the very late reply...I've not had a chance to look until this weekend.

        Does the code below produce the incorrect results for you?

        The code produces this result:

        Perl: 5.016003 Database: MySQL 10.2.39-MariaDB Driver: mysql DBI Ver: 1.643 DBD::mysql Ver: 4.050 CRID: hello TEST: hello

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (3)
As of 2021-09-22 22:04 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?