====== 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.
{{ https://protonvpn.com/blog/wp-content/uploads/2021/12/2022-01-pv-blog-what-is-aes.png?400 }}
Referències:
* https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html
{{tag> #FpInfor #Daw #DawMp07 #DawMp07Uf3 #DawMp07Uf03 #Dam #DamMp09 mysql database encriptació db }}
\\
===== Exemple bàsic =====
L'exemple més senzill seria el següent:
ULL! El ''block_encryption_mode = 'aes-256-ecb';'' és important
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.
{{db_vota.png}}
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", "pepa@mail.com",
SHA2("pepapass",512), AES_ENCRYPT("pepasalt","pepapass"));
insert into usuaris values(2, "manolo", "manolo@mail.com",
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 | pepa@mail.com | 67109992... | 0x5CDBCDE0E5B34F... |
| 2 | manolo | manolo@mail.com | 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 |
+-----------+-------------+----------+