3 namespace Drush\Commands\core;
5 use Drupal\Component\Utility\Random;
6 use Drupal\Core\Database\Database;
7 use Drush\Commands\DrushCommands;
10 * Class SanitizeCommands
11 * @package Drush\Commands\core
13 class SanitizeCommands {
17 * Whether database table names should be wrapped in brackets for prefixing.
22 * Sets $this->wrap to TRUE if a db-prefix is set with drush.
24 protected function setWrap() {
25 $this->wrap = $wrap_table_name = (bool) drush_get_option('db-prefix');
30 * Sanitize the database by removed and obfuscating user data.
32 * @command sql-sanitize
34 * @todo "drush dependencies" array('sqlsync')
36 * @bootstrap DRUSH_BOOTSTRAP_NONE
37 * @description Run sanitization operations on the current database.
38 * @option db-prefix Enable replacement of braces in sanitize queries.
39 * @option db-url A Drupal 6 style database URL. E.g.,
40 * mysql://root:pass@127.0.0.1/db
41 * @option sanitize-email The pattern for test email addresses in the
42 * sanitization operation, or "no" to keep email addresses unchanged. May
43 * contain replacement patterns %uid, %mail or %name. Example value:
45 * @option sanitize-password The password to assign to all accounts in the
46 * sanitization operation, or "no" to keep passwords unchanged. Example
48 * @option whitelist-fields A comma delimited list of fields exempt from sanitization.
50 * @usage drush sql-sanitize --sanitize-password=no
51 * Sanitize database without modifying any passwords.
52 * @usage drush sql-sanitize --whitelist-fields=field_biography,field_phone_number
53 * Sanitizes database but exempts two user fields from modification.
54 * @see hook_drush_sql_sync_sanitize() for adding custom sanitize routines.
56 public function sqlSanitize($options = [
59 'sanitize-email' => '',
60 'sanitize-password' => '',
61 'whitelist-fields' => '',
63 drush_sql_bootstrap_further();
64 if ($options['db-prefix']) {
65 drush_bootstrap_max(DRUSH_BOOTSTRAP_DRUPAL_DATABASE);
68 // Drush itself implements this via sql_drush_sql_sync_sanitize().
69 drush_command_invoke_all('drush_sql_sync_sanitize', 'default');
70 $operations = drush_get_context('post-sync-ops');
71 if (!empty($operations)) {
72 if (!drush_get_context('DRUSH_SIMULATE')) {
73 $messages = _drush_sql_get_post_sync_messages();
76 drush_print($messages);
79 $queries = array_column($operations, 'query');
80 $sanitize_query = implode(" ", $queries);
82 if (!drush_confirm(dt('Do you really want to sanitize the current database?'))) {
83 return drush_user_abort();
86 if ($sanitize_query) {
87 $sql = drush_sql_get_class();
88 $sanitize_query = $sql->query_prefix($sanitize_query);
89 $result = $sql->query($sanitize_query);
91 throw new \Exception(dt('Sanitize query failed.'));
97 * Performs database sanitization.
99 * @param int $major_version
102 public function doSanitize($major_version) {
104 $this->sanitizeSessions();
106 if ($major_version == 8) {
107 $this->sanitizeComments();
108 $this->sanitizeUserFields();
113 * Sanitize string fields associated with the user.
115 * We've got to do a good bit of SQL-foo here because Drupal services are
118 public function sanitizeUserFields() {
119 /** @var SqlBase $sql_class */
120 $sql_class = drush_sql_get_class();
121 $tables = $sql_class->listTables();
122 $whitelist_fields = (array) explode(',', drush_get_option('whitelist-fields'));
124 foreach ($tables as $table) {
125 if (strpos($table, 'user__field_') === 0) {
126 $field_name = substr($table, 6, strlen($table));
127 if (in_array($field_name, $whitelist_fields)) {
131 $output = $this->query("SELECT data FROM config WHERE name = 'field.field.user.user.$field_name';");
132 $field_config = unserialize($output[0]);
133 $field_type = $field_config['field_type'];
134 $randomizer = new Random();
136 switch ($field_type) {
139 $this->sanitizeTableColumn($table, $field_name . '_value', $randomizer->name(10) . '@example.com');
143 $this->sanitizeTableColumn($table, $field_name . '_value', $randomizer->name(255));
147 $this->sanitizeTableColumn($table, $field_name . '_value', $randomizer->sentences(1));
151 $this->sanitizeTableColumn($table, $field_name . '_value', '15555555555');
155 $this->sanitizeTableColumn($table, $field_name . '_value', $randomizer->paragraphs(2));
159 $this->sanitizeTableColumn($table, $field_name . '_value', $randomizer->paragraphs(10));
162 case 'text_with_summary':
163 $this->sanitizeTableColumn($table, $field_name . '_value', $randomizer->paragraphs(2));
164 $this->sanitizeTableColumn($table, $field_name . '_summary', $randomizer->name(255));
172 * Replaces all values in given table column with the specified value.
174 * @param string $table
175 * The database table name.
176 * @param string $column
177 * The database column to be updated.
181 public function sanitizeTableColumn($table, $column, $value) {
182 $table_name_wrapped = $this->wrapTableName($table);
183 $sql = "UPDATE $table_name_wrapped SET $column='$value';";
184 drush_sql_register_post_sync_op($table.$column, dt("Replaces all values in $table table with the same random long string."), $sql);
188 * Truncates the session table.
190 public function sanitizeSessions() {
191 // Seems quite portable (SQLite?) - http://en.wikipedia.org/wiki/Truncate_(SQL)
192 $table_name = $this->wrapTableName('sessions');
193 $sql_sessions = "TRUNCATE TABLE $table_name;";
194 drush_sql_register_post_sync_op('sessions', dt('Truncate Drupal\'s sessions table'), $sql_sessions);
198 * Sanitizes comments_field_data table.
200 public function sanitizeComments() {
202 $comments_enabled = $this->query("SHOW TABLES LIKE 'comment_field_data';");
203 if (!$comments_enabled) {
207 $comments_table = $this->wrapTableName('comment_field_data');
208 $sql_comments = "UPDATE $comments_table SET name='Anonymous', mail='', homepage='http://example.com' WHERE uid = 0;";
209 drush_sql_register_post_sync_op('anon_comments', dt('Remove names and email addresses from anonymous user comments.'), $sql_comments);
211 $sql_comments = "UPDATE $comments_table SET name=CONCAT('User', `uid`), mail=CONCAT('user+', `uid`, '@example.com'), homepage='http://example.com' WHERE uid <> 0;";
212 drush_sql_register_post_sync_op('auth_comments', dt('Replace names and email addresses from authenticated user comments.'), $sql_comments);
216 * Wraps a table name in brackets if a database prefix is being used.
218 * @param string $table_name
219 * The name of the database table.
222 * The (possibly wrapped) table name.
224 public function wrapTableName($table_name) {
226 $processed = '{' . $table_name . '}';
229 $processed = $table_name;
236 * Executes a sql command using drush sqlq and returns the output.
238 * @param string $query
239 * The SQL query to execute. Must end in a semicolon!
242 * The output of the query.
244 protected function query($query) {
245 $current = drush_get_context('DRUSH_SIMULATE');
246 drush_set_context('DRUSH_SIMULATE', FALSE);
247 $sql = drush_sql_get_class();
248 $success = $sql->query($query);
249 $output = drush_shell_exec_output();
250 drush_set_context('DRUSH_SIMULATE', $current);