FAQ: How do I remove sensitive credit card data?

Applies To:  All versions of AbleCommerce Gold that store credit card data.

NOTE:  This is a PA-DSS requirement.

To purge sensitive credit card data:

This document describes how to purge old credit card data from a backup of the AbleCommerce database.  This is required by the PCI Payment Standards Security Council if you are storing credit card information.

Keep in mind that a live AbleCommerce store will automatically purge this data when the 'Days to Save' threshold is met.  This does not apply if you are not storing credit card data.

Step 1 -

Get the credit card data retention period from "Administration  >  Configure  >  Security  >  System Settings" (/Admin/Store/Security/Default.aspx) page. The value is specified under "Credit Card Storage" section. You only need to purge the Credit Card data if you have checked the option "Enable Payment Data Storage” and the value of "Days to Save" field is greater than zero.

Make a note of the value from this field and use it to calculate the date from which you need to purge the credit card data.

Step 2 -

If you have configured the Gift Certificate payment method then we need to keep the account data for gift certificate payments. So, execute the following SQL query to get the Id of the Gift certificate payment method.

--
SELECT PM.PaymentMethodId from ac_PaymentMethods PM
WHERE PM.PaymentInstrumentId = 12
AND PM.Name = 'Gift Certificate';
--

Make a note of this ID value.

Step 3 -

If you have configured the Gift certificate payment method then execute the following SQL query, otherwise skip to step # 4:

--
UPDATE ac_Payments SET EncryptedAccountData = NULL
WHERE ac_Payments.PaymentId IN
(
    SELECT P.PaymentId from ac_Payments P
    INNER JOIN ac_Orders O on P.OrderId = O.OrderId
    WHERE O.StoreId = 1
    AND P.EncryptedAccountData IS NOT NULL
    AND P.CompletedDate < :completedDate
    AND P.PaymentMethodId = :PaymentMethodId
)

In the above SQL query, replace the ':completedDate' value with the date value we obtained in step #1, and the ':PaymentMethodId' value with the Gift Certificate Id value we obtained in step #2.


Step 4 -

If you have NOT configured the Gift certificate payment method then execute the following SQL query:

--
UPDATE ac_Payments SET EncryptedAccountData = ''
WHERE ac_Payments.PaymentId IN
(
    SELECT P.PaymentId from ac_Payments P
    INNER JOIN ac_Orders O on P.OrderId = O.OrderId
    WHERE O.StoreId = 1
    AND P.EncryptedAccountData IS NOT NULL
    AND P.CompletedDate < :completedDate
)

In the above SQL query, replace the ':completedDate' value with the date value we obtained in step #1.