Project

General

Profile

Actions

Task #93305

closed

Long lists of parameters in QueryBuilder handled differently ("too many placeholders")

Added by Sybille Peters over 3 years ago. Updated 6 days ago.

Status:
Closed
Priority:
Should have
Assignee:
-
Category:
Code Cleanup
Target version:
-
Start date:
2021-01-18
Due date:
% Done:

0%

Estimated time:
TYPO3 Version:
11
PHP Version:
Tags:
large-site, placeholderlimit, db, prepared statement
Complexity:
Sprint Focus:

Description

To prevent "too many placeholders in prepared statements" there are currently 2 different solutions for lists of pids.

IMHO, there is no security risk in not using prepared statements here (as we have a fixed list of integers), so solution 1 would be less complex and more readable.

It might make sense to do this consistently.

1. Just use array of pids (as int) without prepared statement:

in TYPO3\CMS\Lowlevel\Command\OrphanedRecordsCommand:

$result = $queryBuilder
    ->select('uid')
    ->from($tableName)
    ->where(
        $queryBuilder->expr()->notIn(
            'uid',
            // do not use named parameter here as the list can get too long
            array_map('intval', $idList)
        )
    )
->orderBy('uid')
->execute();

2. Use array_chunk to split up the pids (or other parameters) into chunks

e.g. TYPO3\CMS\Core\Database\RelationHandler

foreach (array_chunk(current($this->tableArray), $maxBindParameters - 10, true) as $chunk) {
    if (empty($chunk)) {
        continue;
    }
    $this->itemArray = [];
    $this->tableArray = [];
    $queryBuilder = $connection->createQueryBuilder();
    $queryBuilder->getRestrictions()->removeAll();
    $queryBuilder->select('uid')
        ->from($table)
        ->where(
        $queryBuilder->expr()->in(
            'uid',
            $queryBuilder->createNamedParameter($chunk, Connection::PARAM_INT_ARRAY)
        )
    );


Search for expr()->in or expr()->notIn (or array_chunk) to find more of these occurances.


Related issues 6 (2 open4 closed)

Related to TYPO3 Core - Bug #92493: linkvalidator: scheduler task + list of broken links dies if too many pages ("Prepared statement contains too many placeholders")Closed2020-10-06

Actions
Related to TYPO3 Core - Bug #81555: Do not use prepared statements for reference index queriesClosedSascha Egerer2017-06-12

Actions
Related to TYPO3 Core - Bug #86859: Search with indexed_search plugin throws exception: Prepared statement contains too many placeholders Resolved2018-11-05

Actions
Related to TYPO3 Core - Epic #93547: Collection of problems with large sitesAccepted2021-02-19

Actions
Related to TYPO3 Core - Bug #95812: Record Statistics runs into an error when reaching it's limitsNew2021-10-28

Actions
Related to TYPO3 Core - Bug #99326: DataHandler - Process too many recordsResolved2022-12-09

Actions
Actions #1

Updated by Sybille Peters over 3 years ago

  • Related to Bug #92493: linkvalidator: scheduler task + list of broken links dies if too many pages ("Prepared statement contains too many placeholders") added
Actions #2

Updated by Sybille Peters over 3 years ago

  • Related to Bug #81555: Do not use prepared statements for reference index queries added
Actions #3

Updated by Sybille Peters over 3 years ago

  • Subject changed from Long lists of parameters in QueryBuilder handled differently to Long lists of parameters in QueryBuilder handled differently ("too many placeholders")
Actions #4

Updated by Sybille Peters over 3 years ago

  • Related to Bug #86859: Search with indexed_search plugin throws exception: Prepared statement contains too many placeholders added
Actions #5

Updated by Sybille Peters over 3 years ago

  • Related to Epic #93547: Collection of problems with large sites added
Actions #6

Updated by Sybille Peters over 2 years ago

  • Related to Bug #95812: Record Statistics runs into an error when reaching it's limits added
Actions #7

Updated by Sybille Peters over 2 years ago

  • Tags set to large-site, placeholderlimit, db, prepared statement
Actions #8

Updated by Guillaume Germain over 1 year ago

  • Related to Bug #99326: DataHandler - Process too many records added
Actions #9

Updated by Sybille Peters 6 days ago

  • Status changed from New to Closed

Closing. There are some solutions for handling of many placeholders (e.g. array_chunk). The solutions may be revisited at some time in future because of CTE, but there is no reason at the moment to keep this issue open.

Actions

Also available in: Atom PDF