Attention
TYPO3 v11 has reached end-of-life as of October 31th 2024 and is no longer being maintained. Use the version switcher on the top left of this page to select documentation for a supported version of TYPO3.
Need more time before upgrading? You can purchase Extended Long Term Support (ELTS) for TYPO3 v11 here: TYPO3 ELTS.
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
conditions - Manipulate default
WHERE
restrictions added by TYPO3 for->select
() - Add
LIMIT
,GROUP BY
and other SQL functions execute
executes aQuery () SELECT
query and returns a result, a\Doctrine\
objectDBAL\ Result execute
executes anStatement () INSERT
,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
Connection
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
{
private ConnectionPool $connectionPool;
public function __construct(ConnectionPool $connectionPool)
{
$this->connectionPool = $connectionPool;
}
public function findSomething()
{
$queryBuilder = $this->connectionPool
->getQueryBuilderForTable('aTable');
}
}
Attention
Never instantiate and initialize the query builder manually using
dependency injection or General
, otherwise you
will miss essential dependencies and runtime setup.
Warning
The QueryBuilder holds internal state and must not be reused for
different queries. In addition, a reuse comes with a
significant performance penalty and memory consumption.
Use one query builder per query. Get a fresh one by calling
$connection->create
if the same table is
involved, or use $connection
for a
query to a different table. Do not 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
->add
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 useful to call select
twice in a
code flow or after an ->add
. The methods ->where
and
->and
share the same behavior: ->where
replaces all
formerly registered constraints, ->and
appends additional
constraints.
A useful combination of ->select
and ->add
can be:
$queryBuilder->select(...$defaultList);
if ($needAdditionalFields) {
$queryBuilder->addSelect(...$additionalFields);
}
Calling the execute
function on a ->select
query returns
a result object of type \Doctrine\
. To receive single rows, a
->fetch
loop is used on that object, or
->fetch
to return a single array with all rows. A typical
code flow of a SELECT
query looks like this:
// use TYPO3\CMS\Core\Database\Connection;
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$result = $queryBuilder
->select('uid', 'header', 'bodytext')
->from('tt_content')
->where(
$queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('lorem', Connection::PARAM_STR))
)
->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. See available parameter types.
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
. See the RestrictionBuilder section for details on that topic.
count()
Create a COUNT
query, a typical usage:
// use TYPO3\CMS\Core\Database\Connection;
// 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', Connection::PARAM_STR))
)
->executeQuery()
->fetchOne();
Read how to correctly instantiate a query builder with the connection pool. See available parameter types.
Remarks:
- Similar to the
->select
query type,() ->count
automatically triggers the magic of the RestrictionBuilder that adds default restrictions such as() deleted
,hidden
,starttime
andendtime
when defined in TCA. - Similar to
->select
query types,() ->execute
withQuery () ->count
returns a result object of type() \Doctrine\
. To fetch the number of rows directly, useDBAL\ Result ->fetch
.One () - The first argument to
->count
is required, typically() ->count
or(*) ->count
is used, the field name is automatically quoted.('uid') -
There is no support for
DISTINCT
, instead a->group
has to be used, for example:By () // 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');
Copied! - If
->count
is combined with() ->group
, 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, aBy () ->group
should always be combined with anBy () ->order
.By ()
delete()
Create a DELETE FROM
query. The method requires the table name from which
data is to be deleted. Classic usage:
// use TYPO3\CMS\Core\Database\Connection;
// 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', Connection::PARAM_STR))
)
->executeStatement();
Read how to correctly instantiate a query builder with the connection pool. See available parameter types.
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 add() WHERE
restrictions likeAND `deleted` = 0
. ->delete
does not magically transform a() DELETE 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
to check if a specific table can handle the soft-delete, together with an['TCA'] ['the Table'] ['ctrl'] ['delete'] ->update
instead.() - Deleting from multiple tables at once is not supported:
DELETE FROM `table1`, `table2`
can not be created. ->delete
ignores() ->join
() ->delete
ignores() set
:Max Results () DELETE
withLIMIT
does not work.
update() and set()
Create an UPDATE
query. Typical usage:
// use TYPO3\CMS\Core\Database\Connection;
// 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', Connection::PARAM_STR))
)
->set('bodytext', 'dolor')
->executeStatement();
Read how to correctly instantiate a query builder with the connection pool. See available parameter types.
->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:
// use TYPO3\CMS\Core\Database\Connection;
// 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', Connection::PARAM_STR))
)
->set('t.bodytext', 'dolor')
->executeStatement();
Read how to correctly instantiate a query builder with the connection pool. See available parameter types.
->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 ->quote
and false
have to
be used:
// use TYPO3\CMS\Core\Database\Connection;
// 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', Connection::PARAM_STR))
)
->set('bodytext', $queryBuilder->quoteIdentifier('header'), false)
->executeStatement();
Read how to correctly instantiate a query builder with the connection pool. See available parameter types.
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() ->set
.Max Results () - 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.
Remarks:
- The
uid
of the created database row can be fetched from the connection by using $queryBuilder->getConnection()->lastInsertId(). ->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 to() false
. Then quoting must be done manually, typically by using->create
on the values.Named Parameter () ->execute
afterStatement () ->insert
returns the number of inserted rows, which is typically() 1
.- An alternative to using the query builder for inserting data is using the
Connection object with its
->insert
method.() - The query builder does not provide a method for inserting multiple rows
at once, use
->bulk
of the Connection object instead to achieve that.Insert ()
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 ->get
. 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
->and
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 ->execute
or
->execute
:
// 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', Connection::PARAM_STR)),
$queryBuilder->expr()->eq('header', $queryBuilder->createNamedParameter('a name', Connection::PARAM_STR))
)
->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. See available parameter types.
Note the parenthesis of the above example: ->and
encapsulates both
->where
and ->or
with an additional restriction.
Argument unpacking can become handy with these methods:
// use TYPO3\CMS\Core\Database\Connection;
$whereExpressions = [
$queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('lorem', Connection::PARAM_STR)),
$queryBuilder->expr()->eq('header', $queryBuilder->createNamedParameter('a name', Connection::PARAM_STR))
];
if ($needsAdditionalExpression) {
$whereExpressions[] = $someAdditionalExpression;
}
$queryBuilder->where(...$whereExpressions);
See available parameter types.
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 with() AND
. - 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
,() ->and
andWhere () ->or
expressions. AWhere () ->where
call after a previous() ->where
,() ->and
orWhere () ->or
usually indicates a bug or a rather weird code flow. Doing so is discouraged.Where () - When creating complex
WHERE
restrictions,->get
andSQL () ->get
are helpful debugging tools to verify parenthesis and single query parts.Parameters () - 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:
// use TYPO3\CMS\Core\Database\Connection;
// 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, Connection::PARAM_INT))
)
->executeQuery();
Read how to correctly instantiate a query builder with the connection pool. See available parameter types.
Notes to the example above:
- The query operates with the
sys_
table as the main table, this table name is given tolanguage get
.Query Builder For Table () - 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', $query Builder->quote Identifier ('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 use() pages
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_
table obviously only specifies alanguage 'disabled' => 'hidden'
asenable
in its TCA ctrl section, while theColumns pages
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:
// use TYPO3\CMS\Core\Database\Connection;
// 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, Connection::PARAM_INT)),
$queryBuilder->expr()->eq('tt_content.pid', $queryBuilder->createNamedParameter(42, Connection::PARAM_INT)),
$queryBuilder->expr()->eq('tt_content.sys_language_uid', $queryBuilder->createNamedParameter(2, Connection::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. See available parameter types.
Further remarks:
->join
and() inner
are identical. They create anJoin INNER JOIN
query, this is identical to aJOIN
query.->left
creates aJoin () LEFT JOIN
query, this is identical to aLEFT OUTER JOIN
query.->right
creates aJoin () RIGHT 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
and() update
do not support joins, these query parts are ignored and do not end up in the final statement.() - The argument of
->get
should be the leftmost main table.Query Builder For 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 ->order
and
->add
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:
->order
resets all previously specified orders. It makes no sense to call this function again after a previousBy () ->order
orBy () ->add
.Order By () - Both methods need a field name or a
table.
or afield Name table
as first argument. In the example above the call toAlias. field Name ->order
would have been identical. All identifiers are quoted automatically.By ('sys_ language. sorting') - 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
->order
and then multipleBy () ->add
calls. The call toOrder By () ->order
createsBy ('header')->add Order By ('bodytext')->add Order By ('uid', 'DESC') ORDER BY `header` ASC, `bodytext` ASC, `uid` DESC
- To add more complex sorting you can use
->add
, remember to quote properly!('order By', 'FIELD (eventtype, 0, 4, 1, 2, 3)', true)
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:() ->group
By (...$my Group Array) - Each argument is either a direct field name
GROUP BY `bodytext`
, atable.
or afield Name table
and is properly quoted.Alias. field Name ->group
resets all previously defined group specification and should only be called once per statement.By () - For more complex statements you can use
->add
, remember to quote properly!('group By', $sql, $append)
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
->set
without callingMax Results () ->set
.First Result () - It is possible to call
->set
without callingFirst Result () set
: This is equivalent to "Fetch everything, but leave out the first n records". Internally,Max Results () LIMIT
will be added by Doctrine DBAL and set to a very high value. - Use
->set
to retrieve all results.Max Results (null)
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
,group
,By having
ororder
.By - 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 ->get
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
->execute
orQuery () ->execute
to output the final statement.Statement () - Casting a query builder object to
(string)
has the same effect as calling->get
, but the explicit call using the method should be preferred to simplify a search operation for this kind of debugging statements.SQL () - 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
->execute
orQuery () ->execute
.Statement () ->get
does not show these values, instead it displays the placeholder names, usually with a string likeSQL () :dc
. 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.Value1
getParameters()
The ->get
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
->execute
orQuery () ->execute
to output the final statement.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
->execute
orQuery () ->execute
.Statement () ->get
does not show the statement or the placeholders, instead the values are displayed, usually an array using keys likeParameters () :dc
. 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.Value1
execute(), executeQuery() and executeStatement()
Changed in version 11.5
The widely used ->execute
method has been split into:
->execute
returning aQuery () \Doctrine\
instead of aDBAL\ Result \Doctrine\
andDBAL\ Statement ->execute
returning the number of affected rows.Statement ()
Although ->execute
still works for backwards compatibility, you
should prefer to use ->execute
for SELECT
and
COUNT
statements and ->execute
for INSERT
,
UPDATE
and DELETE
queries.
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\
representing the result set. The Result
object can then be used by
fetch
, fetch
and fetch
.
execute
returns a \Doctrine\
and not a
\Doctrine\
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\
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 execute
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. See available parameter types.
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()
Note
Doctrine DBAL v4 (used with TYPO3 v13) dropped the support for using the \PDO::
constants in favor of the enum types. Be aware of this and use
\TYPO3\
, which can already be
used in TYPO3 v12 and v11 (like in the examples below).
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 = GeneralUtility::_GP('searchword');
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder->getRestrictions()->removeAll();
$queryBuilder
->select('uid')
->from('tt_content')
->where(
$queryBuilder->expr()->eq(
'bodytext',
$queryBuilder->createNamedParameter($searchWord, Connection::PARAM_STR)
)
)
->executeQuery();
Read how to correctly instantiate a query builder with the connection pool. See available parameter types.
The above example shows the importance of using ->create
:
The search word kl'aus
is "tainted" and would break the query if not
channeled through ->create
, 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 = GeneralUtility::_GP('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 ->create
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 ->create
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 = GeneralUtility::_GP('searchword');
$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)
)
)
->execute();
Read how to correctly instantiate a query builder with the connection pool. See available parameter types.
Rules
- Always use
->create
for any input, no matter where it comes from.Named Parameter () - The second argument of
->expr
is always either a call to() ->create
orNamed Parameter () ->quote
.Identifier () - The second argument of
->create
specifies the type of input. For string, this can be omitted, but it is good practice to addNamed Parameter () \TYPO3\
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. TheCMS\ Core\ Database\ Connection:: PARAM_ INT Connection
constants can be used for simple types likebool
,string
,null
,lob
andinteger
. Additionally, the two constantsConnection::
andPARAM_ INT_ ARRAY Connection::
can be used when handling an array of strings or integers, for instance in anPARAM_ STR_ ARRAY IN
expression.() - Keep the
->create
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. HavingNamed Parameter () ->create
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 likeNamed Parameter () htmlspecialchars
or() General
. Sanitization should be obvious directly at the very place where it is important:Utility:: quote JSvalue ()
// use TYPO3\CMS\Core\Database\Connection;
// DO
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder->getRestrictions()->removeAll();
$queryBuilder
->select('uid')
->from('tt_content')
->where(
$queryBuilder->expr()->eq(
'bodytext',
$queryBuilder->createNamedParameter($searchWord, Connection::PARAM_STR)
)
)
// 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. See available parameter types.
quoteIdentifier() and quoteIdentifiers()
->quote
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->create
. If a field should be set to the value of another field, this quoting can be turned off and an explicit call toNamed Parameter () ->quote
must be added.Identifier () - Internally,
->quote
is automatically called on all method arguments that must be a field name. For instance,Identifier () ->quote
is called for all arguments of ->select().Identifier () ->quote
(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.Identifiers ()
escapeLikeWildcards()
Helper method to quote %
characters within a search string. This is helpful in
->like
and ->not
expressions:
// use TYPO3\CMS\Core\Database\Connection;
// 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) . '%', Connection::PARAM_STR)
)
);
Read how to correctly instantiate a query builder with the connection pool. See available parameter types.
Warning
Even when using ->escape
the value must be
encapsulated again in a ->create
call. Only calling
->escape
does not make the value SQL injection
safe!
getRestrictions(), setRestrictions(), resetRestrictions()
API
methods to deal with the RestrictionBuilder.