Highlight SQL query
@param        $query
@param string $sqlName
@param bool   $nl2br
@return JBDump
766415
public static function sql($query, $sqlName = 'SQL Query', $nl2br = false)
    {
        // Joomla hack
        if (defined('_JEXEC')) {
            $config = new JConfig();
            $prefix = $config->dbprefix;
            $query  = str_replace('#__', $prefix, $query);
        }

        if (class_exists('JBDump_SqlFormatter')) {
            $sqlHtml = JBDump_SqlFormatter::format($query);
            return self::i()->dump($sqlHtml, $sqlName . '::html');
        }

        return self::i()->dump($query, $sqlName . '::html');
    }
354738
protected function getListQuery($query = null)
	{
		$db = JFactory::getDbo();

		// Check if we can use the supplied SQL query.
		$query = $query instanceof JDatabaseQuery ? $query : $db->getQuery(true)
			->select('a.id, a.title, a.alias, a.description AS summary, a.extension')
			->select('a.created_user_id AS created_by, a.modified_time AS modified, a.modified_user_id AS modified_by')
			->select('a.metakey, a.metadesc, a.metadata, a.language, a.lft, a.parent_id, a.level')
			->select('a.created_time AS start_date, a.published AS state, a.access, a.params');

		// Handle the alias CASE WHEN portion of the query.
		$case_when_item_alias = ' CASE WHEN ';
		$case_when_item_alias .= $query->charLength('a.alias', '!=', '0');
		$case_when_item_alias .= ' THEN ';
		$a_id = $query->castAsChar('a.id');
		$case_when_item_alias .= $query->concatenate(array($a_id, 'a.alias'), ':');
		$case_when_item_alias .= ' ELSE ';
		$case_when_item_alias .= $a_id . ' END as slug';
		$query->select($case_when_item_alias)
			->from('#__categories AS a')
			->where($db->quoteName('a.id') . ' > 1');

		return $query;
	}
786773
protected function parseRQL($query) {
    if (strlen($query) > 0) {
      // replace rql operators
      $operatorMap = ['eq' => '=', 'ne' => '!=', 'lt' => '<', 'lte' => '<=',
          'gt' => '>', 'gte' => '>=', 'in' => 'in', 'match' => 'regexp', '=' => '='];
      $combineMap = ['|' => 'OR', '&' => 'AND'];
      $queryType = $this->getQueryType();
      $mapper = self::getMapper($queryType);
      $persistenceFacade = ObjectFactory::getInstance('persistenceFacade');
      foreach ($operatorMap as $rqlOp => $sqlOp) {
        // add '=' separator for letter-only operators
        $operator = preg_match('/^[a-zA-Z]+$/', $rqlOp) ? '='.$rqlOp.'=' : $rqlOp;
        $query = preg_replace_callback('/([^ <>!=|&\(\)]+) ?'.$operator.' ?([^ <>!=|&\(\)]+)/', function ($match)
                use($rqlOp, $sqlOp, $mapper, $persistenceFacade) {
          $typeAttr = $match[1];
          $value = preg_replace('/^[\'"]|[\'"]$/', '', $match[2]);

          // try to determine value type
          list($typeOrRole, $attribute) = explode('.', preg_replace('/[`"\'\(\)]/', '', $typeAttr), 2);
          $attributeDef = null;
          if ($persistenceFacade->isKnownType($typeOrRole) &&
                  $persistenceFacade->getFullyQualifiedType($typeOrRole) == $mapper->getType() && $mapper->hasAttribute($attribute)) {
            $attributeDef = $mapper->getAttribute($attribute);
          }
          elseif ($mapper->hasRelation($typeOrRole)) {
            $otherMapper = self::getMapper($mapper->getRelation($typeOrRole)->getOtherType());
            $attributeDef = $otherMapper->hasAttribute($attribute) ? $otherMapper->getAttribute($attribute) : null;
          }
          $attributeType = $attributeDef != null ? strtolower($attributeDef->getType()) : null;
          $isNumber = $attributeType == 'integer' || $attributeType == 'float';

          // strip slashes from regexp
          if ($sqlOp === 'regexp') {
            $value = preg_replace('/^\/|\/[a-zA-Z]*$/', '', $value);
          }

          // handle null values
          if ($value === 'null') {
            $sqlOp = $sqlOp == '=' ? 'is' : 'is not';
            $replace = $typeAttr.' '.$sqlOp.' null';
          }
          else {
            // quote value according to type
            $replace = $typeAttr.' '.$sqlOp.' ';
            if ($rqlOp == 'in') {
              $replace .= '('.($isNumber ? $value : join(',', array_map([$mapper, 'quoteValue'], explode(',', $value)))).')';
            }
            else {
              $replace .= $isNumber ? $value : $mapper->quoteValue($value);
            }
          }

          return $replace;
        }, $query);
      }
      foreach ($combineMap as $rqlOp => $sqlOp) {
        $query = str_replace($rqlOp, ' '.$sqlOp.' ', $query);
      }
    }
    return $query;
  }
957638
private function renderSQL($sql, $line)
    {
        try {
            $sql = trim($sql);
            $count = count(explode(PHP_EOL, $sql));
            $code  = '<code>' . SqlFormatter::highlight($sql) . '</code>';
            return $this->wrapLineNumbers($count, $code, $line);
        }
        catch(Throwable $e) { // Error or Exception (executed only in PHP 7, will not match in PHP 5)
            return null;
        }
        catch(Exception $e) { // Once PHP 5 support is no longer needed, this block can be removed.
            return null;
        }
    }
1044041
protected function sendQuery(string $query)
    {
        if (self::$monitorQueries) {
            self::$queriesCount++;
        }
        $result = $this->link->query($query);
        if ($result === false) {
            throw new SqlException(
                'SQL-error [' . $this->link->errno . ']: ' . $this->link->error . "\nSQL-query: $query",
                $this->link->errno
            );
        }

        return $result === true ? $result : new Query\Result($result);
    }
1030536
public function realQuery($sql) {
		BLog::addToLog($this->logsuffix . ' Query: ' . $sql);
		$this->queries_count++;
		$r = $this->mysqli->real_query($sql);
		if ((DEBUG_MODE) && (empty($r))) {
			BLog::addToLog($this->logsuffix . ' query failed!', LL_ERROR);
			BLog::addToLog($this->logsuffix . ' query="' . $sql . '";', LL_ERROR);
			BLog::addToLog($this->logsuffix . ' query error=' . $this->lastError(), LL_ERROR);
		}
		return $r;
	}
535770
private static function logQuery(string $query): void
  {
    $query = trim($query);

    if (strpos($query, "\n")!==false)
    {
      // Query is a multi line query.
      self::$io->logVeryVerbose('Executing query:');
      self::$io->logVeryVerbose('<sql>%s</sql>', $query);
    }
    else
    {
      // Query is a single line query.
      self::$io->logVeryVerbose('Executing query: <sql>%s</sql>', $query);
    }
  }
671210
public function sphinx_highlight($text, $index, $query, $options = array())
    {
        $result = $this->searchd->getClient()->BuildExcerpts(array((string)$text), $index, $query, $options);

        if (!empty($result[0])) {
            return $result[0];
        } else {
            return '';
        }
    }
855719
protected function _renderFrom($sql)
    {
        /*
         * If no table specified, use RDBMS-dependent solution
         * for table-less query.  e.g. DUAL in Oracle.
         */
        if (empty($this->_parts[self::FROM])) {
            $this->_parts[self::FROM] = $this->_getDummyTable();
        }

        $from = array();

        foreach ($this->_parts[self::FROM] as $correlationName => $table) {
            $tmp = '';

            $joinType = ($table['joinType'] == self::FROM) ? self::INNER_JOIN : $table['joinType'];

            // Add join clause (if applicable)
            if (! empty($from)) {
                $tmp .= ' ' . strtoupper($joinType) . ' ';
            }

            $tmp .= $this->_getQuotedSchema($table['schema']);
            $tmp .= $this->_getQuotedTable($table['tableName'], $correlationName);

            // Add join conditions (if applicable)
            if (!empty($from) && ! empty($table['joinCondition'])) {
                $tmp .= ' ' . self::SQL_ON . ' ' . $table['joinCondition'];
            }

            // Add the table name and condition add to the list
            $from[] = $tmp;
        }

        // Add the list of all joins
        if (!empty($from)) {
            $sql .= ' ' . self::SQL_FROM . ' ' . implode("\n", $from);
        }

        return $sql;
    }
832705
public function createQuery(Query $query): Query
    {
        $dbQuery = $this->connection->createQueryBuilder()
            ->insert('ngbm_collection_query')
            ->values(
                [
                    'id' => ':id',
                    'status' => ':status',
                    'collection_id' => ':collection_id',
                    'type' => ':type',
                ]
            )
            ->setValue(
                'id',
                $query->id !== null ?
                    (int) $query->id :
                    $this->connectionHelper->getAutoIncrementValue('ngbm_collection_query')
            )
            ->setParameter('status', $query->status, Type::INTEGER)
            ->setParameter('collection_id', $query->collectionId, Type::INTEGER)
            ->setParameter('type', $query->type, Type::STRING);

        $dbQuery->execute();

        $query->id = $query->id ?? (int) $this->connectionHelper->lastInsertId('ngbm_collection_query');

        return $query;
    }
19712
public static function format_sql( $sql ) {

		$sql = str_replace( array( "\r\n", "\r", "\n", "\t" ), ' ', $sql );
		$sql = esc_html( $sql );
		$sql = trim( $sql );

		$regex = 'ADD|AFTER|ALTER|AND|BEGIN|COMMIT|CREATE|DELETE|DESCRIBE|DO|DROP|ELSE|END|EXCEPT|EXPLAIN|FROM|GROUP|HAVING|INNER|INSERT|INTERSECT|LEFT|LIMIT|ON|OR|ORDER|OUTER|RENAME|REPLACE|RIGHT|ROLLBACK|SELECT|SET|SHOW|START|THEN|TRUNCATE|UNION|UPDATE|USE|USING|VALUES|WHEN|WHERE|XOR';
		$sql   = preg_replace( '# (' . $regex . ') #', '<br> $1 ', $sql );

		$keywords = '\b(?:ACTION|ADD|AFTER|ALTER|AND|ASC|AS|AUTO_INCREMENT|BEGIN|BETWEEN|BIGINT|BINARY|BIT|BLOB|BOOLEAN|BOOL|BREAK|BY|CASE|COLLATE|COLUMNS?|COMMIT|CONTINUE|CREATE|DATA(?:BASES?)?|DATE(?:TIME)?|DECIMAL|DECLARE|DEC|DEFAULT|DELAYED|DELETE|DESCRIBE|DESC|DISTINCT|DOUBLE|DO|DROP|DUPLICATE|ELSE|END|ENUM|EXCEPT|EXISTS|EXPLAIN|FIELDS|FLOAT|FOREIGN|FOR|FROM|FULL|FUNCTION|GROUP|HAVING|IF|IGNORE|INDEX|INNER|INSERT|INTEGER|INTERSECT|INTERVAL|INTO|INT|IN|IS|JOIN|KEYS?|LEFT|LIKE|LIMIT|LONG(?:BLOB|TEXT)|MEDIUM(?:BLOB|INT|TEXT)|MERGE|MIDDLEINT|NOT|NO|NULLIF|ON|ORDER|OR|OUTER|PRIMARY|PROC(?:EDURE)?|REGEXP|RENAME|REPLACE|RIGHT|RLIKE|ROLLBACK|SCHEMA|SELECT|SET|SHOW|SMALLINT|START|TABLES?|TEXT(?:SIZE)?|THEN|TIME(?:STAMP)?|TINY(?:BLOB|INT|TEXT)|TRUNCATE|UNION|UNIQUE|UNSIGNED|UPDATE|USE|USING|VALUES?|VAR(?:BINARY|CHAR)|WHEN|WHERE|WHILE|XOR)\b';
		$sql      = preg_replace( '#' . $keywords . '#', '<b>$0</b>', $sql );

		return '<code>' . $sql . '</code>';

	}
473641
public function rawSelectOne(Query $query, $join = 'all')
        {
            $query->setTable($this);
            $query->setupJoinTables($join);
            $query->setLimit(1);
            $query->generateSelectSQL();

            $resultset = Statement::getPreparedStatement($query)->execute();
            $resultset->next();

            return $resultset->getCurrentRow();
        }
638425
public function output_html($data, $query='') {
        global $CFG, $OUTPUT;

        $return = "";
        $brtag = html_writer::empty_tag('br');

        /// One system controlling Moodle with Token
        $return .= $OUTPUT->heading(get_string('onesystemcontrolling', 'webservice'), 3, 'main');
        $table = new html_table();
        $table->head = array(get_string('step', 'webservice'), get_string('status'),
            get_string('description'));
        $table->colclasses = array('leftalign step', 'leftalign status', 'leftalign description');
        $table->id = 'onesystemcontrol';
        $table->attributes['class'] = 'admintable wsoverview generaltable';
        $table->data = array();

        $return .= $brtag . get_string('onesystemcontrollingdescription', 'webservice')
                . $brtag . $brtag;

        /// 1. Enable Web Services
        $row = array();
        $url = new moodle_url("/admin/search.php?query=enablewebservices");
        $row[0] = "1. " . html_writer::tag('a', get_string('enablews', 'webservice'),
                        array('href' => $url));
        $status = html_writer::tag('span', get_string('no'), array('class' => 'statuscritical'));
        if ($CFG->enablewebservices) {
            $status = get_string('yes');
        }
        $row[1] = $status;
        $row[2] = get_string('enablewsdescription', 'webservice');
        $table->data[] = $row;

        /// 2. Enable protocols
        $row = array();
        $url = new moodle_url("/admin/settings.php?section=webserviceprotocols");
        $row[0] = "2. " . html_writer::tag('a', get_string('enableprotocols', 'webservice'),
                        array('href' => $url));
        $status = html_writer::tag('span', get_string('none'), array('class' => 'statuscritical'));
        //retrieve activated protocol
        $active_protocols = empty($CFG->webserviceprotocols) ?
                array() : explode(',', $CFG->webserviceprotocols);
        if (!empty($active_protocols)) {
            $status = "";
            foreach ($active_protocols as $protocol) {
                $status .= $protocol . $brtag;
            }
        }
        $row[1] = $status;
        $row[2] = get_string('enableprotocolsdescription', 'webservice');
        $table->data[] = $row;

        /// 3. Create user account
        $row = array();
        $url = new moodle_url("/user/editadvanced.php?id=-1");
        $row[0] = "3. " . html_writer::tag('a', get_string('createuser', 'webservice'),
                        array('href' => $url));
        $row[1] = "";
        $row[2] = get_string('createuserdescription', 'webservice');
        $table->data[] = $row;

        /// 4. Add capability to users
        $row = array();
        $url = new moodle_url("/admin/roles/check.php?contextid=1");
        $row[0] = "4. " . html_writer::tag('a', get_string('checkusercapability', 'webservice'),
                        array('href' => $url));
        $row[1] = "";
        $row[2] = get_string('checkusercapabilitydescription', 'webservice');
        $table->data[] = $row;

        /// 5. Select a web service
        $row = array();
        $url = new moodle_url("/admin/settings.php?section=externalservices");
        $row[0] = "5. " . html_writer::tag('a', get_string('selectservice', 'webservice'),
                        array('href' => $url));
        $row[1] = "";
        $row[2] = get_string('createservicedescription', 'webservice');
        $table->data[] = $row;

        /// 6. Add functions
        $row = array();
        $url = new moodle_url("/admin/settings.php?section=externalservices");
        $row[0] = "6. " . html_writer::tag('a', get_string('addfunctions', 'webservice'),
                        array('href' => $url));
        $row[1] = "";
        $row[2] = get_string('addfunctionsdescription', 'webservice');
        $table->data[] = $row;

        /// 7. Add the specific user
        $row = array();
        $url = new moodle_url("/admin/settings.php?section=externalservices");
        $row[0] = "7. " . html_writer::tag('a', get_string('selectspecificuser', 'webservice'),
                        array('href' => $url));
        $row[1] = "";
        $row[2] = get_string('selectspecificuserdescription', 'webservice');
        $table->data[] = $row;

        /// 8. Create token for the specific user
        $row = array();
        $url = new moodle_url("/admin/webservice/tokens.php?sesskey=" . sesskey() . "&action=create");
        $row[0] = "8. " . html_writer::tag('a', get_string('createtokenforuser', 'webservice'),
                        array('href' => $url));
        $row[1] = "";
        $row[2] = get_string('createtokenforuserdescription', 'webservice');
        $table->data[] = $row;

        /// 9. Enable the documentation
        $row = array();
        $url = new moodle_url("/admin/search.php?query=enablewsdocumentation");
        $row[0] = "9. " . html_writer::tag('a', get_string('enabledocumentation', 'webservice'),
                        array('href' => $url));
        $status = '<span class="warning">' . get_string('no') . '</span>';
        if ($CFG->enablewsdocumentation) {
            $status = get_string('yes');
        }
        $row[1] = $status;
        $row[2] = get_string('enabledocumentationdescription', 'webservice');
        $table->data[] = $row;

        /// 10. Test the service
        $row = array();
        $url = new moodle_url("/admin/webservice/testclient.php");
        $row[0] = "10. " . html_writer::tag('a', get_string('testwithtestclient', 'webservice'),
                        array('href' => $url));
        $row[1] = "";
        $row[2] = get_string('testwithtestclientdescription', 'webservice');
        $table->data[] = $row;

        $return .= html_writer::table($table);

        /// Users as clients with token
        $return .= $brtag . $brtag . $brtag;
        $return .= $OUTPUT->heading(get_string('userasclients', 'webservice'), 3, 'main');
        $table = new html_table();
        $table->head = array(get_string('step', 'webservice'), get_string('status'),
            get_string('description'));
        $table->colclasses = array('leftalign step', 'leftalign status', 'leftalign description');
        $table->id = 'userasclients';
        $table->attributes['class'] = 'admintable wsoverview generaltable';
        $table->data = array();

        $return .= $brtag . get_string('userasclientsdescription', 'webservice') .
                $brtag . $brtag;

        /// 1. Enable Web Services
        $row = array();
        $url = new moodle_url("/admin/search.php?query=enablewebservices");
        $row[0] = "1. " . html_writer::tag('a', get_string('enablews', 'webservice'),
                        array('href' => $url));
        $status = html_writer::tag('span', get_string('no'), array('class' => 'statuscritical'));
        if ($CFG->enablewebservices) {
            $status = get_string('yes');
        }
        $row[1] = $status;
        $row[2] = get_string('enablewsdescription', 'webservice');
        $table->data[] = $row;

        /// 2. Enable protocols
        $row = array();
        $url = new moodle_url("/admin/settings.php?section=webserviceprotocols");
        $row[0] = "2. " . html_writer::tag('a', get_string('enableprotocols', 'webservice'),
                        array('href' => $url));
        $status = html_writer::tag('span', get_string('none'), array('class' => 'statuscritical'));
        //retrieve activated protocol
        $active_protocols = empty($CFG->webserviceprotocols) ?
                array() : explode(',', $CFG->webserviceprotocols);
        if (!empty($active_protocols)) {
            $status = "";
            foreach ($active_protocols as $protocol) {
                $status .= $protocol . $brtag;
            }
        }
        $row[1] = $status;
        $row[2] = get_string('enableprotocolsdescription', 'webservice');
        $table->data[] = $row;


        /// 3. Select a web service
        $row = array();
        $url = new moodle_url("/admin/settings.php?section=externalservices");
        $row[0] = "3. " . html_writer::tag('a', get_string('selectservice', 'webservice'),
                        array('href' => $url));
        $row[1] = "";
        $row[2] = get_string('createserviceforusersdescription', 'webservice');
        $table->data[] = $row;

        /// 4. Add functions
        $row = array();
        $url = new moodle_url("/admin/settings.php?section=externalservices");
        $row[0] = "4. " . html_writer::tag('a', get_string('addfunctions', 'webservice'),
                        array('href' => $url));
        $row[1] = "";
        $row[2] = get_string('addfunctionsdescription', 'webservice');
        $table->data[] = $row;

        /// 5. Add capability to users
        $row = array();
        $url = new moodle_url("/admin/roles/check.php?contextid=1");
        $row[0] = "5. " . html_writer::tag('a', get_string('addcapabilitytousers', 'webservice'),
                        array('href' => $url));
        $row[1] = "";
        $row[2] = get_string('addcapabilitytousersdescription', 'webservice');
        $table->data[] = $row;

        /// 6. Test the service
        $row = array();
        $url = new moodle_url("/admin/webservice/testclient.php");
        $row[0] = "6. " . html_writer::tag('a', get_string('testwithtestclient', 'webservice'),
                        array('href' => $url));
        $row[1] = "";
        $row[2] = get_string('testauserwithtestclientdescription', 'webservice');
        $table->data[] = $row;

        $return .= html_writer::table($table);

        return highlight($query, $return);
    }
321017
protected function getVisibleOnlySubQuery(SelectQuery $query)
    {
        // TODO: The following query requires optimization
        $subSelect = $query->subSelect();
        $subSelect
            ->selectDistinct('ezurl_object_link.url_id')
            ->from('ezurl_object_link')
            ->innerJoin(
                'ezcontentobject_attribute',
                $query->expr->lAnd(
                    $query->expr->eq('ezurl_object_link.contentobject_attribute_id', 'ezcontentobject_attribute.id'),
                    $query->expr->eq('ezurl_object_link.contentobject_attribute_version', 'ezcontentobject_attribute.version')
                )
            )
            ->innerJoin(
                'ezcontentobject_tree',
                $query->expr->lAnd(
                    $query->expr->eq('ezcontentobject_tree.contentobject_id', 'ezcontentobject_attribute.contentobject_id'),
                    $query->expr->eq('ezcontentobject_tree.contentobject_version', 'ezcontentobject_attribute.version')
                )
            )
            ->where(
                $query->expr->eq(
                    'ezcontentobject_tree.is_invisible',
                    $query->bindValue(0, null, PDO::PARAM_INT)
                )
            );

        return $subSelect;
    }
812558
public function buildLimitFragment(SQLSelect $query, array &$parameters)
    {
        $nl = $this->getSeparator();

        // Ensure limit is given
        $limit = $query->getLimit();
        if (empty($limit)) {
            return '';
        }

        // For literal values return this as the limit SQL
        if (!is_array($limit)) {
            return "{$nl}LIMIT $limit";
        }

        // Assert that the array version provides the 'limit' key
        if (!isset($limit['limit']) || !is_numeric($limit['limit'])) {
            throw new InvalidArgumentException(
                'DBQueryBuilder::buildLimitSQL(): Wrong format for $limit: ' . var_export($limit, true)
            );
        }

        // Format the array limit, given an optional start key
        $clause = "{$nl}LIMIT {$limit['limit']}";
        if (isset($limit['start']) && is_numeric($limit['start']) && $limit['start'] !== 0) {
            $clause .= " OFFSET {$limit['start']}";
        }
        return $clause;
    }
288141
public function buildHavingFragment(SQLSelect $query, array &$parameters)
    {
        $having = $query->getHavingParameterised($havingParameters);
        if (empty($having)) {
            return '';
        }

        // Generate having, considering parameters present
        $connective = $query->getConnective();
        $parameters = array_merge($parameters, $havingParameters);
        $nl = $this->getSeparator();
        return "{$nl}HAVING (" . implode("){$nl}{$connective} (", $having) . ")";
    }
795417
protected function _selectQueryTranslator($query)
    {
        $limit = $query->clause('limit');
        $offset = $query->clause('offset');

        if ($limit && $offset === null) {
            $query->modifier(['_auto_top_' => sprintf('TOP %d', $limit)]);
        }

        if ($offset !== null && !$query->clause('order')) {
            $query->order($query->newExpr()->add('(SELECT NULL)'));
        }

        if ($this->_version() < 11 && $offset !== null) {
            return $this->_pagingSubquery($query, $limit, $offset);
        }

        return $this->_transformDistinct($query);
    }