Re: SQL fails on prepared LIMIT

At 19:16 -0400 5/29/05, Jason Dixon wrote:
>I have a small-ish Perl application running beautifully on CentOS 
>3.4 (RHEL 3 Update 4) using MySQL 3.23.58, Perl 5.8.0 and DBD::mysql 
>2.1021.  However, another developer ran into problems with the SQL 
>execution on his Fedora Core 2 system running MySQL 3.23.58 with 
>Perl 5.8.3 and DBD::mysql 2.9003-4.  The execute fails when using a 
>placeholder for LIMIT.  Here is the SQL query with placeholders:
>
>$query = "SELECT SUM(flow_octets) as bytes, ${direction}_port, 
>protocol, agent_addr ";
>$query   .= "FROM flows ";
>$query   .= "WHERE ${direction}_port < 1024 ";
>$query   .= "AND agent_addr=? ";
>$query   .= "AND (if_index_in=? or if_index_out=?) ";
>$query   .= "AND (from_unixtime(time_sec) >= date_sub(NOW(), 
>interval ? hour)) ";
>$query   .= "GROUP BY ${direction}_port, protocol ";
>$query   .= "ORDER BY bytes desc ";
>$query   .= "LIMIT ?";
>
>The prepared query is then prepared and then executed with multiple 
>values from CGI and an internal-only value:
>
>$sth->execute($cgi->param('d'), int $cgi->param('i'), int 
>$cgi->param('i'), ($cgi->param('t') || 1), int ($limit * 2)) ||
>  die $dbh->errstr;
>
>On his system, it fails with "You have an error in your SQL syntax. 
>Check the manual that corresponds to your MySQL server version for 
>the right syntax to use near ''20'' at line 1 at Monitor/Services.pm 
>line 33."  Right off, I figured it might have something to do with 
>placeholders so we moved the "($limit * 2)" into the query and 
>removed it from the execute.

The placeholder mechanism is quoting the value, but the argument to LIMIT
must be a literal integer.  The tell DBD::mysql to treat the value as
an integer, you can use bind_param().  For example:

     $rv = $sth->bind_param ($n, $value, { TYPE => DBI::SQL_INTEGER });
     $rv = $sth->bind_param ($n, $value, DBI::SQL_INTEGER);


>
>...
>$query   .= "LIMIT ($limit * 2)";
>...
>$sth->execute($cgi->param('d'), int $cgi->param('i'), int 
>$cgi->param('i'), ($cgi->param('t') || 1)) || die $dbh->errstr;
>...
>
>After this, it complains "DBD::mysql::st execute failed: You have an 
>error in your SQL syntax.  Check the manual that corresponds to your 
>MySQL server version for the right syntax to use near '(10 * 2)' at 
>line 1 at Monitor/Services.pm line 34."  We create a new variable 
>just to hold this alternate value and adjust the query accordingly.
>
>...
>$query   .= "LIMIT $limit";
>...
>$sth->execute($cgi->param('d'), int $cgi->param('i'), int 
>$cgi->param('i'), ($cgi->param('t') || 1)) || die $dbh->errstr;
>...
>
>The program finally runs without error, but I'm not sure why these 
>changes are necessary.  As I mentioned before, the application runs 
>fine on my own production RHEL server.  However, it fails 
>identically on his FC2 box and another system I setup just to 
>recreate this problem (OpenBSD 3.6-stable, MySQL 4.0.20, Perl 5.8.5 
>and DBD::mysql 2.9004).  The common denominator in this appears to 
>be DBD::mysql version 2.9, but I'm grasping at straws.  Anyone run 
>into this before or know of any changes that I should be aware of to 
>suggest this isn't a bug?


-- 
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

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