Sanitize-email update users.init column

Yes, I agree that we should sanitize this.

Attached is a simple fix. It involves two additional passes on the {user} table. But I thought this was preferable to some fairly messy SQL, that would need to be different for MySQL and Postgres.

I don't think I set the version right. This is a patch for 6.0-dev.

Attached is an updated version that (a) makes sure we are sanitizing emails, and (b) goes a step further and sanitizes user names that look like emails.

... And fixing a message to be consistent ("email" instead of "mail").

I think this is a better version.

This looks like a good idea. If users change their email address, though, then init != mail, and init will not be sanitized. Would it make more sense to just invariantly set init to mail after sanitizing mail?

It's really hard to say what the "right" thing is to do. If you use bakery and want to sanitize a subsite then init will contain http://example.com/user/1/edit - is it appropriate to clear out the init column in that case?

I think #4 attempts to retain some data if that's appropriate, but as #6 points out that might leave some emails exposed. I think it would be best to just munge the init column 100% and/or move that to http://drupal.org/project/paranoia

Yes, the init = mail check does not catch everything. But it will catch quite a bit more that we don't current catch. So, while it's not perfect, it is better than what we have.

The other option is to write a LIKE to find email addresses. But as soon as we do this, someone will need to test the SQL on Postgres.

This will look for email addresses like foo@bar and ignore it if it's within a serialized string.

UPDATE users SET init = 'sql-sync' WHERE init LIKE '%@%' AND init NOT LIKE '%:%';

FYI, any module that does something different with init should implement hook_drush_sql_sync_sanitize.

I think that LIKE '%@%' AND init NOT LIKE '%:%' is good. It is unfortunate that bakery and other modules overload the users init field, but I suppose there is nothing we can do about that here.

re-rolled with new WHERE clause ....

I think we should just set init to the new value of mail and be done with it. We don't need an extra query just for init. if bakery module is too lazy to add own column or join to own table, then those sites will need to write own --my-sanitize routine (if they actually care abut preserving the init column).

@moshe, are you also against the user name UPDATE?

Attached is a patch that just sets init, but also resets name's that look like email addresses.

We should leave username alone IMO.

Ok, attached patch fixes only the init issue per Moshe's request/suggestion.

I'm a bit frustrated that the additional sanitation code isn't making it into drush. For anyone else interested in this, here is the custom code to do the additional sanitation, just put this in your own sample.drush.inc.

/**
 * Implements hook_drush_sql_sync_sanitize().
 */
function sample_drush_sql_sync_sanitize($source) {
  drush_sql_register_post_sync_op('stratfor-user-name', dt('Reset email based names in user table'), "UPDATE users SET name = mail WHERE name LIKE '%@%';");
  drush_sql_register_post_sync_op('stratfor-user-init', dt('Reset customer init in user table'), "UPDATE users SET init = NULL;");
  drush_sql_register_post_sync_op("stratfor-vars", dt('Reset variables'), "UPDATE variables SET value = NULL WHERE name IN ('cron_key')");
}

@douggreen - I feel your frustration, a similar discussion happened in #1565686: make drush sql sanitize more thorough which is part of why I suggested earlier that you make your work a patch to paranoia. It would be totally appropriate there to mess with usernames. I hope you will make a followup to paranoia.

Committed to 5 and 6. Thanks.

I created #1884338: more sanitizing - usernames/emails in paranoia so we don't forget it.