Skip Menu |
 

This queue is for tickets about the DBD-mysql CPAN distribution.

Report information
The Basics
Id: 42723
Status: resolved
Priority: 0/
Queue: DBD-mysql

People
Owner: CAPTTOFU [...] cpan.org
Requestors: andreas.stahlbauer [...] tngtech.com
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)



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>
Download (untitled) / with headers
text/plain 1.6k
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 $!;
Download (untitled) / with headers
text/plain 1.9k
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
From: daniel.frett [...] ccci.org
Download (untitled) / with headers
text/plain 2.6k
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
Download bug42723.patch
text/x-diff 1.8k
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();
Download (untitled) / with headers
text/plain 103b
ugh, sorry I didn't get to this. Just finished my book, so I'll get this patch in this week - thankyou!
Download (untitled) / with headers
text/plain 3.2k
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
Download (untitled) / with headers
text/plain 103b
Please try out latest http://svn.perl.org/modules/DBD-mysql/trunk I will be releasing this, this week


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.