Encriptació AES a MySQL

L'encriptació AES és reversible (permet desencriptar), a diferència de SHA2 que és una funció de hash destructiva. Els strings codificats en AES seran recuperables si es té la clau d'encriptació.

En AES es fa servir la mateixa clau per encriptar i desencriptar, d'aquí que es classifiqui com a sistema d'encriptació de clau simètrica. Altre algorismes com RSA son de clau asimètrica, ja que requereixen una clau diferent per encriptar i desencriptar (sistema de clau pública/privada). Els asimètrics permeten certes utilitats però són computacionalment més feixucs.

Referències:

• https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html

Exemple bàsic

L'exemple més senzill seria el següent:

mysql> SET block_encryption_mode = 'aes-256-ecb';
mysql> select AES_ENCRYPT("text","mykey");
+----------------------------------------------------------+
| AES_ENCRYPT("text","mykey")                              |
+----------------------------------------------------------+
| 0x51C4607C0A37C8B875DE31682E4E6212                       |
+----------------------------------------------------------+
1 row in set (0,00 sec)

mysql> select cast(AES_DECRYPT(0x51C4607C0A37C8B875DE31682E4E6212,"mykey") as char);
+-----------------------------------------------------------------------+
| cast(AES_DECRYPT(0x51C4607C0A37C8B875DE31682E4E6212,"mykey") as char) |
+-----------------------------------------------------------------------+
| text                                                                  |
+-----------------------------------------------------------------------+
1 row in set (0,00 sec)

Exemple: encriptació per a vot secret

En aquest exemple volem encriptar el vot en una aplicació de votacions per tal de que sigui secret. Això significa que ningú que no sigui l'usuari pot veure el què ha votat, ni tan sols l'administrador del sistema.

Tenim invitacions a l'enquesta, i enlloc de posar la opció de vot directament, la separarem en una taula de votacions i encriptarem el id_invitació amb una «sal» que només és accessible a l'usuari, ja que la codifiquem amb la pròpia contrasenya. El sysadmin no podrà relacionar la invitació amb la votació, però sí que podrà fer el recompte.

L'encriptació dle ID_INVITACIÓ la podem expressar així, concatenant el ID_INVITACIO amb la sal de l'usuari:

ID_INVITACIO_ENC = SHA2( ID_INVITACIO + SAL_USUARI )

Creació de la BD de votacions

create database vota;
use vota;
SET block_encryption_mode = 'aes-256-ecb';
 
create table usuaris (
	id int auto_increment primary key,
	nom varchar(100),
	email varchar(100),
	contrasenya varchar(512),
	enc_salt varbinary(300)
);
 
create table invitacions (
	id int,
	id_usuari int,
	id_enquesta int,
	FOREIGN KEY (id_usuari) REFERENCES usuaris(id)
);
 
create table vots (
	id_invitacio_enc varbinary(300),
	id_opcio int
);
 
-- usuaris amb contrasenya i sal per a encriptació
-- la sal s'encripta amb la contrasenya
-- "pepasalt" i "manolosalt" haurien de ser strings aleatoris
insert into usuaris values(1, "pepa", "[email protected]",
    SHA2("pepapass",512), AES_ENCRYPT("pepasalt","pepapass"));
insert into usuaris values(2, "manolo", "[email protected]",
    SHA2("manolopass",512), AES_ENCRYPT("manolosalt","manolopass"));
 
-- invitació a enquesta
-- values(id_invitacio,id_usuari,id_enquesta)
insert into invitacions values(10,1,11);
insert into invitacions values(20,2,22);
 
-- votació: insertem la id_invitacio_enc i la id_opcio
-- s'encripta la id_invitacio amb la sal de cada usuari
-- per ex: id_invitacio+sal => "10"+"pepasalt"
insert into vots values (SHA2(CONCAT(CONVERT(10,char),"pepasalt"),512),111);
insert into vots values (SHA2(CONCAT(CONVERT(20,char),"manolosalt"),512),222);

Les dades quedarien així:

mysql> select * from usuaris;
+----+--------+-----------------+--------------+---------------------+
| id | nom    | email           | contrasenya  | enc_salt            |
+----+--------+-----------------+--------------+---------------------+
|  1 | pepa   | [email protected]   | 67109992...  | 0x5CDBCDE0E5B34F... |
|  2 | manolo | [email protected] | b1d731d8...  | 0x188CC1D6391244... |
+----+--------+-----------------+--------------+---------------------+

mysql> select * from invitacions;
+------+-----------+-------------+
| id   | id_usuari | id_enquesta |
+------+-----------+-------------+
|   10 |         1 |          11 |
|   20 |         2 |          22 |
+------+-----------+-------------+

mysql> select * from vots;
+------------------------------+----------+
| id_invitacio_enc             | id_opcio |
+------------------------------+----------+
| 0x613835326530353...         |      111 |
| 0x653837363036323...         |      222 |
+------------------------------+----------+

Accedir als vots d'un usuari

Podem accedir a les opcions votades per un usuari només si disposem de la seva contrasenya, que ens desencriptarà la sal que ens permet relacionar id_invitacio_enc (encriptada) de la taula de vots amb la id de la taula d'invitacions.

Així, l'usuari podria veure què ha votat i canviar el seu vot (però no ho podria fer un sysadmin).

SET block_encryption_mode = 'aes-256-ecb';
 
-- exemple de query per cercar les votacions
set @id_usuari = 2;
set @pass_usuari = "manolopass";
 
-- l'usuari obté la seva sal amb la password
set @sal = (select cast(aes_decrypt(enc_salt,@pass_usuari) as char)
    from usuaris where id=@id_usuari);
 
-- cerquem totes les votacions de l'usuari
select i.id_usuari , i.id_enquesta, v.id_opcio
from vots v, invitacions i
where i.id_usuari = @id_usuari
  and v.id_invitacio_enc = SHA2(CONCAT(CONVERT(i.id,char),@sal),512);

Amb el què obtindrem, en cas de que l'usuari sigui el id=1:

+-----------+-------------+----------+
| id_usuari | id_enquesta | id_opcio |
+-----------+-------------+----------+
|         2 |          22 |      222 |
+-----------+-------------+----------+