Query builder¶
Table of Contents
The query builder provides a set of methods to create queries programmatically.
This chapter provides examples of the most common queries.
Warning
From a security point of view, the documentation of ->createNamedParameter() and ->quoteIdentifier() are an absolute must read and follow section. Make very sure you understand this and use it for each and every query to prevent SQL injections!
The query builder comes with a happy little list of small methods:
Set type of query:
->select()
,->count()
,->update()
,->insert()
and->delete()
Prepare
WHERE
conditionsManipulate default
WHERE
restrictions added by TYPO3 for->select()
Add
LIMIT
,GROUP BY
and other SQL functionsexecuteQuery()
executes aSELECT
query and returns a result, a\Doctrine\DBAL\Result
objectexecuteStatement()
executes anINSERT
,UPDATE
orDELETE
statement and returns the number of affected rows.
Most of the query builder methods provide a fluent interface, return an instance of the current query builder itself, and can be chained:
$queryBuilder
->select('uid')
->from('pages');
Instantiation¶
To create an instance of the query builder, call
ConnectionPool::getQueryBuilderForTable()
and pass the table as an
argument. The ConnectionPool object can be
injected via dependency injection.
<?php
declare(strict_types=1);
namespace MyVendor\MyExtension\Domain\Repository;
use TYPO3\CMS\Core\Database\ConnectionPool;
final class MyRepository
{
public function __construct(
private readonly ConnectionPool $connectionPool
) {}
public function findSomething()
{
$queryBuilder = $this->connectionPool
->getQueryBuilderForTable('aTable');
}
}
Attention
Never instantiate and initialize the query builder manually using
dependency injection or GeneralUtility::makeInstance()
, otherwise you
will miss essential dependencies and runtime setup.
Note
The QueryBuilder holds internal state and should not be reused for
different queries: Use one query builder per query. Get a fresh one by
calling $connection->createQueryBuilder()
if the same table is
involved, or use $connectionPool->getQueryBuilderForTable()
for a
query to a different table. Don't worry, creating those object instances
is quite fast.
select() and addSelect()¶
Create a SELECT
query.
Select all fields:
// SELECT *
$queryBuilder->select('*')
->select()
and a number of other methods of the query builder
are variadic
and can handle any number of arguments. In ->select()
each argument
is interpreted as a single field name to be selected:
// SELECT `uid`, `pid`, `aField`
$queryBuilder->select('uid', 'pid', 'aField');
Argument unpacking can be used if the list of fields already is available as array:
// SELECT `uid`, `pid`, `aField`, `anotherField`
$fields = ['uid', 'pid', 'aField', 'anotherField'];
$queryBuilder->select(...$fields);
->select()
automatically supports AS
and quotes identifiers. This
can be especially useful for join()
operations:
// SELECT `tt_content`.`bodytext` AS `t1`.`text`
$queryBuilder->select('tt_content.bodytext AS t1.text')
With ->select()
the list of fields to be selected is specified, and with
->addSelect()
further elements can be added to an existing list.
Mind that ->select()
replaces any formerly registered list instead of
appending it. Thus, it is not very usefule to call select()
twice in a
code flow or after an ->addSelect()
. The methods ->where()
and
->andWhere()
share the same behavior: ->where()
replaces all
formerly registered constraints, ->andWhere()
appends additional
constraints.
A useful combination of ->select()
and ->addSelect()
can be:
$queryBuilder->select(...$defaultList);
if ($needAdditionalFields) {
$queryBuilder->addSelect(...$additionalFields);
}
Calling the executeQuery()
function on a ->select()
query returns
a result object of type \Doctrine\DBAL\Result
. To receive single rows, a
->fetchAssociative()
loop is used on that object, or
->fetchAllAssociative()
to return a single array with all rows. A typical
code flow of a SELECT
query looks like this:
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$result = $queryBuilder
->select('uid', 'header', 'bodytext')
->from('tt_content')
->where(
$queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('lorem'))
)
->executeQuery();
while ($row = $result->fetchAssociative()) {
// Do something with that single row
debug($row);
}
Read how to correctly instantiate a query builder with the connection pool.
Default Restrictions¶
Note
->select()
and ->count()
queries trigger TYPO3 magic that adds
further default where clauses if the queried table is also registered via
$GLOBALS['TCA']
. See the RestrictionBuilder section for details on that topic.
count()¶
Create a COUNT
query, a typical usage:
// SELECT COUNT(`uid`) FROM `tt_content` WHERE (`bodytext` = 'lorem')
// AND ((`tt_content`.`deleted` = 0) AND (`tt_content`.`hidden` = 0)
// AND (`tt_content`.`starttime` <= 1669885410)
// AND ((`tt_content`.`endtime` = 0) OR (`tt_content`.`endtime` > 1669885410)))
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$count = $queryBuilder
->count('uid')
->from('tt_content')
->where(
$queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('lorem'))
)
->executeQuery()
->fetchOne();
Read how to correctly instantiate a query builder with the connection pool.
Remarks:
Similar to the
->select()
query type,->count()
automatically triggers the magic of the RestrictionBuilder that adds default restrictions such asdeleted
,hidden
,starttime
andendtime
when defined in TCA.Similar to
->select()
query types,->executeQuery()
with->count()
returns a result object of type\Doctrine\DBAL\Result
. To fetch the number of rows directly, use->fetchOne()
.The first argument to
->count()
is required, typically->count(*)
or->count('uid')
is used, the field name is automatically quoted.There is no support for
DISTINCT
, instead a->groupBy()
has to be used, for example:// Equivalent to: // SELECT DISTINCT some_field, another_field FROM my_table $queryBuilder ->select('some_field', 'another_field') ->from('my_table') ->groupBy('some_field') ->addGroupBy('another_field');
If
->count()
is combined with->groupBy()
, the result may return multiple rows. The order of those rows depends on the used DBMS. Therefore, to ensure the same order of result rows on multiple different databases, a->groupBy()
should always be combined with an->orderBy()
.
delete()¶
Create a DELETE FROM
query. The method requires the table name from which
data is to be deleted. Classic usage:
// DELETE FROM `tt_content` WHERE `bodytext` = 'lorem'
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$affectedRows = $queryBuilder
->delete('tt_content')
->where(
$queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('lorem'))
)
->executeStatement();
Read how to correctly instantiate a query builder with the connection pool.
Remarks:
For simple cases it is often easier to write and read using the
->delete()
method of the Connection object.In contrast to
->select()
,->delete()
does not automatically addWHERE
restrictions likeAND `deleted` = 0
.->delete()
does not magically transform aDELETE FROM `tt_content` WHERE `uid` = 4711
into something likeUPDATE `tt_content` SET `deleted` = 1 WHERE `uid` = 4711
internally. A soft-delete must be handled at application level with a dedicated lookup in$GLOBALS['TCA']['theTable']['ctrl']['delete']
to check if a specific table can handle the soft-delete, together with an->update()
instead.Deleting from multiple tables at once is not supported:
DELETE FROM `table1`, `table2`
can not be created.->delete()
ignores->join()
->delete()
ignoressetMaxResults()
:DELETE
withLIMIT
does not work.
update() and set()¶
Create an UPDATE
query. Typical usage:
// UPDATE `tt_content` SET `bodytext` = 'dolor' WHERE `bodytext` = 'lorem'
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder
->update('tt_content')
->where(
$queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('lorem'))
)
->set('bodytext', 'dolor')
->executeStatement();
Read how to correctly instantiate a query builder with the connection pool.
->update()
requires the table to update as the first argument and a table
alias (for example, t
) as optional second argument. The table alias can
then be used in ->set()
and ->where()
expressions:
// UPDATE `tt_content` `t` SET `t`.`bodytext` = 'dolor' WHERE `t`.`bodytext` = 'lorem'
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder
->update('tt_content', 't')
->where(
$queryBuilder->expr()->eq('t.bodytext', $queryBuilder->createNamedParameter('lorem'))
)
->set('t.bodytext', 'dolor')
->executeStatement();
Read how to correctly instantiate a query builder with the connection pool.
->set()
requires a field name as the first argument and automatically
quotes it internally. The second mandatory argument is the value to set a field
to. The value is automatically transformed to a named parameter of a prepared
statement. This way, ->set()
key/value pairs are automatically SQL
protected from injection by default.
If a field should be set to the value of another field from the row, quoting
must be turned off and ->quoteIdentifier()
and false
have to
be used:
// UPDATE `tt_content` SET `bodytext` = `header` WHERE `bodytext` = 'lorem'
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder
->update('tt_content')
->where(
$queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('lorem'))
)
->set('bodytext', $queryBuilder->quoteIdentifier('header'), false)
->executeStatement();
Read how to correctly instantiate a query builder with the connection pool.
Remarks:
For simple cases it is often easier to use the
->update()
method of the Connection object.->set()
can be called multiple times if multiple fields should be updated.->set()
requires a field name as the first argument and automatically quotes it internally.->set()
requires the value to which a field is to be set as the second parameter.->update()
ignores->join()
and->setMaxResults()
.The API does not magically add
deleted = 0
or other restrictions, as is currently the case with select, for example. (See also RestrictionBuilder).
insert() and values()¶
Create an INSERT
query. Typical usage:
// INSERT INTO `tt_content` (`bodytext`, `header`) VALUES(`lorem`, `dolor`)
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$affectedRows = $queryBuilder
->insert('tt_content')
->values([
'bodytext' => 'lorem',
'header' => 'dolor',
])
->executeStatement();
Read how to correctly instantiate a query builder with the connection pool.
The uid
of the created database row can be fetched from the connection
by using $queryBuilder->getConnection()->lastInsertId().
Remarks:
Often it is often to use
->insert()
or->bulkInsert()
of the Connection object.->values()
expects an array of key/value pairs. Both keys (field names / identifiers) and values are automatically quoted. In rare cases, quoting of values can be turned off by setting the second argument tofalse
. In those cases, quoting has must done manually, typically by using->createNamedParameter()
on the values, use with care ...->executeStatement()
after->insert()
returns the number of inserted rows, which is typically1
.The query builder does not contain a method for inserting multiple rows at once, use
->bulkInsert()
of the Connection object instead to achieve that.
from()¶
->from()
is essential for ->select()
and ->count()
query
types. ->from()
requires a table name and an optional alias name. The
method is usually called once per query creation and the table name is usually
the same as the one passed to ->getQueryBuilderForTable()
. If the query
joins multiple tables, the argument should be the name of the first table within
the ->join()
chain:
// FROM `myTable`
$queryBuilder->from('myTable');
// FROM `myTable` AS `anAlias`
$queryBuilder->from('myTable', 'anAlias');
->from()
can be called multiple times and will create the Cartesian
product of tables if not constrained by a respective ->where()
or
->andWhere()
expression. In general, it is a good idea to use
->from()
only once per query and instead model the selection of multiple
tables with an explicit ->join()
.
where(), andWhere() and orWhere()¶
The three methods are used to create WHERE
restrictions for SELECT
,
COUNT
, UPDATE
and DELETE
query types. Each argument is
usually an ExpressionBuilder object that
is converted to a string on ->executeQuery()
or
->executeStatement()
:
// use TYPO3\CMS\Core\Database\Connection;
// SELECT `uid`, `header`, `bodytext`
// FROM `tt_content`
// WHERE
// (
// ((`bodytext` = 'lorem') AND (`header` = 'a name'))
// OR (`bodytext` = 'dolor') OR (`bodytext` = 'hans')
// )
// AND (`pid` = 42)
// AND ... RestrictionBuilder TCA restrictions ...
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$result = $queryBuilder
->select('uid', 'header', 'bodytext')
->from('tt_content')
->where(
$queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('lorem')),
$queryBuilder->expr()->eq('header', $queryBuilder->createNamedParameter('a name'))
)
->orWhere(
$queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('dolor')),
$queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('hans'))
)
->andWhere(
$queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter(42, Connection::PARAM_INT))
)
->executeQuery();
Read how to correctly instantiate a query builder with the connection pool.
Note the parenthesis of the above example: ->andWhere()
encapsulates both
->where()
and ->orWhere()
with an additional restriction.
Argument unpacking can become handy with these methods:
$whereExpressions = [
$queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('lorem')),
$queryBuilder->expr()->eq('header', $queryBuilder->createNamedParameter('a name'))
];
if ($needsAdditionalExpression) {
$whereExpressions[] = $someAdditionalExpression;
}
$queryBuilder->where(...$whereExpressions);
Remarks:
The three methods are variadic. They can handle any number of arguments. For instance, if
->where()
receives four arguments, they are handled as single expressions, all combined withAND
.createNamedParameter is used to create a placeholder for a field value of a prepared statement. Always use this when dealing with user input in expressions to protect the statement from SQL injections.
->where()
should be called only once per query and resets all previously set->where()
,->andWhere()
and->orWhere()
expressions. A->where()
call after a previous->where()
,->andWhere()
or->orWhere()
usually indicates a bug or a rather weird code flow. Doing so is discouraged.When creating complex
WHERE
restrictions,->getSQL()
and->getParameters()
are helpful debugging tools to verify parenthesis and single query parts.If only
->eq()
expressions are used, it is often easier to switch to the according method of the Connection object to simplify quoting and improve readability.It is possible to feed the methods directly with strings, but this is discouraged and usually used only in rare cases where expression strings are created in a different place that can not be easily resolved.
join(), innerJoin(), rightJoin() and leftJoin()¶
Joining multiple tables in a ->select()
or ->count()
query is done
with one of these methods. Multiple joins are supported by calling the methods
more than once. All methods require four arguments: The name of the table on the
left (or its alias), the name of the table on the right, an alias for the name
of the table on the right, and the join restriction as fourth argument:
// SELECT `sys_language`.`uid`, `sys_language`.`title`
// FROM `sys_language`
// INNER JOIN `pages` `p`
// ON `p`.`sys_language_uid` = `sys_language`.`uid`
// WHERE
// (`p`.`uid` = 42)
// AND (
// (`p`.`deleted` = 0)
// AND (
// (`sys_language`.`hidden` = 0) AND (`overlay`.`hidden` = 0)
// )
// AND (`p`.`starttime` <= 1475591280)
// AND ((`p`.`endtime` = 0) OR (`overlay`.`endtime` > 1475591280))
// )
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_language')
$result = $queryBuilder
->select('sys_language.uid', 'sys_language.title')
->from('sys_language')
->join(
'sys_language',
'pages',
'p',
$queryBuilder->expr()->eq('p.sys_language_uid', $queryBuilder->quoteIdentifier('sys_language.uid'))
)
->where(
$queryBuilder->expr()->eq('p.uid', $queryBuilder->createNamedParameter(42, \PDO::PARAM_INT))
)
->executeQuery();
Read how to correctly instantiate a query builder with the connection pool.
Notes to the example above:
The query operates with the
sys_language
table as the main table, this table name is given togetQueryBuilderForTable()
.The query joins the
pages
table asINNER JOIN
and gives it the aliasp
.The join condition is
`p`.`sys_language_uid` = `sys_language`.`uid`
. It would have been identical to swap the expression arguments of the fourth->join()
argument->eq('sys_language.uid', $queryBuilder->quoteIdentifier('p.sys_language_uid'))
.The second argument of the join expression instructs the ExpressionBuilder to quote the value as a field identifier (a field name, here a combination of table and field name). Using createNamedParameter would lead in quoting as value (
'
instead of`
in MySQL) and the query would fail.The alias
p
- the third argument of the->join()
call - does not necessarily have to be set to a different name than the table name itself here. It is sufficient to usepages
as third argument and not to specify any other name. Aliases are mostly useful when a join to the same table is needed:SELECT `something` FROM `tt_content` JOIN `tt_content` `content2` ON ...
. Aliases are also useful to increase the readability of->where()
expressions.The RestrictionBuilder has added additional
WHERE
conditions for both tables involved! Thesys_language
table obviously only specifies a'disabled' => 'hidden'
asenableColumns
in its TCA ctrl section, while thepages
table specifies the fieldsdeleted
,hidden
,starttime
andstoptime
.
A more complex example with two joins. The first join points to the first table, again using an alias to resolve a language overlay scenario. The second join uses the alias of the first join target as left side:
// SELECT `tt_content_orig`.`sys_language_uid`
// FROM `tt_content`
// INNER JOIN `tt_content` `tt_content_orig` ON `tt_content`.`t3_origuid` = `tt_content_orig`.`uid`
// INNER JOIN `sys_language` `sys_language` ON `tt_content_orig`.`sys_language_uid` = `sys_language`.`uid`
// WHERE
// (`tt_content`.`colPos` = 1)
// AND (`tt_content`.`pid` = 42)
// AND (`tt_content`.`sys_language_uid` = 2)
// AND ... RestrictionBuilder TCA restrictions for tables tt_content and sys_language ...
// GROUP BY `tt_content_orig`.`sys_language_uid`
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_language')
$constraints = [
$queryBuilder->expr()->eq('tt_content.colPos', $queryBuilder->createNamedParameter(1, \PDO::PARAM_INT)),
$queryBuilder->expr()->eq('tt_content.pid', $queryBuilder->createNamedParameter(42, \PDO::PARAM_INT)),
$queryBuilder->expr()->eq('tt_content.sys_language_uid', $queryBuilder->createNamedParameter(2, \PDO::PARAM_INT)),
];
$queryBuilder
->select('tt_content_orig.sys_language_uid')
->from('tt_content')
->join(
'tt_content',
'tt_content',
'tt_content_orig',
$queryBuilder->expr()->eq(
'tt_content.t3_origuid',
$queryBuilder->quoteIdentifier('tt_content_orig.uid')
)
)
->join(
'tt_content_orig',
'sys_language',
'sys_language',
$queryBuilder->expr()->eq(
'tt_content_orig.sys_language_uid',
$queryBuilder->quoteIdentifier('sys_language.uid')
)
)
->where(...$constraints)
->groupBy('tt_content_orig.sys_language_uid')
->executeQuery();
Read how to correctly instantiate a query builder with the connection pool.
Further remarks:
->join()
andinnerJoin
are identical. They create anINNER JOIN
query, this is identical to aJOIN
query.->leftJoin()
creates aLEFT JOIN
query, this is identical to aLEFT OUTER JOIN
query.->rightJoin()
creates aRIGHT JOIN
query, this is identical to aRIGHT OUTER JOIN
query.Calls to
join()
methods are only considered for->select()
and->count()
type queries.->delete()
,->insert()
andupdate()
do not support joins, these query parts are ignored and do not end up in the final statement.The argument of
->getQueryBuilderForTable()
should be the leftmost main table.Joining two tables that are configured to different connections will throw an exception. This restricts the tables that can be configured for different database endpoints. It is possible to test the connection objects of the involved tables for equality and implement a fallback logic in PHP if they are different.
Doctrine DBAL does not support the use of join methods in combination with
->update()
,->insert()
and->delete()
methods, because such a statement is not cross-platform compatible.
orderBy() and addOrderBy()¶
Add ORDER BY
to a ->select()
statement. Both ->orderBy()
and
->addOrderBy()
require a field name as first argument:
// SELECT * FROM `sys_language` ORDER BY `sorting` ASC
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_language');
$queryBuilder->getRestrictions()->removeAll();
$languageRecords = $queryBuilder
->select('*')
->from('sys_language')
->orderBy('sorting')
->executeQuery()
->fetchAllAssociative();
Read how to correctly instantiate a query builder with the connection pool.
Remarks:
->orderBy()
resets all previously specified orders. It makes no sense to call this function again after a previous->orderBy()
or->addOrderBy()
.Both methods need a field name or a
table.fieldName
or atableAlias.fieldName
as first argument. In the example above the call to->orderBy('sys_language.sorting')
would have been identical. All identifiers are quoted automatically.The second, optional argument of both methods specifies the sort order. The two allowed values are
'ASC'
and'DESC'
, where'ASC'
is default and can be omitted.To create a chain of orders, use
->orderBy()
and then multiple->addOrderBy()
calls. The call to->orderBy('header')->addOrderBy('bodytext')->addOrderBy('uid', 'DESC')
createsORDER BY `header` ASC, `bodytext` ASC, `uid` DESC
To add more complex sorting you can use
->add('orderBy', 'FIELD(eventtype, 0, 4, 1, 2, 3)', true)
, remember to quote properly!
groupBy() and addGroupBy()¶
Add GROUP BY
to a ->select()
statement. Each argument of the
methods is a single identifier:
// GROUP BY `pages`.`sys_language_uid`, `sys_language`.`uid`
->groupBy('pages.sys_language_uid', 'sys_language.uid');
Remarks:
Similar to
->select()
and->where()
, both methods are variadic and take any number of arguments, argument unpacking is supported:->groupBy(...$myGroupArray)
Each argument is either a direct field name
GROUP BY `bodytext`
, atable.fieldName
or atableAlias.fieldName
and is properly quoted.->groupBy()
resets all previously defined group specification and should only be called once per statement.For more complex statements you can use
->add('groupBy', $sql, $append)
, remember to quote properly!
setMaxResults() and setFirstResult()¶
Add LIMIT
to restrict the number of records and OFFSET
for
pagination of query parts. Both methods should be called only once per
statement:
// SELECT * FROM `sys_language` LIMIT 2 OFFSET 4
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_language');
$queryBuilder
->select('*')
->from('sys_language')
->setMaxResults(2)
->setFirstResult(4)
->executeQuery();
Read how to correctly instantiate a query builder with the connection pool.
Remarks:
It is allowed to call
->setMaxResults()
without calling->setFirstResult()
.It is possible to call
->setFirstResult()
without callingsetMaxResults()
: This is equivalent to "Fetch everything, but leave out the first n records". Internally,LIMIT
will be added by Doctrine DBAL and set to a very high value.
add()¶
The ->add()
method appends or replaces a single, generic query part. It
can be used as a low level call when more specific calls do not provide enough
freedom to express parts of statements:
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_language');
$queryBuilder
->select('*')
->from('sys_language')
->add('orderBy', 'FIELD(eventtype, 0, 4, 1, 2, 3)');
Read how to correctly instantiate a query builder with the connection pool.
Remarks:
The first argument is the SQL part. One of:
select
,from
,set
,where
,groupBy
,having
ororderBy
.The second argument is the (properly quoted!) SQL segment of this part.
The optional third boolean argument specifies whether the SQL fragment should be appended (
true
) or replace a possibly existing SQL part of this name (false
, default).
getSQL()¶
The ->getSQL()
method returns the created query statement as string. It
is incredibly useful during development to verify that the final statement is
executed exactly as a developer expects:
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_language');
$queryBuilder
->select('*')
->from('sys_language');
debug($queryBuilder->getSQL());
$result = $queryBuilder->executeQuery();
Read how to correctly instantiate a query builder with the connection pool.
Remarks:
This is debugging code. Take proper actions to ensure that these calls do not end up in production!
The method is usually called directly before
->executeQuery()
or->executeStatement()
to output the final statement.Casting a query builder object to
(string)
has the same effect as calling->getSQL()
, but the explicit call using the method should be preferred to simplify a search operation for this kind of debugging statements.The method is a simple way to see what restrictions the RestrictionBuilder has added.
Doctrine DBAL always creates prepared statements: Each value added via createNamedParameter creates a placeholder that is later replaced when the real query is triggered via
->executeQuery()
or->executeStatement()
.->getSQL()
does not show these values, instead it displays the placeholder names, usually with a string like:dcValue1
. There is no simple solution to show the fully replaced query within the framework, but you can use getParameters to see the array of parameters used to replace these placeholders within the query. On the frontend, the queries and parameters are available in the admin panel.
getParameters()¶
The ->getParameters()
method returns the values for the placeholders of
the prepared statement in an array. This is incredibly useful during development
to verify that the final statement is executed as a developer expects:
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_language');
$queryBuilder
->select('*')
->from('sys_language');
debug($queryBuilder->getParameters());
$statement = $queryBuilder->executeQuery();
Read how to correctly instantiate a query builder with the connection pool.
Remarks:
This is debugging code. Take proper actions to ensure that these calls do not end up in production!
The method is usually called directly before
->executeQuery()
or->executeStatement()
to output the final statement.Doctrine DBAL always creates prepared statements: Each value added via createNamedParameter creates a placeholder that is later replaced when the real query is triggered via
->executeQuery()
or->executeStatement()
.->getParameters()
does not show the statement or the placeholders, instead the values are displayed, usually an array using keys like:dcValue1
. There is no simple solution to show the fully replaced query within the framework, but you can use getSql to see the string with placeholders, which is used as a prepared statement.
executeQuery() and executeStatement()¶
Changed in version 11.5: The widely used ->execute()
method has been split into
executeQuery()
and executeStatement()
. executeQuery()
returns a \Doctrine\DBAL\Result
instead of a
\Doctrine\DBAL\Statement
. executeStatement()
returns the
number of affected rows. The ->execute()
method has been removed with
TYPO3 v13.0.
executeQuery()¶
This method compiles and fires the final query statement. This is usually the
last call on a query builder object. It can be called for SELECT
and
COUNT
queries.
On success, it returns a result object of type \Doctrine\DBAL\Result
representing the result set. The Result
object can then be used by
fetchAssociative()
, fetchAllAssociative()
and fetchOne()
.
executeQuery()
returns a \Doctrine\DBAL\Result
and not a
\Doctrine\DBAL\Statement
anymore.
Note
It is not possible to rebind placeholder values on the result and execute
another query, as was sometimes done with the Statement
returned by
execute()
.
If the query fails for some reason (for instance, if the database connection
was lost or if the query contains a syntax error), an
\Doctrine\DBAL\Exception
is thrown. It is usually bad habit to catch and
suppress this exception, as it indicates a runtime error a program error. Both
should bubble up. For more information on proper exception handling, see the
coding guidelines.
executeStatement()¶
The executeStatement()
method can be used for INSERT
,
UPDATE
and DELETE
statements. It returns the number of affected
rows as an integer.
expr()¶
This method returns an instance of the ExpressionBuilder. It is used to create complex WHERE
query
parts and JOIN
expressions:
// use TYPO3\CMS\Core\Database\Connection;
// SELECT `uid` FROM `tt_content` WHERE (`uid` > 42)
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder
->select('uid')
->from('tt_content')
->where(
$queryBuilder->expr()->gt(
'uid',
$queryBuilder->createNamedParameter(42, Connection::PARAM_INT)
)
)
->executeQuery();
Read how to correctly instantiate a query builder with the connection pool.
Remarks:
This object is stateless and can be called and worked on as often as needed. However, it is bound to the specific connection for which a statement is created and therefore only available through the query builder, which is specific to a connection.
Never reuse the ExpressionBuilder, especially not between multiple query builder objects, but always get an instance of the expression builder by calling
->expr()
.
createNamedParameter()¶
This method creates a placeholder for a field value of a prepared statement. Always use this when dealing with user input in expressions to protect the statement from SQL injections:
// SELECT * FROM `tt_content` WHERE (`bodytext` = 'kl\'aus')
$searchWord = "kl'aus"; // $searchWord retrieved from the PSR-7 request
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder->getRestrictions()->removeAll();
$queryBuilder
->select('uid')
->from('tt_content')
->where(
$queryBuilder->expr()->eq(
'bodytext',
$queryBuilder->createNamedParameter($searchWord)
)
)
->executeQuery();
Read how to correctly instantiate a query builder with the connection pool.
The above example shows the importance of using ->createNamedParameter()
:
The search word kl'aus
is "tainted" and would break the query if not
channeled through ->createNamedParameter()
, which quotes the backtick and
makes the value SQL injection-safe.
Not convinced? Suppose the code would look like this:
// NEVER EVER DO THIS!
$_POST['searchword'] = "'foo' UNION SELECT username FROM be_users";
$searchWord = $request->getParsedBody()['searchword']);
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder->getRestrictions()->removeAll();
this fails with syntax error to prevent copy and paste
$queryBuilder
->select('uid')
->from('tt_content')
->where(
// MASSIVE SECURITY ISSUE DEMONSTRATED HERE
// USE ->createNamedParameter() ON $searchWord!
$queryBuilder->expr()->eq('bodytext', $searchWord)
);
Read how to correctly instantiate a query builder with the connection pool.
Mind the missing ->createNamedParameter()
method call in the
->eq()
expression for a given value! This code would happily execute
the statement
SELECT uid FROM `tt_content` WHERE `bodytext` = 'foo' UNION SELECT username FROM be_users;
returning a list of backend user names!
Note
->set()
automatically converts the second mandatory parameter into
a named parameter of a prepared statement. If the second parameter is
wrapped in a ->createNamedParameter()
call, this will result in an
error during execution. This behaviour can be disabled by passing
false
as third parameter to ->set()
.
More examples¶
Use integer, integer array:
// use TYPO3\CMS\Core\Database\Connection;
// SELECT * FROM `tt_content`
// WHERE `bodytext` = 'kl\'aus'
// AND sys_language_uid = 0
// AND pid in (2, 42,13333)
$searchWord = "kl'aus"; // $searchWord retrieved from the PSR-7 request
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder->getRestrictions()->removeAll();
$queryBuilder
->select('uid')
->from('tt_content')
->where(
$queryBuilder->expr()->eq(
'bodytext',
$queryBuilder->createNamedParameter($searchWord)
),
$queryBuilder->expr()->eq(
'sys_language_uid',
$queryBuilder->createNamedParameter($language, Connection::PARAM_INT)
),
$queryBuilder->expr()->in(
'pid',
$queryBuilder->createNamedParameter($pageIds, Connection::PARAM_INT_ARRAY)
)
)
->executeQuery();
Read how to correctly instantiate a query builder with the connection pool.
Rules¶
Always use
->createNamedParameter()
for any input, no matter where it comes from.The second argument of
->expr()
is always either a call to->createNamedParameter()
or->quoteIdentifier()
.The second argument of
->createNamedParameter()
specifies the type of input. For string, this can be omitted, but it is good practice to add\TYPO3\CMS\Core\Database\Connection::PARAM_INT
for integers or similar for other field types. This is not strict rule currently, but if you follow it you will have fewer headaches in the future, especially with DBMSes that are not as relaxed as MySQL when it comes to field types. TheConnection
constants can be used for simple types likebool
,string
,null
,lob
andinteger
. Additionally, the two constantsConnection::PARAM_INT_ARRAY
andConnection::PARAM_STR_ARRAY
can be used when handling an array of strings or integers, for instance in anIN()
expression.Keep the
->createNamedParameter()
method as close to the expression as possible. Do not structure your code in a way that it quotes something first and only later stuffs the already prepared names into the expression. Having->createNamedParameter()
directly within the created expression, is much less error-prone and easier to review. This is a general rule: Sanitizing input must be done as close as possible to the "sink" where a value is passed to a lower part of the framework. This paradigm should also be followed for other quote operations likehtmlspecialchars()
orGeneralUtility::quoteJSvalue()
. Sanitization should be obvious directly at the very place where it is important:
// DO
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder->getRestrictions()->removeAll();
$queryBuilder
->select('uid')
->from('tt_content')
->where(
$queryBuilder->expr()->eq(
'bodytext',
$queryBuilder->createNamedParameter($searchWord)
)
)
// DON'T DO, this is much harder to track:
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$myValue = $queryBuilder->createNamedParameter($searchWord);
// Imagine much more code here
$queryBuilder->getRestrictions()->removeAll();
$queryBuilder
->select('uid')
->from('tt_content')
->where(
$queryBuilder->expr()->eq('bodytext', $myValue)
)
Read how to correctly instantiate a query builder with the connection pool.
quoteIdentifier() and quoteIdentifiers()¶
->quoteIdentifier()
must be used when not a value but a field name is
handled. The quoting is different in those cases and typically ends up with
backticks `
instead of ticks '
:
// SELECT `uid` FROM `tt_content` WHERE (`header` = `bodytext`)
// Return list of rows where header and bodytext values are identical
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder
->select('uid')
->from('tt_content')
->where(
$queryBuilder->expr()->eq(
'header',
$queryBuilder->quoteIdentifier('bodytext')
)
);
Read how to correctly instantiate a query builder with the connection pool.
The method quotes single field names or combinations of table names or table aliases with field names:
// Single field name: `bodytext`
->quoteIdentifier('bodytext');
// Table name and field name: `tt_content`.`bodytext`
->quoteIdentifier('tt_content.bodytext')
// Table alias and field name: `foo`.`bodytext`
->from('tt_content', 'foo')->quoteIdentifier('foo.bodytext')
Remarks:
Similar to ->createNamedParameter() this method is crucial to prevent SQL injections. The same rules apply here.
The ->set() method for
UPDATE
statements expects its second argument to be a field value by default, and quotes it internally using->createNamedParameter()
. If a field should be set to the value of another field, this quoting can be turned off and an explicit call to->quoteIdentifier()
must be added.Internally,
->quoteIdentifier()
is automatically called on all method arguments that must be a field name. For instance,->quoteIdentifier()
is called for all arguments of ->select().->quoteIdentifiers()
(mind the plural) can be used to quote multiple field names at once. While that method is "public" and thus exposed as an API method, this is mostly useful internally only.
escapeLikeWildcards()¶
Helper method to quote %
characters within a search string. This is helpful in
->like()
and ->notLike()
expressions:
// SELECT `uid` FROM `tt_content` WHERE (`bodytext` LIKE '%kl\\%aus%')
$searchWord = 'kl%aus';
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder
->select('uid')
->from('tt_content')
->where(
$queryBuilder->expr()->like(
'bodytext',
$queryBuilder->createNamedParameter('%' . $queryBuilder->escapeLikeWildcards($searchWord) . '%')
)
);
Read how to correctly instantiate a query builder with the connection pool.
Warning
Even when using ->escapeLikeWildcards()
the value must be
encapsulated again in a ->createNamedParameter()
call. Only calling
->escapeLikeWildcards()
does not make the value SQL injection
safe!
getRestrictions(), setRestrictions(), resetRestrictions()¶
API
methods to deal with the RestrictionBuilder.