Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Re^8: Recalcitrant placeholders

by Bod (Parson)
on Jul 25, 2021 at 14:31 UTC ( [id://11135379]=note: print w/replies, xml ) Need Help??


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

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

Replies are listed 'Best First'.
Re^9: Recalcitrant placeholders
by haukex (Archbishop) on Jul 28, 2021 at 09:17 UTC
    DBI Ver: 1.643 DBD::mysql Ver: 4.050 CRID: hello TEST: hello

    That means you've got the latest drivers, and more importantly, that my code fails to reproduce your issue even on your end. Unfortunately, I don't know where the significant difference between my code (which doesn't have the issue) and your code (which apparently does have the issue) is. Is there any chance you could modify my SSCCE so that it no longer works correctly on your system, so that I can try to reproduce the issue on my end? (Of course, you could also just turn on TaintIn and work around the issue that way.)

      Sorry for the delay haukex

      It's taken a little while but I have replicated the problem. First I converted it to run in a CGI context on a webserver. I could not replicate it and was beginning to think I never had the problem...but by changing the idPerson column to an INT I can break it by turning on taint mode:

      #!/usr/bin/perl -T use CGI::Carp qw(fatalsToBrowser); use warnings; use strict; use feature 'say'; use Scalar::Util qw/tainted/; use DBI; use DBI::Const::GetInfoType; my @argv; if ($ENV{'GATEWAY_INTERFACE'}) { @argv = split /&/, $ENV{'QUERY_STRING'}; } else { @argv = @ARGV; } my $db_user = 'xxx'; my $db_pass = 'xxx'; my $dbh = DBI->connect( "DBI:mysql:database=shoples1_testing;host=127.0.0.1", $db_user, $db_pass, { RaiseError=>1, AutoCommit=>1, TaintIn=>0 }); print "Content-type: text/plain\n\n"; 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 INT, email VARCHAR(256), altEmail VARCHAR(256) ); ENDSQL $dbh->do('INSERT INTO Person (idPerson, email, altEmail) VALUES (5, ?, + "foo@bar.com");', undef, $argv[1]); die "run me with an empty string as the first argument" unless @argv && !length $argv[0]; my %data = ( email => $argv[1] ); say "Email is tainted" if tainted($data{'email'}); say "EMAIL: $argv[1]"; 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__

      Without taint mode I get this:

      Perl: 5.016003 Database: MySQL 10.2.39-MariaDB Driver: mysql DBI Ver: 1.643 DBD::mysql Ver: 4.050 EMAIL: foo@bar.com CRID: 5 TEST: 5
      By doing nothing other than adding the -T switch to the shebang and I get this:
      Perl: 5.016003 Database: MySQL 10.2.39-MariaDB Driver: mysql DBI Ver: 1.643 DBD::mysql Ver: 4.050 Email is tainted EMAIL: foo@bar.com CRID: 5 TEST: 0

      The script has been adapted to run from the command line or under CGI. The output is the same in both cases so it is not an environment issue.

        What happens if you untaint the email address? I don't have a mysql to test against, and didn't feel like changing the database aspects. But to untaint the email entry of your hash, you just need to follow the general rules for laundering tainted data. I would assume that once it's untainted, the TEST would give 5 again (untested).

        Example of untainting an email address hash entry:

        C:\Users\peter.jones\Downloads\TempData\perl>perl -T -M5.012 -Mwarning +s -MScalar::Util=tainted -le "my %h = (email => $ARGV[0]); print tain +ted($h{email}); $h{email} =~ m/\b([[:graph:]]+@[[:graph:]]+)\b/g and +$h{email} = $1; print tainted($h{email});" me@example 1 0
        (you should probably pick a stronger/more-accurate regex than I used for "valid email address"...)
        Sorry for the delay haukex

        I hope we can stop apologizing to each other, sometimes good things take time ;-D

        Update 2: Sorry, I should have also said: Thanks for taking the time to work out the SSCCE, I can now reproduce the issue reliably! /Update2

        I've made a bit of progress: The issue exists in Perl 5.16, but not in any Perl version after that (same versions of MySQL, DBI, and DBD::mysql).

        Unfortunately, DBI and DBD::mysql contain a bunch of XS code. I can only guess that there was some bug in regards to XS code and taint mode in Perl itself or in the interaction between the driver's XS and Perl - whatever it is, it appears to have been fixed in 5.18.

        Perl 5.16.3 is now over 8 years old and no longer supported. You may want to ask your webhost to upgrade their Perl. Or, perhaps they already have a newer Perl installed, under a different name such as perl5.XX.

        Update: I ran a bisect, and it points to 4bac9ae Magic flags harmonization. However, since this is very internals-heavy, I have no idea if this is a red herring or not. Unfortunately, this is as far as I can go at the moment - my suggestion to get a newer Perl stands.

      Is there any chance you could modify my SSCCE so that it no longer works correctly on your system

      Yes - of course

      It won't be for a few days though but I will get it done.
      Many thanks for your help :)

Log In?
Username:
Password:

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

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

    No recent polls found