Anonimizzare le email di un database con uno script

È possibile anonimizzare un intero database, colonna per colonna, riga per riga, cella per cella usando un semplice script.

Lo script che vi propongo è stato scritto in PHP, in collaborazione con BriganteMik.

Prima di lanciarlo è necessario solo configurare opportunamente i parametri di accesso al database.

<?php

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "dbname";
$destinatario = "myemail.com"; // ES: "tolkien.us" will be <random-name>@tolkien.us


function email_cleanup($servername, $username, $password, $dbname){
    
    // Connecting to database
    $conn = new mysqli($servername, $username, $password, $dbname);
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } else {
        print("Connected to db: ".$dbname);
    }

    $table_query = "SHOW TABLES";
    $result = $conn->query($table_query);

    while($row = $result->fetch_assoc()) {
        $table_name =  ($row['Tables_in_'. $dbname]);
        $columns_query = 'SHOW COLUMNS FROM ' . $table_name . '  WHERE (TYPE LIKE "VARCHAR%")'; // OR TYPE LIKE "TEXT")';
        $result_column = $conn->query($columns_query);
        var_dump("TABLE: " . $table_name); 
            while( $row_column =  $result_column->fetch_assoc() ) {
                $data_query = "UPDATE ". $table_name . " SET " . $row_column['Field'] . " = 
                    REPLACE(
                        " . $row_column['Field'] . ", 
                        SUBSTRING(
                            " . $row_column['Field'] . ", 
                            INSTR(" . $row_column['Field'] . ", '@') - 1
                        ), 
                        CONCAT(FLOOR(RAND() * (1000) +1),'@" . $destinatario . "'))
                    WHERE " . $row_column['Field'] . " RLIKE '[A-Z0-9._-]+@[A-Z0-9.-]+\.[A-Z]{2,4}'" ;
                $conn->query($data_query);
                var_dump("Query executed: $data_query");
            }
        }
    $conn->close();
}

Invio email via SMTP su AWS con SES

In questo articolo illustro come si fa, senza dare di matto, a inviare email da un’istanza EC2 via SMTP con il servizio SES (Simple Email System) di Amazon AWS.

Nozioni generali

Prima di continuare con la lettura, è importante fare alcune premesse per chiarire alcuni punti fondamentali.

  1. Non si può inviare posta da una EC2 o in generale da AWS senza passare per il servizio SES;
  2. Per ogni regione AWS esiste uno e uno solo server SMTP;
  3. Non tutte le regioni hanno il servizio SMTP via SES abilitato;
  4. È possibile usare il server SMTP di una regione anche da istanze che non sono su quella regione (es Server SMTP su eu-west-1 e invio da EC2 a eu-south-1);
  5. Gli indirizzi di posta elettronica esterni che si intende usare vanno validati via SES;
  6. L’utente, le cui credenziali vengono usate per inviare per inviare posta, deve essere un utente IAM di AWS;
  7. La password da usare nell’SMTP relativa ad un certo utente, NON è la sua secret key, per ottenere la password da usare nell’SMTP (questa cosa è cambiata a partire dagli inizi del 2019).

Convertire la secret key di un utente IAM in password per SMTP

Per convertire la secret key di un utente in password da usare per l’invio di posta via SMTP è necessario utilizzare un algoritmo codificato in uno script.

ATTENZIONE
Lo script è stato prelevato da Obtaining your Amazon SES SMTP credentials, riferirsi a questo link per eventuali aggiornamenti.

Creare un file .py (anche sulla propria macchina) e assegnargli permessi di esecuzione:

touch aws-smtp-convert.py
chmod a+x aws-smtp-convert.py

Aprire un editor (vim, nano, gedit eccetera) e incollare il seguente codice (controlla prima sempre se esistono eventuali aggiornamenti al link https://docs.aws.amazon.com/ses/latest/DeveloperGuide/smtp-credentials.html?icmpid=docs_ses_console)

#!/usr/bin/env python3

import hmac
import hashlib
import base64
import argparse

SMTP_REGIONS = [
    'us-east-2',       # US East (Ohio)
    'us-east-1',       # US East (N. Virginia)
    'us-west-2',       # US West (Oregon)
    'ap-south-1',      # Asia Pacific (Mumbai)
    'ap-northeast-2',  # Asia Pacific (Seoul)
    'ap-southeast-1',  # Asia Pacific (Singapore)
    'ap-southeast-2',  # Asia Pacific (Sydney)
    'ap-northeast-1',  # Asia Pacific (Tokyo)
    'ca-central-1',    # Canada (Central)
    'eu-central-1',    # Europe (Frankfurt)
    'eu-west-1',       # Europe (Ireland)
    'eu-west-2',       # Europe (London)
    'sa-east-1',       # South America (Sao Paulo)
    'us-gov-west-1',   # AWS GovCloud (US)
]

# These values are required to calculate the signature. Do not change them.
DATE = "11111111" 
SERVICE = "ses" 
MESSAGE = "SendRawEmail" 
TERMINAL = "aws4_request" 
VERSION = 0x04

def sign(key, msg):
    return hmac.new(key, msg.encode('utf-8'), hashlib.sha256).digest()

def calculate_key(secret_access_key, region):
    if region not in SMTP_REGIONS:
        raise ValueError(f"The {region} Region doesn't have an SMTP endpoint.")

    signature = sign(("AWS4" + secret_access_key).encode('utf-8'), DATE)
    signature = sign(signature, region)
    signature = sign(signature, SERVICE)
    signature = sign(signature, TERMINAL)
    signature = sign(signature, MESSAGE)
    signature_and_version = bytes([VERSION]) + signature
    smtp_password = base64.b64encode(signature_and_version)
    return smtp_password.decode('utf-8')

def main():
    parser = argparse.ArgumentParser(
        description='Convert a Secret Access Key for an IAM user to an SMTP password.')
    parser.add_argument(
        'secret', help='The Secret Access Key to convert.')
    parser.add_argument(
        'region',
        help='The AWS Region where the SMTP password will be used.',
        choices=SMTP_REGIONS)
    args = parser.parse_args()
    print(calculate_key(args.secret, args.region))

if __name__ == '__main__':
    main()

Quindi lanciare il comando:

./aws-smtp-convert.py <IAM-user-Secret-key> <region-name>Modifica questa sezione

Test invio email via PHP

Lo script è basato sulla libreria PHPMAILER ottenibile via composer,

composer require phpmailer/phpmailer

quindi collegando (require) le classi Exception, PHPMailer ed SMTP.

<?php

use PHPMailer\PHPMailer\PHPMailer;
require 'vendor/autoload.php';

require 'vendor/phpmailer/phpmailer/src/Exception.php';
require 'vendor/phpmailer/phpmailer/src/PHPMailer.php';
require 'vendor/phpmailer/phpmailer/src/SMTP.php';

$mail = new PHPMailer;

// SMTP configuration
$mail->isSMTP();

// AWS eu-west-1
$mail->Host     = 'email-smtp.eu-west-1.amazonaws.com';
$mail->SMTPAuth = true;
$mail->Username = '<IAM-user-name>';
$mail->Password = '<password-decodificata-dalla-secret-key-utente-IAM>'; //decoded password see python script @ https://docs.aws.amazon.com/ses/late>
$mail->SMTPSecure = 'tls';
$mail->Port     = 587;

$mail->setFrom('<from-email>', 'Test');
$mail->addReplyTo('<reply-to-email>', 'Test');

// Add a recipient
$mail->addAddress('<receiver-1>');
$mail->addAddress('<receiver-2>');

// Add cc or bcc 
//$mail->addCC('cc@example.com');
//$mail->addBCC('bcc@example.com');

// Email subject
$mail->Subject = 'Send Email via SMTP using PHPMailer';

// Set email format to HTML
$mail->isHTML(true);

// Email body content
$mailContent = '
    <h2>Hello Dude!</h2>
    <p>Here it is a test email</p>';
$mail->Body = $mailContent;

// Send email
if(!$mail->send()){
    echo 'Message could not be sent.';
    echo 'Mailer Error: ' . $mail->ErrorInfo;
}else{
    echo 'Message has been sent';
}

Outlook messaggi in posta in uscita nonostante inviati

La solita follia che capita a programmi del gruppo Microsoft che dovrebbero avere una maturità tale da essere quasi esenti da bug.

Io non capisco come sia possibile che un’azienda di quel calibro tiri fuori dei prodotti che a volte si perdono in un bicchiere d’acqua.

Il problema

Una volta configurato in IMAP e SMTP l’account all’atto di inviare un messaggio si nota che:

  1. Il messaggio è correttamente inviato
  2. Il messaggio permane nella posta in uscita
  3. La cartella posta inviata non c’è in locale

La soluzione

Troppo facile cambiare client di posta, meglio provare a:

  1. Raggiungere “Impostazioni account”
  2. Cliccare su “Impostazioni avanzate”
  3. E quindi sulla scheda “Impostazioni account avanzate”
  4. Nel campo “Radice posta…” inserire “Inbox” (senza virgolette)

In pratica il software non riesce a riconoscere in maniera autonoma l’alberatura delle cartelle del server e bisogna suggeriglielo … piccola nota “Inbox” è praticamente lo standard!!

Sembra che il problema si verifichi tanto su Outlook 2013 quanto su Outlook 2013.

Che follia …

Spero che quanto scritto torni utile
Diversamente avete tutta la mia solidarietà.