Problems with PDO parameters binding

Question

I'm building a CRUD with pure PHP+PDO Mysql driver.

I've got a page that lists the data contained in a DB table and I want to implements some filters, but I'm stuck on binding paramenters to PDO with a dynamic query.

For example this code works well with and without the $_GET["op"].

$params = [];
$pag_query = "SELECT COUNT(*) FROM cells WHERE 1 ";

if(!empty($_GET["op"])){
    $pag_query .= "AND op=:op ";
    $params["op"] = $_GET["op"];
}

$stmt = db()->prepare($pag_query);
$stmt->execute($params);

$n_rows = $stmt->fetchColumn();

But I cannot make this query work.

<?php

//some logic here to get $limit and $offset

$params["limit"] = $limit;
$params["offset"] = $offset;
$tbl_query = "SELECT * FROM cells WHERE 1 ";

if(!empty($_GET["op"])){
    $tbl_query .= "AND op=:op ";
    $params["op"] = $_GET["op"];
}

$tbl_query .= "ORDER BY cid LIMIT :limit OFFSET :offset";

//Prepare the query
$stmt = db()->prepare($tbl_query);
$stmt->execute($params);

$table = $stmt->fetchAll();

It gives me this error message.

Warning: PDOStatement::execute(): 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 ''20' OFFSET '0'' at line 1 in C:\xampp\htdocs\celldb\manage.php on line 64

If I simulate the query on PhpMyadmin it works well and it works also if I remove if(!empty($_GET["op"]) case and pass $limit and $offset using $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);

`$pag_query` != `$tbl_query` – cmorrissey Apr 13 '16 at 15:18 — cmorrissey, Apr 13 '16 at 15:18
I wrote wrong here but in my code was right :P – Claudio King Apr 13 '16 at 15:21 — Claudio King, Apr 13 '16 at 15:21
amazing ... retyping code instead of copy and pasting :) – cmorrissey Apr 13 '16 at 15:23 — cmorrissey, Apr 13 '16 at 15:23
No, it's because i edited something, like some comments – Claudio King Apr 13 '16 at 15:25 — Claudio King, Apr 13 '16 at 15:25

Oldskool · Answer 1 · 2016-04-13T15:26:49.133

2

If you look closely at the error message, it gives you the answer:

near ''20' OFFSET '0''

The parameters are both strings, where integers are expected. And as the documentation for PDOStatement::execute explains:

All values are treated as PDO::PARAM_STR.

That is why you must explicitly define them as integers with:

$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);

edited Apr 13 '16 at 15:26

answered Apr 13 '16 at 15:21

Oldskool

34,211
7
53
66

var_dump of both variables: `int(20) int(0)`, this is not the problem – Claudio King Apr 13 '16 at 15:24
@ClaudioKing My bad, I updated the answer. You need to explicitly define integers as such in cases like this. – Oldskool Apr 13 '16 at 15:27
How to do this if i have 5/6 clauses like `if(!empty($_GET["op"]))` with mixed types parameters (int, string and floats) ? – Claudio King Apr 13 '16 at 15:30
@ClaudioKing MySQL is pretty forgiving in WHERE conditions (like you can check an integer column with `WHERE id='12'` and it'll work), but the LIMIT/OFFSET are not so forgiving. They always require integers. – Oldskool Apr 13 '16 at 15:33
1

@ClaudioKing check the variable type and assign it the type. Something like `is_int($param) ? PDO::PARAM_INT : PDO::PARAM_STR` – chris85 Apr 13 '16 at 15:35
Thank you, I will make a loop that does that – Claudio King Apr 13 '16 at 15:35

Problems with PDO parameters binding

1 Answers1