以HMAC密钥形式发放密钥令牌
功能如下
1：记录用户的登录的IP地址、时间
2：实现密码错误次数超限后锁定，并提示何时解锁
CREATE DEFINER=`root`@`%` FUNCTION `uc_session_login`( `reqjson` JSON, `srvjson` JSON ) RETURNS json LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT 'JSON用户登录' BEGIN #SELECT `uc_session_login`('{"email":"xxscn@qq.com","pwdmd5":"md5加密后的密码"}','{"ipaddress":"用户登录的IP地址"}')json DECLARE retjson JSON DEFAULT'{"errno":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 _beforedel 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),'{}');#IFNULL(@srvjson,'{}')#已由会话变量改为函数的参数 DECLARE secretkey2 VARCHAR(32)DEFAULT IFNULL(srvjson->>'$.secretkey2','');#二级密钥 DECLARE useripaddress VARCHAR(39)DEFAULT srvjson->>'$.ipaddress';#用户IP地址 DECLARE req_email VARCHAR(50)DEFAULT reqjson->>'$.email'; DECLARE req_pwdmd5 CHAR(32)DEFAULT reqjson->>'$.pwdmd5'; #与字段无关的输出 DECLARE json JSON DEFAULT'{}'; #首先判断IP地址是否已被禁止尝试密码 DELETE FROM uc_session_ipaddress WHERE beforedel<CURRENT_TIMESTAMP();#删除较早的IP SET _failedlogins=0; SELECT failedlogins,beforedel INTO _failedlogins,_beforedel FROM uc_session_ipaddress WHERE ipaddress=useripaddress; IF _failedlogins>=failedlogins_max THEN SET retjson=JSON_SET(retjson,'$.errno',-11); SET retjson=JSON_SET(retjson,'$.message',CONCAT('由于密码错误次数过多，已被锁定。解锁时间：',_beforedel)); RETURN retjson; END IF; SELECT uid,username,`password`,salt INTO _uid,_username,_password,_salt FROM uc_members WHERE email=req_email; IF ISNULL(_uid) THEN RETURN '{"error":-1,"message":"您输入的E-Mail尚未注册"}';END IF; IF MD5(CONCAT(req_pwdmd5,_salt))<>_password THEN INSERT INTO uc_session_log(uid,ipaddress,errno,error)VALUES(_uid,useripaddress,-2,'登录密码错误'); SET @curfailed=_failedlogins+1; IF _failedlogins=0 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=@curfailed,beforedel=TIMESTAMPADD(SECOND,failedlogins_unlocktime,CURRENT_TIMESTAMP()) WHERE ipaddress=useripaddress; END IF; IF @curfailed>=failedlogins_max THEN SET retjson=JSON_SET(retjson,'$.errno',-3); SET retjson=JSON_SET(retjson,'$.message',CONCAT('由于密码错误次数已达',failedlogins_max,'次，现已被锁定',failedlogins_unlocktime,'秒')); RETURN retjson; END IF; SET retjson=JSON_SET(retjson,'$.errno',-2); SET retjson=JSON_SET(retjson,'$.message',CONCAT('您已输错',@curfailed,'次密码，如再错',failedlogins_max-@curfailed,'次将被锁定',failedlogins_unlocktime,'秒')); RETURN retjson; 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 RETURN '{"error":-3,"message":"secretkey获取失败"}';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',req_email); SET json=JSON_SET(json,'$.username',_username); SET json=JSON_SET(json,'$.password',_password); SET json=JSON_SET(json,'$.salt',_salt); DELETE FROM uc_session_ipaddress WHERE ipaddress=useripaddress;#删除较早的IP INSERT INTO uc_session_log(uid,ipaddress,errno,error)VALUES(_uid,useripaddress,0,'登录成功'); SET retjson=JSON_SET(retjson,'$.userjson',CAST(json AS CHAR)); SET retjson=JSON_SET(retjson,'$.keyid',_keyid); SET retjson=JSON_SET(retjson,'$.hmacsha1',uc_session_hmacsha1(CONCAT(_secretkey,secretkey2),retjson->>'$.userjson')); SET retjson=JSON_SET(retjson,'$.errno',0); SET retjson=JSON_SET(retjson,'$.message','登录成功'); RETURN retjson; END