PHP PDO bug when trying to parameterize SQL LIMIT :offset, :display?

Question

This query returns 5 results in phpMyAdmin:

SELECT * FROM tbl_product WHERE 1 ORDER BY last_update DESC LIMIT 0,5

And this returns count=12 in phpMyAdmin (which is fine, because there are 12 records):

SELECT COUNT(*) AS count FROM tbl_product WHERE 1 ORDER BY last_update DESC LIMIT 0,5

This function was working fine before I added the two variables (offset, display), however now it doesn't work, and printing the variables out gives me offset=0, display=5 (so still LIMIT 0,5).

function getProducts($offset, $display) {
    $sql = "SELECT * FROM tbl_product WHERE 1 ORDER BY last_update DESC LIMIT ?,?;";
    $data = array((int)$offset, (int)$display);
    $rows = dbRowsCount($sql, $data);
logErrors("getProducts(".$offset.",".$display.") returned ".$rows." rows.");
    if ($rows > 0) {
        dbQuery($sql, $data);
        return dbFetchAll();
    } else {
        return null;
    }
}

It's not working because my dbRowsCount(...) method was returning empty string (stupid PDOStatement::fetchColumn), so I changed it to return the count with PDO::FETCH_ASSOC and it returns count=0.

Here is the function which does the row-count:

function dbRowsCount($sql, $data) {
    global $db, $query;
    $regex = '/^SELECT\s+(?:ALL\s+|DISTINCT\s+)?(?:.*?)\s+FROM\s+(.*)$/is';
    if (preg_match($regex, $sql, $output) > 0) {
        $query = $db->prepare("SELECT COUNT(*) AS count FROM {$output[1]}");
logErrors("Regex output: "."SELECT COUNT(*) AS count FROM {$output[1]}");
        $query->setFetchMode(PDO::FETCH_ASSOC);
        if ($data != null) $query->execute($data); else $query->execute();
        if (!$query) {
            echo "Oops! There was an error: PDOStatement returned false.";
            exit;
        }
        $result = $query->fetch();
        return (int)$result["count"];
    } else {
logErrors("Regex did not match: ".$sql);
    }
    return -1;
}

My error log gives me this output from the program:

Regex output: SELECT COUNT(*) AS count FROM tbl_product WHERE 1 ORDER BY last_update DESC LIMIT ?,?;
getProducts(0,5) returned 0 rows.

As you can see, the SQL has not been malformed, and the method input variables were 0 and 5 as expected.

Does anyone know what has gone wrong?

Update

Following a suggestion, I did try to execute the query directly, and it returned the correct result:

function dbDebugTest() {
    global $db;
    $stmt = $db->query("SELECT COUNT(*) AS count FROM tbl_product WHERE 1 ORDER BY last_update LIMIT 0,5;");
    $result = $stmt->fetch();
    $rows = (int)$result["count"];
    logErrors("dbDebugTest() returned rows=".$rows);
}

Output:

> dbDebugTest() returned rows=12

Following another suggestion, I changed !=null to !==null, and I also printed out the $data array:

logErrors("Data: ".implode(",",$data));
if ($data !== null) $query->execute($data); else $query->execute();

Output:

> Data: 0,5

However, the dbRowsCount($sql, $data) still returns 0 rows for this query!

Update 2

Following advice to implement a custom PDOStatement class which would allow me to output the query after the values have been binded, I found that the function would stop after $query->execute($data) and so the output would not be printed, although the custom class works for every other query in my program.

Updated code:

function dbRowsCount($sql, $data) {
    global $db, $query;
    $regex = '/^SELECT\s+(?:ALL\s+|DISTINCT\s+)?(?:.*?)\s+FROM\s+(.*)$/is';
    if (preg_match($regex, $sql, $output) > 0) {
        $query = $db->prepare("SELECT COUNT(*) AS count FROM {$output[1]}");
logErrors("Regex output: "."SELECT COUNT(*) AS count FROM {$output[1]}");
        $query->setFetchMode(PDO::FETCH_ASSOC);
logErrors("Data: ".implode(",",$data));
        $query->execute($data);
logErrors("queryString:".$query->queryString);
logErrors("_debugQuery():".$query->_debugQuery());
        if (!$query) {
            echo "Oops! There was an error: PDOStatement returned false.";
            exit;
        }
        $result = $query->fetch();
        return (int)$result["count"];
    } else {
logErrors("Regex did not match: ".$sql);
    }
    return -1;
}

Output:

Regex output: SELECT COUNT() AS count FROM tbl_product_category WHERE id=?;
Data: 5
queryString:SELECT COUNT() AS count FROM tbl_product_category WHERE id=?;
_debugQuery():SELECT COUNT(*) AS count FROM tbl_product_category WHERE id=?;

Regex output: SELECT COUNT(*) AS count FROM tbl_product WHERE 1 ORDER BY last_update DESC LIMIT ?,?;
Data: 0,5
// function stopped and _debugQuery couldn't be output

Update 3

Since I couldn't get the custom PDOStatement class to give me some output, I thought I'd rewrite the getProducts(...) class to bind the params with named placeholders instead.

function getProducts($offset, $display) {
    $sql = "SELECT * FROM tbl_product WHERE 1 ORDER BY last_update DESC LIMIT :offset, :display;";
    $data = array(':offset'=>$offset, ':display'=>$display);
    $rows = dbRowsCount($sql, $data);
logErrors("getProducts(".$offset.",".$display.") returned ".$rows." rows.");
    if ($rows > 0) {
        dbQuery($sql, $data);
        return dbFetchAll();
    } else {
        return null;
    }
}

Output:

Regex output: SELECT COUNT(*) AS count FROM tbl_product WHERE 1 ORDER BY last_update DESC LIMIT :offset, :display;
Data: 0,5
// Still crashes after $query->execute($data) and so logErrors("getProducts(".$offset."...)) wasn't printed out

Update 4

This dbDebugTest previously worked with declaring the limit values 0,5 directly in the SQL string. Now I've updated it to bind the parameters properly:

function dbDebugTest($offset, $display) {
    logErrors("Beginning dbDebugTest()");
    global $db;
    $stmt = $db->prepare("SELECT COUNT(*) AS count FROM tbl_product WHERE 1 ORDER BY last_update LIMIT :offset,:display;");
    $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
    $stmt->bindParam(':display', $display, PDO::PARAM_INT);
    if ($stmt->execute()) {
      $result = $stmt->fetch();
      $rows = (int)$result["count"];
      logErrors("dbDebugTest() returned rows=".$rows);
    } else {
      logErrors("dbDebugTest() failed!");
    }
}

The function crashes, and only this is output:

Beginning dbDebugTest()

Update 5

Following a suggestion to turn errors on (they are off by default), I did this:

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

That itself, made the dbDebugTest() from Update 4 work!

Beginning dbDebugTest() dbDebugTest() returned rows=12

And now an error is generated in my webserver logs:

[warn] mod_fcgid: stderr: PHP 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', '5'' at line 1'
in /home/linweb09/b/example.com-1050548206/user/my_program/database/dal.php:36

Line 36 refers to dbRowsCount(...) method and the line is $query->execute($data).

So the other method getProducts(...) still doesn't work because it uses this method of binding the data and the params turn into ''0' and '5'' (is this a bug?). A bit annoying but I'll have to create a new method in my dal.php to allow myself to bind the params in the stricter way - with bindParam.

Thanks especially to @Travesty3 and @eggyal for their help!! Much, much appreciated.

did you try to run the query manually? like using $db->query(xxx) without any parameters or wrapper functions? does it work then? — kuba
– kuba, Commented May 1, 2012 at 17:10
I don't understand why this is being downvoted? @kuba I didn't because I tried the SQL directly in phpMyAdmin and so I assumed it would work, but I will try that now. — Ozzy
– Ozzy, Commented May 1, 2012 at 17:16
@Ozzy: Nor do I understand the downvotes (you have my +1). Have you tested the if ($data != null) to see which path the execution takes? Shouldn't one use !is_null() or !== in PHP? — eggyal
– eggyal, Commented May 1, 2012 at 17:19
kuba, yes that returns rows=12. I have a feeling the limit params aren't binding through the associative array. @eggyal I actually did try that and $data is not null p.s thanks for the upvote — Ozzy
– Ozzy, Commented May 1, 2012 at 17:25

Community · Accepted Answer · 2017-05-23 12:02:59Z

2

Based on Update 2 in the question, where execution stops after the execute statement, it looks like the query is failing. After looking at some PDO documentation, it looks like the default error handling setting is PDO::ERRMODE_SILENT, which would result in the behavior you are seeing.

This is most likely due to the numbers in your LIMIT clause being put into single-quotes when passed in as parameters, as was happening in this post.

The solution to that post was to specify the parameters as integers, using the bindValue method. So you will probably have to do something similar.

And it looks like you should also be executing your queries with try-catch blocks in order to catch the MySQL error.

bindValue method:

if ($data !== null)
{
    for ($i=0; $i<count($data); $i++)
        $query->bindValue($i+1, $data[$i], PDO::PARAM_INT);
    $query->execute($data);
}
else
    $query->execute();

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered May 1, 2012 at 18:44

Travesty3

14.5k7 gold badges64 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Ozzy Over a year ago

I'm type-casting to (int) in my getProducts(...) method and besides which I'm doing it the same way for many other queries with integer values, and those work fine. I have to see bindParam if that works...

Travesty3 Over a year ago

Yes, you are type-casting to int, but the trick is to tell PDO that the value is an int. To do that, you need to use PDO::PARAM_INT. To use that, you need to use the bindValue method.

Ozzy Over a year ago

Okay so I've tried it with the bindParam, type INT, and it still crashes. Please see my update.

Travesty3 Over a year ago

@Ozzy: Did you try changing your error handling setting? Try $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);.

Ozzy Over a year ago

wow, that made the dbDebugTest() work! But the other method doesn't work, and an error is generated in my webserver log files, with a weird param input (it added an apostrophe)

|

eggyal · Accepted Answer · 2012-05-01 17:53:43Z

1

You're testing to see if $data is NULL with the equality, rather than the identity, operator (see the PHP manual for more information on how NULL values are handled by the various comparison operators). You need to either use the identity test === / !==, or else call is_null().

As @Travesty3 mentioned above, to test whether an array is empty, use empty().

edited May 1, 2012 at 17:53

answered May 1, 2012 at 17:25

eggyal

127k18 gold badges221 silver badges245 bronze badges

12 Comments

Ozzy Over a year ago

So thats a mistake, which I will now ammend. However, I did at one point print the the $data and it gave me "Array" so it is not null.

Ozzy Over a year ago

you are right, but I printed it outside of the IF clause, and just did it again now, its an array with values 0,5. I have a hunch this is to do with execute($data) not binding properly?

Travesty3 Over a year ago

@eggyal: How did you determine that the test was failing? If it did take the $query->execute(); path, then wouldn't MySQL throw an error if you tried to use LIMIT ?,?? (I'm actually asking, as I don't have much experience with PDO)

Ozzy Over a year ago

@eggyal Oh I agreed with you and changed it back then (when I said 'you are right'). I just updated my post to include the info.

eggyal Over a year ago

@Travesty3: The documentation is clear that loose equality comparison of an array with NULL is always true (and therefore != is always false). QED.

|

Collectives™ on Stack Overflow

PHP PDO bug when trying to parameterize SQL LIMIT :offset, :display?

Update

Update 2

Update 3

Update 4

Update 5

2 Answers 2

7 Comments

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Update

Update 2

Update 3

Update 4

Update 5

2 Answers 2

7 Comments

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related