What is bind_param() supposed to do when given invalid data, i.e. data
that doesn't match the specified type?
I've just come across a nasty situation in which a supposed "real
number", output by a separate piece of C software, was "1.#INF" due to a
divide-by-zero error. This value was then used as the second bind value
in an SQL statement like:
UPDATE foo SET str = ?, num = ? WHERE id = ?
The bind_param() calls looked like:
$sth->bind_param(1, 'one', SQL_VARCHAR);
$sth->bind_param(2, '1.#INF', SQL_DOUBLE);
$sth->bind_param(3, 1, SQL_INTEGER);
When the resulting SQL statement was then run, it set foo.str (to "one")
and foo.num (to "1") for *every* row in the table! Oops! It would have
been nice if bind_param() had either alerted me to the invalid data, or
at least only set the truncated value in 1 row (where id = 1) as intended.
This is using DBI-1.46 and DBD-mysql-2.9004 with perl-5.8.6 and
mysql-4.1.9 on WinXP.
The attached test program reproduces the behaviour described above.
Running it with the "$dbh->trace(2)" line uncommented, we see:
Binding parameters: UPDATE foo SET str = 'one', num = 1.#INF WHERE
id = 1
Notice that "1.#INF" is not quoted. Running the above SQL statement in
the mysql console client does indeed affect every row in the table.
Re-running it with "1.#INF" quoted only affects one row.
Is DBD::mysql's bind_param() working the way it is intended to according
to the DBI spec?
- Steve
------------------------------------------------
Radan Computational Ltd.
The information contained in this message and any files transmitted with it are
confidential and intended for the addressee(s) only. If you have received this
message in error or there are any problems, please notify the sender
immediately. The unauthorized use, disclosure, copying or alteration of this
message is strictly forbidden. Note that any views or opinions presented in
this email are solely those of the author and do not necessarily represent
those of Radan Computational Ltd. The recipient(s) of this message should
check it and any attached files for viruses: Radan Computational will accept no
liability for any damage caused by any virus transmitted by this email.
use strict;
use warnings;
use DBI qw(:sql_types);
my $tmp_dbh = DBI->connect(
'dbi:mysql:database=mysql', 'root', undef,
{ AutoCommit => 1, PrintError => 0, RaiseError => 1 }
);
$tmp_dbh->do('CREATE DATABASE IF NOT EXISTS test');
$tmp_dbh->disconnect();
my $dbh = DBI->connect(
'dbi:mysql:database=test', 'root', undef,
{ AutoCommit => 1, PrintError => 0, RaiseError => 1 }
);
$dbh->do('DROP TABLE IF EXISTS foo');
$dbh->do(qq{CREATE TABLE foo (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
str VARCHAR(64),
num DOUBLE
) ENGINE=InnoDB});
$dbh->do("INSERT INTO foo VALUES(NULL, 'three', 3)");
$dbh->do("INSERT INTO foo VALUES(NULL, 'two', 2.0)");
my $rows = $dbh->selectall_arrayref('SELECT * FROM foo');
my $i = 0;
foreach my $row (@$rows) {
++$i;
local $" = ', ';
print "ROW $i: @$row\n";
}
my $sql = 'UPDATE foo SET str = ?, num = ? WHERE id = ?';
# $dbh->trace(2);
my $sth = $dbh->prepare($sql);
$sth->bind_param(1, 'one', SQL_VARCHAR);
$sth->bind_param(2, '1.#INF', SQL_DOUBLE);
$sth->bind_param(3, 1, SQL_INTEGER);
my $num_rows = $sth->execute();
print "UPDATE affected $num_rows rows\n";
$rows = $dbh->selectall_arrayref('SELECT * FROM foo');
$i = 0;
foreach my $row (@$rows) {
++$i;
local $" = ', ';
print "ROW $i: @$row\n";
}
$dbh->disconnect();