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('',16,10),10,16),16,"0"),
LPAD(CONV(CONV(MID(hexkey,17 ,16),16,10) ^ CONV('',16,10),10,16),16,"0"),
LPAD(CONV(CONV(MID(hexkey,33 ,16),16,10) ^ CONV('',16,10),10,16),16,"0"),
LPAD(CONV(CONV(MID(hexkey,49 ,16),16,10) ^ CONV('',16,10),10,16),16,"0"),
LPAD(CONV(CONV(MID(hexkey,65 ,16),16,10) ^ CONV('',16,10),10,16),16,"0"),
LPAD(CONV(CONV(MID(hexkey,81 ,16),16,10) ^ CONV('',16,10),10,16),16,"0"),
LPAD(CONV(CONV(MID(hexkey,97 ,16),16,10) ^ CONV('',16,10),10,16),16,"0"),
LPAD(CONV(CONV(MID(hexkey,113,16),16,10) ^ CONV('',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', '');
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