UC登录功能：商户需要创建的表

1：类似PHP的hash_hmac

DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `uc_session_hmacsha1`(
    `_secretkey` VARCHAR(64),
    `_message` VARCHAR(256)
)
RETURNS char(40) CHARSET utf8
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '类似PHP的hash_hmac'
BEGIN
DECLARE ipad,opad BINARY(64);
DECLARE hexkey CHAR(128);
DECLARE hmac CHAR(40);
SET hexkey = RPAD(HEX(_secretkey),128,"0");
/* process in 64-bit blocks to avoid overflow when converting to decimal*/
SET ipad = UNHEX(CONCAT(
LPAD(CONV(CONV(MID(hexkey,1  ,16),16,10) ^ CONV('3636363636363636',16,10),10,16),16,"0"),
LPAD(CONV(CONV(MID(hexkey,17 ,16),16,10) ^ CONV('3636363636363636',16,10),10,16),16,"0"),
LPAD(CONV(CONV(MID(hexkey,33 ,16),16,10) ^ CONV('3636363636363636',16,10),10,16),16,"0"),
LPAD(CONV(CONV(MID(hexkey,49 ,16),16,10) ^ CONV('3636363636363636',16,10),10,16),16,"0"),
LPAD(CONV(CONV(MID(hexkey,65 ,16),16,10) ^ CONV('3636363636363636',16,10),10,16),16,"0"),
LPAD(CONV(CONV(MID(hexkey,81 ,16),16,10) ^ CONV('3636363636363636',16,10),10,16),16,"0"),
LPAD(CONV(CONV(MID(hexkey,97 ,16),16,10) ^ CONV('3636363636363636',16,10),10,16),16,"0"),
LPAD(CONV(CONV(MID(hexkey,113,16),16,10) ^ CONV('3636363636363636',16,10),10,16),16,"0")
));
SET opad = UNHEX(CONCAT(
LPAD(CONV(CONV(MID(hexkey,1  ,16),16,10) ^ CONV('5c5c5c5c5c5c5c5c',16,10),10,16),16,"0"),
LPAD(CONV(CONV(MID(hexkey,17 ,16),16,10) ^ CONV('5c5c5c5c5c5c5c5c',16,10),10,16),16,"0"),
LPAD(CONV(CONV(MID(hexkey,33 ,16),16,10) ^ CONV('5c5c5c5c5c5c5c5c',16,10),10,16),16,"0"),
LPAD(CONV(CONV(MID(hexkey,49 ,16),16,10) ^ CONV('5c5c5c5c5c5c5c5c',16,10),10,16),16,"0"),
LPAD(CONV(CONV(MID(hexkey,65 ,16),16,10) ^ CONV('5c5c5c5c5c5c5c5c',16,10),10,16),16,"0"),
LPAD(CONV(CONV(MID(hexkey,81 ,16),16,10) ^ CONV('5c5c5c5c5c5c5c5c',16,10),10,16),16,"0"),
LPAD(CONV(CONV(MID(hexkey,97 ,16),16,10) ^ CONV('5c5c5c5c5c5c5c5c',16,10),10,16),16,"0"),
LPAD(CONV(CONV(MID(hexkey,113,16),16,10) ^ CONV('5c5c5c5c5c5c5c5c',16,10),10,16),16,"0")
));
SET hmac = SHA1(CONCAT(opad,UNHEX(SHA1(CONCAT(ipad,_message)))));
RETURN hmac;
END;;
DELIMITER ;

2：商户自己生成的登录密钥

CREATE TABLE `uc_session_key` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `started` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expired` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `secretkey` CHAR(32) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `secertkey` (`secretkey`),
    UNIQUE INDEX `expired` (`expired`),
    UNIQUE INDEX `started` (`started`)
)
COMMENT='商户自己生成的登录密钥'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

 3：商户自己自动来生成随机的登录密钥

CREATE DEFINER=`root`@`%` EVENT `uc_session_autoadd`
    ON SCHEDULE
        EVERY 10 MINUTE STARTS '2018-10-28'
    ON COMPLETION PRESERVE
    ENABLE
    COMMENT '商户自己自动来生成随机的登录密钥'
    DO BEGIN
    DELETE FROM uc_session_key WHERE expired<CURRENT_TIMESTAMP();
    INSERT INTO uc_session_key(started,expired,secretkey)VALUES(CURRENT_TIMESTAMP(),TIMESTAMPADD(SECOND,86400,CURRENT_TIMESTAMP()),MD5(CONCAT(CURRENT_TIMESTAMP(6),RAND())));
END

4：商户获取自己指定keyid的信息

DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `uc_session_key_getbykeyid`(
    `keyid` INT unsigned,
    `ssskey` VARCHAR(32)
)
RETURNS json
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '商户获取自己指定keyid的信息'
BEGIN
    DECLARE _ssskey VARCHAR(32);
    DECLARE _secretkey VARCHAR(32);
    DECLARE _expired TIMESTAMP;
    SELECT v INTO _ssskey FROM uc_settings WHERE k='ssskey';
    IF ISNULL(_ssskey) THEN
        RETURN JSON_OBJECT('errno',-1,'message','ssskey未设置');
    END IF;
    IF _ssskey<>ssskey THEN
        RETURN JSON_OBJECT('errno',-2,'message','ssskey不匹配');
    END IF;
    SELECT secretkey,expired INTO _secretkey,_expired FROM uc_session_key WHERE id=keyid;
    IF ISNULL(_secretkey) THEN
        RETURN JSON_OBJECT('errno',-3,'message',CONCAT('keyid=',keyid,'不存在'));
    END IF;
    RETURN JSON_OBJECT('errno',0,'secretkey',_secretkey,'expired',_expired);
END;;
DELIMITER ;

5：商户获取自己的最新keyid

DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `uc_session_key_getlastkeyid`()
RETURNS int(10) unsigned
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '商户获取自己的最新keyid'
BEGIN
    DECLARE _keyid INT(10)UNSIGNED;
    SELECT id INTO _keyid FROM uc_session_key WHERE started<CURRENT_TIMESTAMP() AND expired>CURRENT_TIMESTAMP() ORDER BY expired DESC LIMIT 1;
    RETURN _keyid;
END;;
DELIMITER ;

6：配置表

CREATE TABLE `uc_settings` (
    `k` VARCHAR(32) NOT NULL DEFAULT '',
    `v` TEXT NOT NULL,
    PRIMARY KEY (`k`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
;
INSERT INTO `uc_settings` (`k`, `v`) VALUES ('ssskey', '123456123456');

 7：PHP脚本

<?php

$appid = 1;
$ssskey = '123456123456';
$pdo1 = new MyPDO('10.86.2.36', 3324, 'feieryun', 'feieryun', 'firadio_ucenter');
$pdo2 = new MyPDO('10.86.2.36', 3324, 'feieryun', 'feieryun', 'firadio_ucenter');
//取得UC的当前keyid
$uc_keyid = $pdo1->fetch('SELECT `uc_session_keycenter_getkeyid`(:_appid)', array('_appid' => $appid));
if (!is_numeric($uc_keyid)) {
    console('uc_keyid is not numeric.');
}
//取得商户的最新keyid
$app_keyid = $pdo2->fetch('SELECT `uc_session_key_getlastkeyid`()');
if (!is_numeric($app_keyid)) {
    console('app_keyid is not numeric.');
}
if ($uc_keyid === $app_keyid) {
    console('its same.');
}
//从商户数据库里分别取得UC和商户的secretkey
$bykeyid_old = json_decode($pdo2->fetch('SELECT `uc_session_key_getbykeyid`(:keyid,:ssskey)', array('ssskey' => $ssskey, 'keyid' => $uc_keyid)), true);
if (!isset($bykeyid_old['errno']) || $bykeyid_old['errno'] !== 0) {
    console($bykeyid_old);
}
$bykeyid_new = json_decode($pdo2->fetch('SELECT `uc_session_key_getbykeyid`(:keyid,:ssskey)', array('ssskey' => $ssskey, 'keyid' => $app_keyid)), true);
if (!isset($bykeyid_new['errno']) || $bykeyid_new['errno'] !== 0) {
    console($bykeyid_new);
}
//更新UC的secretkey
$result = json_decode($pdo1->fetch('SELECT `uc_session_keycenter_setnew`(:_appid,:old_secretkey,:new_keyid,:new_secretkey,:new_expired)',
array('_appid' => $appid, 'old_secretkey' => $bykeyid_old['secretkey'],
'new_keyid' => $app_keyid, 'new_secretkey' => $bykeyid_new['secretkey'], 'new_expired' => $bykeyid_new['expired'])), true);
if (!isset($result['errno']) || $result['errno'] !== 0) {
    console($result);
}
console('OK.');
function console($obj) {
    print_r($obj);
    exit;
}
class MyPDO {
    public function __construct($host, $port, $username, $password, $db) {
        $set = array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'");
        try {
            $this->dbh = new PDO("mysql:host=$host;port=$port;dbname=$db;", $username, $password, $set);
        } catch (PDOException $e) {
            print "Error: " . $e->getMessage() . "<br/>";
            die();
        }
    }
    public function fetch($sql, $arr = array()) {
        $stmt = $this->dbh->prepare($sql);
        if (!$stmt->execute($arr)) {
            print_r($stmt->errorInfo());
            die();
        }
        return $stmt->fetch(3)[0];
    }
}

8：BAT

@ECHO OFF
:1
php -c php.ini php-mysql-pdo.php
TIMEOUT 10
GOTO 1
PAUSE

9：php.ini

[PHP]
extension_dir = "ext"
extension=pdo_mysql