Bug #42723 for DBD-mysql: Binding server side integer parameters results in corrupt data

Fri Jan 23 09:57:40 2009 andreas.stahlbauer [...] tngtech.com - Ticket created

Subject:	Binding server side integer parameters results in corrupt data
Date:	Fri, 23 Jan 2009 15:55:01 +0100
To:	bug-DBD-mysql [...] rt.cpan.org
From:	Andreas Stahlbauer <andreas.stahlbauer [...] tngtech.com>

Hi, Server side prepared statements do not work in version 4.010 of DBD::mysql correctly. Binded integer-parameters are not passed correctly to the database if mysql_server_prepare is enabled (see below). Version 4.007 of the module works fine. Version 4.008 and 4.009 have not been tested. Thanks Show quoted text

---------- Environment infos ----------- DBD::mysql, version 4.010 DBI, version 1.607 perl, v5.10.0 built for i486-linux-gnu-thread-multi Linux myhost 2.6.27-7-generic #1 SMP Tue Nov 4 19:33:20 UTC 2008 i686 GNU/Linux

---------- Steps to reproduce ---------- Create the following table (used for testing): CREATE TABLE `inttest` ( `fld_integer` int(11) DEFAULT NULL, `fld_smallint` smallint(6) DEFAULT NULL, `fld_tinyint` tinyint(4) DEFAULT NULL, `fld_bigint` bigint(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 Script that results in wrong data: use strict; use DBI; use DBI qw(:sql_types); # Using mysql_server_prepare!!! my $dbh = DBI->connect("DBI:mysql:dbname=testdb;host=127.0.0.1;port=3306;mysql_server_prepare=1", "root", "") or die $!; my $sth = $dbh->prepare("INSERT INTO inttest VALUES (?,?,?,?)"); $sth->bind_param(1, 101, SQL_INTEGER); $sth->bind_param(2, 102, SQL_SMALLINT); $sth->bind_param(3, 103, SQL_TINYINT); $sth->bind_param(4, 104, SQL_BIGINT); # This execute works fine, but does not insert the desired values! # (using version 4.010 of the DBD::mysql; version 4.007 works fine in this case!!!!) # # 3223601 is inserted instead of 101 (sql type INTEGER) # 32767 is inserted instead of 102 (sql type SMALLINT) # 127 is inserted instead of 103 (sql type TINYINT) # 3420209 is inserted instead of 104 (sql type BIGINT) # $sth->execute() or die $!;

Fri Jan 23 13:07:20 2009 CAPTTOFU [...] cpan.org - Taken

Fri Jan 23 13:08:29 2009 CAPTTOFU [...] cpan.org - Correspondence added

On Fri Jan 23 09:57:40 2009, andreas.stahlbauer@tngtech.com wrote: Show quoted text

> Hi, > > Server side prepared statements do not work in version 4.010 of > DBD::mysql correctly. Binded integer-parameters are not passed > correctly > to the database if mysql_server_prepare is enabled (see below). > Version > 4.007 of the module works fine. Version 4.008 and 4.009 have not been > tested. > > Thanks > > ---------- Environment infos ----------- > DBD::mysql, version 4.010 > DBI, version 1.607 > perl, v5.10.0 built for i486-linux-gnu-thread-multi > Linux myhost 2.6.27-7-generic #1 SMP Tue Nov 4 19:33:20 UTC 2008 > i686 > GNU/Linux > > > ---------- Steps to reproduce ---------- > > Create the following table (used for testing): > > CREATE TABLE `inttest` ( > `fld_integer` int(11) DEFAULT NULL, > `fld_smallint` smallint(6) DEFAULT NULL, > `fld_tinyint` tinyint(4) DEFAULT NULL, > `fld_bigint` bigint(20) DEFAULT NULL > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 > > Script that results in wrong data: > > use strict; > use DBI; > use DBI qw(:sql_types); > > # Using mysql_server_prepare!!! > my $dbh = > DBI- > >connect("DBI:mysql:dbname=testdb;host=127.0.0.1;port=3306;mysql_server_prepare=1", > "root", "") or die $!; > my $sth = $dbh->prepare("INSERT INTO inttest VALUES (?,?,?,?)"); > > $sth->bind_param(1, 101, SQL_INTEGER); > $sth->bind_param(2, 102, SQL_SMALLINT); > $sth->bind_param(3, 103, SQL_TINYINT); > $sth->bind_param(4, 104, SQL_BIGINT); > > # This execute works fine, but does not insert the desired values! > # (using version 4.010 of the DBD::mysql; version 4.007 works fine in > this case!!!!) > # > # 3223601 is inserted instead of 101 (sql type INTEGER) > # 32767 is inserted instead of 102 (sql type SMALLINT) > # 127 is inserted instead of 103 (sql type TINYINT) > # 3420209 is inserted instead of 104 (sql type BIGINT) > # > $sth->execute() or die $!; >

Hi, and thank you for this report. I'll look into this. regards, Patrick

Fri Jan 23 13:08:29 2009 The RT System itself - Status changed from 'new' to 'open'

Sat Apr 11 00:00:38 2009 daniel.frett [...] ccci.org - Correspondence added

From:

daniel.frett [...] ccci.org

On Fri Jan 23 13:08:29 2009, CAPTTOFU wrote: Show quoted text

> On Fri Jan 23 09:57:40 2009, andreas.stahlbauer@tngtech.com wrote:

> > Hi, > > > > Server side prepared statements do not work in version 4.010 of > > DBD::mysql correctly. Binded integer-parameters are not passed > > correctly > > to the database if mysql_server_prepare is enabled (see below). > > Version > > 4.007 of the module works fine. Version 4.008 and 4.009 have not

> been

> > tested. > > > > Thanks > > > > ---------- Environment infos ----------- > > DBD::mysql, version 4.010 > > DBI, version 1.607 > > perl, v5.10.0 built for i486-linux-gnu-thread-multi > > Linux myhost 2.6.27-7-generic #1 SMP Tue Nov 4 19:33:20 UTC 2008 > > i686 > > GNU/Linux > > > > > > ---------- Steps to reproduce ---------- > > > > Create the following table (used for testing): > > > > CREATE TABLE `inttest` ( > > `fld_integer` int(11) DEFAULT NULL, > > `fld_smallint` smallint(6) DEFAULT NULL, > > `fld_tinyint` tinyint(4) DEFAULT NULL, > > `fld_bigint` bigint(20) DEFAULT NULL > > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 > > > > Script that results in wrong data: > > > > use strict; > > use DBI; > > use DBI qw(:sql_types); > > > > # Using mysql_server_prepare!!! > > my $dbh = > > DBI- > >

> >connect("DBI:mysql:dbname=testdb;host=127.0.0.1;port=3306;mysql_server_prepare=1",

> > "root", "") or die $!; > > my $sth = $dbh->prepare("INSERT INTO inttest VALUES (?,?,?,?)"); > > > > $sth->bind_param(1, 101, SQL_INTEGER); > > $sth->bind_param(2, 102, SQL_SMALLINT); > > $sth->bind_param(3, 103, SQL_TINYINT); > > $sth->bind_param(4, 104, SQL_BIGINT); > > > > # This execute works fine, but does not insert the desired values! > > # (using version 4.010 of the DBD::mysql; version 4.007 works fine

> in

> > this case!!!!) > > # > > # 3223601 is inserted instead of 101 (sql type INTEGER) > > # 32767 is inserted instead of 102 (sql type SMALLINT) > > # 127 is inserted instead of 103 (sql type TINYINT) > > # 3420209 is inserted instead of 104 (sql type BIGINT) > > # > > $sth->execute() or die $!; > >

> > > Hi, and thank you for this report. I'll look into this. > > regards, > > Patrick

Here's a patch to fix this issue, I also included the supplied test. This issue was introduced with svn rev 11997. The revision got rid of a test that would only set the buffer length to the length of the supplied string when it was a blob type. Without that test the buffer length for integers and floats was being set to the string length of the passed in value. This code was originally in the patch submitted for bug #40278 (http://rt.cpan.org/Public/Bug/Display.html?id=40278), it must have gotten lost in cleaning up the patch before submitting it to svn. -Daniel Frett

Index: dbdimp.c =================================================================== --- dbdimp.c (revision 12683) +++ dbdimp.c (working copy) @@ -4322,8 +4322,15 @@ break; } - buffer= SvPV(imp_sth->params[idx].value, slen); - buffer_length= slen; + if (buffer_type == MYSQL_TYPE_STRING || buffer_type == MYSQL_TYPE_BLOB) + { + buffer= SvPV(imp_sth->params[idx].value, slen); + buffer_length= slen; + if (DBIc_TRACE_LEVEL(imp_xxh) >= 2) + PerlIO_printf(DBILOGFP, + " SCALAR type %d ->length %d<- IS A STRING or BLOB\n", + sql_type, buffer_length); + } } else { Index: t/40server_prepare.t =================================================================== --- t/40server_prepare.t (revision 12683) +++ t/40server_prepare.t (working copy) @@ -19,7 +19,7 @@ if ($@) { plan skip_all => "ERROR: $@. Can't continue test"; } -plan tests => 10; +plan tests => 20; ok(defined $dbh, "connecting"); @@ -45,4 +45,26 @@ ok ($dbh->do(qq{DROP TABLE t1}), "cleaning up"); +# +# Bug #42723: Binding server side integer parameters results in corrupt data +# +ok($dbh->do(qq{DROP TABLE IF EXISTS t2}), "making slate clean"); + +ok($dbh->do(q{CREATE TABLE `t2` (`i` int,`si` smallint,`ti` tinyint,`bi` bigint)}), "creating test table"); + +my $sth2; +ok($sth2 = $dbh->prepare('INSERT INTO t2 VALUES (?,?,?,?)')); + +#bind test values +ok($sth2->bind_param(1, 101, DBI::SQL_INTEGER), "binding int"); +ok($sth2->bind_param(2, 102, DBI::SQL_SMALLINT), "binding smallint"); +ok($sth2->bind_param(3, 103, DBI::SQL_TINYINT), "binding tinyint"); +ok($sth2->bind_param(4, 104, DBI::SQL_BIGINT), "binding bigint"); + +ok($sth2->execute(), "inserting data"); + +is_deeply($dbh->selectall_arrayref('SELECT * FROM t2'), [[101, 102, 103, 104]]); + +ok ($dbh->do(qq{DROP TABLE t2}), "cleaning up"); + $dbh->disconnect();

Sat Apr 11 09:44:50 2009 CAPTTOFU [...] cpan.org - Correspondence added

ugh, sorry I didn't get to this. Just finished my book, so I'll get this patch in this week - thankyou!

Sat Apr 11 10:58:21 2009 CAPTTOFU [...] cpan.org - Correspondence added

Daniel, could you please send me something to deal with: t/40server_prepare..........Bareword "DBI::SQL_BIGINT" not allowed while "strict subs" in use at t/40server_prepare.t line 62. If you look at DBI.pm: These constants are defined by SQL/CLI, ODBC or both. C<SQL_BIGINT> is (currently) omitted, because SQL/CLI and ODBC provide conflicting codes. Thanks! On Sat Apr 11 00:00:38 2009, dfrett wrote: Show quoted text

> On Fri Jan 23 13:08:29 2009, CAPTTOFU wrote:

> > On Fri Jan 23 09:57:40 2009, andreas.stahlbauer@tngtech.com wrote:

> > > Hi, > > > > > > Server side prepared statements do not work in version 4.010 of > > > DBD::mysql correctly. Binded integer-parameters are not passed > > > correctly > > > to the database if mysql_server_prepare is enabled (see below). > > > Version > > > 4.007 of the module works fine. Version 4.008 and 4.009 have not

> > been

> > > tested. > > > > > > Thanks > > > > > > ---------- Environment infos ----------- > > > DBD::mysql, version 4.010 > > > DBI, version 1.607 > > > perl, v5.10.0 built for i486-linux-gnu-thread-multi > > > Linux myhost 2.6.27-7-generic #1 SMP Tue Nov 4 19:33:20 UTC

> 2008

> > > i686 > > > GNU/Linux > > > > > > > > > ---------- Steps to reproduce ---------- > > > > > > Create the following table (used for testing): > > > > > > CREATE TABLE `inttest` ( > > > `fld_integer` int(11) DEFAULT NULL, > > > `fld_smallint` smallint(6) DEFAULT NULL, > > > `fld_tinyint` tinyint(4) DEFAULT NULL, > > > `fld_bigint` bigint(20) DEFAULT NULL > > > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 > > > > > > Script that results in wrong data: > > > > > > use strict; > > > use DBI; > > > use DBI qw(:sql_types); > > > > > > # Using mysql_server_prepare!!! > > > my $dbh = > > > DBI- > > >

> >

> >connect("DBI:mysql:dbname=testdb;host=127.0.0.1;port=3306;mysql_server_prepare=1",

> > > "root", "") or die $!; > > > my $sth = $dbh->prepare("INSERT INTO inttest VALUES (?,?,?,?)"); > > > > > > $sth->bind_param(1, 101, SQL_INTEGER); > > > $sth->bind_param(2, 102, SQL_SMALLINT); > > > $sth->bind_param(3, 103, SQL_TINYINT); > > > $sth->bind_param(4, 104, SQL_BIGINT); > > > > > > # This execute works fine, but does not insert the desired values! > > > # (using version 4.010 of the DBD::mysql; version 4.007 works fine

> > in

> > > this case!!!!) > > > # > > > # 3223601 is inserted instead of 101 (sql type INTEGER) > > > # 32767 is inserted instead of 102 (sql type SMALLINT) > > > # 127 is inserted instead of 103 (sql type TINYINT) > > > # 3420209 is inserted instead of 104 (sql type BIGINT) > > > # > > > $sth->execute() or die $!; > > >

> > > > > > Hi, and thank you for this report. I'll look into this. > > > > regards, > > > > Patrick

> > Here's a patch to fix this issue, I also included the supplied test. > > This issue was introduced with svn rev 11997. > The revision got rid of a test that would only set the buffer length > to > the length of the supplied string when it was a blob type. Without > that > test the buffer length for integers and floats was being set to the > string length of the passed in value. > > This code was originally in the patch submitted for bug #40278 > (http://rt.cpan.org/Public/Bug/Display.html?id=40278), it must have > gotten lost in cleaning up the patch before submitting it to svn. > > -Daniel Frett

Sat Apr 11 12:49:25 2009 CAPTTOFU [...] cpan.org - Correspondence added

Please try out latest http://svn.perl.org/modules/DBD-mysql/trunk I will be releasing this, this week

Sat Apr 11 12:49:26 2009 CAPTTOFU [...] cpan.org - Status changed from 'open' to 'resolved'