Purging Custom Options data

Question

Magento Version : 1.9.2.2

I have some Custom Options fields for my products which could potentially contain sensitive information. I do not want the data associated with the customer after I have fulfilled the order. I would therefore like to purge just the Custom Options data from the database.

I have crafted the following mysql query which removes ALL the "Custom Options" from both sales and quotes and it appears to work. Obviously I would limit this to just the orders I wanted to clear.

UPDATE `sales_flat_order_item` SET product_options='';
UPDATE `sales_flat_quote_item_option` SET VALUE='' WHERE (`code` NOT LIKE 'info_buyRequest' AND `code` NOT LIKE 'option_ids');

• Will the above queries purge all the "Custom Options" data from the database?
• Will clearing the data in this way cause any other problems within Magento ?
• Is there perhaps a better way of doing this than resorting to manually clearing the fields with a mysql query?

Answer 1

• Will the above queries purge all the "Custom Options" data from the database?

Yes. Also those the data of orders and quotes that you did not fulfil yet.

• Will clearing the data in this way cause any other problems within Magento ?

Yes again, even if you only delete old data. For the quote items, you exclude info_buyRequest but for the order items you did not (of course, since that's not possible in a single serialized field). This means, the "reorder" functionality will break.

• Is there perhaps a better way of doing this than resorting to manually clearing the fields with a mysql query?

I would suggest the following approach:

• create a new order attribute custom_options_purged with a default value of "0"
• create a Magento cron job that does the following:
  1. find all orders with custom_options_purged=0
  2. for each order iterate over the order items and remove the sensitive custom option values (but not all custom options!), save the order items. Mage_Sales_Model_Order_Item provides the methods for this.
  3. for each order find the quote and also delete the custom options in the quote items
  4. set custom_options_purged to "1".