Magento Database Anonymization after Live-Database Import

gistfile1.txt

#!/bin/bash
echo "*** This script is anonymizing a DB-dump of the LIVE-DB in the DEMO-Environment ***"

HOST=`grep host ../app/etc/local.xml | sed 's/ *<host>\(.*\)<\/host>/\1/' | sed 's/<!\[CDATA\[//' | sed 's/\]\]>//'`
USER=`grep username ../app/etc/local.xml | sed 's/ *<username>\(.*\)<\/username>/\1/' | sed 's/<!\[CDATA\[//' | sed 's/\]\]>//'`
PASS=`grep password ../app/etc/local.xml | sed 's/ *<password>\(.*\)<\/password>/\1/' | sed 's/<!\[CDATA\[//' | sed 's/\]\]>//'`
NAME=`grep dbname ../app/etc/local.xml | sed 's/ *<dbname>\(.*\)<\/dbname>/\1/' | sed 's/<!\[CDATA\[//' | sed 's/\]\]>//'`

echo ""

if [ "$NAME" = "LIVE_DB_NAME" ]; then
    echo "Error: This is the LIVE environment -> EXIT";
    exit;
fi

DBCALL="mysql -u$USER -p$PASS -h$HOST $NAME"

echo "* Step 2: Anonymize customer address, customers, orders, quotes, creditmemos, payments, shipments and newsletter data."

# customer address
$DBCALL -e "UPDATE customer_address_entity_varchar SET value=CONCAT('firstname_',entity_id) WHERE attribute_id=19"
$DBCALL -e "UPDATE customer_address_entity_varchar SET value=CONCAT('lastname_',entity_id) WHERE attribute_id=21"
$DBCALL -e "UPDATE customer_address_entity_varchar SET value=CONCAT('0341 12345',entity_id) WHERE attribute_id=30"
$DBCALL -e "UPDATE customer_address_entity_varchar SET value=CONCAT('0171 12345',entity_id) WHERE attribute_id=31"
$DBCALL -e "UPDATE customer_address_entity_text SET value=CONCAT(entity_id,' test avenue') WHERE attribute_id=24"

# customer account data
$DBCALL -e "UPDATE customer_entity SET email=CONCAT('magentoshop',entity_id,'@trash-mail.com')"
$DBCALL -e "UPDATE customer_entity_varchar SET value=CONCAT('firstname_',entity_id) WHERE attribute_id=5"
$DBCALL -e "UPDATE customer_entity_varchar SET value=CONCAT('lastname_',entity_id) WHERE attribute_id=7"
$DBCALL -e "UPDATE customer_entity_varchar SET value=MD5(CONCAT('magentoshop',entity_id,'@trash-mail.com')) WHERE attribute_id=12"

# credit memo
$DBCALL -e "UPDATE sales_flat_creditmemo_grid SET billing_name='Firstname Lastname'"

# invoices
$DBCALL -e "UPDATE sales_flat_invoice_grid SET billing_name='Firstname Lastname'"

# shipments
$DBCALL -e "UPDATE sales_flat_shipment_grid SET shipping_name='Firstname Lastname'"

# quotes
$DBCALL -e "UPDATE sales_flat_quote SET customer_email=CONCAT('magentoshop',entity_id,'@trash-mail.com'), customer_firstname='Firstname', customer_lastname='Lastname', customer_middlename=NULL, remote_ip='192.168.1.1', password_hash=NULL"
$DBCALL -e "UPDATE sales_flat_quote_address SET firstname='Firstname', lastname='Lastname', company=NULL, telephone=CONCAT('0351-6540', address_id), email=CONCAT('magentoshop',address_id,'@trash-mail.com'), street=CONCAT('Devstreet  ',address_id)"

# orders
$DBCALL -e "UPDATE sales_flat_order SET customer_email=CONCAT('magentoshop',entity_id,'@trash-mail.com'), soco_email=CONCAT('magentoshop',entity_id,'@trash-mail.com'), customer_firstname='Firstname', customer_lastname='Lastname', customer_middlename=NULL"
$DBCALL -e "UPDATE sales_flat_order_address SET firstname='Firstname', lastname='Lastname', email=CONCAT('magentoshop',entity_id,'@trash-mail.com'), company=NULL, telephone=CONCAT('0351-6540', entity_id), street=CONCAT('Devstreet ',entity_id)"
$DBCALL -e "UPDATE sales_flat_order_grid SET shipping_name='Firstname Lastname', billing_name='Firstname Lastname'"

# payments
$DBCALL -e "UPDATE sales_flat_order_payment SET additional_data=NULL, additional_information=NULL"

# newsletter
$DBCALL -e "UPDATE newsletter_subscriber SET subscriber_email=CONCAT('magentoshop-newsletter_',subscriber_id,'@trash-mail.com')"