Task #93305

Long lists of parameters in QueryBuilder handled differently

Added by Sybille Peters about 1 month ago.

Status:
New
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:
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

Related to TYPO3 Core - Bug #92493: linkvalidator: scheduler task + list of broken links dies if too many pagesUnder Review2020-10-06

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

Actions
#1

Updated by Sybille Peters about 1 month ago

  • Related to Bug #92493: linkvalidator: scheduler task + list of broken links dies if too many pages added
#2

Updated by Sybille Peters about 1 month ago

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

Also available in: Atom PDF