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

Steve,

I'm working on another release as we speak, and will take a look at 
including this patch, of course after testing ;)

Kind regards,

Patrick

On Jun 21, 2005, at 5:56 PM, Steve Hay wrote:

> 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.
> 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))
>        {
>
>
> -- 
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe:    http://lists.mysql.com/[email protected]
Patrick Galbraith Senior Software Developer
[email protected] http://www.mysql.com

Those who fear climbing mountains
Shall live forever in the holes - Arab Poet


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