data masking queries.

maskdata.sql

USE ushahidi;

SET NAMES utf8;

CREATE TABLE IF NOT EXISTS `maskdatas` (
	`id` int(11) unsigned NOT NULL auto_increment,
	`fromdata` varchar(255) default NULL,
	`todata` varchar(255) default NULL,
	PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO maskdatas (fromdata ,todata) VALUES ('0','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('1','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('2','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('3','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('4','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('5','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('6','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('7','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('8','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('9','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('０','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('１','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('２','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('３','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('４','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('５','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('６','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('７','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('８','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('９','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('〇','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('一','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('二','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('三','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('四','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('五','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('六','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('七','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('八','*');
INSERT INTO maskdatas (fromdata ,todata) VALUES ('九','*');

delimiter //
CREATE FUNCTION maskdata(txt LONGTEXT) RETURNS LONGTEXT
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE f,t varchar(255);
  DECLARE result TEXT;
  DECLARE mc CURSOR FOR SELECT fromdata,todata FROM maskdatas;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  
  IF txt IS NULL THEN
    RETURN NULL;
  END IF;

  OPEN mc;
  SET result = txt;
  REPEAT
    FETCH mc INTO f, t;
    IF done < 1 THEN
      SET result = REPLACE(result,f,t);
    END IF;
  UNTIL 0 < done END REPEAT;
  CLOSE mc;
  RETURN result;
END;

CREATE PROCEDURE maskusers()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE c INT DEFAULT 0;
  DECLARE i INT(11) DEFAULT 0;
  DECLARE mc CURSOR FOR SELECT id from users order by id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN mc;
  REPEAT
    FETCH mc INTO i;
    IF done < 1 THEN
      SET c = c + 1;
      UPDATE users SET name='モデレータ',email= CONCAT('mod' ,c ,'@example.com'),username = CONCAT('mod',c) WHERE id = i;
    END IF;
  UNTIL 0 < done END REPEAT;
  CLOSE mc;
END;
//
delimiter ;


UPDATE alert SET alert_recipient = '[email protected]';
UPDATE comment SET comment_author='comment_author',comment_email = '[email protected]',comment_description=maskdata(comment_description);
UPDATE idp SET idp_idnumber='0',idp_orig_idnumber='1',idp_fname='idp_fname',idp_lname='idp_lname',idp_email='[email protected]',idp_phone=maskdata(idp_phone);
UPDATE incident SET incident_title=maskdata(incident_title),incident_description=maskdata(incident_description);
UPDATE incident_lang SET incident_title=maskdata(incident_title),incident_description=maskdata(incident_description);
UPDATE incident_person SET person_first ='太郎',person_last='災害',person_email='[email protected]',person_phone=maskdata(person_phone);
UPDATE media SET media_title=maskdata(media_title),media_description=maskdata(media_description);
UPDATE message SET service_messageid='svc_messageid',message_from='msgfrom',message_to='msgto', message=maskdata(message),message_detail=maskdata(message_detail);
UPDATE reporter SET service_userid='service_userid', service_account='svc_account', reporter_first='次郎',reporter_last='震災', reporter_email = '[email protected]',reporter_phone=maskdata(reporter_phone);
UPDATE sharing_incident SET incident_title=maskdata(incident_title);
UPDATE translatereports SET incident_description=maskdata(incident_description);
CALL maskusers();
UPDATE verified SET verified_comment=maskdata(verified_comment);