当前位置:Gxlcms > 数据库问题 > MYSQL存储过程实现用户登录

MYSQL存储过程实现用户登录

时间:2021-07-01 10:21:17 帮助过:22人阅读

DEFINER=`root`@`%` PROCEDURE `uc_session_login`( IN `_email` VARCHAR(50), IN `_pwdmd5` CHAR(32) ) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT ‘‘ BEGIN #密钥表 DECLARE _keyid INT(10)UNSIGNED; DECLARE _secretkey CHAR(32); #登录表 DECLARE _uid INT(10)UNSIGNED; DECLARE _username VARCHAR(50); DECLARE _password CHAR(32); DECLARE _salt CHAR(6); #地址记录表 DECLARE _failedlogins INT(10)UNSIGNED; #与字段无关的输入 DECLARE failedlogins_max INT(10)UNSIGNED DEFAULT 1;#密码错误次数上限 DECLARE failedlogins_timeout INT(10)UNSIGNED DEFAULT 2;#时间范围内清零计数 DECLARE failedlogins_unlocktime INT(10)UNSIGNED DEFAULT 10;#密码错误次数超限后锁定时间 DECLARE secretkey2 VARCHAR(32)DEFAULT IFNULL(@secretkey2,‘‘); DECLARE useripaddress VARCHAR(39)DEFAULT@ipaddress; #与字段无关的输出 DECLARE json JSON DEFAULT{}; loop0:LOOP #首先判断IP地址是否已被禁止尝试密码 DELETE FROM uc_session_ipaddress WHERE beforedel<CURRENT_TIMESTAMP();#删除较早的IP SELECT failedlogins INTO _failedlogins FROM uc_session_ipaddress WHERE ipaddress=useripaddress; IF _failedlogins>failedlogins_max THEN SELECT -11 AS errno,您尝试密码错误过多,请稍候再试 AS message; LEAVE loop0; END IF; SELECT uid,username,`password`,salt INTO _uid,_username,_password,_salt FROM uc_members WHERE email=_email; IF ISNULL(_uid) THEN SELECT -1 AS errno,您输入的E-Mail尚未注册 AS message;LEAVE loop0;END IF; IF MD5(CONCAT(_pwdmd5,_salt))<>_password THEN SELECT -2 AS errno,您输入的密码错误 AS message; INSERT INTO uc_session_log(uid,ipaddress,errno,error)VALUES(_uid,useripaddress,-2,登录密码错误); IF ISNULL(_failedlogins) THEN INSERT INTO uc_session_ipaddress(ipaddress,failedlogins,beforedel)VALUES(useripaddress,1,TIMESTAMPADD(SECOND,failedlogins_timeout,CURRENT_TIMESTAMP())); ELSE UPDATE uc_session_ipaddress SET failedlogins=_failedlogins+1,beforedel=TIMESTAMPADD(SECOND,failedlogins_unlocktime,CURRENT_TIMESTAMP()) WHERE ipaddress=useripaddress; END IF; LEAVE loop0; END IF; #登录校验已通过,开始取得secretkey并出hmac SELECT id,secretkey INTO _keyid,_secretkey FROM uc_session_key WHERE started<CURRENT_TIMESTAMP() AND expired>CURRENT_TIMESTAMP() ORDER BY expired DESC LIMIT 1; IF ISNULL(_keyid) OR ISNULL(_secretkey) THEN SELECT -3 AS errno,secretkey获取失败 AS message;LEAVE loop0;END IF; SET json=JSON_SET(json,$.logintime,UNIX_TIMESTAMP(CURRENT_TIMESTAMP(6))); SET json=JSON_SET(json,$.loginip,useripaddress); SET json=JSON_SET(json,$.uid,_uid); SET json=JSON_SET(json,$.email,_email); SET json=JSON_SET(json,$.username,_username); SET json=JSON_SET(json,$.password,_password); SET json=JSON_SET(json,$.salt,_salt); SELECT json,_keyid AS keyid,uc_session_hmacsha1(CONCAT(_secretkey,secretkey2),json)AS hmacsha1; INSERT INTO uc_session_log(uid,ipaddress,errno,error)VALUES(_uid,useripaddress,0,登录成功); LEAVE loop0; END LOOP; END

 

MYSQL存储过程实现用户登录

标签:mit   ted   cat   error   timeout   定时   secret   ESS   started   

人气教程排行