PHP PDO bindValue() don't work

Question

<?php
try
{
    $db = new PDO("mysql:host=localhost;dbname=DBNAME", "USER", "PASSWD");

    $stmt = $db->prepare("SELECT id, name FROM testdb ORDER BY time DESC LIMIT :index, 10");
    $stmt->bindValue(":index", $_GET['index'], PDO::PARAM_INT);
    $stmt->execute();

    while( $r = $stmt->fetch(PDO::FETCH_ASSOC) )
    {
        echo var_dump($r);                                      
    }

}
catch( PDOException $e )
{
    die("Exception");
}

The problem is on this line: $stmt->bindValue(":index", $_GET['index'], PDO::PARAM_INT);

And the specific parameter is the second one.

The code as it is above doesn't work, it doesn't return anything so the while loop isn't executed. If I replace $_GET['index'] with a number, like 10, it works just fine, it returns 10 rows. Echoing $_GET['index'] displays a number, so it should pass a number. I've also tried bindParam, but the result is same.

Why isn't this working?

EDIT:

Interesting... If I replace $_GET['index'] with (int)$_GET['index'] it works.

Answer 1

If you check $stmt->errorInfo() you will actually find that your query failed.

PDO::PARAM_INT tells PDO that you are giving it an integer. If you do not give PDO an integer, PDO will quote the value in the sql string.

SELECT id, name FROM testdb ORDER BY time DESC LIMIT "1", 10

All values in $_GET are either an array or a string. You did the correct thing by coercing $_GET['index'] to an integer before binding it as a value. By doing this, PDO gets an integer, was expecting an integer from PDO::PARAM_INT, and therefore will not quote the value in the sql string.

SELECT id, name FROM testdb ORDER BY time DESC LIMIT 1, 10