[MYSQL] Signature SHA-2 de tous les champs d'une table

Bonjour,

Je dois signer numériquement les données d'une table SQL à l'aide de SHA-2 ou SHA-3 (pas de SHA-1 ou MD5) afin de garantir l'intégrité des données.

La table en question contient aussi bien des champs INT, VAR, VARCHAR que des champs LONGTEXT (Code HTML stocké dedans).

Voici une table d'exemple, la vrai table contient beaucoup plus d'informations :

CREATE TABLE `test` (
  `id` int(250) NOT NULL,
  `field1` varchar(250) NOT NULL,
  `field2` int(250) NOT NULL,
  `field3` decimal(65,5) NOT NULL,
  `field4` longtext NOT NULL,
  `hash` char(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `test` ADD PRIMARY KEY (`id`);
ALTER TABLE `test` MODIFY `id` int(250) NOT NULL AUTO_INCREMENT;

Les données ne seront jamais modifiées.
J'utilise adodb pour insérer les données côté PHP et c'est un serveur MySQL.

Vous me conseilleriez d'utiliser PHP pour calculer le hachage à l'insertion ou de passer par une requête SQL après l'insertion ?

Merci à vous

Salut,
je ne comprends pas bien ce que tu cherches a faire :s
(et si c'est ce que j'ai compris, je ne comprends pas l'intérêt ^^)

Est ce que tu pourrais expliquer ce que tu entends par "signer numériquement" ? Et ce que tu cherches à faire exactement ?

En l'état, j'ai l'impression que tu cherches à calculer une espèce de valeur (hash) qui correspondra à la valeur de chaque/tous les champs pour "assurer" qu'ils n'ont pas été modifier. Le truc qui me semble réduire l'intérêt, c'est que si tu te fais pirater, qu'est ce qui empêchera à la personne de modifier les champs mais pas le hash ?

C'est un peu le même cas quand on télécharge des logiciels et qu'a coté on nous donne le hash pour que l'on puisse vérifier que c'est bien la même chose, sauf que si un pirate a pu modifier le lien de téléchargement logiciel, il a probablement aussi pu changer la valeur du hash...

Bonjour,

Pour calculer juste un hash, les deux choix sont possibles: le faire en PHP ou autre langage serveur, ou bien directement avec MySQL.

Par contre niveau sécuritaire, le pirate qui modifie les champs pourra presque aussi facilement modifier le ash. Donc c'est plutôt limité dans ce que ça apporte.

Pour être sûr que l'intégrité de tes données soit conservée, ce serait au minimum un MAC qu'il te faut. De façon raccourcie, un hash qui est aussi chiffré.
Ainsi le pirate qui veut modifier ta base sans que tu ne le remarques devra aussi mettre la main sur la clé de chiffrement, sans quoi il ne pourra pas modifier le hash, et donc tu sauras que les données ont été corrompues.

Il y a aussi des fonctions de chiffrement en MySQL (sauf erreur des fonctions AES_ENCRYPT/AES_DECRYPT), mais je pense que ça va quand même être plus simple de faire ça en PHP ou autre langage serveur; pas seulement pour le calcul lors de l'insertion, mais aussi pour la vérification à la lecture.


Si tu ne veux pas te pencher sur le chiffrement parce que c'est trop compliqué, prévois au moins un salage à partir d'une donnée non stockée en base.
C'est vrai que passer par du MAC peut être lourd, surtout si les données de départ elles-mêmes ne sont pas chiffrées.

Bonjour,

Merci pour vos réponses. Je viens juste de les voir car je n'ai pas reçu de notifications.

Pour vous expliquer plus clairement, je dois avoir 3-4 tables dans base de données où je dois certifier qu'aucune modification n'a été apportée. Le contenu de ces tables ne sera jamais modifié par la suite mais conservé comme historique. Je dois donc signer numériquement le contenu de chaque ligne avec l'empreinte de la précédente.

Ce n'est pas pour me protéger contre le piratage mais la loi qui impose de procéder comme ça à partir de 2018. C'est pour le logiciel de comptabilité interne de ma boite.

Plusieurs choses :
- Les anciennes données n'ont pas besoin d'être modifiées.
- Aucun problème pour développer quelque chose de lourd, c'est mon métier.
- J'ai le droit d'utiliser au minimum ces algorithmes : SHA-2, SHA-3, Whilpool, Blake et la taille de l'empreinte doit être supérieure à 256 bits.
- Je ne suis pas contre le cryptage mais qu'en est-il d'une recherche en base de données par la suite ? Je pensais l'utiliser uniquement pour l'archivage.

Je pensai procéder de cette manière :
- Insertion des données
- Lecture des données insérées
- Creation d'une chaine avec toutes les données + l'empreinte de l'enregistrement précédent.
- Signature de la chaine et enregistrement en bdd.
Pourquoi insertion puis lecture, c'est pour être sûr de bien utiliser l'ordre des colonnes de mysql.

Merci à vous.

ju91a a écrit :

Je pensai procéder de cette manière :
- Insertion des données
- Lecture des données insérées
- Creation d'une chaine avec toutes les données + l'empreinte de l'enregistrement précédent.
- Signature de la chaine et enregistrement en bdd.
Pourquoi insertion puis lecture, c'est pour être sûr de bien utiliser l'ordre des colonnes de mysql.

Merci à vous.

Avec le cas d'utilisation c'est beaucoup plus clair.
Le problème avec cette méthode est la gestion de la concurrence. Est-ce un risque? utilises-tu des transactions ou des LOCK?

Qu'appelles-tu gestion de la concurrence ?

Je viens de comprendre en relisant ton post, c'est dans le cas où 2 personnes valident le formulaire en même temps. Je n'y avais pas pensé et effectivement, ça va poser problème car on ne doit avoir aucune erreur. En plus, nous avons 2 serveurs avec MySQL en Master/Master.

Pour le moment, on utilise pas les transactions car en cas de coupure, on préfère avoir la moitié d'une facture d'enregistrée plutôt que rien du tout.
Pour les locks, j'ai peur que ça entraîne des ralentissements ?

Je peux très bien mettre en place une sécurité au niveau de PHP qui check si l'entrée précédente à une signature mais c'est loin d'être propre comme solution.

Le plus clean c'est les transactions puis les locks. Mais oui le temps de réponse et les perfs peuvent en souffrir. Si c'est important (beaucoup d'utilisateurs) il est imaginable aussi d'utiliser une table temporaire: Chaque écriture est inscrite dans une table temporaire, directement et sans somme de contrôle, et un script serveur ou un cron les insère au fur et à mesure dans la table définitive, sans risque de concurrence.

Pour les transactions, ça va être mis en place car on ne pourra plus se permettre d'avoir qu'un bout de facture. Les locks, clairement je ne pense pas, on aurait des ralentissements aux heures de pointe.

La table temporaire, oui et non, autant rester directement sur la table d'origine avec un script cron qui tourne toutes les minutes pour signer ces lignes. C'est pas très propre mais je ne vois pas d'autres solutions.

Est-ce qu'on peut mettre des LOCK sur une partie de la table en utilisant une clé étrangère ?

Pourquoi dois-tu calculer l'empreinte de la dernière ligne insérée et non pas celle que tu es en train d'insérer ?

Ce serait plus simple et avec des transactions, ce serait suffisant pour ne pas avoir de problème de concurrence.
Astuce: il existe une fonction pour récupérer l'ID de la ligne que tu viens d'insérer, sauf erreur last_insert_id().

Pour l'algorithme, pas de problème, tu peux utiliser la fonction sha2 de MySQL qui supporte sha256, sha384 et sha512.
ET effectivement vu ton cas d'utilisation, le chiffrement n'est pas nécessaire.

Le problème pour calculer l'empreinte de la ligne à l'insertion, c'est que si 2 enregistrements se font en même temps je me retrouve avec 2 problèmes :
- le numéro de la facture sera en doublon, - de 1% de chance que ça arrive mais comme on n'a plus le droit de faire de modification à la main c'est à prendre en compte.
- toutes les signatures ultérieures invalides car chaque ligne est signée avec l'empreinte de la ligne précédente.

Si tu me confirmes que 2 transactions provenant de 2 postes différents sont forcément exécutées l'une après l'autre, c'est bon pour moi. Sinon sans lock, je vais devoir repasser via une tâche cron pour valider le numéro de facture et signer le contenu.

a écrit :
Le problème pour calculer l'empreinte de la ligne à l'insertion, c'est que si 2 enregistrements se font en même temps je me retrouve avec 2 problèmes :
- le numéro de la facture sera en doublon, - de 1% de chance que ça arrive mais comme on n'a plus le droit de faire de modification à la main c'est à prendre en compte.

Avec les transactions, ça ne peut pas arriver. ET la fonction last_insert_id() renvoie l'ID de la dernière ligne insérée avec la connexion en cours (et non pas de manière globale), donc tu n'as pas de risque non plus de récupérer un ID de ligne inséré par un autre processus.

Si ton numéro de facture n'est pas trivial, vu que le concept de séquence n'existe pas (je peux me tromper mais en tout cas je n'en ai jamais vu) en MySQL, tu dois par contre t'arranger pour qu'il soit calculable à partir de l'ID de la ligne.
Si ces numéros sont un temps soit peu linéaires, ça ne devrait pas être trop compliqué.

Merci pour ta réponse.

Aucun problème pour récupérer l'ID de la ligne ajoutée, on l'utilise déjà pour enregistrer le contenu de la facture à la suite.

Mon problème, c'est plutôt si deux personnes créent une facture exactement en même temps, ils vont tous les deux aller récupérer l'empreinte de la même facture précédente pour leur signature.

Bonjour,

J'ai trouvé la solution pour ne pas avoir de problème.

Poser un lock uniquement sur la dernière facture et pas sur toute la table. Cela ne ralentira donc pas les autres utilisateurs qui veulent créer un document autre qu'une facture.

Cela implique de passer sur PostgreSQL mais on y pensait déjà.

Dans ce cas tu n'auras pas le choix, tu dois loquer toute la table.

OU alors, utiliser une autre table pour stocker l'ID de la dernière facture (que tu devras quand même loquer), ce qui te permet d'éviter les contentions.

Oui du coup c'est prévu, transitions + lock et migration de mysql vers postgresql.

Merci pour votre aide
Modifié par ju91a (18 Jun 2017 - 20:23)