[BUGFIX] Prevent crash in DatabaseIntegrityCheck
[Packages/TYPO3.CMS.git] / typo3 / sysext / core / Classes / Integrity / DatabaseIntegrityCheck.php
1 <?php
2 namespace TYPO3\CMS\Core\Integrity;
3
4 /*
5 * This file is part of the TYPO3 CMS project.
6 *
7 * It is free software; you can redistribute it and/or modify it under
8 * the terms of the GNU General Public License, either version 2
9 * of the License, or any later version.
10 *
11 * For the full copyright and license information, please read the
12 * LICENSE.txt file that was distributed with this source code.
13 *
14 * The TYPO3 project - inspiring people to share!
15 */
16
17 use Doctrine\DBAL\Types\Type;
18 use TYPO3\CMS\Backend\Utility\BackendUtility;
19 use TYPO3\CMS\Core\Core\Environment;
20 use TYPO3\CMS\Core\Database\Connection;
21 use TYPO3\CMS\Core\Database\ConnectionPool;
22 use TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder;
23 use TYPO3\CMS\Core\Database\Query\Restriction\DeletedRestriction;
24 use TYPO3\CMS\Core\Database\RelationHandler;
25 use TYPO3\CMS\Core\Utility\GeneralUtility;
26 use TYPO3\CMS\Core\Utility\PathUtility;
27
28 /**
29 * This class holds functions used by the TYPO3 backend to check the integrity of the database (The DBint module, 'lowlevel' extension)
30 *
31 * Depends on \TYPO3\CMS\Core\Database\RelationHandler
32 *
33 * @todo Need to really extend this class when the DataHandler library has been updated and the whole API is better defined. There are some known bugs in this library. Further it would be nice with a facility to not only analyze but also clean up!
34 * @see \TYPO3\CMS\Lowlevel\Controller\DatabaseIntegrityController::func_relations(), \TYPO3\CMS\Lowlevel\Controller\DatabaseIntegrityController::func_records()
35 */
36 class DatabaseIntegrityCheck
37 {
38 /**
39 * @var bool If set, genTree() includes deleted pages. This is default.
40 */
41 public $genTree_includeDeleted = true;
42
43 /**
44 * @var bool If set, genTree() includes versionized pages/records. This is default.
45 */
46 public $genTree_includeVersions = true;
47
48 /**
49 * @var bool If set, genTree() includes records from pages.
50 */
51 public $genTree_includeRecords = false;
52
53 /**
54 * @var array Will hold id/rec pairs from genTree()
55 */
56 public $page_idArray = [];
57
58 /**
59 * @var array Will hold id/rec pairs from genTree() that are not default language
60 */
61 protected $page_translatedPageIDArray = [];
62
63 /**
64 * @var array
65 */
66 public $rec_idArray = [];
67
68 /**
69 * @var array
70 */
71 public $checkFileRefs = [];
72
73 /**
74 * @var array From the select-fields
75 */
76 public $checkSelectDBRefs = [];
77
78 /**
79 * @var array From the group-fields
80 */
81 public $checkGroupDBRefs = [];
82
83 /**
84 * @var array Statistics
85 */
86 public $recStats = [
87 'allValid' => [],
88 'published_versions' => [],
89 'deleted' => []
90 ];
91
92 /**
93 * @var array
94 */
95 public $lRecords = [];
96
97 /**
98 * @var string
99 */
100 public $lostPagesList = '';
101
102 /**
103 * DatabaseIntegrityCheck constructor.
104 */
105 public function __construct()
106 {
107 trigger_error('TYPO3\CMS\Core\Integrity\DatabaseIntegrityCheck will be removed in TYPO3 v10.0, use TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck instead.', E_USER_DEPRECATED);
108 }
109
110 /**
111 * @return array
112 */
113 public function getPageTranslatedPageIDArray(): array
114 {
115 return $this->page_translatedPageIDArray;
116 }
117
118 /**
119 * Generates a list of Page-uid's that corresponds to the tables in the tree.
120 * This list should ideally include all records in the pages-table.
121 *
122 * @param int $theID a pid (page-record id) from which to start making the tree
123 * @param string $depthData HTML-code (image-tags) used when this function calls itself recursively.
124 * @param bool $versions Internal variable, don't set from outside!
125 */
126 public function genTree($theID, $depthData = '', $versions = false)
127 {
128 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('pages');
129 $queryBuilder->getRestrictions()->removeAll();
130 if (!$this->genTree_includeDeleted) {
131 $queryBuilder->getRestrictions()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
132 }
133 $queryBuilder->select('uid', 'title', 'doktype', 'deleted', 'hidden', 'sys_language_uid')
134 ->from('pages')
135 ->orderBy('sorting');
136 if ($versions) {
137 $queryBuilder->addSelect('t3ver_wsid', 't3ver_id', 't3ver_count');
138 $queryBuilder->where(
139 $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter(-1, \PDO::PARAM_INT)),
140 $queryBuilder->expr()->eq('t3ver_oid', $queryBuilder->createNamedParameter($theID, \PDO::PARAM_INT))
141 );
142 } else {
143 $queryBuilder->where(
144 $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter($theID, \PDO::PARAM_INT))
145 );
146 }
147 $result = $queryBuilder->execute();
148 // Traverse the records selected
149 while ($row = $result->fetch()) {
150 $newID = $row['uid'];
151 // Register various data for this item:
152 if ($row['sys_language_uid'] === 0) {
153 $this->page_idArray[$newID] = $row;
154 } else {
155 $this->page_translatedPageIDArray[$newID] = $row;
156 }
157 $this->recStats['all_valid']['pages'][$newID] = $newID;
158 if ($row['deleted']) {
159 $this->recStats['deleted']['pages'][$newID] = $newID;
160 }
161 if ($versions && $row['t3ver_count'] >= 1) {
162 $this->recStats['published_versions']['pages'][$newID] = $newID;
163 }
164 if ($row['deleted']) {
165 $this->recStats['deleted']++;
166 }
167 if ($row['hidden']) {
168 $this->recStats['hidden']++;
169 }
170 $this->recStats['doktype'][$row['doktype']]++;
171 // If all records should be shown, do so:
172 if ($this->genTree_includeRecords) {
173 foreach ($GLOBALS['TCA'] as $tableName => $cfg) {
174 if ($tableName !== 'pages') {
175 $this->genTree_records($newID, '', $tableName);
176 }
177 }
178 }
179 // Add sub pages:
180 $this->genTree($newID);
181 // If versions are included in the tree, add those now:
182 if ($this->genTree_includeVersions) {
183 $this->genTree($newID, '', true);
184 }
185 }
186 }
187
188 /**
189 * @param int $theID a pid (page-record id) from which to start making the tree
190 * @param string $_ Unused parameter
191 * @param string $table Table to get the records from
192 * @param bool $versions Internal variable, don't set from outside!
193 */
194 public function genTree_records($theID, $_ = '', $table = '', $versions = false)
195 {
196 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
197 $queryBuilder->getRestrictions()->removeAll();
198 if (!$this->genTree_includeDeleted) {
199 $queryBuilder->getRestrictions()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
200 }
201 $queryBuilder
202 ->select(...explode(',', BackendUtility::getCommonSelectFields($table)))
203 ->from($table);
204
205 // Select all records from table pointing to this page
206 if ($versions) {
207 $queryBuilder->where(
208 $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter(-1, \PDO::PARAM_INT)),
209 $queryBuilder->expr()->eq('t3ver_oid', $queryBuilder->createNamedParameter($theID, \PDO::PARAM_INT))
210 );
211 } else {
212 $queryBuilder->where(
213 $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter($theID, \PDO::PARAM_INT))
214 );
215 }
216 $queryResult = $queryBuilder->execute();
217 // Traverse selected
218 while ($row = $queryResult->fetch()) {
219 $newID = $row['uid'];
220 // Register various data for this item:
221 $this->rec_idArray[$table][$newID] = $row;
222 $this->recStats['all_valid'][$table][$newID] = $newID;
223 if ($row['deleted']) {
224 $this->recStats['deleted'][$table][$newID] = $newID;
225 }
226 if ($versions && $row['t3ver_count'] >= 1 && $row['t3ver_wsid'] == 0) {
227 $this->recStats['published_versions'][$table][$newID] = $newID;
228 }
229 // Select all versions of this record:
230 if ($this->genTree_includeVersions && $GLOBALS['TCA'][$table]['ctrl']['versioningWS']) {
231 $this->genTree_records($newID, '', $table, true);
232 }
233 }
234 }
235
236 /**
237 * Fills $this->lRecords with the records from all tc-tables that are not attached to a PID in the pid-list.
238 *
239 * @param string $pid_list list of pid's (page-record uid's). This list is probably made by genTree()
240 */
241 public function lostRecords($pid_list)
242 {
243 $this->lostPagesList = '';
244 $pageIds = GeneralUtility::intExplode(',', $pid_list);
245 if (is_array($pageIds)) {
246 foreach ($GLOBALS['TCA'] as $table => $tableConf) {
247 $pageIdsForTable = $pageIds;
248 // Remove preceding "-1," for non-versioned tables
249 if (!BackendUtility::isTableWorkspaceEnabled($table)) {
250 $pageIdsForTable = array_combine($pageIdsForTable, $pageIdsForTable);
251 unset($pageIdsForTable[-1]);
252 }
253 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
254 $queryBuilder->getRestrictions()->removeAll();
255 $selectFields = ['uid', 'pid'];
256 if (!empty($GLOBALS['TCA'][$table]['ctrl']['label'])) {
257 $selectFields[] = $GLOBALS['TCA'][$table]['ctrl']['label'];
258 }
259 $queryResult = $queryBuilder->select(...$selectFields)
260 ->from($table)
261 ->where(
262 $queryBuilder->expr()->notIn(
263 'pid',
264 $queryBuilder->createNamedParameter($pageIdsForTable, Connection::PARAM_INT_ARRAY)
265 )
266 )
267 ->execute();
268 $lostIdList = [];
269 while ($row = $queryResult->fetch()) {
270 $this->lRecords[$table][$row['uid']] = [
271 'uid' => $row['uid'],
272 'pid' => $row['pid'],
273 'title' => strip_tags(BackendUtility::getRecordTitle($table, $row))
274 ];
275 $lostIdList[] = $row['uid'];
276 }
277 if ($table === 'pages') {
278 $this->lostPagesList = implode(',', $lostIdList);
279 }
280 }
281 }
282 }
283
284 /**
285 * Fixes lost record from $table with uid $uid by setting the PID to zero.
286 * If there is a disabled column for the record that will be set as well.
287 *
288 * @param string $table Database tablename
289 * @param int $uid The uid of the record which will have the PID value set to 0 (zero)
290 * @return bool TRUE if done.
291 */
292 public function fixLostRecord($table, $uid)
293 {
294 if ($table && $GLOBALS['TCA'][$table] && $uid && is_array($this->lRecords[$table][$uid]) && $GLOBALS['BE_USER']->isAdmin()) {
295 $updateFields = [
296 'pid' => 0
297 ];
298 // If possible a lost record restored is hidden as default
299 if ($GLOBALS['TCA'][$table]['ctrl']['enablecolumns']['disabled']) {
300 $updateFields[$GLOBALS['TCA'][$table]['ctrl']['enablecolumns']['disabled']] = 1;
301 }
302 GeneralUtility::makeInstance(ConnectionPool::class)
303 ->getConnectionForTable($table)
304 ->update($table, $updateFields, ['uid' => (int)$uid]);
305 return true;
306 }
307 return false;
308 }
309
310 /**
311 * Counts records from $GLOBALS['TCA']-tables that ARE attached to an existing page.
312 *
313 * @param string $pid_list list of pid's (page-record uid's). This list is probably made by genTree()
314 * @return array an array with the number of records from all $GLOBALS['TCA']-tables that are attached to a PID in the pid-list.
315 */
316 public function countRecords($pid_list)
317 {
318 $list = [];
319 $list_n = [];
320 $pageIds = GeneralUtility::intExplode(',', $pid_list);
321 if (!empty($pageIds)) {
322 foreach ($GLOBALS['TCA'] as $table => $tableConf) {
323 $pageIdsForTable = $pageIds;
324 // Remove preceding "-1," for non-versioned tables
325 if (!BackendUtility::isTableWorkspaceEnabled($table)) {
326 $pageIdsForTable = array_combine($pageIdsForTable, $pageIdsForTable);
327 unset($pageIdsForTable[-1]);
328 }
329 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
330 $queryBuilder->getRestrictions()->removeAll();
331 $count = $queryBuilder->count('uid')
332 ->from($table)
333 ->where(
334 $queryBuilder->expr()->in(
335 'pid',
336 $queryBuilder->createNamedParameter($pageIds, Connection::PARAM_INT_ARRAY)
337 )
338 )
339 ->execute()
340 ->fetchColumn(0);
341 if ($count) {
342 $list[$table] = $count;
343 }
344
345 // same query excluding all deleted records
346 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
347 $queryBuilder->getRestrictions()
348 ->removeAll()
349 ->add(GeneralUtility::makeInstance(DeletedRestriction::class));
350 $count = $queryBuilder->count('uid')
351 ->from($table)
352 ->where(
353 $queryBuilder->expr()->in(
354 'pid',
355 $queryBuilder->createNamedParameter($pageIdsForTable, Connection::PARAM_INT_ARRAY)
356 )
357 )
358 ->execute()
359 ->fetchColumn(0);
360 if ($count) {
361 $list_n[$table] = $count;
362 }
363 }
364 }
365 return ['all' => $list, 'non_deleted' => $list_n];
366 }
367
368 /**
369 * Finding relations in database based on type 'group' (files or database-uid's in a list)
370 *
371 * @param string $mode $mode = file, $mode = db, $mode = '' (all...)
372 * @return array An array with all fields listed that somehow are references to other records (foreign-keys) or files
373 */
374 public function getGroupFields($mode)
375 {
376 $result = [];
377 foreach ($GLOBALS['TCA'] as $table => $tableConf) {
378 $cols = $GLOBALS['TCA'][$table]['columns'];
379 foreach ($cols as $field => $config) {
380 if ($config['config']['type'] === 'group') {
381 if ((!$mode || $mode === 'file') && $config['config']['internal_type'] === 'file' || (!$mode || $mode === 'db') && $config['config']['internal_type'] === 'db') {
382 $result[$table][] = $field;
383 }
384 }
385 if ((!$mode || $mode === 'db') && $config['config']['type'] === 'select' && $config['config']['foreign_table']) {
386 $result[$table][] = $field;
387 }
388 }
389 if ($result[$table]) {
390 $result[$table] = implode(',', $result[$table]);
391 }
392 }
393 return $result;
394 }
395
396 /**
397 * Finds all fields that hold filenames from uploadfolder
398 *
399 * @param string $uploadfolder Path to uploadfolder
400 * @return array An array with all fields listed that have references to files in the $uploadfolder
401 */
402 public function getFileFields($uploadfolder)
403 {
404 $result = [];
405 foreach ($GLOBALS['TCA'] as $table => $tableConf) {
406 $cols = $GLOBALS['TCA'][$table]['columns'];
407 foreach ($cols as $field => $config) {
408 if ($config['config']['type'] === 'group' && $config['config']['internal_type'] === 'file' && $config['config']['uploadfolder'] == $uploadfolder) {
409 $result[] = [$table, $field];
410 }
411 }
412 }
413 return $result;
414 }
415
416 /**
417 * Returns an array with arrays of table/field pairs which are allowed to hold references to the input table name - according to $GLOBALS['TCA']
418 *
419 * @param string $theSearchTable Table name
420 * @return array
421 */
422 public function getDBFields($theSearchTable)
423 {
424 $result = [];
425 foreach ($GLOBALS['TCA'] as $table => $tableConf) {
426 $cols = $GLOBALS['TCA'][$table]['columns'];
427 foreach ($cols as $field => $config) {
428 if ($config['config']['type'] === 'group' && $config['config']['internal_type'] === 'db') {
429 if (trim($config['config']['allowed']) === '*' || strstr($config['config']['allowed'], $theSearchTable)) {
430 $result[] = [$table, $field];
431 }
432 } elseif ($config['config']['type'] === 'select' && $config['config']['foreign_table'] == $theSearchTable) {
433 $result[] = [$table, $field];
434 }
435 }
436 }
437 return $result;
438 }
439
440 /**
441 * This selects non-empty-records from the tables/fields in the fkey_array generated by getGroupFields()
442 *
443 * @param array $fkey_arrays Array with tables/fields generated by getGroupFields()
444 * @see getGroupFields()
445 */
446 public function selectNonEmptyRecordsWithFkeys($fkey_arrays)
447 {
448 if (is_array($fkey_arrays)) {
449 $connectionPool = GeneralUtility::makeInstance(ConnectionPool::class);
450 foreach ($fkey_arrays as $table => $field_list) {
451 if ($GLOBALS['TCA'][$table] && trim($field_list)) {
452 $connection = $connectionPool->getConnectionForTable($table);
453 $schemaManager = $connection->getSchemaManager();
454 $tableColumns = $schemaManager->listTableColumns($table);
455
456 $queryBuilder = $connectionPool->getQueryBuilderForTable($table);
457 $queryBuilder->getRestrictions()->removeAll();
458
459 $fields = GeneralUtility::trimExplode(',', $field_list, true);
460
461 $queryBuilder->select('uid')
462 ->from($table);
463 $whereClause = [];
464
465 foreach ($fields as $fieldName) {
466 // The array index of $tableColumns is the lowercased column name!
467 // It is quoted for keywords
468 $column = $tableColumns[strtolower($fieldName)]
469 ?? $tableColumns[$connection->quoteIdentifier(strtolower($fieldName))];
470 if (!$column) {
471 // Throw meaningful exception if field does not exist in DB - 'none' is not filtered here since the
472 // method is only called with type=group fields
473 throw new \RuntimeException(
474 'Field ' . $fieldName . ' for table ' . $table . ' has been defined in TCA, but does not exist in DB',
475 1536248936
476 );
477 }
478 $fieldType = $column->getType()->getName();
479 if (in_array(
480 $fieldType,
481 [Type::BIGINT, Type::INTEGER, Type::SMALLINT, Type::DECIMAL, Type::FLOAT],
482 true
483 )) {
484 $whereClause[] = $queryBuilder->expr()->andX(
485 $queryBuilder->expr()->isNotNull($fieldName),
486 $queryBuilder->expr()->neq(
487 $fieldName,
488 $queryBuilder->createNamedParameter(0, \PDO::PARAM_INT)
489 )
490 );
491 } elseif (in_array($fieldType, [Type::STRING, Type::TEXT], true)) {
492 $whereClause[] = $queryBuilder->expr()->andX(
493 $queryBuilder->expr()->isNotNull($fieldName),
494 $queryBuilder->expr()->neq(
495 $fieldName,
496 $queryBuilder->createNamedParameter('', \PDO::PARAM_STR)
497 )
498 );
499 } elseif ($fieldType === Type::BLOB) {
500 $whereClause[] = $queryBuilder->expr()->andX(
501 $queryBuilder->expr()->isNotNull($fieldName),
502 $queryBuilder->expr()
503 ->comparison(
504 $queryBuilder->expr()->length($fieldName),
505 ExpressionBuilder::GT,
506 $queryBuilder->createNamedParameter(0, \PDO::PARAM_INT)
507 )
508 );
509 }
510 }
511 $queryResult = $queryBuilder->orWhere(...$whereClause)->execute();
512
513 while ($row = $queryResult->fetch()) {
514 foreach ($fields as $field) {
515 if (trim($row[$field])) {
516 $fieldConf = $GLOBALS['TCA'][$table]['columns'][$field]['config'];
517 if ($fieldConf['type'] === 'group') {
518 if ($fieldConf['internal_type'] === 'file') {
519 // Files...
520 if ($fieldConf['MM']) {
521 $tempArr = [];
522 $dbAnalysis = GeneralUtility::makeInstance(RelationHandler::class);
523 $dbAnalysis->start('', 'files', $fieldConf['MM'], $row['uid']);
524 foreach ($dbAnalysis->itemArray as $somekey => $someval) {
525 if ($someval['id']) {
526 $tempArr[] = $someval['id'];
527 }
528 }
529 } else {
530 $tempArr = explode(',', trim($row[$field]));
531 }
532 foreach ($tempArr as $file) {
533 $file = trim($file);
534 if ($file) {
535 $this->checkFileRefs[$fieldConf['uploadfolder']][$file] += 1;
536 }
537 }
538 }
539 if ($fieldConf['internal_type'] === 'db') {
540 $dbAnalysis = GeneralUtility::makeInstance(RelationHandler::class);
541 $dbAnalysis->start(
542 $row[$field],
543 $fieldConf['allowed'],
544 $fieldConf['MM'],
545 $row['uid'],
546 $table,
547 $fieldConf
548 );
549 foreach ($dbAnalysis->itemArray as $tempArr) {
550 $this->checkGroupDBRefs[$tempArr['table']][$tempArr['id']] += 1;
551 }
552 }
553 }
554 if ($fieldConf['type'] === 'select' && $fieldConf['foreign_table']) {
555 $dbAnalysis = GeneralUtility::makeInstance(RelationHandler::class);
556 $dbAnalysis->start(
557 $row[$field],
558 $fieldConf['foreign_table'],
559 $fieldConf['MM'],
560 $row['uid'],
561 $table,
562 $fieldConf
563 );
564 foreach ($dbAnalysis->itemArray as $tempArr) {
565 if ($tempArr['id'] > 0) {
566 $this->checkGroupDBRefs[$fieldConf['foreign_table']][$tempArr['id']] += 1;
567 }
568 }
569 }
570 }
571 }
572 }
573 }
574 }
575 }
576 }
577
578 /**
579 * Depends on selectNonEmpty.... to be executed first!!
580 *
581 * @return array Report over files; keys are "moreReferences", "noReferences", "noFile", "error
582 */
583 public function testFileRefs()
584 {
585 $output = [];
586 // Handle direct references with upload folder setting (workaround)
587 $newCheckFileRefs = [];
588 foreach ($this->checkFileRefs as $folder => $files) {
589 // Only direct references without a folder setting
590 if ($folder !== '') {
591 $newCheckFileRefs[$folder] = $files;
592 continue;
593 }
594 foreach ($files as $file => $references) {
595 // Direct file references have often many references (removes occurrences in the moreReferences section of the result array)
596 if ($references > 1) {
597 $references = 1;
598 }
599 // The directory must be empty (prevents checking of the root directory)
600 $directory = PathUtility::dirname($file);
601 if ($directory !== '') {
602 $newCheckFileRefs[$directory][PathUtility::basename($file)] = $references;
603 }
604 }
605 }
606 $this->checkFileRefs = $newCheckFileRefs;
607 foreach ($this->checkFileRefs as $folder => $fileArr) {
608 $path = Environment::getPublicPath() . '/' . $folder;
609 if (@is_dir($path) && @is_readable($path)) {
610 $d = dir($path);
611 while ($entry = $d->read()) {
612 if (@is_file($path . '/' . $entry)) {
613 if (isset($fileArr[$entry])) {
614 if ($fileArr[$entry] > 1) {
615 $temp = $this->whereIsFileReferenced($folder, $entry);
616 $tempList = '';
617 foreach ($temp as $inf) {
618 $tempList .= '[' . $inf['table'] . '][' . $inf['uid'] . '][' . $inf['field'] . '] (pid:' . $inf['pid'] . ') - ';
619 }
620 $output['moreReferences'][] = [$path, $entry, $fileArr[$entry], $tempList];
621 }
622 unset($fileArr[$entry]);
623 } else {
624 // Contains workaround for direct references
625 if (!strstr($entry, 'index.htm') && !preg_match('/^' . preg_quote($GLOBALS['TYPO3_CONF_VARS']['BE']['fileadminDir'], '/') . '/', $folder)) {
626 $output['noReferences'][] = [$path, $entry];
627 }
628 }
629 }
630 }
631 $d->close();
632 $tempCounter = 0;
633 foreach ($fileArr as $file => $value) {
634 // Workaround for direct file references
635 if (preg_match('/^' . preg_quote($GLOBALS['TYPO3_CONF_VARS']['BE']['fileadminDir'], '/') . '/', $folder)) {
636 $file = $folder . '/' . $file;
637 $folder = '';
638 $path = Environment::getPublicPath();
639 }
640 $temp = $this->whereIsFileReferenced($folder, $file);
641 $tempList = '';
642 foreach ($temp as $inf) {
643 $tempList .= '[' . $inf['table'] . '][' . $inf['uid'] . '][' . $inf['field'] . '] (pid:' . $inf['pid'] . ') - ';
644 }
645 $tempCounter++;
646 $output['noFile'][substr($path, -3) . '_' . substr($file, 0, 3) . '_' . $tempCounter] = [$path, $file, $tempList];
647 }
648 } else {
649 $output['error'][] = [$path];
650 }
651 }
652 return $output;
653 }
654
655 /**
656 * Depends on selectNonEmpty.... to be executed first!!
657 *
658 * @param array $theArray Table with key/value pairs being table names and arrays with uid numbers
659 * @return string HTML Error message
660 */
661 public function testDBRefs($theArray)
662 {
663 $result = '';
664 foreach ($theArray as $table => $dbArr) {
665 if ($GLOBALS['TCA'][$table]) {
666 $ids = array_keys($dbArr);
667 if (!empty($ids)) {
668 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
669 ->getQueryBuilderForTable($table);
670 $queryBuilder->getRestrictions()
671 ->removeAll()
672 ->add(GeneralUtility::makeInstance(DeletedRestriction::class));
673 $queryResult = $queryBuilder
674 ->select('uid')
675 ->from($table)
676 ->where(
677 $queryBuilder->expr()->in(
678 'uid',
679 $queryBuilder->createNamedParameter($ids, Connection::PARAM_INT_ARRAY)
680 )
681 )
682 ->execute();
683 while ($row = $queryResult->fetch()) {
684 if (isset($dbArr[$row['uid']])) {
685 unset($dbArr[$row['uid']]);
686 } else {
687 $result .= 'Strange Error. ...<br />';
688 }
689 }
690 foreach ($dbArr as $theId => $theC) {
691 $result .= 'There are ' . $theC . ' records pointing to this missing or deleted record; [' . $table . '][' . $theId . ']<br />';
692 }
693 }
694 } else {
695 $result .= 'Codeerror. Table is not a table...<br />';
696 }
697 }
698 return $result;
699 }
700
701 /**
702 * Finding all references to record based on table/uid
703 *
704 * @param string $searchTable Table name
705 * @param int $id Uid of database record
706 * @return array Array with other arrays containing information about where references was found
707 */
708 public function whereIsRecordReferenced($searchTable, $id)
709 {
710 // Gets tables / Fields that reference to files
711 $fileFields = $this->getDBFields($searchTable);
712 $theRecordList = [];
713 foreach ($fileFields as $info) {
714 list($table, $field) = $info;
715 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
716 $queryBuilder->getRestrictions()->removeAll();
717 $queryResult = $queryBuilder
718 ->select('uid', 'pid', $GLOBALS['TCA'][$table]['ctrl']['label'], $field)
719 ->from($table)
720 ->where(
721 $queryBuilder->expr()->like(
722 $field,
723 $queryBuilder->createNamedParameter('%' . $queryBuilder->escapeLikeWildcards($id) . '%')
724 )
725 )
726 ->execute();
727
728 while ($row = $queryResult->fetch()) {
729 // Now this is the field, where the reference COULD come from.
730 // But we're not guaranteed, so we must carefully examine the data.
731 $fieldConf = $GLOBALS['TCA'][$table]['columns'][$field]['config'];
732 $allowedTables = $fieldConf['type'] === 'group' ? $fieldConf['allowed'] : $fieldConf['foreign_table'];
733 $dbAnalysis = GeneralUtility::makeInstance(RelationHandler::class);
734 $dbAnalysis->start($row[$field], $allowedTables, $fieldConf['MM'], $row['uid'], $table, $fieldConf);
735 foreach ($dbAnalysis->itemArray as $tempArr) {
736 if ($tempArr['table'] == $searchTable && $tempArr['id'] == $id) {
737 $theRecordList[] = [
738 'table' => $table,
739 'uid' => $row['uid'],
740 'field' => $field,
741 'pid' => $row['pid']
742 ];
743 }
744 }
745 }
746 }
747 return $theRecordList;
748 }
749
750 /**
751 * Finding all references to file based on uploadfolder / filename
752 *
753 * @param string $uploadFolder Upload folder where file is found
754 * @param string $filename Filename to search for
755 * @return array Array with other arrays containing information about where references was found
756 */
757 public function whereIsFileReferenced($uploadFolder, $filename)
758 {
759 // Gets tables / Fields that reference to files
760 $fileFields = $this->getFileFields($uploadFolder);
761 $theRecordList = [];
762 foreach ($fileFields as $info) {
763 list($table, $field) = $info;
764 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
765 $queryBuilder->getRestrictions()->removeAll();
766 $queryResult = $queryBuilder
767 ->select('uid', 'pid', $GLOBALS['TCA'][$table]['ctrl']['label'], $field)
768 ->from($table)
769 ->where(
770 $queryBuilder->expr()->like(
771 $field,
772 $queryBuilder->createNamedParameter('%' . $queryBuilder->escapeLikeWildcards($filename) . '%')
773 )
774 )
775 ->execute();
776 while ($row = $queryResult->fetch()) {
777 // Now this is the field, where the reference COULD come from.
778 // But we're not guaranteed, so we must carefully examine the data.
779 $tempArr = explode(',', trim($row[$field]));
780 foreach ($tempArr as $file) {
781 $file = trim($file);
782 if ($file == $filename) {
783 $theRecordList[] = [
784 'table' => $table,
785 'uid' => $row['uid'],
786 'field' => $field,
787 'pid' => $row['pid']
788 ];
789 }
790 }
791 }
792 }
793 return $theRecordList;
794 }
795 }