Last active
April 13, 2020 20:46
-
-
Save peterjaap/2b53ca6712e6eca0ae95cf0dcecd976d to your computer and use it in GitHub Desktop.
Updated Magento 2 cleanup script for sensitive data in wishlist_item_option, quote_item_option AND order_item_option (not in original script). Also added try/catch block for unserializable data. See for more info https://support.magento.com/hc/en-us/articles/360040209352 and https://magento.com/security/hot-fix-available-cve-2019-8118
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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\DB\Select\QueryModifierFactory; | |
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 QueryModifierFactory | |
*/ | |
private $queryModifierFactory; | |
/** | |
* @var AdapterInterface | |
*/ | |
private $adapter; | |
/** | |
* Constructor | |
* @param Json|null $json | |
* @param Generator|null $queryGenerator | |
* @param QueryModifierFactory|null $queryModifierFactory | |
* @param ResourceConnection $resourceConnection | |
*/ | |
public function __construct( | |
Json $json, | |
Generator $queryGenerator, | |
QueryModifierFactory $queryModifierFactory, | |
ResourceConnection $resourceConnection | |
) { | |
$this->queryModifierFactory = $queryModifierFactory; | |
$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; | |
$rowValue = $this->json->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; | |
} | |
} | |
} |
A way to check this via SQL queries:
SELECT * FROM quote_item_option WHERE value LIKE "%login%";
SELECT * FROM sales_order_item WHERE product_options LIKE "%login%";
SELECT * FROM wishlist_item_option WHERE value LIKE "%login%";
FYI for anyone running across this Gist: An updated version from Magento covering a few more potentially affected tables was released today: https://support.magento.com/hc/en-us/articles/360040209352
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Easy way to QC check the removal of the data (or check to see if there is any there to start with):
Example output (number of records in each of 3 tables):