Re^7: Recalcitrant placeholders

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

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

Comment on Re^7: Recalcitrant placeholders Select or Download Code

Replies are listed 'Best First'.
Re^8: Recalcitrant placeholders by Bod (Parson) on Jul 25, 2021 at 14:31 UTC
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` [download]	[reply] [d/l]
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` [download] 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.)	[reply] [d/l] [select]
Re^10: Recalcitrant placeholders by Bod (Parson) on Aug 05, 2021 at 19:07 UTC
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__ [download] 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` [download] 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` [download] 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.	[reply] [d/l] [select]
Re^11: Recalcitrant placeholders by pryrt (Abbot) on Aug 05, 2021 at 20:27 UTC
Re^12: Recalcitrant placeholders by Bod (Parson) on Aug 05, 2021 at 23:24 UTC
Some notes below your chosen depth have not been shown here
Re^11: Recalcitrant placeholders (updated x2) by haukex (Archbishop) on Aug 06, 2021 at 17:20 UTC
Re^12: Recalcitrant placeholders (updated x2) by Bod (Parson) on Aug 08, 2021 at 18:52 UTC
Some notes below your chosen depth have not been shown here
Re^10: Recalcitrant placeholders by Bod (Parson) on Aug 02, 2021 at 22:36 UTC
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 :)	[reply]