[PATCH] Fix bind_param() bug in DBD-mysql-2.9008

Hi all,

DBD-mysql-2.9007 introduced a fix for a bug that I had reported, in 
which non-numeric values bound to numeric types could break the SQL by 
placing arbitrary unquoted strings into the SQL.

However, the bug fix seems to have a problem of its own -- it prevents 
you from using the value "undef" in such places.  AFAIK this use of 
"undef" is a perfectly valid way of setting the value of a numeric 
column to NULL, so should not be prevented from running.

Attached is a short test program that illustrates the problem.  Using 
2.9008 this outputs

ROW 1: 1, 1
DBD::mysql::st bind_param failed: Binding non-numeric field 1, value 
undef as a numeric! at C:\Temp\dbi.pl line 29.

Attached is also a patch against 2.9008 that fixes this.  With the 
patch, the test program now correctly outputs

ROW 1: 1, 1
UPDATE affected 1 rows
Use of uninitialized value in join or string at C:\Temp\dbi.pl line 38.
ROW 1: 1,

A new release with this patch in place would be appreciated as this is 
quite an issue for me.  My database code makes frequent use of NULL'ing 
numeric columns by this means, and is completely broken with the current 
release :-(

Cheers,
- 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.


-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]

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,
  num INT
) ENGINE=InnoDB});
$dbh->do("INSERT INTO foo VALUES(NULL, 1)");
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 num = ? WHERE id = ?';
my $sth = $dbh->prepare($sql);
$sth->bind_param(1, undef, SQL_INTEGER);
$sth->bind_param(2, 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();

--- dbdimp.c.orig	2005-04-22 23:09:56.000000000 +0100
+++ dbdimp.c	2005-06-21 16:43:48.206975300 +0100
@@ -2337,15 +2337,16 @@
 
     /* 
       This fixes the bug whereby no warning was issued upone binding a 
-      non-numeric as numeric
+      defined non-numeric as numeric
     */
-    if (sql_type == SQL_NUMERIC  ||
-        sql_type == SQL_DECIMAL  ||
-        sql_type == SQL_INTEGER  ||
-        sql_type == SQL_SMALLINT ||
-        sql_type == SQL_FLOAT    ||
-        sql_type == SQL_REAL     ||
-        sql_type == SQL_DOUBLE)
+    if (SvOK(value) &&
+        (sql_type == SQL_NUMERIC  ||
+         sql_type == SQL_DECIMAL  ||
+         sql_type == SQL_INTEGER  ||
+         sql_type == SQL_SMALLINT ||
+         sql_type == SQL_FLOAT    ||
+         sql_type == SQL_REAL     ||
+         sql_type == SQL_DOUBLE) )
     {
       if (! looks_like_number(value))
       {