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(
‘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 _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 IFNULL(srvjson
->>‘$.ipaddress‘,uc_session_ipaddress_client());#用户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();
IF ISNULL(req_appid)
THEN
RETURN JSON_SET(retjson,
‘$.errno‘,
-101,
‘$.message‘,
‘appid不能为空‘);
END IF;
IF ISNULL(req_email)
THEN
RETURN JSON_SET(retjson,
‘$.errno‘,
-102,
‘$.message‘,
‘email不能为空‘);
END IF;
IF ISNULL(req_pwdmd5)
THEN
RETURN JSON_SET(retjson,
‘$.errno‘,
-103,
‘$.message‘,
‘pwdmd5不能为空‘);
END IF;
IF LENGTH(req_pwdmd5)
<>32 THEN
RETURN JSON_SET(retjson,
‘$.errno‘,
-104,
‘$.message‘,
‘pwdmd5需要提供32个字符‘);
END IF;
#首先判断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 keyid,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,
‘$.keyid‘,_keyid);
#SET retjson
=JSON_SET(retjson,
‘$.userjson‘,
CAST(userjson
AS CHAR));
#SET retjson
=JSON_SET(retjson,
‘$.hmacsha1‘,uc_session_hmacsha1(CONCAT(_secretkey,secretkey2),retjson
->>‘$.userjson‘));
SET retjson
=JSON_SET(retjson,
‘$.sessdata‘,JSON_OBJECT(
‘keyid‘,_keyid,
‘userjson‘,TO_BASE64(AES_ENCRYPT(userjson,CONCAT(_secretkey,secretkey2)))));
RETURN JSON_SET(retjson,
‘$.errno‘,
0,
‘$.message‘,
‘登录成功‘);
END
MYSQL登录函数(第3版本)
标签:ppi mes member key mac bsp 开始 HERE pid