mysql存储登录_MYSQL存储过程实现用户登录

CREATE DEFINER=`root`@`%` FUNCTION`uc_session_login`(

`reqjson` JSON,

`srvjson` JSON

)RETURNSjson

LANGUAGE SQLNOTDETERMINISTICCONTAINSSQL

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'))jsonDECLARE 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 DEFAULTJSON_OBJECT();

#首先判断IP地址是否已被禁止尝试密码

#DELETE FROM uc_session_ipaddress WHERE resetwhen=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并出hmacSELECT 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