MySQL中的注冊和登錄程式-有解無憂

我正在創建一個資料庫，用于使用 PHPMyAdmin 5.1.1 為學校專案托管用戶憑據和其他資料。對于注冊和登錄，我想使用存盤程序來確保一定程度的安全性，而不是像網路上的許多其他示例那樣在 MySQL 和 PHP 之間拆分功能。不確定它是否真的是一個很好的實作，因為我沒有找到很多其他的例子，其中整個登錄都是用 MySQL 制作的。

我正在使用 sha1 來散列與隨機生成的鹽連接的密碼。

我的桌子是這樣制作的：

CREATE TABLE `credentials` (
`Code` int(32) NOT NULL,
`Email` varchar(48) NOT NULL,
`Passwd` binary(40) NOT NULL,
`Salt` binary(20) NOT NULL,
`Date` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

我寫的兩個程式：

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Register_new_user` (IN `mail` VARCHAR(48), IN 
`pass` VARCHAR(48))  NO SQL
BEGIN
    DECLARE `salt` BINARY(20);
    SET `salt`=SUBSTRING(MD5(RAND()), -24);
    INSERT INTO `credentials`(`Email`,`Passwd`, `Salt`) VALUES(`mail`, SHA1(CONCAT(`pass`,`salt`)),`salt`);
END$$


CREATE DEFINER=`root`@`localhost` PROCEDURE `Log_in` (IN `mail` VARCHAR(48), IN `pass` 
VARCHAR(48), OUT `responseMessage` VARCHAR(64), OUT `ID` INT(32))  NO SQL
BEGIN
   DECLARE `salt` BINARY(20);
   SET `ID`=NULL;

   IF (EXISTS(SELECT `Code` FROM `credentials` WHERE BINARY `Email`=`mail` ORDER BY `Code` DESC LIMIT 1))
   THEN
       SET `salt`=(SELECT `Salt` FROM `credentials` WHERE BINARY `Email`=`mail`);
       SET `ID`=(SELECT `Code` FROM `credentials` WHERE BINARY `Email`=`mail` AND BINARY `Passwd`=SHA1(CONCAT(`Pass`,`salt`)));
       IF(`ID` IS NULL) THEN
           SET `responseMessage`='wrong password';
       ELSE
           SET `responseMessage`='Success';
       END IF;

   ELSE
       SET `responseMessage`='Error';
   END IF;
END$$

DELIMETER;

我不確定為什么這不起作用。每次我使用正確的憑據測驗登錄程序時，它總是輸出“錯誤的密碼”。如果我手動測驗存盤的密碼與 sha1 輸出的比較，它在我看來是相同的，所以我不知道為什么會失敗。

 SET @p=(SELECT `salt` FROM `credentials` WHERE `Email`="AAAA");
 SELECT SHA1(CONCAT("banana",@p));
 SELECT `Passwd` FROM `credentials` WHERE `Email`="AAAA";

第一次和第二次選擇的測驗回傳：

b6830fcf435250097bfa6233a47fded36fb33f17
b6830fcf435250097bfa6233a47fded36fb33f17

拜托，有人能指出我做錯了什么嗎？可能是我存盤的二進制檔案的格式或大小嗎？或者還有什么？謝謝。

uj5u.com熱心網友回復：

我不會就這相當于一個好的實施還是壞的實施發表我的意見。關于 SO 的這個話題有很多很好的討論。

這不作業的原因有很多，您在嘗試代碼時應該會收到許多錯誤訊息。

您的意圖是什么Code int(32)- 不推薦使用整數顯示寬度，并且整數的最大無符號值為 4,294,967,295 - 11.1.2 整數型別（精確值）
我不確定您為什么要使用二進制檔案Passwd，Salt因為您當前正在存盤十六進制字串值。如果您想從更高效的存盤中受益，您需要在存盤之前對值進行UNHEX。在這種情況下，您Passwd可以存盤在 BINARY(20) 中，Salt也可以存盤在 BINARY(12) 中。看看11.3.3 BINARY 和 VARBINARY 型別。
兩個存盤程序都被宣告為NO SQL第一個應該在MODIFIES SQL DATA插入資料時的狀態，第二個應該在READS SQL DATA它選擇資料時宣告。這不是什么大問題，因為 MySQL 僅將這些特征視為建議。
您將 salt 宣告為 BINARY(20)，然后嘗試分配一個 24 個字符的字串 - SET salt = SUBSTRING(MD5(RAND()), -24);
在您的表宣告Code中，NOT NULL但是您的 INSERT 陳述句沒有為指定值Code。
您在搜索憑據時使用電子郵件表明應該有一個唯一的索引。
列名不區分大小寫，因此當您SET salt=(SELECT Salt FROM credentials WHERE BINARY Email=mail);選擇區域變數而不是表列時。

這是一個修改后的表宣告 -

CREATE TABLE `credentials` (
    `Code` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `Email` VARCHAR(48) NOT NULL,
    `Passwd` BINARY(40) NOT NULL,
    `Salt` BINARY(24) NOT NULL,
    `Date` DATETIME NOT NULL DEFAULT current_timestamp(),
    UNIQUE INDEX `UQ_credentials_email` (`Email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

和存盤程序 -

DELIMITER $$

DROP PROCEDURE IF EXISTS `Register_new_user`$$
CREATE PROCEDURE `Register_new_user` (
    IN `mail` VARCHAR(48),
    IN `pass` VARCHAR(48)
)
MODIFIES SQL DATA
BEGIN
    DECLARE `_salt` BINARY(24);
    SET `_salt` = SUBSTRING(MD5(RAND()), -24);
    INSERT INTO `credentials`(`Email`, `Passwd`, `Salt`) VALUES (`mail`, SHA1(CONCAT(`pass`, `_salt`)), `_salt`);
END$$

DROP PROCEDURE IF EXISTS `Log_in`$$
CREATE PROCEDURE `Log_in` (
    IN `mail` VARCHAR(48),
    IN `pass` VARCHAR(48),
    OUT `responseMessage` VARCHAR(64),
    OUT `ID` INT
)
READS SQL DATA
BEGIN
   DECLARE `_salt` BINARY(24);
   SET `ID` = NULL;

   SELECT `Salt` INTO `_salt` FROM `credentials` WHERE `Email` = `mail`;
   IF (`_salt` IS NOT NULL)
   THEN
       SELECT `Code` INTO `ID` FROM `credentials` WHERE `Email` = `mail` AND `Passwd` = SHA1(CONCAT(`Pass`, `_salt`));
       IF(`ID` IS NULL) THEN
           SET `responseMessage` = 'wrong password';
       ELSE
           SET `responseMessage` = 'Success';
       END IF;

   ELSE
       SET `responseMessage`='Error';
   END IF;
END$$

DELIMITER ;

或者更有效地使用 BINARY 存盤 -

CREATE TABLE `credentials` (
    `Code` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `Email` VARCHAR(48) NOT NULL,
    `Passwd` BINARY(20) NOT NULL,
    `Salt` BINARY(12) NOT NULL,
    `Date` DATETIME NOT NULL DEFAULT current_timestamp(),
    UNIQUE INDEX `UQ_credentials_email` (`Email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

以及根據需要使用 HEX() 和 UNHEX() 修改的存盤程序 -

DELIMITER $$

DROP PROCEDURE IF EXISTS `Register_new_user`$$
CREATE PROCEDURE `Register_new_user` (
    IN `mail` VARCHAR(48),
    IN `pass` VARCHAR(48)
)
MODIFIES SQL DATA
BEGIN
    DECLARE `_salt` CHAR(24);
    SET `_salt` = SUBSTRING(MD5(RAND()), -24);
    INSERT INTO `credentials`(`Email`, `Passwd`, `Salt`) VALUES (`mail`, UNHEX(SHA1(CONCAT(`pass`, `_salt`))), UNHEX(`_salt`));
END$$

DROP PROCEDURE IF EXISTS `Log_in`$$
CREATE PROCEDURE `Log_in` (
    IN `mail` VARCHAR(48),
    IN `pass` VARCHAR(48),
    OUT `responseMessage` VARCHAR(64),
    OUT `ID` INT
)
READS SQL DATA
BEGIN
   DECLARE `_salt` CHAR(24);
   SET `ID` = NULL;

   SELECT LOWER(HEX(`Salt`)) INTO `_salt` FROM `credentials` WHERE `Email` = `mail`;
   IF (`_salt` IS NOT NULL)
   THEN
       SELECT `Code` INTO `ID` FROM `credentials` WHERE `Email` = `mail` AND `Passwd` = UNHEX(SHA1(CONCAT(`Pass`, `_salt`)));
       IF(`ID` IS NULL) THEN
           SET `responseMessage` = 'wrong password';
       ELSE
           SET `responseMessage` = 'Success';
       END IF;

   ELSE
       SET `responseMessage`='Error';
   END IF;
END$$

DELIMITER ;

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/401823.html

標籤：mysql 存储过程

上一篇：連接兩個表以生成一個結果集以回圈遍歷以填充MySQL中的缺失資料

下一篇：在沒有本地安裝庫的情況下測驗C#應用程式