CVE-2019-8118 - DB_CLEANUP_SCRIPT

DB_CLEANUP_SCRIPT.php

<?php
/**
 * Copyright © Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */

use Magento\Framework\App\Bootstrap;
use Magento\Framework\DB\Adapter\AdapterInterface;
use Magento\Framework\DB\Query\Generator;
use Magento\Framework\Exception\LocalizedException;
use Magento\Framework\Serialize\Serializer\Json;
use Magento\Framework\App\ResourceConnection;

if (PHP_SAPI !== 'cli') {
    echo 'Clean up script must be run as a CLI application';
    exit(1);
}

try {
    require __DIR__ . '/app/bootstrap.php';
} catch (\Exception $e) {
    echo 'Autoload error: ' . $e->getMessage();
    exit(1);
}
try {
    $params = $_SERVER;
    $bootstrap = Bootstrap::create(BP, $params);
    /** @var CleanupData $cleanup */
    $cleanup = $bootstrap->getObjectManager()->get(CleanupData::class);
    $timeStart = microtime(true);
    $cleanup->cleanUpWishListItemOptionValue();
    $timeEnd = microtime(true);
    echo 'wishlist_item_option table entry clean-up completed in ' . (int)($timeEnd - $timeStart) . ' seconds' . \PHP_EOL;

    $timeStart = microtime(true);
    $cleanup->cleanUpQuoteItemOptionValue();
    $timeEnd = microtime(true);
    echo 'quote_item_option table entry clean-up completed in ' . (int)($timeEnd - $timeStart) . ' seconds' . \PHP_EOL;

    $timeStart = microtime(true);
    $cleanup->cleanUpOrderItemOptionValue();
    $timeEnd = microtime(true);
    echo 'order_item_option table entry clean-up completed in ' . (int)($timeEnd - $timeStart) . ' seconds' . \PHP_EOL;
    exit(0);
} catch (\Exception $e) {
    while ($e) {
        echo $e->getMessage();
        echo $e->getTraceAsString();
        echo "\n\n";
        $e = $e->getPrevious();
    }
    exit(1);
}

class CleanUpData
{
    const BATCH_SIZE = 1000;
    /**
     * @var Generator
     */
    private $queryGenerator;

    /**
     * @var Json
     */
    private $json;
    /**
     * @var AdapterInterface
     */
    private $adapter;

    /**
     * Constructor
     * @param Json|null $json
     * @param Generator|null $queryGenerator
     * @param ResourceConnection $resourceConnection
     */
    public function __construct(
        Json $json,
        Generator $queryGenerator,
        ResourceConnection $resourceConnection
    ) {
        $this->queryGenerator = $queryGenerator;
        $this->json = $json;
        $this->adapter = $resourceConnection;
    }

    /**
     * Clean up unused data in `value` field in `wishlist_item_option` table
     *
     * @return void
     * @throws LocalizedException
     */
    public function cleanUpWishListItemOptionValue()
    {
        $tableName = $this->adapter->getTableName('wishlist_item_option');
        $this->cleanUpTable($tableName, ['code = ?', 'info_buyRequest']);
    }

    /**
     * Clean up unused data in `value` field in `quote_item_option` table
     *
     * @return void
     * @throws LocalizedException
     */
    public function cleanUpQuoteItemOptionValue()
    {
        $tableName = $this->adapter->getTableName('quote_item_option');
        $this->cleanUpTable($tableName, ['code = ?', 'info_buyRequest']);
    }
    /**
     * Clean up unused data in `value` field in `quote_item_option` table
     *
     * @return void
     * @throws LocalizedException
     */
    public function cleanUpOrderItemOptionValue()
    {
        $tableName = $this->adapter->getTableName('sales_order_item');
        $this->cleanUpTable($tableName, [], 'item_id', 'product_options');
    }

    /**
     * @param $tableName
     * @param array $filter
     * @param string $pk
     * @param string $columnName
     * @throws LocalizedException
     */
    public function cleanUpTable($tableName, $filter = [], $pk = 'option_id', $columnName = 'value')
    {
        $select = $this->adapter
            ->getConnection()
            ->select()
            ->from(
                $tableName,
                [$pk, $columnName]
            );
        $countSelect = $this->adapter
            ->getConnection()
            ->select()
            ->from(
                $tableName,
                ['COUNT(*)']
            );

        if (!empty($filter)) {
            $select->where($filter[0], $filter[1]);
            $countSelect->where($filter[0], $filter[1]);
        }

        $batches = ceil(floatval($this->adapter->getConnection()->fetchRow($countSelect)['COUNT(*)']) / self::BATCH_SIZE);
        $iterator = $this->queryGenerator->generate($pk, $select, self::BATCH_SIZE);

        foreach ($iterator as $key => $selectByRange) {
            $optionRows = $this->adapter->getConnection()->fetchAll($selectByRange);
            foreach ($optionRows as $optionRow) {
                try {
                    $updateRow = false;
                    $rows = 0;
                    try {
                        $rowValue = $this->json->unserialize($optionRow[$columnName]);
                    } catch (\Exception $exception) {
                        /** options that are not migrated from 2.1 to new serialize */
                        $rowValue = unserialize($optionRow[$columnName]);
                    }
                    if (is_array($rowValue) && isset($rowValue['login'])) {
                        unset($rowValue['login']);
                        $updateRow = true;
                    }
                    if (is_array($rowValue) && isset($rowValue['info_buyRequest']) && is_array($rowValue['info_buyRequest']) && isset($rowValue['info_buyRequest']['login'])) {
                        unset($rowValue['info_buyRequest']['login']);
                        $updateRow = true;
                    }
                    if ($updateRow) {
                        $rowValue = $this->json->serialize($rowValue);
                        $rows = $this->adapter->getConnection()->update(
                            $tableName,
                            [$columnName => $rowValue],
                            [$pk.' = ?' => $optionRow[$pk]]
                        );
                    }
                    if ($rows) {
                        echo 'Removed login data from option ID '.$optionRow[$pk].PHP_EOL;
                    }
                } catch (\Exception $e) {
                    echo 'Error for option ID '.$optionRow[$pk].'; '.$e->getMessage().PHP_EOL;
                }
            }
            echo 'Batch ' . ($key + 1) . ' out of ' . $batches . " completed" . \PHP_EOL;
        }
    }
}

DB_CLEANUP_SCRIPT_2.1.php

<?php
/**
 * Copyright © Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */

use Magento\Framework\App\Bootstrap;
use Magento\Framework\DB\Adapter\AdapterInterface;
use Magento\Framework\DB\Query\Generator;
use Magento\Framework\Exception\LocalizedException;
use Magento\Framework\App\ResourceConnection;

if (PHP_SAPI !== 'cli') {
    echo 'Clean up script must be run as a CLI application';
    exit(1);
}

try {
    require __DIR__ . '/app/bootstrap.php';
} catch (\Exception $e) {
    echo 'Autoload error: ' . $e->getMessage();
    exit(1);
}
try {
    $params = $_SERVER;
    $bootstrap = Bootstrap::create(BP, $params);
    /** @var CleanupData $cleanup */
    $cleanup = $bootstrap->getObjectManager()->get(CleanupData::class);
    $timeStart = microtime(true);
    $cleanup->cleanUpWishListItemOptionValue();
    $timeEnd = microtime(true);
    echo 'wishlist_item_option table entry clean-up completed in ' . (int)($timeEnd - $timeStart) . ' seconds' . \PHP_EOL;

    $timeStart = microtime(true);
    $cleanup->cleanUpQuoteItemOptionValue();
    $timeEnd = microtime(true);
    echo 'quote_item_option table entry clean-up completed in ' . (int)($timeEnd - $timeStart) . ' seconds' . \PHP_EOL;

    $timeStart = microtime(true);
    $cleanup->cleanUpOrderItemOptionValue();
    $timeEnd = microtime(true);
    echo 'order_item_option table entry clean-up completed in ' . (int)($timeEnd - $timeStart) . ' seconds' . \PHP_EOL;
    exit(0);
} catch (\Exception $e) {
    while ($e) {
        echo $e->getMessage();
        echo $e->getTraceAsString();
        echo "\n\n";
        $e = $e->getPrevious();
    }
    exit(1);
}

class CleanUpData
{
    const BATCH_SIZE = 1000;
    /**
     * @var Generator
     */
    private $queryGenerator;

    /**
     * @var AdapterInterface
     */
    private $adapter;

    /**
     * Constructor
     * @param Generator|null $queryGenerator
     * @param ResourceConnection $resourceConnection
     */
    public function __construct(
        Generator $queryGenerator,
        ResourceConnection $resourceConnection
    ) {
        $this->queryGenerator = $queryGenerator;
        $this->adapter = $resourceConnection;
    }

    /**
     * Clean up unused data in `value` field in `wishlist_item_option` table
     *
     * @return void
     * @throws LocalizedException
     */
    public function cleanUpWishListItemOptionValue()
    {
        $tableName = $this->adapter->getTableName('wishlist_item_option');
        $this->cleanUpTable($tableName, ['code = ?', 'info_buyRequest']);
    }

    /**
     * Clean up unused data in `value` field in `quote_item_option` table
     *
     * @return void
     * @throws LocalizedException
     */
    public function cleanUpQuoteItemOptionValue()
    {
        $tableName = $this->adapter->getTableName('quote_item_option');
        $this->cleanUpTable($tableName, ['code = ?', 'info_buyRequest']);
    }
    /**
     * Clean up unused data in `value` field in `quote_item_option` table
     *
     * @return void
     * @throws LocalizedException
     */
    public function cleanUpOrderItemOptionValue()
    {
        $tableName = $this->adapter->getTableName('sales_order_item');
        $this->cleanUpTable($tableName, [], 'item_id', 'product_options');
    }

    /**
     * @param $tableName
     * @param array $filter
     * @param string $pk
     * @param string $columnName
     * @throws LocalizedException
     */
    public function cleanUpTable($tableName, $filter = [], $pk = 'option_id', $columnName = 'value')
    {
        $select = $this->adapter
            ->getConnection()
            ->select()
            ->from(
                $tableName,
                [$pk, $columnName]
            );
        $countSelect = $this->adapter
            ->getConnection()
            ->select()
            ->from(
                $tableName,
                ['COUNT(*)']
            );

        if (!empty($filter)) {
            $select->where($filter[0], $filter[1]);
            $countSelect->where($filter[0], $filter[1]);
        }

        $batches = ceil(floatval($this->adapter->getConnection()->fetchRow($countSelect)['COUNT(*)']) / self::BATCH_SIZE);
        $iterator = $this->queryGenerator->generate($pk, $select, self::BATCH_SIZE);

        foreach ($iterator as $key => $selectByRange) {
            $optionRows = $this->adapter->getConnection()->fetchAll($selectByRange);
            foreach ($optionRows as $optionRow) {
                try {
                    $updateRow = false;
                    $rows = 0;
                    $rowValue = unserialize($optionRow[$columnName]);
                    if (is_array($rowValue) && isset($rowValue['login'])) {
                        unset($rowValue['login']);
                        $updateRow = true;
                    }
                    if (is_array($rowValue) && isset($rowValue['info_buyRequest']) && is_array($rowValue['info_buyRequest']) && isset($rowValue['info_buyRequest']['login'])) {
                        unset($rowValue['info_buyRequest']['login']);
                        $updateRow = true;
                    }
                    if ($updateRow) {
                        $rowValue = serialize($rowValue);
                        $rows = $this->adapter->getConnection()->update(
                            $tableName,
                            [$columnName => $rowValue],
                            [$pk.' = ?' => $optionRow[$pk]]
                        );
                    }
                    if ($rows) {
                        echo 'Removed login data from option ID '.$optionRow[$pk].PHP_EOL;
                    }
                } catch (\Exception $e) {
                    echo 'Error for option ID '.$optionRow[$pk].'; '.$e->getMessage().PHP_EOL;
                }
            }
            echo 'Batch ' . ($key + 1) . ' out of ' . $batches . " completed" . \PHP_EOL;
        }
    }
}