PDO adding single quotes to bound parameters

Question

I'm using Slim framework to build a RESTful back end for a site, using PHP's PDO to query the database. However, when I'm trying to bind parameters to the prepared statement, I get the following error:

string(206) "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 ''10'' at line 1"`

It looks as though PDO is surrounding my bound parameter with single quotes. My route code is as follows:

$app->get("/members/next10/:offset", function($offset) use($app) {
    $app->response()->header('Content-Type', 'application/json');

    try {
        $pdo = connectToDatabase();

        $query = $pdo->prepare("SELECT * FROM `members` LIMIT 10 OFFSET :offset");
        $query->bindParam(":offset", $offset, PDO::PARAM_INT);
        $query->execute();

        $result = $query->fetchAll(PDO::FETCH_ASSOC);

        if (!empty($result)) {
            echo json_encode($result);
        } else {
            $app->response()->status(404);
        }
    } catch (PDOException $d) {
        $app->response()->status(500);
        var_dump($d->getMessage());
    }
});

Am I missing something? I've tried grabbing :offset from the URL and assigning it to $offset cast as an integer before binding but that doesn't make a difference, either.

possible duplicate http://stackoverflow.com/questions/5508993/pdo-limit-and-offset — fortune, Jan 10 '15 at 18:13

score 1 · Answer 1 · answered Oct 23 '13 at 10:38

Possible solution: casting the param as an integer properly. If, in the first line of the method, I use $offset = (int) $offset; it works. Previously I was trying to do the cast in the $query->bindParam() method.

Route looks like:

$app->get("/members/next10/:offset", function($offset) use($app) {
    $offset = (int) $offset;

    $app->response()->header('Content-Type', 'application/json');

    try {
        $pdo = connectToDatabase();

        $query = $pdo->prepare("SELECT * FROM `members` LIMIT 10 OFFSET :offset");
        $query->bindParam(":offset", $offset, PDO::PARAM_INT);
        $query->execute();

        $result = $query->fetchAll(PDO::FETCH_ASSOC);

        if (!empty($result)) {
            echo json_encode($result);
        } else {
            $app->response()->status(404);
        }
    } catch (PDOException $d) {
        $app->response()->status(500);
        var_dump($d->getMessage());
    }
});

PDO adding single quotes to bound parameters

1 Answers1