From 2c79deb68b0eed88c3d8b7f543481ad25ba860ca Mon Sep 17 00:00:00 2001 From: "Jean-Marc Pigeon (Delson)" Date: Wed, 9 Jul 2025 21:18:49 -0400 Subject: [PATCH] Adding password field to email --- sql/datatest.sql | 16 ++++++++-------- sql/mailleur.sql | 13 ++++++++++++- 2 files changed, 20 insertions(+), 9 deletions(-) diff --git a/sql/datatest.sql b/sql/datatest.sql index b134272..8912612 100644 --- a/sql/datatest.sql +++ b/sql/datatest.sql @@ -5,24 +5,24 @@ DELETE FROM emails; //password is generated via command line: //openssl password -//present password is crypted as a hash +//present password is crypted as a hash and in password too //adding a list of local email -INSERT INTO emails (email,hash) \ +INSERT INTO emails (email,password) \ values ('postmaster@example.com','postmaster'); -INSERT INTO emails (email,hash) \ +INSERT INTO emails (email,password) \ values ('webmaster@example.com','webmaster'); -INSERT INTO emails (email,hash) \ +INSERT INTO emails (email,password) \ values ('utf8-áö_üñ@example.com','utf8-áö_üñ'); //Set for Postgresql database -INSERT INTO emails (email,hash) \ +INSERT INTO emails (email,password) \ values ('user1@posdb.example.com','user1'); -INSERT INTO emails (email,hash) \ +INSERT INTO emails (email,password) \ values ('user2@posdb.example.com','user2'); //Set for MySQL database -INSERT INTO emails (email,hash) \ +INSERT INTO emails (email,password) \ values ('user1@mardb.example.com','user1'); -INSERT INTO emails (email,hash) \ +INSERT INTO emails (email,password) \ values ('user2@mardb.example.com','user2'); diff --git a/sql/mailleur.sql b/sql/mailleur.sql index 8cb8e48..9a0dccf 100644 --- a/sql/mailleur.sql +++ b/sql/mailleur.sql @@ -53,6 +53,8 @@ CREATE TABLE emails ( creation DBTIMESTAMP //record creation DFLT NOW(), email TEXTUNIQUE, //User email + password TEXT //user (encrypted) password + DFLT '!', hash TEXT, //'email:realm:password' MD5 space INTEGER //space used by user email DFLT 0, @@ -100,7 +102,7 @@ GRANT SELECT ON sessions TO mailapache; //Caution this REALM value MUST be the same as defined //in mailleur.conf #define REALM 'mailleur-email' -#define HASHING concat(NEW.email,':',REALM,':',NEW.hash) +#define HASHING concat(NEW.email,':',REALM,':',NEW.password) #ifdef POSTGRESQL @@ -108,6 +110,7 @@ CREATE FUNCTION updpass() RETURNS trigger AS $$ BEGIN NEW.hash = md5(HASHING); + new.password = crypt (NEW.password,gen_salt('md5')); RETURN NEW; END $$ LANGUAGE 'plpgsql'; @@ -119,12 +122,20 @@ CREATE TRIGGER keephash #if defined(MYSQL) || defined(MYSQLI) +DELIMITER $$ CREATE TRIGGER sethash BEFORE UPDATE ON emails FOR EACH ROW + BEGIN set NEW.hash=MD5(HASHING); + set NEW.password=encrypt(NEW.password,'$1$abcdef'); + END$$ + CREATE TRIGGER updhash BEFORE INSERT ON emails FOR EACH ROW + BEGIN set NEW.hash=MD5(HASHING); + set NEW.password=encrypt(NEW.password,'$1$abcdef'); + END$$ #endif -- 2.47.3