3
votes

Comment remplacer par lots des chaînes dans un champ en SQL

Contexte et objectif

J'essaie de pseudo-anonymiser certains numéros de produits dans mon tableau de données. Consultez l'exemple de code ci-dessous. Les numéros de produit sont composés de 10 chiffres et peuvent ou non être uniques pour le tableau.

Puisqu'il existe potentiellement d'autres tables vers lesquelles je voudrais créer un lien, j'aimerais utiliser une manière non aléatoire de pseudo-anonymiser les données.

Le système est SQLite 3.10. 1. Cependant, tout type de SGBD avec SQL conviendra.

Mes contraintes sont les suivantes:

Conserver la même longueur que l'original
Échangez chaque chiffre contre un autre chiffre ou contre une lettre

Actions que j'ai entreprises

J'allais littéralement parcourir tous les chiffres possibles et les mettre à jour comme ci-dessous. Cependant, cela semble être une manière très inefficace de le faire.

CREATE TABLE test (
  prod_num varchar(14),
  owner varchar(255) default NULL,
  prod_date varchar(255)
);

INSERT INTO test (prod_num,owner,prod_date) VALUES ("260619275","Kieran","Feb 10, 2018"),("316556232","Steven","Jan 6, 2020"),("625302534","Oliver","Feb 10, 2018"),("811424845","Jeremy","Apr 12, 2018"),("060961216","Quinlan","Jul 19, 2019"),("713794360","Stuart","Nov 1, 2019"),("553381666","George","Jan 8, 2019"),("978519361","Macon","Nov 26, 2018"),("352718969","Raphael","Jul 21, 2019"),("803299478","Byron","Nov 26, 2019");
INSERT INTO test (prod_num,owner,prod_date) VALUES ("696124452","Dalton","Jul 17, 2018"),("892088485","Keane","Jul 9, 2018"),("817054190","Dillon","Apr 23, 2018"),("500170097","Fitzgerald","Feb 11, 2019"),("663252252","Thomas","Apr 10, 2018"),("061983557","Alan","May 12, 2018"),("492057435","Jarrod","Apr 16, 2018"),("837802495","Shad","Mar 22, 2019"),("725698187","Mark","Jul 22, 2018"),("153352349","Akeem","Feb 19, 2018");

ALTER TABLE test 
ADD pseudo_num NVARCHAR(20);

UPDATE test 
SET pseudo_num = prod_num;

Questions

Existe-t-il un moyen plus rapide de le faire, par exemple via des remplacements par lots?
Existe-t-il une méthode standard alternative de pseudo-anonymisation sur laquelle je peux m'appuyer et qui respecte les contraintes que j'ai décrites ci-dessus?

Exemple de code pour créer un tableau de données

UPDATE test
SET pseudo_num = replace(pseudo_num, '0', 'B');
UPDATE test
SET pseudo_num = replace(pseudo_num, '1', 'T');
UPDATE test
SET pseudo_num = replace(pseudo_num, '2', 'A');
UPDATE test
SET pseudo_num = replace(pseudo_num, '3', 'A');
UPDATE test
SET pseudo_num = replace(pseudo_num, '4', 'D');
UPDATE test
SET pseudo_num = replace(pseudo_num, '5', '3');
UPDATE test
SET pseudo_num = replace(pseudo_num, '6', '2');
UPDATE test
SET pseudo_num = replace(pseudo_num, '7', '4');
UPDATE test
SET pseudo_num = replace(pseudo_num, '8', 'X');
UPDATE test
SET pseudo_num = replace(pseudo_num, '9', 'L');

performance sql sqlite

4 commentaires

Y a-t-il une logique globale aux remplacements? Quelle version de SQL utilisez-vous?

Je ne suis pas sûr que le remplacement des chiffres par des chiffres (ou des lettres) connus soit une bonne anonymisation, car il est réversible (sauf pour le A, qui était un 2 ou un 3)

C'est certainement réversible, mais je pense que c'est correct - il devrait être pseudo-anonymisé plutôt que complètement

Si vous utilisez SQLite, vous voulez une solution dans SQLite.

4 Réponses :

1
votes

Vous pouvez essayer d'utiliser une jointure ici pour effectuer les remplacements. Si vous n'avez pas de table formelle contenant le mappage de l'ancien au nouveau pseduo_num , nous pouvons essayer d'utiliser un CTE.

WITH map AS (
    SELECT '0' AS pseudo_num, 'B' AS output UNION ALL
    SELECT '1', 'T' UNION ALL
    SELECT '2', 'A' UNION ALL
    SELECT '3', 'A' UNION ALL
    SELECT '4', 'D' UNION ALL
    SELECT '5', '3' UNION ALL
    SELECT '6', '2' UNION ALL
    SELECT '7', '4' UNION ALL
    SELECT '8', 'X' UNION ALL
    SELECT '9', 'L'
),
cte AS (
    SELECT t.pseudo_num, m.output
    FROM test t
    INNER JOIN map m
        ON t.pseudo_num = m.psuedo_num
)

UPDATE cte
SET pseudo_num = output;

0 commentaires

1
votes

Vous avez dit que "tout type de SGBD avec SQL ira bien", c'est donc pour Postgres:

Dans Postgres, vous pouvez utiliser translate () pour cela:

UPDATE test
  SET pseudo_num = translate(pseudo_num, '0123456789', 'BTAAD324XL');

Exemple en ligne: https://rextester.com/OIMBB72939

0 commentaires

1
votes

Vous pouvez utiliser une fonction de hachage (ou de chiffrement) pour convertir les numéros de produit en chaînes avec des caractères et des nombres de même longueur. Les mêmes numéros de produit reçoivent également le même hachage / valeur:

Exemple sur TSQL:

-- preview (old and new prod_num)
SELECT prod_num, UPPER(RIGHT(MD5(prod_num), LENGTH(prod_num))) 
FROM test;

-- the UPDATE
UPDATE test SET pseudo_num = UPPER(RIGHT(MD5(prod_num), LENGTH(prod_num)));

démo sur dbfiddle.uk

Exemple sur MySQL:

-- preview (old and new prod_num)
SELECT prod_num, SUBSTR(STANDARD_HASH(prod_num, 'MD5'), LENGTH(prod_num) * -1) pseudo_prod_num 
FROM test;

-- the UPDATE
UPDATE test SET pseudo_num = SUBSTR(STANDARD_HASH(prod_num, 'MD5'), LENGTH(prod_num) * -1);

démo sur dbfiddle.uk p>

Exemple sur Oracle:

-- preview (old and new prod_num)
SELECT prod_num, UPPER(RIGHT(MD5(prod_num), LENGTH(prod_num))) 
FROM test;

-- the UPDATE
UPDATE test SET pseudo_num = UPPER(RIGHT(MD5(prod_num), LENGTH(prod_num)));

démo sur dbfiddle.uk

Exemple PostgreSQL:

-- preview (old and new prod_num)
SELECT prod_num, RIGHT(CONVERT(VARCHAR(32), HASHBYTES('SHA1', prod_num), 2), LEN(prod_num)) 
FROM test;

-- the UPDATE
UPDATE test SET pseudo_num = RIGHT(CONVERT(VARCHAR(32), HASHBYTES('SHA1', prod_num), 2), LEN(prod_num));

démo sur dbfiddle.uk

3 commentaires

Cette conversion est-elle reproductible, de sorte que si je veux qu'elle soit effectuée sur une autre table et que le même prod_num soit présent, elle produira le même pseudo_num ?

Oui. Le même prod_num est le même hachage. Vous pouvez vérifier cela si vous regardez l'aperçu. Les mêmes numéros de produit ont le même hachage. Sur tous les gros SGBD, il pourrait y avoir une fonction de hachage.

Comment ferais-je la même chose avec PostgreSQL et Oracle?

0
votes

Sur Mariadb:

alter table test add primary key (prod_num);
replace into test(prod_num, owner, prod_date, pseudo_num)
select 
    prod_num,
    owner,
    prod_date,
    replace(
        replace(
            replace(
                replace(
                    replace(
                        replace(
                            replace(
                                replace(
                                    replace(
                                        replace(prod_num,'0','B')
                                    ,'1','T')
                                ,'2','A')
                            ,'3','A')
                        ,'4','D')
                    ,'5','3')
                ,'6','2')
            ,'7','4')
        ,'8','X')
    ,'9','L') as pseudo_num
from test;

0 commentaires