Cryptage d'un champ en AES

Salut deyanos.

Voici un exemple d'utilisation de AES_ENCRYPT et AES_DECRYPT.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
SET AUTOCOMMIT = 0;
START TRANSACTION;
 
-- ============
-- Base `Essai`
-- ============
 
DROP DATABASE IF EXISTS `essai`;
 
CREATE DATABASE `essai`
	DEFAULT CHARACTER SET `utf8`
	DEFAULT COLLATE       `utf8_general_ci`;
 
USE `essai`;
 
COMMIT;
 
-- ============
-- Table `user`
-- ============
 
DROP TABLE IF EXISTS  `user`;
 
CREATE TABLE `user`
(
  `id`     int unsigned    NOT NULL AUTO_INCREMENT,
  `name`   varbinary(255)  NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB 
  DEFAULT CHARACTER SET = utf8  COLLATE = utf8_general_ci;
 
-- ==================
-- Remplissage `user`
-- ==================
 
INSERT into `user` (`name`) VALUES
(AES_ENCRYPT('Albert',   'clefsecrete')),
(AES_ENCRYPT('Einstein', 'clefsecrete'));
 
-- =============
-- Vidage `user`
-- =============
 
select * from user;
 
select AES_DECRYPT(name, 'clefsecrete') from user;
 
COMMIT;
 
SET AUTOCOMMIT = 1;

Tu remarqueras que la colonne 'name' est au format 'varbinary'.

Et voici le résultat à l'exécution :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `essai`
--------------
 
--------------
CREATE DATABASE `essai`
        DEFAULT CHARACTER SET `utf8`
        DEFAULT COLLATE       `utf8_general_ci`
--------------
 
--------------
COMMIT
--------------
 
--------------
DROP TABLE IF EXISTS  `user`
--------------
 
--------------
CREATE TABLE `user`
(
  `id`     int unsigned    NOT NULL AUTO_INCREMENT,
  `name`   varbinary(255)  NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
  DEFAULT CHARACTER SET = utf8  COLLATE = utf8_general_ci
--------------
 
--------------
INSERT into `user` (`name`) VALUES
(AES_ENCRYPT('Albert',   'clefsecrete')),
(AES_ENCRYPT('Einstein', 'clefsecrete'))
--------------
 
--------------
select * from user
--------------
 
+----+------------------+
| id | name             |
+----+------------------+
|  1 | —bŽcp—»[ïEÇÇe™/ |
|  2 | Ù«¬ýÂ£Þ;·PCïJK* |
+----+------------------+
--------------
select AES_DECRYPT(name, 'clefsecrete') from user
--------------
 
+----------------------------------+
| AES_DECRYPT(name, 'clefsecrete') |
+----------------------------------+
| Albert                           |
| Einstein                         |
+----------------------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...