SQL fails on prepared LIMIT

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.

....
$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?

Thanks,

--
Jason Dixon
DixonGroup Consulting
http://www.dixongroup.net


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

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/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: SQL fails on prepared LIMIT

On May 29, 2005, at 7:29 PM, Paul DuBois wrote:

> 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);

So why does it work ok on my main server running DBD::mysql 2.1021? It
doesn't seem like MySQL is enforcing the literal integer, but
DBD::mysql.

Thanks,

--
Jason Dixon
DixonGroup Consulting
http://www.dixongroup.net



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: SQL fails on prepared LIMIT

At 19:38 -0400 5/29/05, Jason Dixon wrote:
>On May 29, 2005, at 7:29 PM, Paul DuBois wrote:
>
>>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);
>
>So why does it work ok on my main server running DBD::mysql 2.1021?
>It doesn't seem like MySQL is enforcing the literal integer, but
>DBD::mysql.

Probably a version-specific change in placeholder parameters. Rudy might
be able to clarify.

Use bind_param(), it should work consistently across DBD::mysql versions.


--
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/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: SQL fails on prepared LIMIT

On May 29, 2005, at 7:42 PM, Paul DuBois wrote:

> At 19:38 -0400 5/29/05, Jason Dixon wrote:
>> On May 29, 2005, at 7:29 PM, Paul DuBois wrote:
>>
>>> 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);
>>
>> So why does it work ok on my main server running DBD::mysql 2.1021?
>> It doesn't seem like MySQL is enforcing the literal integer, but
>> DBD::mysql.
>
> Probably a version-specific change in placeholder parameters. Rudy
> might
> be able to clarify.
>
> Use bind_param(), it should work consistently across DBD::mysql
> versions.

Thanks, that seems to be working well.

--
Jason Dixon
DixonGroup Consulting
http://www.dixongroup.net



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org