Drumbeat/PaulBooker/An example santization script for a drupal site running CiviCRM

From MozillaWiki
Jump to navigation Jump to search

  <?php
require './includes/bootstrap.inc';
drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);
set_time_limit(0);
_sanitize_mozcrm_drupal();
_sanitize_mozcrm_civicrm();
function _sanitize_mozcrm_drupal() {
print("Starting sanitization of mozcrm_drupal database ..<BR><BR>");
db_query("TRUNCATE {access}");
db_query("TRUNCATE {boost_access}");
db_query("TRUNCATE {boost_access_relationships}");
db_query("TRUNCATE {boost_access_settings}");
db_query("TRUNCATE {boost_crawler}");
db_query("TRUNCATE {cache}");
db_query("TRUNCATE {cache_block}");
db_query("TRUNCATE {cache_content}");
db_query("TRUNCATE {cache_drumbeat_tabs}");
db_query("TRUNCATE {cache_filter}");
db_query("TRUNCATE {cache_form}");
db_query("TRUNCATE {cache_location}");
db_query("TRUNCATE {cache_menu}");
db_query("TRUNCATE {cache_node}");
db_query("TRUNCATE {cache_page}");
db_query("TRUNCATE {cache_pathdst}");
db_query("TRUNCATE {cache_pathsrc}");
db_query("TRUNCATE {cache_update}");
db_query("TRUNCATE {cache_views}");
db_query("TRUNCATE {cache_views_data}");
db_query("TRUNCATE {domain_2_cache}");
db_query("TRUNCATE {domain_2_cache_block}");
db_query("TRUNCATE {domain_2_cache_content}");
db_query("TRUNCATE {domain_2_cache_filter}");
db_query("TRUNCATE {domain_2_cache_form}");
db_query("TRUNCATE {domain_2_cache_location}");
db_query("TRUNCATE {domain_2_cache_menu}");
db_query("TRUNCATE {domain_2_cache_page}");
db_query("TRUNCATE {domain_2_cache_pathdst}");
db_query("TRUNCATE {domain_2_cache_pathsrc}");
db_query("TRUNCATE {domain_2_cache_update}");
db_query("TRUNCATE {domain_2_cache_views}");
db_query("TRUNCATE {domain_2_cache_views_data}");
db_query("TRUNCATE {domain_2_ctools_css_cache}");
db_query("TRUNCATE {domain_2_ctools_object_cache}");
db_query("TRUNCATE {domain_2_views_object_cache}");
db_query("TRUNCATE {location}");
db_query("TRUNCATE {location_instance}");
db_query("TRUNCATE {sessions}");
db_query("TRUNCATE {signup_log}");
db_query("TRUNCATE {signup_views}");
db_query("TRUNCATE {watchdog}");
db_query("UPDATE {comments} SET hostname = '127.0.0.1'");

db_query("UPDATE {users} SET init = CONCAT(users.uid, '@testmail.drumbeat.org')");
db_query("UPDATE {users} SET mail = CONCAT(users.uid, '@testmail.drumbeat.org')");
db_query("UPDATE {users} SET pass = MD5('pass')");
db_query("UPDATE {users} SET status = 1 WHERE uid = 1");
db_query("UPDATE {users} SET name = 'test.admin' WHERE uid = 1");
db_query("UPDATE {users} SET data = ");
db_query("UPDATE {content_field_im_account} SET field_im_account_value = NULL");
db_query("UPDATE {content_field_im_provider} SET field_im_provider_value = NULL");
db_query("UPDATE {content_field_job_area} SET field_job_area_value = NULL");
db_query("UPDATE {content_field_profile_groups} SET field_profile_groups_value = NULL");
db_query("UPDATE {content_field_profile_hidden_city} SET field_profile_hidden_city_value = NULL");
db_query("UPDATE {content_field_profile_hidden_country} SET field_profile_hidden_country_value = NULL");
db_query("UPDATE {content_field_profile_hidden_state} SET field_profile_hidden_state_value = NULL");
db_query("UPDATE {content_field_profile_hidden_zip} SET field_profile_hidden_zip_value = NULL");
db_query("UPDATE {content_field_profile_languages} SET field_profile_languages_value = NULL");
db_query("UPDATE {content_field_profile_operating_system} SET field_profile_operating_system_value = NULL");
db_query("UPDATE {content_field_profile_organisations} SET field_profile_organisations_value = NULL");
db_query("UPDATE {content_field_profile_programming_lang} SET field_profile_programming_lang_value = NULL");
db_query("UPDATE {content_field_profile_projects} SET field_profile_projects_value = NULL");
db_query("UPDATE {content_field_profile_roles SET field_profile_roles_value = NULL");
db_query("UPDATE {content_field_profile_skills} SET field_profile_skills_value = NULL");
db_query("UPDATE {content_field_profile_social} SET field_profile_social_url = NULL");
db_query("UPDATE {content_field_profile_social} SET field_profile_social_title = NULL");
db_query("UPDATE {content_field_profile_social} SET field_profile_social_attributes = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_first_name_value = 'first_name'");
db_query("UPDATE {content_type_public_profile} SET field_profile_last_name_value = 'last_name'");
db_query("UPDATE {content_type_public_profile} SET field_profile_occupation_value = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_nationality_value = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_birthday_value = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_gender_value = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_size_value = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_contact_mail_email = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_bugzilla_email = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_irc_value = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_blog_url = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_blog_title = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_blog_attributes = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_location_lid = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_hidden_first_name_value = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_hidden_org_value = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_hidden_products_value = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_hidden_skills_value = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_hidden_location_value = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_hidden_last_name_value = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_picture_data = NULL");
db_query("UPDATE {content_type_public_profile} SET field_hidden_city_value = NULL");
db_query("UPDATE {content_type_public_profile} SET field_hidden_state_value = NULL");
db_query("UPDATE {content_type_public_profile} SET field_hidden_country_value = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_hidden_gender_value = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_hidden_roles_value = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_hidden_email_value = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_hidden_occupation_value = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_hidden_irc_value = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_hidden_languages_value = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_svn_email = NULL");
db_query("UPDATE {content_type_public_profile} SET field_profile_cvs_email = NULL");
db_query("DELETE FROM {variable} WHERE name = 'googlemap_api_key'");
db_query("DELETE FROM {variable} WHERE name = 'boost_crawler_key'");
db_query("DELETE FROM {variable} WHERE name = 'flickr_api_secret'");
db_query("DELETE FROM {variable} WHERE name = 'flickr_api_key'");
db_query("DELETE FROM {variable} WHERE name = 'flickr_default_userid'");
db_query("DELETE FROM {variable} WHERE name = 'drupal_private_key'");
db_query("DROP TABLE IF EXISTS {captcha_points}");
db_query("DROP TABLE IF EXISTS {captcha_sessions}");

print("Finished sanitization of mozcrm_drupal database.<BR><BR>");
}
function _sanitize_mozcrm_civicrm() {
print("Starting sanitization of mozcrm_civicrm database.<BR><BR>");
db_query("TRUNCATE {civicrm_cache}");
db_query("TRUNCATE {civicrm_contribution}");
db_query("TRUNCATE {civicrm_contribution_recur}");
db_query("TRUNCATE {civicrm_contribution_financial_trxn}");
db_query("TRUNCATE {civicrm_payment_processor}");
db_query("UPDATE {civicrm_address} SET street_address = NULL");
db_query("UPDATE {civicrm_address} SET street_number = NULL");
db_query("UPDATE {civicrm_address} SET street_number_suffix = NULL");
db_query("UPDATE {civicrm_address} SET street_number_predirectional = NULL");
db_query("UPDATE {civicrm_address} SET street_name = NULL");
db_query("UPDATE {civicrm_address} SET street_type = NULL");
db_query("UPDATE {civicrm_address} SET street_number_postdirectional = NULL");
db_query("UPDATE {civicrm_address} SET street_unit = NULL");
db_query("UPDATE {civicrm_address} SET supplemental_address_1 = NULL");
db_query("UPDATE {civicrm_address} SET supplemental_address_2 = NULL");
db_query("UPDATE {civicrm_address} SET supplemental_address_3 = NULL");
db_query("UPDATE {civicrm_address} SET city = NULL");
db_query("UPDATE {civicrm_address} SET county_id = NULL");
db_query("UPDATE {civicrm_address} SET state_province_id = NULL");
db_query("UPDATE {civicrm_address} SET postal_code_suffix = NULL");
db_query("UPDATE {civicrm_address} SET postal_code = NULL");
db_query("UPDATE {civicrm_address} SET usps_adc = NULL");
db_query("UPDATE {civicrm_address} SET country_id = NULL");
db_query("UPDATE {civicrm_address} SET geo_code_1 = NULL");
db_query("UPDATE {civicrm_address} SET geo_code_2 = NULL");
db_query("UPDATE {civicrm_address} SET timezone = NULL");
db_query("UPDATE {civicrm_address} SET name = NULL");
db_query("UPDATE {civicrm_contact} SET name = NULL");
db_query("UPDATE {civicrm_contact} SET sort_name = 'lastname, firstname'");
db_query("UPDATE {civicrm_contact} SET display_name = 'lastname, firstname'");
db_query("UPDATE {civicrm_contact} SET nickname = NULL");
db_query("UPDATE {civicrm_contact} SET legal_name = NULL");
db_query("UPDATE {civicrm_contact} SET home_URL = NULL");
db_query("UPDATE {civicrm_contact} SET image_URL = NULL");
db_query("UPDATE {civicrm_contact} SET preferred_communication_method = NULL");
db_query("UPDATE {civicrm_contact} SET source = NULL");
db_query("UPDATE {civicrm_contact} SET first_name = 'firstname'");
db_query("UPDATE {civicrm_contact} SET middle_name = NULL");
db_query("UPDATE {civicrm_contact} SET last_name = 'lastname'");
db_query("UPDATE {civicrm_contact} SET prefix_id = NULL");
db_query("UPDATE {civicrm_contact} SET suffix_id = NULL");
db_query("UPDATE {civicrm_contact} SET greeting_type_id = NULL");
db_query("UPDATE {civicrm_contact} SET custom_greeting = NULL");
db_query("UPDATE {civicrm_contact} SET job_title = NULL");
db_query("UPDATE {civicrm_contact} SET gender_id = NULL");
db_query("UPDATE {civicrm_contact} SET birth_date = NULL");
db_query("UPDATE {civicrm_contact} SET mail_to_household_id = NULL");
db_query("UPDATE {civicrm_contact} SET household_name = NULL");
db_query("UPDATE {civicrm_contact} SET primary_contact_id = NULL");
db_query("UPDATE {civicrm_contact} SET organization_name = NULL");
db_query("UPDATE {civicrm_contact} SET sic_code = NULL");
db_query("UPDATE {civicrm_contact} SET user_unique_id = NULL");
db_query("UPDATE {civicrm_contact} SET employer_id = NULL");
db_query("UPDATE {civicrm_contact} SET api_key = NULL");
db_query("UPDATE {civicrm_contribution_widget} SET url_homepage = NULL");
db_query("UPDATE {civicrm_email} SET mail = CONCAT(civicrm_email.contact_id, '@testmail.drumbeat.org')");
db_query("UPDATE {civicrm_im} SET name = NULL");
db_query("UPDATE {civicrm_phone} SET phone = NULL");
db_query("UPDATE {civicrm_uf_group} SET post_URL = NULL");
db_query("UPDATE {civicrm_uf_group} SET notify = NULL");
db_query("UPDATE {civicrm_uf_match} SET uf_name = NULL");
db_query("UPDATE {civicrm_value_profile_fields_4} SET preferred_name_18 = NULL");
db_query("UPDATE {civicrm_value_profile_fields_4} SET organizations_20 = NULL");
db_query("UPDATE {civicrm_value_profile_fields_4} SET nationality_21 = NULL");
db_query("UPDATE {civicrm_value_profile_fields_4} SET mail_address_24 = NULL");
db_query("UPDATE {civicrm_value_profile_fields_4} SET contributer_start_28 = NULL");
db_query("UPDATE {civicrm_value_profile_fields_4} SET contact_email_29 = NULL");
db_query("UPDATE {civicrm_value_profile_fields_4} SET bugzilla_email_30 = NULL");
db_query("UPDATE {civicrm_value_profile_fields_4} SET irc_nick_31 = NULL");
db_query("UPDATE {civicrm_value_profile_fields_4} SET blog_url_32 = NULL");
db_query("UPDATE {civicrm_value_profile_fields_4} SET twitter_33 = NULL");
db_query("UPDATE {civicrm_value_profile_fields_4} SET linkedin_34 = NULL");
db_query("UPDATE {civicrm_value_profile_fields_4} SET social_networks_35 = NULL");
db_query("UPDATE {civicrm_value_profile_fields_4} SET languages_36 = NULL");
db_query("UPDATE {civicrm_value_profile_fields_4} SET skills_37 = NULL");
db_query("UPDATE {civicrm_value_profile_fields_4} SET rolls_38 = NULL");
db_query("UPDATE {civicrm_value_profile_fields_4} SET projects_39 = NULL");
db_query("UPDATE {civicrm_value_profile_fields_4} SET programming_languages_40 = NULL");
db_query("UPDATE {civicrm_value_profile_fields_4} SET operating_system_41 = NULL");
db_query("UPDATE {civicrm_value_profile_fields_4} SET cvs_access_44 = NULL");
db_query("UPDATE {civicrm_value_profile_fields_4} SET svn_access_45 = NULL");
db_query("UPDATE {civicrm_value_profile_fields_4} SET hg_account_47 = NULL");
print("Finished sanitization of mozcrm_civicrm database.<BR><BR>");
}

Retrieved from "https://wiki.allizom.org/index.php?title=Drumbeat/PaulBooker/An_example_santization_script_for_a_drupal_site_running_CiviCRM&oldid=219662"