/lib/core/Tiki/Command/RedactDBCommand.php
PHP | 204 lines | 158 code | 23 blank | 23 comment | 7 complexity | 7a80481dbee7160adf4834fe74a00417 MD5 | raw file
- <?php
- // (c) Copyright by authors of the Tiki Wiki CMS Groupware Project
- //
- // All Rights Reserved. See copyright.txt for details and a complete list of authors.
- // Licensed under the GNU LESSER GENERAL PUBLIC LICENSE. See license.txt for details.
- // $Id$
- namespace Tiki\Command;
- use Symfony\Component\Console\Command\Command;
- use Symfony\Component\Console\Input\InputArgument;
- use Symfony\Component\Console\Input\InputInterface;
- use Symfony\Component\Console\Input\InputOption;
- use Symfony\Component\Console\Output\OutputInterface;
- class RedactDBCommand extends Command
- {
- protected function configure()
- {
- $this
- ->setName('database:redact')
- ->setDescription('Redact database')
- ->addOption(
- 'force',
- null,
- InputOption::VALUE_NONE,
- 'Force installation. Overwrite any current database.'
- );
- }
- protected function execute(InputInterface $input, OutputInterface $output)
- {
- // Not used, just a reminder
- $force = $input->getOption('force');
- // Here we go
- $tikilib = \TikiLib::lib('tiki');
- // For $dbs_tiki - better way?
- require('db/redact/local.php');
- $output->writeln('<info>Redacting database.</info>');
- // Reset admin account
- $output->writeln('<info>Resetting admin account.</info>');
- $query = "UPDATE users_users SET email = ? WHERE login='admin';";
- $bindvars = ['admin@example.com'];
- $result = $tikilib->query($query, $bindvars);
- $query = "UPDATE `users_users` SET `hash`= md5('admin') WHERE `login`='admin'";
- $result = $tikilib->query($query);
- // first get valid prefix
- $userprefix = 'user_';
- $userprefixready = false;
- $userprefixindex = 0;
- while (! $userprefixready) {
- $query = "SELECT count(*) FROM users_users WHERE login LIKE '" . $userprefix . "%';";
- $result = $tikilib->getOne($query);
- if ($result > 0) {
- $userprefixindex++;
- $userprefix = 'user' . $userprefixindex . '_';
- } else {
- $userprefixready = true;
- }
- }
- $output->writeln('<comment>Using user names like ' . $userprefix . '123.</comment>');
- // Pseudonymise e-mail
- $output->writeln('<comment>Pseudonymising user e-mails.</comment>');
- $query = " SELECT DISTINCT table_name
- FROM information_schema.columns
- WHERE column_name = 'email'
- AND table_name <> 'users_users'
- AND table_schema = '$dbs_tiki';";
- $result = $tikilib->query($query);
- $ret = [];
- while ($res = $result->fetchRow()) {
- $ret[] = $res;
- }
- foreach ($ret as $table) {
- unset($bindvars);
- $output->writeln('<info>' . $table['table_name'] . '</info>');
- $query = "UPDATE " . $table['table_name'] . " t, users_users u SET t.email = CONCAT('" . $userprefix . "', u.userId, '@example.com') WHERE t.email = u.email AND u.login <> 'admin';";
- $result = $tikilib->query($query);
- $query = "SET @newnum:=0;UPDATE " . $table['table_name'] . " t SET t.email = CONCAT('emailchanged', @newnum:=@newnum+1, '@example.com') WHERE t.email NOT LIKE '" . $userprefix . "%@example.com';";
- $result = $tikilib->query($query);
- }
- // Pseudonymise user name
- $output->writeln('<comment>Pseudonymising user names.</comment>');
- $query = " SELECT DISTINCT table_name
- FROM information_schema.columns
- WHERE column_name = 'user'
- AND table_name <> 'users_users'
- AND table_schema = '$dbs_tiki';";
- $result = $tikilib->query($query);
- $ret = [];
- while ($res = $result->fetchRow()) {
- $ret[] = $res;
- }
- foreach ($ret as $table) {
- unset($bindvars);
- $output->writeln('<info>' . $table['table_name'] . '</info>');
- $query = "UPDATE " . $table['table_name'] . " t, users_users u SET t.user = CONCAT('" . $userprefix . "', u.userId) WHERE t.user = u.login AND u.login <> 'admin';";
- $result = $tikilib->query($query);
- }
- // Pseudonymise user selector tracker fields
- $output->writeln('<comment>Pseudonymising user selector tracker fields.</comment>');
- $query = "SELECT fieldId, trackerId, name FROM tiki_tracker_fields WHERE type='u';";
- $result = $tikilib->query($query);
- $ret = [];
- while ($res = $result->fetchRow()) {
- $ret[] = $res;
- }
- foreach ($ret as $field) {
- unset($bindvars);
- $output->writeln('<info>Tracker ' . $field['trackerId'] . ' Field ' . $field['fieldId'] . ': ' . $field['name'] . '</info>');
- $trackername = $tikilib->getOne('SELECT name FROM tiki_trackers WHERE trackerId = ' . $field['trackerId'] . ';');
- $output->writeln('<comment>Consider removing data from Tracker ' . $field['trackerId'] . ' (' . $trackername . ').</comment>');
- $query = "UPDATE tiki_tracker_item_fields t, users_users u SET t.value = CONCAT('" . $userprefix . "', u.userId) WHERE t.value = u.login AND u.login <> 'admin';";
- $result = $tikilib->query($query);
- }
- // Final user pseudonymisation in users_users
- $query = "SELECT MAX(userId) FROM users_users;";
- $num = $tikilib->getOne($query);
- for ($i = 2; $i <= $num; $i++) {
- $query = "UPDATE `users_users` SET `email` = ?, `login` = ?, `hash`=md5( ? ) WHERE `userId` = ?";
- $bindvars = ["$userprefix$i@example.com", "$userprefix$i", "pass$i", $i];
- $result = $tikilib->query($query, $bindvars);
- // TODO : Update user avatars
- }
- $query = "UPDATE `users_users` SET `provpass` = '';";
- $result = $tikilib->query($query);
- // Remove user web-mail accounts
- $output->writeln('<info>Removing user mail accounts.</info>');
- $query = "DELETE FROM tiki_user_preferences WHERE prefName = 'cypht_user_config';";
- $result = $tikilib->query($query);
- $output->writeln('<info>Removing mail queue.</info>');
- $query = "TRUNCATE TABLE tiki_mail_queue;";
- $result = $tikilib->query($query);
- // Remove messu_messages
- $output->writeln('<info>Removing user messu.</info>');
- $query = "TRUNCATE TABLE messu_messages;";
- $result = $tikilib->query($query);
- // Remove all session data
- $output->writeln('<info>Removing session data.</info>');
- $query = "TRUNCATE TABLE sessions;";
- $result = $tikilib->query($query);
- $query = "TRUNCATE TABLE tiki_cookies;";
- $result = $tikilib->query($query);
- $query = "TRUNCATE TABLE tiki_sessions;";
- $result = $tikilib->query($query);
- // Remove payments
- $output->writeln('<info>Removing payments data.</info>');
- $query = "TRUNCATE TABLE tiki_payment_received;";
- $result = $tikilib->query($query);
- $query = "TRUNCATE TABLE tiki_payment_requests;";
- $result = $tikilib->query($query);
- // Remove DSN and mailin
- $output->writeln('<info>Removing DSN and mailin account data.</info>');
- $query = "TRUNCATE TABLE tiki_dsn;";
- $result = $tikilib->query($query);
- $query = "TRUNCATE TABLE tiki_mailin_accounts;";
- $result = $tikilib->query($query);
- // Remove auth tokens
- $output->writeln('<info>Removing auth tokens.</info>');
- $query = "TRUNCATE TABLE tiki_auth_tokens;";
- $result = $tikilib->query($query);
- // Remove web services
- $output->writeln('<info>Removing webservices info.</info>');
- $query = "TRUNCATE TABLE tiki_webservice;";
- $result = $tikilib->query($query);
- // Remove google, intertiki, ldap and 3rd party data
- $output->writeln('<info>Removing google, intertiki, ldap and other 3rd party app data.</info>');
- $query = "DELETE FROM tiki_preferences WHERE " .
- "name LIKE 'auth_ldap_%' OR " .
- "name LIKE '%key' OR " .
- "name LIKE '%secret' OR " .
- "name LIKE '%secr' OR " .
- "name LIKE '%client_id' OR " .
- "name LIKE '%application_id' OR " .
- "name LIKE '%access_token%' OR " .
- "name LIKE '%salt' OR " .
- "name = 'registerPasscode' OR " .
- "name = 'interlist' OR " .
- "name LIKE '%intertiki%';";
- $result = $tikilib->query($query);
- $output->writeln('<comment>Read the disclaimer!</comment>');
- $output->writeln('<comment>The following means jack:</comment>');
- $output->writeln('<info>Finished redacting database.</info>');
- }
- }