Perl, DBI and MySQL wrongly reads zeros from database

TL;DR: SELECT queries in Perl for numerical columns suddenly turned to zeros after a software upgrade.

This is a really peculiar problem I had after my web hosting provider upgraded some database related software on the server: Numbers that were read with SELECT queries from the database were suddenly all zeros.

Spoiler: It’s about running Perl in Taint Mode.

The setting was DBD::mysql version 4.050, DBI version 1.642, Perl v5.10.1, and MySQL Community Server version 5.7.25 on a Linux machine.

For example, the following script is supposed to write the number of lines in the “session” table:

#!/usr/bin/perl -T -w
use warnings;
use strict;
require DBI;

my $dbh = DBI->connect( "DBI:mysql:mydb:localhost", "mydb", "password",
		     { RaiseError => 1, AutoCommit => 1, PrintError => 0,
		       Taint => 1});

my $sth = $dbh->prepare("SELECT COUNT(*) FROM session");

$sth->execute();

my @l = $sth->fetchrow_array;
my $s = $l[0];
print "$s\n";

$sth->finish();
$dbh->disconnect;

But instead, it prints zero, even though there are rows in the said table. Turning off taint mode by removing the “-T” flag in the shebang line gives the correct output. Needless to say, accessing the database with the “mysql” command-line utility client gave the correct output as well.

This is true for any numeric readout from this MySQL wrapper. This is in particular problematic when an integer is used as a user ID of a web site, and fetched with

my $sth = db::prepare_cached("SELECT id FROM users WHERE username=? AND passwd=?");
$sth->execute($name, $password);
my ($uid) = $sth->fetchrow_array;
$sth->finish();

If the credentials are wrong, $uid will be undef, as usual. But if any valid user gives correct credentials, it’s allocated user number 0. Which I was cautious enough not to allocate as the site’s supervisor, but that’s actually a common choice (what’s the UID of root on a Linux system?).

A softer workaround, instead of dropping the “-T” flag, is to set the TaintIn flag in the DBI->connect() call, instead of Taint. The latter stands for TaintIn and TaintOut, and so this fix effectively disables TaintOut, hence tainting of data from the database is disabled. And in this case, disabling tainting of this data also skips the zero-value bug. This leaves all other tainting checks in place, in particular that of data supplied from the network. So not enforcing sanitizing data from the database is a small sacrifice (in particular if the script already has mileage running with the enforcement on).

And in the end I wonder if I’m the only one who uses Perl’s tainting mechanism. I mean, if there are still (2019) advisories on SQL injections (mostly PHP scripts), maybe people just don’t care much about things of this sort.