Parametrized PDO query for LIMIT clause

Question

I have seen similar questions answered already but I can't seem to apply the same solutions to my code.

$a=1;
$results = DB::query('SELECT posts.`postbody`, posts.`filepost`, posts.`likes`, posts.`posted_at`, users.`id`, posts.`id_of_post` FROM posts, users WHERE posts.`post_id` = users.`id` ORDER BY id_of_post DESC LIMIT :a', array(':a'=>$a));

class DB {
    private static function connect() {
        $pdo = new PDO('mysql:host=127.0.0.1;dbname=SocialNetwork;charset=utf8', 'root', '');
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        return $pdo;
    }
    public static function query($query, $params = array()) {
        $statement = self::connect()->prepare($query);
        $statement->execute($params);
        if (explode(' ', $query)[0] == 'SELECT') {
        $data = $statement->fetchAll();
        return $data;
        }
    }
}

For the record the following code works fine.

$results = DB::query('SELECT posts.`postbody`, posts.`filepost`, posts.`likes`, posts.`posted_at`, users.`id`, posts.`id_of_post` FROM posts, users WHERE posts.`post_id` = users.`id` ORDER BY id_of_post DESC LIMIT 1');

You can't bind the limit parameter. It is not a normal column value. — juergen d, Jan 11 '18 at 11:35
I've seen it works for others [https://stackoverflow.com/questions/18005593/parametrized-pdo-query-and-limit-clause-not-working] — Jimmy, Jan 11 '18 at 11:37

user3647971 · Answer 1 · 2018-01-11T12:24:48.963

0

As stated in the previous answers if you do not define:

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

You have to define the parameter to be binded as integer:

foreach($params as $key => $value){
    if(is_numeric($value))
        $statement->bindParam($key,$value,PDO::PARAM_INT);
    else
        $statement->bindParam($key,$value,PDO::PARAM_STR);
}
$statement->execute();

This is still not a perfect solution, but if you trust the key value pairs(ie they are from code, not user input) it's good enough.

edited Jan 11 '18 at 12:24

answered Jan 11 '18 at 12:00

user3647971

1,069
1
6
13

How do I bind it as an integer? – Jimmy Jan 11 '18 at 12:17

score 0 · Answer 2 · answered Jan 11 '18 at 12:06

0

Not ideal, but you could do away with the PDO parameters.

$a = 1;
$sql = "SELECT stuff FROM table LIMIT {$a};";

Then run your query from the $sql string.

answered Jan 11 '18 at 12:06

Warren

1,984
3
29
60

score 0 · Answer 3 · answered Jan 12 '18 at 01:47

In MySQL's LIMIT clause, it's an error to do this:

LIMIT '1'

Because LIMIT must take an integer, not a string.

If PDO is configured to emulate prepare() (by interpolating values into your SQL string), it's likely to make the interpolated value a quoted string, which will cause an error.

To avoid this, you must use a native integer as your bound variable and you just specify PDO::PARAM_INT.

    $statement = self::connect()->prepare($query);
    $statement->bindParam('a', $a, PDO::PARAM_INT);
    $statement->execute();

That will let the driver know to avoid putting quotes around the interpolated value.

You can also avoid the error if you set the PDO attribute to disable emulated prepares. I always do this, because I don't trust "emulated prepare."

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

See also my tests I wrote up here: Parametrized PDO query and `LIMIT` clause - not working

Parametrized PDO query for LIMIT clause

3 Answers3