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

EverSQL Database Performance Knowledge Base

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

Database type:

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.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid OFFSET In LIMIT Clause (query line: 8): OFFSET clauses can be very slow when used with high offsets (e.g. with high page numbers when implementing paging). Instead, use the following \u003ca target\u003d"_blank" href\u003d"http://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/"\u003eseek method\u003c/a\u003e, which provides better and more stable response rates.
  2. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
  3. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `tbl_product` ADD INDEX `tbl_product_idx_last_update` (`last_update`);
The optimized query:
SELECT
        * 
    FROM
        tbl_product 
    WHERE
        1 
    ORDER BY
        tbl_product.last_update DESC LIMIT 0,
        5

Related Articles



* original question posted on StackOverflow here.