Re: bugreport, Msql-Mysql 1.2216, DBI 1.15, perl 5.005_03

[Jochen Wiedmann]

Artiom Morozov wrote:
> 
> Fair day to ye,
> 
> use DBI;
> $dbh = DBI->connect('DBI:mysql:itv', 'itv', 'itv');
> $dbh->do('CREATE TABLE IF NOT EXISTS foo (bar VARCHAR(16) UNIQUE);');
> $bar = 'foobar';
> $sth = $dbh->prepare('SELECT * FROM foo WHERE bar=?');
> if ($bar < 0) { # the reason
>  die "numeric, string will be treated as zero";
> }
> $sth->execute($bar); # crash
> 
> Note bar remains string after comparasion, yet quoting skips quote-marks
> (this could be observed by removing * from SELECT - execute() issues syntax
> error and shows "WHERE bar=foobar").

This is a known problem. You are treating $bar as
numeric in your code, thus Perl and DBD::mysql believe
it is numeric. It is up to you to avoid such programming
practice.

I *could* change the DBD::mysql driver to verify whether $bar
does indeed contain a number, but this would have a bad impact
on performance, so I refuse it. Possible workarounds on your
side are:

- Use	$sth->execute("$bar");
- Use   $sth->bind_param(1, $bar, DBI::SQL_VARCHAR)


Yours,

Jochen