http://www.perlmonks.org?node_id=231691
Xaositect has asked for the wisdom of the Perl Monks concerning the following question:

I'm hoping someone will just recognize this and know what the problem is, because if not it means a lot more work on my part to figure it out. I've run into a problem where it appears DBI is incorrectly binding a number as a string in the limit on a query.

I'm using DBI and CGI::Application in a website, part of which is a catalog / shopping cart / purchase system. Products in the catalog are broken into pages of 20 items each. This breaking of pages is done by encoding the number of prizes into a link, and limiting my select based on that number. So I have a link like:

<A href="catalog.cgi?startitem=21">Next</A>

where the startitem is set dynamicly based on the current page. (there are other params passed here too, but anyway) The actual query looks something like:

$sth = $self->param('dbh_shopping')->prepare(qq{ SELECT ... a somewhat large query ... WHERE catalogId = ? LIMIT ?, ? }); my $start_item = ($self->query->param('startitem') > 0) ? $self->query->param('startitem') - 1 : 0;
So what I want to do after that is:
$sth->execute( $self->param('catalog_id'), $start_item, 20 );

However, DBI binds $start_item as a string, and quotes it. This causes an error, as limit doesn't take a string. Here's where it gets bizzare. I tried playing with $start_item in all sorts of ways, trying to figure out why DBI was turning it into a string. Some examples of things that didn't work: (DBI still quoted the param)

$start_item + 0 int($start_item) my $start_item2 = $start_item;

As other tests, I tried putting a hardcoded number in the call to $sth->execute(), and it worked. I tried assigning the $start_item in the second part of the limit (third bound param instead of second) and DBI quoted it. So far, the only way I've been able to get it working is the insane:

my $this_stupid_counter = 0; for (my $x=0; $x<$self->query->param('startitem'); $x++) { $this_stupid_counter++; } $sth->execute( $self->param('catalog_id'), $this_stupid_counter, 20 );

which should absolutely not be necessary. Any ideas?