Attention

TYPO3 v8 has reached its end-of-life March 31st, 2020 and is not maintained by the community anymore. Looking for a stable version? Use the version switch on the top left.

There is no further ELTS support. It is recommended that you upgrade your project and use a supported version of TYPO3.

QueryBuilder

The QueryBuilder is a rather huge class that takes care of the main query dealing.

An instance can get hold of by calling the ConnectionPool->getQueryBuilderForTable() and handing over the table. Never instantiate and initialize the QueryBuilder directly via makeInstance()!

$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('aTable');

This documentation does not mention every single available method but sticks to those used in casual queries and normal code flow. There are a couple of not mentioned methods, most of them are either very seldom used or marked as internal. Extension authors typically don't have to deal with anything not mentioned here.

Warning

From security point of view, the documentation of ->createNamedParameter() and ->quoteIdentifier() are an absolute must read and follow section. Make very sure this is understood and use this for each and every query to prevent SQL injections!

The QueryBuilder 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 stuff

• ->execute() a query and retrieve a Statement (a query result) object

Most methods of the QueryBuilder return $this and can be chained:

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

Note

The QueryBuilder holds internal state and should not be re-used for different queries: Use one query builder per query. Get a fresh one by calling $connection->createQueryBuilder() if the same table is affected, or use $connectionPool->getQueryBuilderForTable() for a query on to a different table. Don't worry, creating those object instances is rather quick.

select() and addSelect()

Create a SELECT query.

Select all fields:

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

->select() and a number of other methods of the QueryBuilder are variadic and can handle any number of arguments. For ->select(), every argument is interpreted as a single field name to select:

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

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

$fields = ['uid', 'pid', 'aField', 'anotherField'];
$queryBuilder->select(...$fields);

->select() supports AS and quotes identifiers automatically. This can become especially handy in join() operations:

// SELECT `tt_content`.`bodytext` AS `t1`.`text`
$queryBuilder->select('tt_content.bodytext AS t1.text')

->select() sets the list of fields that should be selected and ->addSelect() can add further items to an existing list.

Mind that ->select() resets any formerly registered list and does not append. Thus, it usually doesn't make much sense to call select() twice in a code flow, or to call it after an ->addSelect(). The methods ->where() and ->andWhere() share the same behavior.

A useful combination of ->select() and ->addSelect() can be:

$queryBuilder->select(...$defaultList);
if ($needAdditionalFields) {
   $queryBuilder->addSelect(...$additionalFields);
}

Calling ->execute() on a ->select() query returns a Statement object. To receive single rows a ->fetch() loop on that object is used, or ->fetchAll() to return a single array with all rows. A typical code flow of a SELECT query looks like:

$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tt_content');
$statement = $queryBuilder
   ->select('uid', 'header', 'bodytext')
   ->from('tt_content')
   ->where(
      $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('klaus'))
   )
   ->execute();
while ($row = $statement->fetch()) {
   // Do something with that single row
   debug($row);
}

Note

->select() and ->count() queries trigger TYPO3 CMS magic that adds further default where clauses if the queried table is also registered via $GLOBALS['TCA']. See the RestrictionBuilder section for details on that topic.

count()

Create a COUNT query, a typical usage:

// SELECT COUNT(`uid`) FROM `tt_content` WHERE (`bodytext` = 'klaus')
//     AND ((`tt_content`.`deleted` = 0) AND (`tt_content`.`hidden` = 0)
//     AND (`tt_content`.`starttime` <= 1475580240)
//     AND ((`tt_content`.`endtime` = 0) OR (`tt_content`.`endtime` > 1475580240)))
$count = $queryBuilder
   ->count('uid')
   ->from('tt_content')
   ->where(
      $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('klaus'))
    )
   ->execute()
   ->fetchColumn(0);

Remarks:

• Similar to the ->select() query type, ->count() automatically triggers RestrictionBuilder magic that adds default deleted, hidden, starttime and endtime restrictions if that is defined in TCA.

• Similar to ->select() query types, ->execute() with ->count() returns a Statement object. To fetch the number of rows directly, use ->fetchColumn(0).

• 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, a ->groupBy() has to be used instead.

• If combining ->count() with a ->groupBy(), the result may return multiple rows. The order of those rows depends on the used DBMS. To ensure same order of result rows on multiple different databases, a ->groupBy() should thus always be combined with a ->orderBy().

delete()

Create a DELETE FROM query. The method requires the table name to drop data from. Classic usage:

// DELETE FROM `tt_content` WHERE `bodytext` = 'klaus'
$affectedRows = $queryBuilder
   ->delete('tt_content')
   ->where(
      $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('klaus'))
   )
   ->execute();

Remarks:

• For simple cases, it is often easier to write and read if using the ->delete() method of the Connection object.

• In contrast to ->select(), ->delete() does not add WHERE restrictions like AND `deleted` = 0 automatically.

• ->delete() does not magically transform a DELETE FROM `tt_content` WHERE `uid` = 4711 to something like UPDATE `tt_content` SET `deleted` = 1 WHERE `uid` = 4711 internally. A soft-delete must be handled on application level code with a dedicated lookup in $GLOBALS['TCA']['theTable']['ctrl']['deleted'] to check if a specific table can handle the soft-delete, together with an ->update() instead.

• Multi-table delete 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()

Create an UPDATE query. Typical usage:

// UPDATE `tt_content` SET `bodytext` = 'peter' WHERE `bodytext` = 'klaus'
$queryBuilder
   ->update('tt_content')
   ->where(
      $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('klaus'))
   )
   ->set('bodytext', 'peter')
   ->execute();

->update() requires the table to update as first argument and a table alias as optional second argument. The table alias can then be used in ->set() and ->where() expressions:

// UPDATE `tt_content` `t` SET `t`.`bodytext` = 'peter' WHERE `u`.`bodytext` = 'klaus'
$queryBuilder
   ->update('tt_content', 'u')
   ->where(
      $queryBuilder->expr()->eq('u.bodytext', $queryBuilder->createNamedParameter('klaus'))
   )
   ->set('u.bodytext', 'peter')
   ->execute();

->set() requires a field name as first argument and automatically quotes it internally. The second mandatory argument is the value a field should be set to, the value is automatically transformed to a named parameter of a prepared statement. This way, ->set() key/value pairs are automatically SQL injection save by default.

If a field should be set to the value of another field from the row, the quoting needs to be turned off and ->quoteIdentifier() has to be used:

// UPDATE `tt_content` SET `bodytext` = `header` WHERE `bodytext` = 'klaus'
$queryBuilder
   ->update('tt_content')
   ->where(
      $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('klaus'))
   )
   ->set('bodytext', $queryBuilder->quoteIdentifier('header'), false)
   ->execute();

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 first argument and automatically quotes it internally.

• ->set() requires the value a field should be set to as second parameter.

• ->update() ignores ->join() and ->setMaxResults().

• The API does not magically add delete = 0 or other restrictions magically.

insert() and values()

Create an INSERT query. Typical usage:

$affectedRows = $queryBuilder
   ->insert('tt_content')
   ->values([
      'bodytext' => 'klaus',
      'header' => 'peter',
   ])
   ->execute();

Remarks:

• It is often easier to use ->insert() or ->bulkInsert() of the Connection object.

• ->values() expects an array of key/value pairs. Both keys (field names / identifiers) and values are automatically quoted. In rare cases, quoting of values can be turned off by setting the second argument to false. In those cases the quoting has to be done manually, typically by using ->createNamedParameter() on the values, use with care ...

• ->execute() after ->insert() returns the number of inserted rows, which is typically 1.

• QueryBuilder does not contain a method to insert multiple rows at once, use ->bulkInsert() of Connection object instead to achieve that.

from()

->from() is a must have call for ->select() and ->count() query types. ->from() needs a table name and an optional alias name. The method is typically called once per query build and the table name is typically the same as what was given to ->getQueryBuilderForTable(). If the query joins multiple tables, the argument should be the name of the first table within the ->join() chain:

// FROM `myTable`
$queryBuilder->from('myTable');

// FROM `myTable` AS `anAlias`
$queryBuilder->from('myTable', 'anAlias');

->from() can be called multiple times and will create the cartesian product of tables if not restricted by an according ->where() or ->andWhere() expression. In general, it is a good idea to use ->from() only once per query and model multi-table selection with an explicit ->join() instead.

where(), andWhere() and orWhere()

The three methods are used to create WHERE restrictions for SELECT, COUNT, UPDATE and DELETE query types. Each argument is typically an ExpressionBuilder object that will be cast to a string on ->execute():

// SELECT `uid`, `header`, `bodytext`
// FROM `tt_content`
// WHERE
//    (
//       ((`bodytext` = 'klaus') AND (`header` = 'a name'))
//       OR (`bodytext` = 'peter') OR (`bodytext` = 'hans')
//    )
//    AND (`pid` = 42)
//    AND ... RestrictionBuilder TCA restrictions ...
$statement = $queryBuilder
   ->select('uid', 'header', 'bodytext')
   ->from('tt_content')
   ->where(
      $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('klaus')),
      $queryBuilder->expr()->eq('header', $queryBuilder->createNamedParameter('a name'))
   )
   ->orWhere(
      $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('peter')),
      $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('hans'))
   )
   ->andWhere(
      $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter(42, \PDO::PARAM_INT))
   )
   ->execute();

Note the parenthesis of the above example: ->andWhere() encapsulates both ->where() and ->orWhere() with an additional restriction.

Argument unpacking can become handy with these methods:

$whereExpressions = [
   $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('klaus')),
   $queryBuilder->expr()->eq('header', $queryBuilder->createNamedParameter('a name'))
];
if ($needsAdditionalExpression) {
   $whereExpressions[] = $someAdditionalExpression;
}
$queryBuilder->where(...$whereExpressions);

Remarks:

• The three methods are variadic. They can handle any number of arguments. If for instance ->where() receives four arguments, they are handled as single expressions, all of them combined with AND.

• createNamedParameter is used to create a placeholder for a prepared statement field value. Always use that when dealing with user input in expressions to make the statement SQL injection safe.

• ->where() should be called only once per query and it resets any previously set ->where(), ->andWhere() and ->orWhere() expression. Having a ->where() call after a previous ->where(), ->andWhere() or ->orWhere() typically indicates a bug or a rather weird code flow. Doing so is discouraged.

• While creating complex WHERE restrictions, ->getSQL() is a helpful debugging friend to verify parenthesis and single query parts.

• If using only ->eq() expressions, it is often easier to switch to the according Connection object method to simplify quoting and increase readability.

• It is possible to feed the methods with strings directly, but that is discouraged and typically only used in rare cases where expression strings are created at a different place that can not be resolved easily. In the core, those places are usually combined with QueryHelper::stripLogicalOperatorPrefix() to remove leading AND or OR parts. Using this gives an additional risk of missing or wrong quoting and is a potential security issue. Use with care if ever.

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 left side table (or its alias), the name of the right side table, an alias for the right side table name and the join restriction as fourth argument:

// SELECT `sys_language`.`uid`, `sys_language`.`title`
// FROM `sys_language`
// INNER JOIN `pages_language_overlay` `overlay`
//     ON `overlay`.`sys_language_uid` = `sys_language`.`uid`
// WHERE
//     (`overlay`.`pid` = 42)
//     AND (
//          (`overlay`.`deleted` = 0)
//          AND (
//              (`sys_language`.`hidden` = 0) AND (`overlay`.`hidden` = 0)
//          )
//          AND (`overlay`.`starttime` <= 1475591280)
//          AND ((`overlay`.`endtime` = 0) OR (`overlay`.`endtime` > 1475591280))
//     )
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('sys_language');
$statement = $queryBuilder
   ->select('sys_language.uid', 'sys_language.title')
   ->from('sys_language')
   ->join(
      'sys_language',
      'pages_language_overlay',
      'overlay',
      $queryBuilder->expr()->eq('overlay.sys_language_uid', $queryBuilder->quoteIdentifier('sys_language.uid'))
   )
   ->where(
      $queryBuilder->expr()->eq('overlay.pid', $queryBuilder->createNamedParameter(42, \PDO::PARAM_INT))
   )
   ->execute();

Notes to the above example:

• The query operates on table sys_language as main table, this table name is given to getQueryBuilderForTable().

• The query joins table pages_language_overlay as INNER JOIN, giving it the alias overlay.

• The join condition is `overlay`.`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('overlay.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 table/field name combination). Using createNamedParameter() would lead to a quoting as value (' instead of ` in mysql) and the query would fail.

• The alias overlay - the third argument of the ->join() call - does not necessarily have to be set to a different name than the table name itself here. Using pages_language_overlay as third argument and not specifying a different name would do. Aliases are mostly useful if a join to the same table is needed: SELECT `something` FROM `tt_content` JOIN `tt_content` `content2` ON .... Aliases additionally become handy to increase readability of ->where() expressions.

• The RestrictionBuilder added additional WHERE conditions for both involved tables! Table sys_language obviously only specifies a 'disabled' => 'hidden' as enableColumns in its TCA ctrl section, while table pages_language_overlay specifies deleted, hidden, starttime and stoptime fields.

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 name of the first join target as left side:

// SELECT `tt_content_orig`.`sys_language_uid`
// FROM `tt_content`
// INNER JOIN `tt_content` `tt_content_orig` ON `tt_content`.`t3_origuid` = `tt_content_orig`.`uid`
// INNER JOIN `sys_language` `sys_language` ON `tt_content_orig`.`sys_language_uid` = `sys_language`.`uid`
// WHERE
//     (`tt_content`.`colPos` = 1)
//     AND (`tt_content`.`pid` = 42)
//     AND (`tt_content`.`sys_language_uid` = 2)
//     AND ... RestrictionBuilder TCA restrictions for tables tt_content and sys_language ...
// GROUP BY `tt_content_orig`.`sys_language_uid`
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tt_content');
$constraints = [
   $queryBuilder->expr()->eq('tt_content.colPos', $queryBuilder->createNamedParameter(1, \PDO::PARAM_INT)),
   $queryBuilder->expr()->eq('tt_content.pid', $queryBuilder->createNamedParameter(42, \PDO::PARAM_INT)),
   $queryBuilder->expr()->eq('tt_content.sys_language_uid', $queryBuilder->createNamedParameter(2, \PDO::PARAM_INT)),
];
$queryBuilder
   ->select('tt_content_orig.sys_language_uid')
   ->from('tt_content')
   ->join(
      'tt_content',
      'tt_content',
      'tt_content_orig',
      $queryBuilder->expr()->eq(
         'tt_content.t3_origuid',
         $queryBuilder->quoteIdentifier('tt_content_orig.uid')
      )
   )
   ->join(
      'tt_content_orig',
      'sys_language',
      'sys_language',
      $queryBuilder->expr()->eq(
         'tt_content_orig.sys_language_uid',
         $queryBuilder->quoteIdentifier('sys_language.uid')
      )
   )
   ->where(...$constraints)
   ->groupBy('tt_content_orig.sys_language_uid')
   ->execute();

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 on join() methods are only considered for ->select() and ->count() type queries. ->delete(), ->insert() and update() do not support joins, those query parts are ignored and do not end up in the final statement.

• The argument of ->getQueryBuilderForTable() should be the left most main table.

• A join of two tables that are configured to different connections will throw an exception. This restricts which tables can be configured to different database endpoints. It is possible to test the connection objects of involved tables for equality and implement a fallback logic in PHP if they are different.

orderBy() and addOrderBy()

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

// SELECT * FROM `sys_language` ORDER BY `sorting` ASC
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('sys_language');
$queryBuilder->getRestrictions()->removeAll();
$languageRecords = $queryBuilder
   ->select('*')
   ->from('sys_language')
   ->orderBy('sorting')
   ->execute()
   ->fetchAll();

Remarks:

• ->orderBy() resets any previously specified orders. It doesn't make sense to call it after a previous ->orderBy() or ->addOrderBy() again.

• Both methods need a field name or a table.fieldName or a tableAlias.fieldName as first argument, in the above example calling ->orderBy('sys_language.sorting') would have been identical. All identifiers are quoted automatically.

• The second, optional argument of both methods specifies the sorting order. The two allowed values are 'ASC' and 'DESC' where 'ASC' is default and can be omited.

• To create a chain of orders, use ->orderBy() and then multiple ->addOrderBy() calls. Calling ->orderBy('header')->addOrderBy('bodytext')->addOrderBy('uid', 'DESC') creates ORDER BY `header` ASC, `bodytext` ASC, `uid` DESC

groupBy() and addGroupBy()

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

// GROUP BY `pages_language_overlay`.`sys_language_uid`, `sys_language`.`uid`
->groupBy('pages_language_overlay.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 will be properly quoted.

• ->groupBy() resets any previously set group specification and should be called only once per statement.

setMaxResults() and setFirstResult()

Add LIMIT to restrict number of records and OFFSET for pagination query parts. Both methods should be called only once per statement:

// SELECT * FROM `sys_language` LIMIT 2 OFFSET 4
$queryBuilder
   ->select('*')
   ->from('sys_language')
   ->setMaxResults(2)
   ->setFirstResult(4)
   ->execute();

Remarks:

• It's allowed to call ->setMaxResults() but not to call ->setFirstResult().

• It is possible to call ->setFirstResult() without calling setMaxResults(): This equals 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.

getSQL()

Method ->getSQL() returns the created query statement as string. It is incredible useful during development to verify the final statement is executed just as a developer expects it:

$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('sys_language');
$queryBuilder->select('*')->from('sys_language');
debug($queryBuilder->getSQL());
$statement = $queryBuilder->execute();

Remarks:

• This is debugging code. Take proper actions to ensure those calls do not end up in production!

• The method is typically called directly in front ->execute() to output the final statement.

• Casting a QueryBuilder object to (string) has the same effect as calling ->getSQL(), the explicit call using the method should be preferred to simplify a search operation for this kind of debugging statements, though.

• The method is a simple way to see which restrictions the RestrictionBuilder added.

• doctrine-dbal always creates prepared statements: Any value that added via ->createNamedParameter() creates a placeholder that is later substituted if the real query is fired via ->execute(). ->getSQL() does not show those values, instead the placeholder names are displayed, usually with a string like :dcValue1. There is no simple solution to show the fully replaced query from within the framework.

execute()

Compile and fire the final query statement. This is usually the last call on a QueryBuilder object. The method has two possible return values: On success, it either returns a Statement object representing the result set of ->select() and ->count() queries, or it returns an integer representing the number of affected rows for ->insert(), ->update() and ->delete() queries.

If the query fails for whatever reason (for instance if the database connection was lost or if the query contains a syntax error), a \Doctrine\DBAL\DBALException is thrown. It is most often bad habit to catch and suppress this exception since it indicates a runtime or a program error. Both should bubble up. See the coding guidelines for more information on proper exception handling.

expr()

Return an instance of the ExpressionBuilder. This object is used to create complex WHERE query parts and JOIN expressions:

// SELECT `uid` FROM `tt_content` WHERE (`uid` > 42)
$queryBuilder
   ->select('uid')
   ->from('tt_content')
   ->where(
      $queryBuilder->expr()->gt('uid', $queryBuilder->createNamedParameter(42, \PDO::PARAM_INT))
   )
   ->execute();

Remarks:

• This object is stateless and can be called and worked on as often as needed. It however bound to the specific connection a statement is created for and is thus only available through the QueryBuilder which is specific for one connection, too.

• Never re-use the ExpressionBuilder, especially not between multiple QueryBuilder objects, always get an instance of the ExpressionBuilder by calling ->expr().

createNamedParameter()

Create a placeholder for a prepared statement field value. Always use that when dealing with user input in expressions to make the statement SQL injection safe:

// SELECT * FROM `tt_content` WHERE (`bodytext` = 'kl\'aus')
$searchWord = "kl'aus"; // $searchWord = GeneralUtility::_GP('searchword');
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tt_content');
$queryBuilder->getRestrictions()->removeAll();
$queryBuilder
   ->select('uid')
   ->from('tt_content')
   ->where(
      $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter($searchWord))
   )
   ->execute();

The above example shows the importance of using ->createNamedParameter(): The search word kl'aus is "tainted" and would break the query if not channeled through ->createNamedParameter() which quotes the backtick and makes the value SQL injection safe.

Not convinced? Suppose the code would look like this:

// NEVER EVER DO THIS!
$_POST['searchword'] = "'foo' UNION SELECT username FROM be_users";
$searchWord = GeneralUtility::_GP('searchword');
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->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)
    );

Mind the missing ->createNamedParameter() in the ->eq() expression on 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 transforms the second mandatory parameter into a named parameter of a prepared statement. Wrapping the second parameter in a ->createNamedParameter() call will result in an error upon execution. This behaviour can be disabled by passing false as a third parameter to ->set().

More examples

Use integer, integer array:

// use TYPO3\CMS\Core\Utility\GeneralUtility;
// use TYPO3\CMS\Core\Database\ConnectionPool;
// 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 = GeneralUtility::makeInstance(ConnectionPool::class)->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, \PDO::PARAM_INT)),
        $queryBuilder->expr()->in('pid', $queryBuilder->createNamedParameter($pageIds, Connection::PARAM_INT_ARRAY))
    )
    ->execute();

Rules:

• Always use ->createNamedParameter() around 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 \PDO::PARAM_INT for integers or similar for other field types. This is currently no strict rule, but following this will reduces headaches in the future, especially for DBMS that are not as relaxed as mysql when it comes to field types. The PDO 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 if an array of strings or integers is handled, for instance in an IN() expression.

• Keep the ->createNamedParameter() as close as possible to the expression. Do not structure your code in a way that it first quotes something 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 as close as possible to the "sink" where a value is submitted to a lower part of the framework. This paradigm should be followed for other quote operations like htmlspecialchars() or GeneralUtility::quoteJSvalue(), too. Sanitizing should be directly obvious at the very place where it is important:

  // DO
  $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tt_content');
  $queryBuilder->getRestrictions()->removeAll();
  $queryBuilder
     ->select('uid')
     ->from('tt_content')
     ->where(
         $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter($searchWord))
     )
  
  // DON'T DO, this is much harder to track:
  $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->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)
     )
  

quoteIdentifier() and quoteIdentifiers()

->quoteIdentifier() must be used if not a value is handled, but a field name. 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 = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tt_content');
$queryBuilder
   ->select('uid')
   ->from('tt_content')
   ->where(
      $queryBuilder->expr()->eq('header', $queryBuilder->quoteIdentifier('bodytext'))
   );

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.

• Method ->set() for UPDATE statements expects their second argument to be a field value by default and quotes them using ->createNamedParameter() internally. In case 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 on all arguments given to ->select().

• ->quoteIdentifiers() (mind the plural) can be used to quote multiple field names at once. While that method is 'public` and thus exposed as API method, this is mostly useful internally only.

escapeLikeWildcards()

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

// SELECT `uid` FROM `tt_content` WHERE (`bodytext` LIKE '%kl\\%aus%')
$searchWord = 'kl%aus';
$queryBuilder
   ->select('uid')
   ->from('tt_content')
   ->where(
      $queryBuilder->expr()->like(
         'bodytext',
         $queryBuilder->createNamedParameter('%' . $queryBuilder->escapeLikeWildcards($searchWord) . '%')
      )
   );

Warning

Even with using ->escapeLikeWildcards(), the value must again be encapsulated in a ->createNamedParameter() call. Only calling ->escapeLikeWildcards() does not make the value SQL injection safe!

getRestrictions(), setRestrictions(), resetRestrictions()

API methods to deal with the RestrictionBuilder.