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.

Answer 1

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();

Answer 2

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().