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

"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

[download]

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

Replies are listed 'Best First'.
Re^4: Recalcitrant placeholders
by Bod (Parson) on Jul 09, 2021 at 18:03 UTC
    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...

[reply]
      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.

[reply]
[d/l]
[select]
        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);

        [download]
        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;

        [download]
        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.

[reply]
[d/l]
[select]
      Those unrelated things have to be there.

      For the SSCCE?

      🦛

[reply]

In Section Seekers of Perl Wisdom