Skip Menu |
 

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

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

People
Owner: Nobody in particular
Requestors: stephane.loeuillet [...] tiscali.fr
Cc:
AdminCc:

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



Subject: new placeholder quoting method broke LIMIT ?,?
Download (untitled) / with headers
text/plain 588b
mysql 3.23 / dbd::mysql 2.9002 / linux x86 statement : SELECT Prelude_CreateTime.parent_ident FROM Prelude_CreateTime WHERE Prelude_CreateTime.parent_type='A' AND (((Prelude_CreateTime.time>='2003-07-22 00:00:00'))) GROUP BY Prelude_CreateTime.parent_ident ORDER BY Prelude_CreateTime.parent_ident DESC LIMIT ?,?; so, 2 placeholders, the 2 LIMIT parameters was using for example xxx->execute(0,30); it did : You have an error in your SQL syntax near ''0','30'' at line 1 looking at changelog, i tried to replace this execute(x,y) by bind_param(1,x) and bind_param(2,y) => same bug
From: stephane.loeuillet [...] tiscali.fr
Download (untitled) / with headers
text/plain 386b
Show quoted text
> looking at changelog, i tried to replace this execute(x,y) by > bind_param(1,x) and bind_param(2,y) => same bug
using this syntax : - changed the $sth->execute(x,y); syntax for : $sth->bind_param(1,x, {TYPE => DBI::SQL_INTEGER }); $sth->bind_param(2,y, {TYPE => DBI::SQL_INTEGER }); $sth->execute(); it also broke so, i reverted to 2.1028 which worked ok
Assigned to the current module maintainer.
RT-Send-CC: RUDY [...] cpan.org
Assigned to RUDY@cpan.org, the current module maintainer.
From: patg [...] mysql.com
[guest - Wed Jul 23 13:28:09 2003]: I've tested this with SuSE 9.0, MySQL 4.1.1 and Redhat 7.2, MySQL 3.23.49. With MySQL 4.1.1, it fails using both versions of the DBD (2.9003 and 2.1028), but passes on MySQL 3.23.49 using both versions of the DBD. It fails on 4.1.1 because the "LIMIT" is passed to MySQL via the DBD as: $sth->prepare("SELECT * FROM foo LIMIT ?, ?"); my ($lower, $upper) = (0,10); $sth->execute($lower, $upper); Ends up exectuting in the server as: "SELECT * FROM foo LIMIT '0','10'" Which causes an SQL error. Upon further inspection, the same happens with any value, whether or not it's an int or varchar (id being an 'int') $sth->prepare("SELECT * FROM foo LIMIT WHERE id = ?"); my $id = 1; $sth->execute($id); Ends up exectuting in the server as: "SELECT * FROM foo LIMIT WHERE id = '1'" This doesn't cause an error becuase MySQL allows INTs to be quoted. When I test this with MySQL 3.23.49, the resulting SQL is properly quoted or unquoted depending upon data type. I suspect this is a change in the C API, which I am looking into. The one thing the DBD should do internally is determine the datatype of each column that each placeholder references, then set the appropriate type for the column that each member of the MYSQL_BIND structure prior to C API call mysql_bind_param(), and then when mysql_execute() is called, the resulting SQL will have the proper quoting (quotes or absence of quotes) for each parameter passed. Show quoted text
> mysql 3.23 / dbd::mysql 2.9002 / linux x86 > > statement : SELECT Prelude_CreateTime.parent_ident FROM > Prelude_CreateTime WHERE Prelude_CreateTime.parent_type='A' AND > (((Prelude_CreateTime.time>='2003-07-22 00:00:00'))) GROUP BY > Prelude_CreateTime.parent_ident ORDER BY > Prelude_CreateTime.parent_ident DESC LIMIT ?,?; > > so, 2 placeholders, the 2 LIMIT parameters > > was using for example xxx->execute(0,30); > > it did : > > You have an error in your SQL syntax near ''0','30'' at line 1 > > looking at changelog, i tried to replace this execute(x,y) by > bind_param(1,x) and bind_param(2,y) => same bug
Download (untitled) / with headers
text/plain 203b
Known, but intended defficiency. Subject to ongoing discussion, see http://lists.mysql.com/perl/2654 If you do not like the current implementation, please join the discussion on the mailing list.


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.