Database plugin placeholder replacement can be better

[jdavidzapatab]

Hi.

In the ZFDebug_Controller_Plugin_Debug_Plugin_Database class, the getProfile() method has a problem when the query parameters include the ? character. This causes the replacement of placeholders to fail, in example, when one of these parameters is an URL with ?foo=1&var=2.

To solve this, replace the getProfile() method with this, or find the differences and apply them to the file.

    public function getProfile() {

        // Generate a unique token hard to find in an SQL query
        $strToken = md5(__METHOD__);

        $queries = '';
        foreach ($this->_db as $name => $adapter) {
            if ($profiles = $adapter->getProfiler()->getQueryProfiles()) {
                $adapter->getProfiler()->setEnabled(false);
                if (1 < count($this->_db)) {
                    $html .= '<h4>Adapter ' . $name . '</h4>';
                }
                $queries .='<table cellspacing="0" cellpadding="0" width="100%">';
                foreach ($profiles as $profile) {
                    $queries .= "<tr>\n<td style='text-align:right;padding-right:2em;' nowrap>\n"
                            . sprintf('%0.2f', $profile->getElapsedSecs() * 1000)
                            . "ms</td>\n<td>";
                    $params = $profile->getQueryParams();
                    array_walk($params, array($this, '_addQuotes'));
                    $paramCount = count($params);
                    if ($paramCount) {

                        // Get the profiled query
                        $sqlQuery = $profile->getQuery();

                        // Replace all the '?' with the generated token
                        $modifiedSqlQuery = str_replace('?', $strToken, $sqlQuery);

                        // Using a regular expression, se replace each token
                        // with its corresponding value in the SQL parameter list
                        $queries .= htmlspecialchars(preg_replace(array_fill(0, $paramCount, '/' . $strToken . '/'), $params, $modifiedSqlQuery, 1));
                    } else {
                        $queries .= htmlspecialchars($profile->getQuery());
                    }

                    $supportedAdapter = ($adapter instanceof Zend_Db_Adapter_Mysqli ||
                            $adapter instanceof Zend_Db_Adapter_Pdo_Mysql);

                    # Run explain if enabled, supported adapter and SELECT query
                    if ($this->_explain && $supportedAdapter) {
                        $queries .= "</td><td style='color:#7F7F7F;padding-left:2em;' nowrap>";

                        foreach ($adapter->fetchAll('EXPLAIN ' . $profile->getQuery()) as $explain) {
                            $queries .= "<div style='padding-bottom:0.5em'>";
                            $explainData = array(
                                'Type' => $explain['select_type'] . ', ' . $explain['type'],
                                'Table' => $explain['table'],
                                'Possible keys' => str_replace(',', ', ', $explain['possible_keys']),
                                'Key used' => $explain['key'],
                            );
                            if ($explain['Extra']) {
                                $explainData['Extra'] = $explain['Extra'];
                            }
                            $explainData['Rows'] = $explain['rows'];

                            $explainEnd = end($explainData);
                            foreach ($explainData as $key => $value) {
                                $queries .= "$key: <span style='color:#ffb13e'>$value</span><br>\n";
                            }
                            $queries .= "</div>";
                        }
                    }

                    $queries .= "</td>\n</tr>\n";
                }
                $queries .= "</table>\n";
            }
        }
        return $queries;
    }

In addition to this, the _addQuotes() method can be improved by keeping the NULL values in the DB profile's pane:

// For adding quotes to query params
    protected function _addQuotes(&$value, $key) {

        if (is_null($value)) {

            // If the current value is NULL, do not add quotes to it.
            // Instead, just return the 'NULL' string.
            $value = "NULL";
        } else {
            $value = "'" . $value . "'";
        }
    }

I hope this to be helpfull.