---
title: "Query builder"
manual: "TYPO3 Explained"
version: "11.5"
permalink: "https://docs.typo3.org/permalink/t3coreapi:database-query-builder@11.5"
source: "ApiOverview/Database/QueryBuilder/Index.rst"
rendered: "2026-10-21T15:30:00+00:00"
---

> [!WARNING]
> **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](https://typo3.com/services/extended-support-elts).

# Query builder {#database-query-builder}

**Table of Contents**

-   [Instantiation](https://docs.typo3.org/permalink/t3coreapi:instantiation@11.5)
-   [select() and addSelect()](https://docs.typo3.org/permalink/t3coreapi:select-and-addselect@11.5)
-   [count()](https://docs.typo3.org/permalink/t3coreapi:count@11.5)
-   [delete()](https://docs.typo3.org/permalink/t3coreapi:delete@11.5)
-   [update() and set()](https://docs.typo3.org/permalink/t3coreapi:update-and-set@11.5)
-   [insert() and values()](https://docs.typo3.org/permalink/t3coreapi:insert-and-values@11.5)
-   [from()](https://docs.typo3.org/permalink/t3coreapi:from@11.5)
-   [where(), andWhere() and orWhere()](https://docs.typo3.org/permalink/t3coreapi:where-andwhere-and-orwhere@11.5)
-   [join(), innerJoin(), rightJoin() and leftJoin()](https://docs.typo3.org/permalink/t3coreapi:join-innerjoin-rightjoin-and-leftjoin@11.5)
-   [orderBy() and addOrderBy()](https://docs.typo3.org/permalink/t3coreapi:orderby-and-addorderby@11.5)
-   [groupBy() and addGroupBy()](https://docs.typo3.org/permalink/t3coreapi:groupby-and-addgroupby@11.5)
-   [setMaxResults() and setFirstResult()](https://docs.typo3.org/permalink/t3coreapi:setmaxresults-and-setfirstresult@11.5)
-   [add()](https://docs.typo3.org/permalink/t3coreapi:add@11.5)
-   [getSQL()](https://docs.typo3.org/permalink/t3coreapi:getsql@11.5)
-   [getParameters()](https://docs.typo3.org/permalink/t3coreapi:getparameters@11.5)
-   [execute(), executeQuery() and executeStatement()](https://docs.typo3.org/permalink/t3coreapi:execute-executequery-and-executestatement@11.5)
-   [expr()](https://docs.typo3.org/permalink/t3coreapi:expr@11.5)
-   [createNamedParameter()](https://docs.typo3.org/permalink/t3coreapi:createnamedparameter@11.5)
-   [quoteIdentifier() and quoteIdentifiers()](https://docs.typo3.org/permalink/t3coreapi:quoteidentifier-and-quoteidentifiers@11.5)
-   [escapeLikeWildcards()](https://docs.typo3.org/permalink/t3coreapi:escapelikewildcards@11.5)
-   [getRestrictions(), setRestrictions(), resetRestrictions()](https://docs.typo3.org/permalink/t3coreapi:getrestrictions-setrestrictions-resetrestrictions@11.5)

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()](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-create-named-parameter@11.5)
> and [->quoteIdentifier()](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-quote-identifier@11.5) 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
-   `executeQuery()` executes a `SELECT` query and returns a result,
    a `\Doctrine\DBAL\Result` object
-   `executeStatement()` executes an `INSERT`, `UPDATE` or
    `DELETE` 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:

```php
$queryBuilder
    ->select('uid')
    ->from('pages');
```

## Instantiation {#database-query-builder-instantiation}

To create an instance of the query builder, call
`ConnectionPool::getQueryBuilderForTable()` and pass the table as an
argument. The [ConnectionPool](https://docs.typo3.org/permalink/t3coreapi:database-connection-pool@11.5) object can be
injected via [dependency injection](https://docs.typo3.org/permalink/t3coreapi:dependencyinjection@11.5).

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
<?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');
    }
}

```

> [!WARNING]
> **Attention**
>
> Never instantiate and initialize the query builder manually using
> dependency injection or `GeneralUtility::makeInstance()`, 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](https://www.derhansen.de/2023/10/the-pitfalls-of-reusing-typo3-querybuilder-analyzing-a-performance-bottleneck.html).
> **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. Do not worry, creating those object instances
> is quite fast.

## select() and addSelect() {#database-query-builder-select}

Create a `SELECT` query.

Select all fields:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// SELECT *
$queryBuilder->select('*')
```

`->select()` and a number of other methods of the query builder
are [variadic](https://en.wikipedia.org/wiki/Variadic_function)
and can handle any number of arguments. In `->select()` each argument
is interpreted as a single field name to be selected:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// SELECT `uid`, `pid`, `aField`
$queryBuilder->select('uid', 'pid', 'aField');
```

Argument unpacking can be used if the list of fields already is available as
array:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// 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:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// 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 useful 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:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
$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:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// 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](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
a query builder with the connection pool.
See available [parameter types](https://docs.typo3.org/permalink/t3coreapi:database-connection-parameter-types@11.5).

### Default Restrictions {#database-query-builder-select-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](https://docs.typo3.org/permalink/t3coreapi:database-restriction-builder@11.5) section for details on that topic.

## count() {#database-query-builder-count}

Create a `COUNT` query, a typical usage:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// 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](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
a query builder with the connection pool.
See available [parameter types](https://docs.typo3.org/permalink/t3coreapi:database-connection-parameter-types@11.5).

Remarks:

-   Similar to the `->select()` query type, `->count()` automatically
    triggers the magic of the [RestrictionBuilder](https://docs.typo3.org/permalink/t3coreapi:database-restriction-builder@11.5) that adds default restrictions such as
    `deleted`, `hidden`, `starttime` and `endtime` 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:

    ```php
    // 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() {#delete}

Create a `DELETE FROM` query. The method requires the table name from which
data is to be deleted. Classic usage:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// 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](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
a query builder with the connection pool.
See available [parameter types](https://docs.typo3.org/permalink/t3coreapi:database-connection-parameter-types@11.5).

Remarks:

-   For simple cases it is often easier to write and read using the
    `->delete()` method of the [Connection](https://docs.typo3.org/permalink/t3coreapi:database-connection@11.5)
    object.
-   In contrast to `->select()`, `->delete()` does **not**
    automatically add `WHERE` restrictions like `AND `deleted` = 0`.
-   `->delete()` does **not** magically transform a
    `DELETE FROM `tt_content` WHERE `uid` = 4711` into something like
    `UPDATE `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()` ignores `setMaxResults()`: `DELETE` with
    `LIMIT` does not work.

## update() and set() {#database-query-builder-update-set}

Create an `UPDATE` query. Typical usage:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// 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](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
a query builder with the connection pool.
See available [parameter types](https://docs.typo3.org/permalink/t3coreapi:database-connection-parameter-types@11.5).

`->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:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// 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](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
a query builder with the connection pool.
See available [parameter types](https://docs.typo3.org/permalink/t3coreapi:database-connection-parameter-types@11.5).

`->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:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// 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](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
a query builder with the connection pool.
See available [parameter types](https://docs.typo3.org/permalink/t3coreapi:database-connection-parameter-types@11.5).

Remarks:

-   For simple cases it is often easier to use the `->update()` method of
    the [Connection](https://docs.typo3.org/permalink/t3coreapi:database-connection@11.5) 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](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-select-restrictions@11.5), for example.
    (See also [RestrictionBuilder](https://docs.typo3.org/permalink/t3coreapi:database-restriction-builder@11.5)).

## insert() and values() {#insert-and-values}

Create an `INSERT` query. Typical usage:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// 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](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
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()](https://docs.typo3.org/permalink/t3coreapi:database-connection-last-insert-id@11.5).
-   `->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
    `->createNamedParameter()` on the values.
-   `->executeStatement()` after `->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](https://docs.typo3.org/permalink/t3coreapi:database-connection@11.5) object with its `->insert()`
    method.
-   The query builder does **not provide** a method for inserting multiple rows
    at once, use `->bulkInsert()` of the [Connection](https://docs.typo3.org/permalink/t3coreapi:database-connection@11.5)
    object instead to achieve that.

## from() {#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:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// 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() {#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](https://docs.typo3.org/permalink/t3coreapi:database-expression-builder@11.5) object that
is converted to a string on `->executeQuery()` or
`->executeStatement()`:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// 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](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
a query builder with the connection pool.
See available [parameter types](https://docs.typo3.org/permalink/t3coreapi:database-connection-parameter-types@11.5).

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:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// 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](https://docs.typo3.org/permalink/t3coreapi:database-connection-parameter-types@11.5).

Remarks:

-   The three methods are [variadic](https://en.wikipedia.org/wiki/Variadic_function).
    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](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-create-named-parameter@11.5)
    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](https://docs.typo3.org/permalink/t3coreapi:database-connection@11.5) 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() {#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:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// 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](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
a query builder with the connection pool.
See available [parameter types](https://docs.typo3.org/permalink/t3coreapi:database-connection-parameter-types@11.5).

Notes to the example above:

-   The query operates with the `sys_language` table as the main table,
    this table name is given to `getQueryBuilderForTable()`.
-   The query joins the `pages` table as `INNER JOIN` and gives it the
    alias `p`.
-   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](https://docs.typo3.org/permalink/t3coreapi:database-expression-builder@11.5) to quote the value as
    a field identifier (a field name, here a combination of table and field
    name). Using [createNamedParameter](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-create-named-parameter@11.5)
    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](https://docs.typo3.org/permalink/t3coreapi:database-restriction-builder@11.5) has added
    additional `WHERE` conditions for both tables involved! The
    `sys_language` table obviously only specifies a
    `'disabled' => 'hidden'` as `enableColumns` in its
    [TCA ctrl](https://docs.typo3.org/m/typo3/reference-tca/11.5/en-us/Ctrl/Index.html#ctrl) section, while the `pages` table
    specifies the fields `deleted`, `hidden`, `starttime` and
    `stoptime`.

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:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// 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](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
a query builder with the connection pool.
See available [parameter types](https://docs.typo3.org/permalink/t3coreapi:database-connection-parameter-types@11.5).

Further remarks:

-   `->join()` and `innerJoin` are identical. They create an
    `INNER JOIN` query, this is identical to a `JOIN` query.
-   `->leftJoin()` creates a `LEFT JOIN` query, this is identical to
    a `LEFT OUTER JOIN` query.
-   `->rightJoin()` creates a `RIGHT JOIN` query, this is identical to
    a `RIGHT 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 `->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() {#orderby-and-addorderby}

Add `ORDER BY` to a `->select()` statement. Both `->orderBy()` and
`->addOrderBy()` require a field name as first argument:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// 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](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
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 a
    `tableAlias.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')`
    creates `ORDER 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() {#groupby-and-addgroupby}

Add `GROUP BY` to a `->select()` statement. Each argument of the
methods is a single identifier:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// 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``,
    a `table.fieldName` or a `tableAlias.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() {#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:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// 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](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
a query builder with the connection pool.

Remarks:

-   It is allowed to call `->setMaxResults()` without calling
    `->setFirstResult()`.
-   It is possible to call `->setFirstResult()` without calling
    `setMaxResults()`: 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.
-   Use `->setMaxResults(null)` to retrieve all results.

## add() {#database-query-builder-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:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
$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](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
a query builder with the connection pool.

Remarks:

-   The first argument is the SQL part. One of: `select`, `from`,
    `set`, `where`, `groupBy`, `having` or `orderBy`.
-   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() {#database-query-builder-get-sql}

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:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_language');
$queryBuilder
    ->select('*')
    ->from('sys_language');
debug($queryBuilder->getSQL());
$result = $queryBuilder->executeQuery();
```

Read [how to correctly instantiate](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
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](https://docs.typo3.org/permalink/t3coreapi:database-restriction-builder@11.5) has added.
-   Doctrine DBAL always creates prepared statements: Each value added via
    [createNamedParameter](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-create-named-parameter@11.5)
    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](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-get-parameters@11.5) 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() {#database-query-builder-get-parameters}

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:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_language');
$queryBuilder
    ->select('*')
    ->from('sys_language');
debug($queryBuilder->getParameters());
$statement = $queryBuilder->executeQuery();
```

Read [how to correctly instantiate](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
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](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-create-named-parameter@11.5)
    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](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-get-sql@11.5) to see the string with placeholders, which
    is used as a prepared statement.

## execute(), executeQuery() and executeStatement() {#database-query-builder-execute}

<!-- TODO: no Markdown rendering for "versionchanged" -->

The widely used ->execute() method has been split into:->executeQuery() returning a \Doctrine\DBAL\Result instead of
a \Doctrine\DBAL\Statement and->executeStatement() returning the number of affected rows.Although ->execute() still works for backwards compatibility, you
should prefer to use ->executeQuery() for SELECT and
COUNT statements and ->executeStatement() for INSERT,
UPDATE and DELETE queries.

### executeQuery() {#database-query-builder-execute-query}

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](https://docs.typo3.org/permalink/t3coreapi:cgl-working-with-exceptions@11.5).

### executeStatement() {#database-query-builder-execute-statement}

The `executeStatement()` method can be used for `INSERT`,
`UPDATE` and `DELETE` statements. It returns the number of affected
rows as an integer.

## expr() {#expr}

This method returns an instance of the [ExpressionBuilder](https://docs.typo3.org/permalink/t3coreapi:database-expression-builder@11.5). It is used to create complex `WHERE` query
parts and `JOIN` expressions:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// 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](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
a query builder with the connection pool.
See available [parameter types](https://docs.typo3.org/permalink/t3coreapi:database-connection-parameter-types@11.5).

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](https://docs.typo3.org/permalink/t3coreapi:database-expression-builder@11.5),
    especially not between multiple query builder objects, but always get an
    instance of the expression builder by calling `->expr()`.

## createNamedParameter() {#database-query-builder-create-named-parameter}

> [!NOTE]
> Doctrine DBAL v4 (used with TYPO3 v13) dropped the support for using the `\PDO::PARAM_*`
> constants in favor of the enum types. Be aware of this and use
> `\TYPO3\CMS\Core\Database\Connection::PARAM_*`, 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:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// 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](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
a query builder with the connection pool.
See available [parameter types](https://docs.typo3.org/permalink/t3coreapi:database-connection-parameter-types@11.5).

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:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// 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](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
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 {#more-examples}

Use integer, integer array:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// 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](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
a query builder with the connection pool.
See available [parameter types](https://docs.typo3.org/permalink/t3coreapi:database-connection-parameter-types@11.5).

### Rules {#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
    `TYPO3CMSCoreDatabaseConnection::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. The `Connection` constants can be used for simple
    types like `bool`, `string`, `null`, `lob` and `integer`. Additionally, the
    two constants `Connection::PARAM_INT_ARRAY` and `Connection::PARAM_STR_ARRAY`
    can be used when handling an array of strings or integers, for instance in
    an `IN()` 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 like `htmlspecialchars()`
    or `GeneralUtility::quoteJSvalue()`. Sanitization should be obvious
    directly at the very place where it is important:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// 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](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
a query builder with the connection pool.
See available [parameter types](https://docs.typo3.org/permalink/t3coreapi:database-connection-parameter-types@11.5).

## quoteIdentifier() and quoteIdentifiers() {#database-query-builder-quote-identifier}

`->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 `'`:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// 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](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
a query builder with the connection pool.

The method quotes single field names or combinations of table names or table
aliases with field names:

**Some quote examples**

```none
// 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()](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-create-named-parameter@11.5) this method is crucial to
    prevent SQL injections. The same rules apply here.
-   The [->set()](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-update-set@11.5) 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()](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-select@11.5).
-   `->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() {#database-query-builder-escape-like-wildcards}

Helper method to quote `%` characters within a search string. This is helpful in
`->like()` and `->notLike()` expressions:

**EXT:my_extension/Classes/Domain/Repository/MyRepository.php**

```php
// 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](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
a query builder with the connection pool.
See available [parameter types](https://docs.typo3.org/permalink/t3coreapi:database-connection-parameter-types@11.5).

> [!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() {#getrestrictions-setrestrictions-resetrestrictions}

`API` methods to deal with the [RestrictionBuilder](https://docs.typo3.org/permalink/t3coreapi:database-restriction-builder@11.5).