MYSQL存储过程实现用户登录
CREATE 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
第2版
CREATE DEFINER=`root`@`%` FUNCTION `uc_session_login`( `reqjson` JSON, `srvjson` JSON ) RETURNS json LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT 'UC的用户登录,返回JSON' BEGIN #调用例子 #SELECT `uc_session_login`(JSON_OBJECT('appid',1,'email','test@firadio.com','pwdmd5',MD5('test')),JSON_OBJECT('ipaddress','1.1.1.1'))json DECLARE retjson JSON DEFAULT JSON_OBJECT('error',0); #密钥表 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 _resetwhen TIMESTAMP;#计次重置时间 #与字段无关的输入 DECLARE failedlogins_max INT(10)UNSIGNED DEFAULT 3;#密码错误次数上限 DECLARE failedlogins_timeout INT(10)UNSIGNED DEFAULT 20;#时间范围内清零计数 DECLARE failedlogins_unlocktime INT(10)UNSIGNED DEFAULT 10;#密码错误次数超限后锁定时间 #DECLARE srvjson JSON DEFAULT IFNULL(CAST(@srvjson AS CHAR),JSON_OBJECT());#已由会话变量改为函数的参数 DECLARE secretkey2 VARCHAR(32)DEFAULT IFNULL(srvjson->>'$.secretkey2','');#二级密钥 DECLARE useripaddress VARCHAR(39)DEFAULT srvjson->>'$.ipaddress';#用户IP地址 DECLARE req_appid INT UNSIGNED DEFAULT reqjson->>'$.appid'; DECLARE req_email VARCHAR(50)DEFAULT reqjson->>'$.email'; DECLARE req_pwdmd5 CHAR(32)DEFAULT reqjson->>'$.pwdmd5'; #与字段无关的输出 DECLARE userjson JSON DEFAULT JSON_OBJECT(); #首先判断IP地址是否已被禁止尝试密码 #DELETE FROM uc_session_ipaddress WHERE resetwhen<CURRENT_TIMESTAMP();#删除较早的IP UPDATE uc_session_ipaddress SET failedlogins=0 WHERE resetwhen<CURRENT_TIMESTAMP();#重置已超时的计数 SET _failedlogins=0; SELECT failedlogins,resetwhen INTO _failedlogins,_resetwhen FROM uc_session_ipaddress WHERE ipaddress=useripaddress FOR UPDATE; IF _failedlogins>=failedlogins_max THEN RETURN JSON_SET(retjson,'$.errno',-11,'$.message',CONCAT('由于密码错误次数过多,已被锁定。解锁时间:',_resetwhen)); END IF; SELECT uid,username,`password`,salt INTO _uid,_username,_password,_salt FROM uc_members WHERE email=req_email FOR UPDATE; IF ISNULL(_uid) THEN RETURN JSON_SET(retjson,'$.errno',-1,'$.message','您输入的E-Mail尚未注册');END IF; IF MD5(CONCAT(req_pwdmd5,_salt))<>_password THEN INSERT INTO uc_session_loginlog(appid,uid,ipaddress,errno,error)VALUES(req_appid,_uid,useripaddress,-2,'登录密码错误'); SET @curfailed=_failedlogins+1;#当前计数值 SET @resetwhen_timeout=TIMESTAMPADD(SECOND,failedlogins_timeout,CURRENT_TIMESTAMP());#清零计数的时间 IF ISNULL(_resetwhen) THEN#记录为空只能新增 INSERT INTO uc_session_ipaddress SET ipaddress=useripaddress,failed=CURRENT_TIMESTAMP(),failedlogins=1,faileduid=_uid,resetwhen=@resetwhen_timeout ON DUPLICATE KEY UPDATE failed=VALUES(failed),failedlogins=VALUES(failedlogins),faileduid=VALUES(faileduid),resetwhen=VALUES(resetwhen); ELSEIF _failedlogins=0 THEN #记录第一次密码错误,并设置新的计数超时时间 UPDATE uc_session_ipaddress SET failed=CURRENT_TIMESTAMP(),failedlogins=@curfailed,faileduid=_uid,resetwhen=@resetwhen_timeout WHERE ipaddress=useripaddress; ELSE UPDATE uc_session_ipaddress SET failed=CURRENT_TIMESTAMP(),failedlogins=@curfailed,faileduid=_uid,resetwhen=TIMESTAMPADD(SECOND,failedlogins_unlocktime,CURRENT_TIMESTAMP()) WHERE ipaddress=useripaddress; END IF; IF @curfailed>=failedlogins_max THEN RETURN JSON_SET(retjson,'$.errno',-3,'$.message',CONCAT('由于密码错误次数已达',failedlogins_max,'次,现已被锁定',failedlogins_unlocktime,'秒')); END IF; RETURN JSON_SET(retjson,'$.errno',-2,'$.message',CONCAT('您已输错',@curfailed,'次密码,如再错',failedlogins_max-@curfailed,'次将被锁定',failedlogins_unlocktime,'秒')); END IF; #登录校验已通过 #开始取得secretkey并出hmac SELECT id,secretkey INTO _keyid,_secretkey FROM uc_session_keycenter WHERE appid=req_appid AND expired>CURRENT_TIMESTAMP() LIMIT 1; IF ISNULL(_keyid) OR ISNULL(_secretkey) THEN RETURN JSON_SET(retjson,'$.errno',-4,'$.message','secretkey获取失败');END IF; SET userjson=JSON_SET(userjson,'$.logintime',UNIX_TIMESTAMP(CURRENT_TIMESTAMP()),'$.loginip',useripaddress); SET userjson=JSON_SET(userjson,'$.uid',_uid,'$.email',req_email,'$.username',_username,'$.password',_password,'$.salt',_salt); IF ISNULL(_resetwhen) THEN#记录为空只能新增 INSERT INTO uc_session_ipaddress SET ipaddress=useripaddress,failedlogins=0,succeed=CURRENT_TIMESTAMP(),succeeduid=_uid,succeedlogins=1 ON DUPLICATE KEY UPDATE failedlogins=VALUES(failedlogins),succeed=VALUES(succeed),succeeduid=VALUES(succeeduid),succeedlogins=succeedlogins+1; ELSE UPDATE uc_session_ipaddress SET failedlogins=0,succeed=CURRENT_TIMESTAMP(),succeeduid=_uid,succeedlogins=succeedlogins+1 WHERE ipaddress=useripaddress; END IF; INSERT INTO uc_session_loginlog(appid,uid,ipaddress,errno,error)VALUES(req_appid,_uid,useripaddress,0,'登录成功'); SET retjson=JSON_SET(retjson,'$.userjson',CAST(userjson AS CHAR),'$.keyid',_keyid); SET retjson=JSON_SET(retjson,'$.hmacsha1',uc_session_hmacsha1(CONCAT(_secretkey,secretkey2),retjson->>'$.userjson')); RETURN JSON_SET(retjson,'$.errno',0,'$.message','登录成功'); END