anonymization.sql

-- anonymize the following tables

UPDATE `users`
SET
    name = CONCAT('Anonymized ', id),
    email = CONCAT('anonymized+', id, "@example.com"),
    password = SHA1(CONCAT('secret', id, RAND()))
WHERE 1;

UPDATE `ls_sponsors`
SET
    first_name = CONCAT('Anonymized ', id),
    infix = '',
    last_name = 'Anonymous',
    gender = IF((RAND() > 0.5) , 'F', 'M'),
    postal_code = CONCAT(ROUND(RAND()*1000), 'AA'),
    house_number = ROUND(RAND()*10),
    house_number_suffix = '',
    street = 'Some Street',
    city = 'Somewhere',
    phone_number = CONCAT('+316X', ROUND(RAND() * 1000000), '00'),
    date_of_birth = FROM_UNIXTIME(RAND() * (UNIX_TIMESTAMP('2019-01-01') - UNIX_TIMESTAMP('2009-01-01')) + UNIX_TIMESTAMP('2009-01-01')),
    mailed_monthly = IF(
        (RAND() > 0.7),
        FROM_UNIXTIME(RAND() * (UNIX_TIMESTAMP('2019-01-01') - UNIX_TIMESTAMP('2009-01-01')) + UNIX_TIMESTAMP('2009-01-01')),
        null
    )
WHERE 1;


UPDATE `ls_sponsorships`
SET
    account_holder = CONCAT('Anonymized ', id),
    iban = CONCAT('NL', ROUND(RAND() * 1000), 'BANK', '00', ROUND(RAND() * 1000000))
WHERE 1;

                        
UPDATE companies
SET
    name = CONCAT('Bedrijf ', LEFT(MD5(RAND()), 8)),
    address = CONCAT('Straatweg ', ROUND(RAND()*10)),
    zipcode = CONCAT(ROUND(RAND()*1000), 'AA'),
    city = 'Ergens',
    country_id = 150,
    default_language = 'nld',
    search_name = CONCAT(number, ' ', name),
    `cs-responsible` = CONCAT('A', id, ' Anoniem')
WHERE
    (
        name NOT LIKE '%something%'
        AND 
        name NOT LIKE '%somethingelse%'
    );

UPDATE companies
SET
    name = CONCAT('something ', LEFT(MD5(RAND()), 8)),
    address = CONCAT('Straatweg ', ROUND(RAND()*10)),
    zipcode = CONCAT(ROUND(RAND()*1000), 'AA'),
    city = 'Ergens',
    country_id = 150,
    default_language = 'nld',
    search_name = CONCAT(number, ' ', name),
    `cs-responsible` = CONCAT('A', id, ' something')
WHERE
    (
        name LIKE '%something%'
    );


UPDATE orders 
SET 
    user_email = concat('anoniem_', id, '@example.com'),
    user_phone_nr = CONCAT('+316X', ROUND(RAND() * 1000000), '00')
WHERE ( 
    (
        user_email NOT LIKE '%@something.nl'
        AND user_email NOT LIKE '%@something.eu'
        AND user_email NOT LIKE '%@example.com'
    )
    OR user_email = ''
    OR user_email IS null
);

UPDATE user_passwords
SET
    password = SHA1(CONCAT('secret', id, RAND()))
WHERE 1;

UPDATE users
SET
    password = SHA1(CONCAT('secret', id, RAND())),
    first_name = CONCAT('A', id),
    last_name = 'Anoniem',
    pre_position = IF((RAND() > 0.6) , 'van', ''),
    email = concat('anoniem_', id, '@example.com'),
    phone_nr = CONCAT('+316X', ROUND(RAND() * 1000000), '00'),
    gender = IF((RAND() > 0.5), 0, 1),
    date_of_birth = FROM_UNIXTIME(RAND() * (UNIX_TIMESTAMP('2019-01-01') - UNIX_TIMESTAMP('2009-01-01')) + UNIX_TIMESTAMP('2009-01-01')),
    ip_address = '127.0.0.1',
    country_id = 150,
    language = 'nld',
    tokenhash = null,
    full_name = CONCAT('A', id, ' Anoniem'),
    userkey = concat('anoniem', id)
WHERE ( 
    (
        email NOT LIKE '%@something.nl'
        AND email NOT LIKE '%@something.eu'
        AND email NOT LIKE '%@example.com'
    )
    OR email = ''
    OR email IS null
);

UPDATE users
SET
    password = SHA1(CONCAT('secret', id, RAND())),
    first_name = CONCAT('A', id),
    last_name = 'something',
    pre_position = IF((RAND() > 0.6) , 'van', ''),
    email = concat('anoniem_', id, '@example.com'),
    phone_nr = CONCAT('+316X', ROUND(RAND() * 1000000), '00'),
    gender = IF((RAND() > 0.5), 0, 1),
    date_of_birth = FROM_UNIXTIME(RAND() * (UNIX_TIMESTAMP('2019-01-01') - UNIX_TIMESTAMP('2009-01-01')) + UNIX_TIMESTAMP('2009-01-01')),
    ip_address = '127.0.0.1',
    country_id = 150,
    language = 'nld',
    tokenhash = null,
    full_name = CONCAT('A', id, ' something'),
    userkey = concat('anoniem', id)
WHERE ( 
    email LIKE '%@something.eu'
);
                                                                                                                          
                                                                                                                          UPDATE people
SET
    name_first_name = LEFT(id, 8),
    name_middle_name = NULL,
    name_last_name = 'Anoniem',
    email_address = IF(
        (RAND() > 0.7 OR (person_type != 'respondent')),
        CONCAT('anoniem+', LEFT(MD5(RAND()), 8), "@example.com"),
        null
    ),
    identification_number = IF(
        (person_type = 'respondent'),
        ROUND(RAND() * 200000000),
        null
    ),
    education_level_code = IF(
        (person_type = 'respondent'),
        IF((RAND() > 0.5) , 'vmbo', 'mavo'),
        null
    ),
    age = IF(
        (person_type = 'respondent'),
        ROUND((RAND() * 10) + 18),
        null
    )
WHERE
    email_address NOT LIKE '%@something.nl'
    OR email_address IS NULL
;