Attention
TYPO3 v9 has reached its end-of-life September 30th, 2021 and is not maintained by the community anymore. Looking for a stable version? Use the version switch on the top left.
You can order Extended Long Term Support (ELTS) here: TYPO3 ELTS.
QueryHelper¶
The class contains miscellaneous helper methods to build syntactically valid SQL queries.
Most helper methods are required to deal with legacy data where the format of the input is not strict enough to reliably use the SQL parts in queries directly.
The whole class is marked as @internal
, should not be used by extension
authors and may - if things go wrong - change at will. The class will hopefully
vanish mid-term. However, there may be situations when the class methods can become
handy if extension authors migrate their own extensions away
from TYPO3_DB
to doctrine-dbal
. In practice, the core will most likely add proper
deprecations to single methods if they are target of removal later.
Extension developers may keep this class in mind for migration, but must not use methods for new code created from scratch. Apart from that, as can be seen below, using those methods often ends up in rather ugly code.
The migration benefits are the only reason the methods are documented here.
Warning
Using those methods raise the risk of SQL injections, especially for methods like
->stripLogicalOperatorPrefix()
since its input string tends to come from user
supplied input and is sometimes added as WHERE
expression without further quoting.
Keep a special eye on those scenarios!
parseOrderBy()¶
Some parts of the core framework allow string definitions like ORDER BY sorting
for instance
in TCA
and TypoScript
. The method rips those strings apart and prepares them to be fed
to QueryBuilder->orderBy()
:
// 'ORDER BY aField ASC,anotherField, aThirdField DESC'
// ->
// [ ['aField', 'ASC'], ['anotherField', null], ['aThirdField', 'DESC'] ]
$uglyOrderBy = 'ORDER BY aField ASC,anotherField, aThirdField DESC'
foreach (QueryHelper::parseOrderBy((string)$uglyOrderBy) as $orderPair) {
list($fieldName, $order) = $orderPair;
$queryBuilder->addOrderBy($fieldName, $order);
}
parseGroupBy()¶
Parses GROUP BY
strings ready to be added via QueryBuilder->groupBy()
,
similar to ->parseOrderBy()
:
// 'GROUP BY be_groups.title, anotherField'
// ->
// ['be_groups.title', 'anotherField']
$uglyGroupBy = 'GROUP BY be_groups.title, anotherField';
$queryBuilder->groupBy(QueryHelper::parseGroupBy($uglyGroupBy));
parseTableList()¶
Parse a table list, possibly prefixed with FROM, and explode it into and array of arrays where
each item consists of a tableName and an optional alias name,
ready to be put into QueryBuilder->from()
:
// 'FROM aTable a,anotherTable, aThirdTable AS c',
// ->
// [ ['aTable', 'a'], ['anotherTable', null], ['aThirdTable', 'c'] ]
$uglyTableString = 'FROM aTable a,anotherTable, aThirdTable AS c;
foreach (QueryHelper::parseTableList($uglyTableString) as $tableNameAndAlias) {
list($tableName, $tableAlias) = $tableNameAndAlias;
$queryBuilder->from($tableName, $tableAlias);
}
parseJoin()¶
Split a JOIN SQL fragment into table name, alias and join conditions:
// 'aTable AS `anAlias` ON anAlias.uid = anotherTable.uid_foreign'
// ->
// [
// 'tableName' => 'aTable',
// 'tableAlias' => 'anAlias',
// 'joinCondition' => 'anAlias.uid = anotherTable.uid_foreign'
// ],
$uglyJoinString = 'aTable AS `anAlias` ON anAlias.uid = anotherTable.uid_foreign';
$joinParts = QueryHelper::parseJoin($uglyJoinString);
$queryBuilder->join(
$leftTable,
$joinParts['tableName'],
$joinParts['tableAlias'],
$joinParts['joinCondition']
);
stripLogicalOperatorPrefix()¶
Removes the prefixes AND
/ OR
from an input string.
Those prefixes are added in doctrine-dbal
via QueryBuilder->where()
, QueryBuilder->orWhere()
,
ExpressionBuilder->andX()
and friends. Some parts of the TYPO3
framework however carry SQL fragments
prefixed with AND
or OR
around and it's not always possible to easily get rid of those. The method
helps by killing those prefixes before they are handed over to the doctrine
API:
// 'AND 1=1'
// ->
// '1=1'
$uglyWherePart = 'AND 1=1'
$queryBuilder->where(
// WARNING: High risk of possible SQL injection here, take additional actions!
QueryHelper::stripLogicalOperatorPrefix($uglyWherePart)
);
getDateTimeFormats()¶
Just a left over method from the old TYPO3_DB
DatabaseConnection
class. Of little to no use
for extension authors. This one is hopefully one of the first methods to vanish from the class.
quoteDatabaseIdentifiers()¶
This helper method is used especially in TCA
and TypoScript
at places where SQL fragments are specified to
correctly quote table and field names for the specific database platform. It for example
substitutes {#aIdentifier}
to `aIdentifier`
if using MySQL or to "aIdentifier"
if using PostgreSQL. Let's suppose a simple TCA
columns select field like this:
'aSelectFieldWithForeignTableWhere' => [
'label' => 'some label',
'config' => [
'type' => 'select',
'renderType' => 'selectSingle',
'foreign_table' => 'tx_some_foreign_table_name',
'foreign_table_where' => 'AND {#tx_some_foreign_table_name}.{#pid} = 42',
],
],
Method quoteDatabaseIdentifiers()
is called for foreign_table_where
, and if using
MySQL, this fragment will be substituted to:
AND `tx_some_foreign_table_name`.`pid` = 42
The core had to come up with this special syntax since the core API contains various places where SQL fragments can be specified by extension developers who do not know and should not restrict on which actual platform a query is performed.
As an extension developer it is important to use this {#...}
syntax in order to make
extensions database platform agnostic. The TCA reference and TypoScript reference contains
hints at the according properties that need this, in general the core calls this helper
method whenever SQL fragments can be specified in TCA
and TypoScript
.