Drush provides a feature called sql-sanitize that helps to delete data that shouldn't leave a production environment.

A drush file with these contents are all you should need.

function password_policy_drush_sql_sync_sanitize($site) {
  drush_sql_register_post_sync_op('password_policy_history',
    dt('Delete all history table entries (depending on the site config, may contain sensitive data).'),
    "TRUNCATE password_policy_history;");
  drush_sql_register_post_sync_op('password_policy_force_change',
    dt('Delete all force change table entries (depending on the site config, may contain sensitive data).'),
    "TRUNCATE password_policy_force_change;");
}

If there are other tables that webform stores data in, then please do add them in a similar fashion.

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

greggles created an issue. See original summary.

AohRveTPV’s picture

SQLite doesn't seem to have TRUNCATE or TRUNCATE TABLE. Do you know if DELETE FROM would work just as well?

I am interested in this feature as currently I have Bash script which sanitizes those tables using direct SQL.

greggles’s picture

Seems fine to me. Delete can be a little slower than a truncate, but speed is not likely to be critical.

AohRveTPV’s picture

I think there might be a problem with this Drush feature. If the query is DELETE FROM password_policy_history;, the rows are deleted as expected when using drush sql-sanitize. I assume that will not work when table prefixes are used and the --db-prefix option is passed to sql-sanitize, so I made the query instead DELETE FROM {password_policy_history};. However, with that query, if not using table prefixes, the rows are not deleted when using drush sql-sanitize.

I think table prefixes are less commonly used, but it would be insecure for the sanitize command to just not work for some configurations.

greggles’s picture

Which version of drush are you using?

AohRveTPV’s picture

Git master, just pulled. Maybe this is a bug that does not exist in other, release branches?

Non-working patch attached. I could drop the curly braces, but then it wouldn't work for table prefixes, presumably.

Note that this patch is for 7.x-2.x. The code you gave is for 7.x-1.x (different tables). We can add this to 7.x-1.x too.

AohRveTPV’s picture

FWIW, I dropped the text "(depending on the site config, may contain sensitive data)" because I think the password_policy_history table almost always has sensitive data (hashes of users' previous passwords). They are stored by the module even if the history constraint is not used.

AohRveTPV’s picture

NancyDru’s picture

What about people that don't Drush?