PHP MYSQL Error when not hardcoding values for Limit Clause

Question

I get and error when trying to use values I retrieve through $_GET[], in particular the $start and $end, which I use to limit the number of results. Whenever I hardcode the values in the bottom most chuck of code, the server fetches the results no problem. Why can't I pass a parameter to Limit using PHP PDO prepared statements for MySQL?

THIS IS THE ERROR I GET

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]:
Syntax error or access violation: 1064 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 ''0', '20'
                                ORDER by orders.order_placed' at line 10' in /base/data/home/apps/s~beta/1.383539951926438776/admin/get/getorderitems.php:35
Stack trace:
#0 /base/data/home/apps/s~beta/1.383539951926438776/admin/get/getorderitems.php(35): PDOStatement->execute()
#1 {main}
thrown in /base/data/home/apps/s~beta/1.383539951926438776/admin/get/getorderitems.php on line 35



<?php

error_reporting(E_ALL); ini_set('display_errors', 1);

require('../../dbconnect.php');

$stadiums_id = $_GET['stadiums_id'];
$time = $_GET['time'];
$time_12ago = $time - 43200000;  // last 12 hours
$start = 0 + $_GET['start'];  // used for limit clause
$end = $start +  20;
$page = $_GET['page'];

$json;

//  incoming order
if($page === "incoming"){
    $statement=$con->prepare('SELECT orders.*,orders_has_items.*,
                                customers.id,customers.fname,customers.lname,items.*
                                FROM orders_has_items,items,orders,customers 
                                WHERE orders.stadiums_id = :stadiums_id 
                                AND orders_has_items.items_id = items.id
                                AND orders.id = orders_has_items.orders_id
                                AND customers.id = orders.customers_id
                                AND (orders.order_prepared IS NULL) 
                                AND orders.create_time BETWEEN :time_12ago AND :time
                                ORDER by orders.order_placed
                                limit :start, :end');
    $statement->bindParam(':stadiums_id',$stadiums_id); // bind param to variable
    $statement->bindParam(':time_12ago',$time_12ago); // bind param to variable
    $statement->bindParam(':time',$time); // bind param to variable
    $statement->bindParam(':start',$start); // bind param to variable
    $statement->bindParam(':end',$end); // bind param to variable
    $statement->execute();
    $results=$statement->fetchAll(PDO::FETCH_ASSOC);
    $json=json_encode($results);
}

However, the code below works just fine when hard coding the Limit as seen below

$statement = $con->prepare('SELECT orders.*,orders_has_items.*,
        customers.id,customers.fname,customers.lname,
        items.*
        from orders_has_items,items,orders,customers 
        where orders.stadiums_id = 1 
        and orders_has_items.items_id = items.id
        and orders.id = orders_has_items.orders_id
        and customers.id = orders.customers_id
        and (orders.order_prepared IS NULL) 
        and orders.create_time between (1428735225152-43200000) and 1428735225152
        order by orders.order_placed
        limit 0,10');
$statement->execute();
$results=$statement->fetchALL(PDO::FETCH_ASSOC);
$json = json_encode($results);

I think you are spot on! It is adding single quotes and I tried casting it to an int earlier hoping it would remove them. I'm gonna try their method of casting and see if that fixes it. Thanks for the link! — Moustache_Me_A_Question, Apr 12 '15 at 00:40
See the double quote in the error message, `''0'`? It's being read as a string not an INT. — chris85, Apr 12 '15 at 00:42

score 0 · Answer 1 · answered Apr 12 '15 at 00:42

0

By default, bindParam() binds the parameter as a string. Cast the value to an integer before passing it to the bind function, and set the datatype to PDO::PARAM_INT

$statement->bindParam(':start',(int)$start, PDO::PARAM_INT)); // bind param to variable
$statement->bindParam(':end',(int)$end, PDO::PARAM_INT)); // bind param to variable

answered Apr 12 '15 at 00:42

Sean

12,443
3
29
47

I added this line to the top right after my requiring my database connection file `$dbconnect->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);` and that seemed to work perfect. Is there anything wrong with adding that line? – Moustache_Me_A_Question Apr 12 '15 at 00:50

PHP MYSQL Error when not hardcoding values for Limit Clause

1 Answers1