Re^10: Recalcitrant placeholders

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.

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

Replies are listed 'Best First'.
Re^11: Recalcitrant placeholders by pryrt (Abbot) on Aug 05, 2021 at 20:27 UTC
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` [download] (you should probably pick a stronger/more-accurate regex than I used for "valid email address"...)	[reply] [d/l]
Re^12: Recalcitrant placeholders by Bod (Parson) on Aug 05, 2021 at 23:24 UTC
What happens if you untaint the email address? Not what I was expecting to happen!!! I was pretty sure that untainting the email address would have the same effect as turning off taint mode but it doesn't. This code: Read more... (3 kB) produces this with taint mode off: `Perl: 5.016003 Database: MySQL 10.2.39-MariaDB Driver: mysql DBI Ver: 1.643 DBD::mysql Ver: 4.050 Tainted... EMAIL: foo@bar.com Untainted... EMAIL: foo@bar.com CRID: 5 TEST: 5` [download] and this with taint mode on: `Perl: 5.016003 Database: MySQL 10.2.39-MariaDB Driver: mysql DBI Ver: 1.643 DBD::mysql Ver: 4.050 Email is tainted Tainted... EMAIL: foo@bar.com Untainted... EMAIL: foo@bar.com CRID: 5 TEST: 0` [download] I realise the regexp is not especially good but it is just for testing and does the job of untainting the email address. The extra `say` statements are added to prove that the emails address really is being untainted.	[reply] [d/l] [select]
Re^13: Recalcitrant placeholders by pryrt (Abbot) on Aug 05, 2021 at 23:59 UTC
When your code is untainting `$argv[1]` after the tainted value has been copied into `$data{'email'}`, why would you expect the database interaction to change? I believe that you need to untaint `$data{'email'}` , since that's your input to the database. I switched your code over to SQLite to try it myself; unfortunately, even with the code you posted (except for the switch to SQLite), both CRID and TEST gave me 5. So I cannot test that portion for you. But if you add debug prints of the taintedness of both after you believe you are untainted, you will see `... snippet ... # here, you untainted the argv[1], but not the hash value! if ($argv[1] =~ /^(.+\@.+\..+)$/) { $argv[1] = $1; say "Looking while Untainted..."; say "EMAIL: $argv[1]"; say __LINE__, ": argv is ", (tainted($argv[1])?'':'not ', "tainted +"); # edit: uncomment here to untaint the hash value as well #$data{'email'} = $argv[1]; } say "argv is ", (tainted($argv[1])?'':'not ', "tainted"); say "data{email} is ", tainted($data{'email'})?'':'not ', "tainted"; ... snippet ... __END__` [download] With the line commented, as shown: `C:\usr\local\share\PassThru\perl\perlmonks>perl -T pm11135636.pl "" fo +o@bar.com Content-type: text/plain Perl: 5.030000 Database: SQLite 3.26.0 Driver: SQLite DBI Ver: 1.642 DBD::SQLite Ver: 1.62 Email is tainted Tainted... EMAIL: foo@bar.com Untainted... EMAIL: foo@bar.com argv is not tainted data{email} is tainted CRID: 5 TEST: 5` [download] With the line uncommented, so it untaints: `C:\usr\local\share\PassThru\perl\perlmonks>perl -T pm11135636.pl "" fo +o@bar.com Content-type: text/plain Perl: 5.030000 Database: SQLite 3.26.0 Driver: SQLite DBI Ver: 1.642 DBD::SQLite Ver: 1.62 Email is tainted Tainted... EMAIL: foo@bar.com Untainted... EMAIL: foo@bar.com argv is not tainted data{email} is not tainted CRID: 5 TEST: 5` [download]	[reply] [d/l] [select]
Re^14: Recalcitrant placeholders by Bod (Parson) on Aug 06, 2021 at 13:14 UTC
Re^14: Recalcitrant placeholders by Bod (Parson) on Aug 08, 2021 at 18:29 UTC
Re^11: Recalcitrant placeholders (updated x2) by haukex (Archbishop) on Aug 06, 2021 at 17:20 UTC
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. Read more... (5 kB)	[reply] [d/l] [select]
Re^12: Recalcitrant placeholders (updated x2) by Bod (Parson) on Aug 08, 2021 at 18:52 UTC
I can now reproduce the issue reliably! Thank goodness! I was beginning to think I was going mad :) Thank you for investigating this with so much energy and perseverance. 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. I have asked before and the answer has always been no... If I want a later version of Perl they say, I need to change from shared hosting to either VPS or a dedicated server. My yearly shared webhosting contract ends at the start of November so I guess now is about the time to make the overdue switch to VPS. My concerns about VPS are that I don't have the time to manage it if (when) anything goes wrong plus the very real possibility that I could break something essential. With shared hosting, the inner workings of the webserver are shielded from me and I am just left to deal with my own code. With a VPS or dedicated server, much more could go wrong! Perhaps I should buy a cheapish refurbed PC and install some flavour of Linux on it. That way I get to find my way around a bit more before making the switch to VPS. It would also give me something to test things on before touching the live webserver...	[reply]
Re^13: Recalcitrant placeholders by haukex (Archbishop) on Aug 08, 2021 at 19:05 UTC
I have asked before and the answer has always been no... Well, you could always try providing them with this list of links... Security fixes in Perl 5.18 (releases 2013-2014) Security fixes in Perl 5.20 (releases 2014-2015) Security fixes in Perl 5.22 (releases 2015-2017) Security fixes in Perl 5.24 (releases 2016-2018) Security fixes in Perl 5.26 (releases 2017-2018) Security fixes in Perl 5.28 (releases 2018) Security fixes in Perl 5.32 (releases 2020-2021) With shared hosting, the inner workings of the webserver are shielded from me and I am just left to deal with my own code. With a VPS or dedicated server, much more could go wrong! I completely understand. There are still shared webhosts out there that have better support, though - e.g. my own site and PerlMonks are both hosted by Pair.	[reply]
Re^14: Recalcitrant placeholders by Bod (Parson) on Aug 08, 2021 at 19:13 UTC
Re^15: Recalcitrant placeholders by haukex (Archbishop) on Aug 08, 2021 at 20:06 UTC
Some notes below your chosen depth have not been shown here