View source
<?php
require_once dirname(__FILE__) . '/OAuthStoreAbstract.class.php';
abstract class OAuthStoreSQL extends OAuthStoreAbstract {
protected $max_timestamp_skew = 600;
protected $max_request_token_ttl = 3600;
function __construct($options = array()) {
if (isset($options['conn'])) {
$this->conn = $options['conn'];
}
else {
if (isset($options['server'])) {
$server = $options['server'];
$username = $options['username'];
if (isset($options['password'])) {
$this->conn = mysql_connect($server, $username, $options['password']);
}
else {
$this->conn = mysql_connect($server, $username);
}
}
else {
$this->conn = mysql_connect();
}
if ($this->conn === false) {
throw new OAuthException2('Could not connect to MySQL database: ' . mysql_error());
}
if (isset($options['database'])) {
if (!mysql_select_db($options['database'], $this->conn)) {
$this
->sql_errcheck();
}
}
$this
->query('set character set utf8');
}
}
public function getSecretsForVerify($consumer_key, $token, $token_type = 'access') {
if ($token_type === false) {
$rs = $this
->query_row_assoc('
SELECT osr_id,
osr_consumer_key as consumer_key,
osr_consumer_secret as consumer_secret
FROM oauth_server_registry
WHERE osr_consumer_key = \'%s\'
AND osr_enabled = 1
', $consumer_key);
if ($rs) {
$rs['token'] = false;
$rs['token_secret'] = false;
$rs['user_id'] = false;
$rs['ost_id'] = false;
}
}
else {
$rs = $this
->query_row_assoc('
SELECT osr_id,
ost_id,
ost_usa_id_ref as user_id,
osr_consumer_key as consumer_key,
osr_consumer_secret as consumer_secret,
ost_token as token,
ost_token_secret as token_secret
FROM oauth_server_registry
JOIN oauth_server_token
ON ost_osr_id_ref = osr_id
WHERE ost_token_type = \'%s\'
AND osr_consumer_key = \'%s\'
AND ost_token = \'%s\'
AND osr_enabled = 1
AND ost_token_ttl >= NOW()
', $token_type, $consumer_key, $token);
}
if (empty($rs)) {
throw new OAuthException2('The consumer_key "' . $consumer_key . '" token "' . $token . '" combination does not exist or is not enabled.');
}
return $rs;
}
public function getSecretsForSignature($uri, $user_id, $name = '') {
$ps = parse_url($uri);
$host = isset($ps['host']) ? $ps['host'] : 'localhost';
$path = isset($ps['path']) ? $ps['path'] : '';
if (empty($path) || substr($path, -1) != '/') {
$path .= '/';
}
$secrets = $this
->query_row_assoc('
SELECT ocr_consumer_key as consumer_key,
ocr_consumer_secret as consumer_secret,
oct_token as token,
oct_token_secret as token_secret,
ocr_signature_methods as signature_methods
FROM oauth_consumer_registry
JOIN oauth_consumer_token ON oct_ocr_id_ref = ocr_id
WHERE ocr_server_uri_host = \'%s\'
AND ocr_server_uri_path = LEFT(\'%s\', LENGTH(ocr_server_uri_path))
AND (ocr_usa_id_ref = \'%d\' OR ocr_usa_id_ref IS NULL)
AND oct_token_type = \'access\'
AND oct_name = \'%s\'
AND oct_token_ttl >= NOW()
ORDER BY ocr_usa_id_ref DESC, ocr_consumer_secret DESC, LENGTH(ocr_server_uri_path) DESC
LIMIT 0,1
', $host, $path, $user_id, $name);
if (empty($secrets)) {
throw new OAuthException2('No server tokens available for ' . $uri);
}
$secrets['signature_methods'] = explode(',', $secrets['signature_methods']);
return $secrets;
}
public function getServerTokenSecrets($consumer_key, $token, $token_type, $user_id, $name = '') {
if ($token_type != 'request' && $token_type != 'access') {
throw new OAuthException2('Unkown token type "' . $token_type . '", must be either "request" or "access"');
}
$r = $this
->query_row_assoc('
SELECT ocr_consumer_key as consumer_key,
ocr_consumer_secret as consumer_secret,
oct_token as token,
oct_token_secret as token_secret,
oct_name as token_name,
ocr_signature_methods as signature_methods,
ocr_server_uri as server_uri,
ocr_request_token_uri as request_token_uri,
ocr_authorize_uri as authorize_uri,
ocr_access_token_uri as access_token_uri,
IF(oct_token_ttl >= \'9999-12-31\', NULL, UNIX_TIMESTAMP(oct_token_ttl) - UNIX_TIMESTAMP(NOW())) as token_ttl
FROM oauth_consumer_registry
JOIN oauth_consumer_token
ON oct_ocr_id_ref = ocr_id
WHERE ocr_consumer_key = \'%s\'
AND oct_token_type = \'%s\'
AND oct_token = \'%s\'
AND oct_usa_id_ref = %d
AND oct_token_ttl >= NOW()
', $consumer_key, $token_type, $token, $user_id);
if (empty($r)) {
throw new OAuthException2('Could not find a "' . $token_type . '" token for consumer "' . $consumer_key . '" and user ' . $user_id);
}
if (isset($r['signature_methods']) && !empty($r['signature_methods'])) {
$r['signature_methods'] = explode(',', $r['signature_methods']);
}
else {
$r['signature_methods'] = array();
}
return $r;
}
public function addServerToken($consumer_key, $token_type, $token, $token_secret, $user_id, $options = array()) {
if ($token_type != 'request' && $token_type != 'access') {
throw new OAuthException2('Unknown token type "' . $token_type . '", must be either "request" or "access"');
}
if (isset($options['token_ttl']) && is_numeric($options['token_ttl'])) {
$ttl = 'DATE_ADD(NOW(), INTERVAL ' . intval($options['token_ttl']) . ' SECOND)';
}
else {
if ($token_type == 'request') {
$ttl = 'DATE_ADD(NOW(), INTERVAL ' . $this->max_request_token_ttl . ' SECOND)';
}
else {
$ttl = "'9999-12-31'";
}
}
if (isset($options['server_uri'])) {
$ocr_id = $this
->query_one('
SELECT ocr_id
FROM oauth_consumer_registry
WHERE ocr_consumer_key = \'%s\'
AND ocr_usa_id_ref = %d
AND ocr_server_uri = \'%s\'
', $consumer_key, $user_id, $options['server_uri']);
}
else {
$ocr_id = $this
->query_one('
SELECT ocr_id
FROM oauth_consumer_registry
WHERE ocr_consumer_key = \'%s\'
AND ocr_usa_id_ref = %d
', $consumer_key, $user_id);
}
if (empty($ocr_id)) {
throw new OAuthException2('No server associated with consumer_key "' . $consumer_key . '"');
}
if (isset($options['name']) && $options['name'] != '') {
$name = $options['name'];
}
else {
$name = '';
}
$this
->query('
DELETE FROM oauth_consumer_token
WHERE oct_ocr_id_ref = %d
AND oct_usa_id_ref = %d
AND oct_token_type = LOWER(\'%s\')
AND oct_name = \'%s\'
', $ocr_id, $user_id, $token_type, $name);
$this
->query('
INSERT IGNORE INTO oauth_consumer_token
SET oct_ocr_id_ref = %d,
oct_usa_id_ref = %d,
oct_name = \'%s\',
oct_token = \'%s\',
oct_token_secret= \'%s\',
oct_token_type = LOWER(\'%s\'),
oct_timestamp = NOW(),
oct_token_ttl = ' . $ttl . '
', $ocr_id, $user_id, $name, $token, $token_secret, $token_type);
if (!$this
->query_affected_rows()) {
throw new OAuthException2('Received duplicate token "' . $token . '" for the same consumer_key "' . $consumer_key . '"');
}
}
public function deleteServer($consumer_key, $user_id, $user_is_admin = false) {
if ($user_is_admin) {
$this
->query('
DELETE FROM oauth_consumer_registry
WHERE ocr_consumer_key = \'%s\'
AND (ocr_usa_id_ref = %d OR ocr_usa_id_ref IS NULL)
', $consumer_key, $user_id);
}
else {
$this
->query('
DELETE FROM oauth_consumer_registry
WHERE ocr_consumer_key = \'%s\'
AND ocr_usa_id_ref = %d
', $consumer_key, $user_id);
}
}
public function getServer($consumer_key, $user_id, $user_is_admin = false) {
$r = $this
->query_row_assoc('
SELECT ocr_id as id,
ocr_usa_id_ref as user_id,
ocr_consumer_key as consumer_key,
ocr_consumer_secret as consumer_secret,
ocr_signature_methods as signature_methods,
ocr_server_uri as server_uri,
ocr_request_token_uri as request_token_uri,
ocr_authorize_uri as authorize_uri,
ocr_access_token_uri as access_token_uri
FROM oauth_consumer_registry
WHERE ocr_consumer_key = \'%s\'
AND (ocr_usa_id_ref = %d OR ocr_usa_id_ref IS NULL)
', $consumer_key, $user_id);
if (empty($r)) {
throw new OAuthException2('No server with consumer_key "' . $consumer_key . '" has been registered (for this user)');
}
if (isset($r['signature_methods']) && !empty($r['signature_methods'])) {
$r['signature_methods'] = explode(',', $r['signature_methods']);
}
else {
$r['signature_methods'] = array();
}
return $r;
}
public function getServerForUri($uri, $user_id) {
$ps = parse_url($uri);
$host = isset($ps['host']) ? $ps['host'] : 'localhost';
$path = isset($ps['path']) ? $ps['path'] : '';
if (empty($path) || substr($path, -1) != '/') {
$path .= '/';
}
$server = $this
->query_row_assoc('
SELECT ocr_id as id,
ocr_usa_id_ref as user_id,
ocr_consumer_key as consumer_key,
ocr_consumer_secret as consumer_secret,
ocr_signature_methods as signature_methods,
ocr_server_uri as server_uri,
ocr_request_token_uri as request_token_uri,
ocr_authorize_uri as authorize_uri,
ocr_access_token_uri as access_token_uri
FROM oauth_consumer_registry
WHERE ocr_server_uri_host = \'%s\'
AND ocr_server_uri_path = LEFT(\'%s\', LENGTH(ocr_server_uri_path))
AND (ocr_usa_id_ref = \'%d\' OR ocr_usa_id_ref IS NULL)
ORDER BY ocr_usa_id_ref DESC, consumer_secret DESC, LENGTH(ocr_server_uri_path) DESC
LIMIT 0,1
', $host, $path, $user_id);
if (empty($server)) {
throw new OAuthException2('No server available for ' . $uri);
}
$server['signature_methods'] = explode(',', $server['signature_methods']);
return $server;
}
public function listServerTokens($user_id) {
$ts = $this
->query_all_assoc('
SELECT ocr_consumer_key as consumer_key,
ocr_consumer_secret as consumer_secret,
oct_id as token_id,
oct_token as token,
oct_token_secret as token_secret,
oct_usa_id_ref as user_id,
ocr_signature_methods as signature_methods,
ocr_server_uri as server_uri,
ocr_server_uri_host as server_uri_host,
ocr_server_uri_path as server_uri_path,
ocr_request_token_uri as request_token_uri,
ocr_authorize_uri as authorize_uri,
ocr_access_token_uri as access_token_uri,
oct_timestamp as timestamp
FROM oauth_consumer_registry
JOIN oauth_consumer_token
ON oct_ocr_id_ref = ocr_id
WHERE oct_usa_id_ref = %d
AND oct_token_type = \'access\'
AND oct_token_ttl >= NOW()
ORDER BY ocr_server_uri_host, ocr_server_uri_path
', $user_id);
return $ts;
}
public function countServerTokens($consumer_key) {
$count = $this
->query_one('
SELECT COUNT(oct_id)
FROM oauth_consumer_token
JOIN oauth_consumer_registry
ON oct_ocr_id_ref = ocr_id
WHERE oct_token_type = \'access\'
AND ocr_consumer_key = \'%s\'
AND oct_token_ttl >= NOW()
', $consumer_key);
return $count;
}
public function getServerToken($consumer_key, $token, $user_id) {
$ts = $this
->query_row_assoc('
SELECT ocr_consumer_key as consumer_key,
ocr_consumer_secret as consumer_secret,
oct_token as token,
oct_token_secret as token_secret,
oct_usa_id_ref as usr_id,
ocr_signature_methods as signature_methods,
ocr_server_uri as server_uri,
ocr_server_uri_host as server_uri_host,
ocr_server_uri_path as server_uri_path,
ocr_request_token_uri as request_token_uri,
ocr_authorize_uri as authorize_uri,
ocr_access_token_uri as access_token_uri,
oct_timestamp as timestamp
FROM oauth_consumer_registry
JOIN oauth_consumer_token
ON oct_ocr_id_ref = ocr_id
WHERE ocr_consumer_key = \'%s\'
AND oct_usa_id_ref = %d
AND oct_token_type = \'access\'
AND oct_token = \'%s\'
AND oct_token_ttl >= NOW()
', $consumer_key, $user_id, $token);
if (empty($ts)) {
throw new OAuthException2('No such consumer key (' . $consumer_key . ') and token (' . $token . ') combination for user "' . $user_id . '"');
}
return $ts;
}
public function deleteServerToken($consumer_key, $token, $user_id, $user_is_admin = false) {
if ($user_is_admin) {
$this
->query('
DELETE oauth_consumer_token
FROM oauth_consumer_token
JOIN oauth_consumer_registry
ON oct_ocr_id_ref = ocr_id
WHERE ocr_consumer_key = \'%s\'
AND oct_token = \'%s\'
', $consumer_key, $token);
}
else {
$this
->query('
DELETE oauth_consumer_token
FROM oauth_consumer_token
JOIN oauth_consumer_registry
ON oct_ocr_id_ref = ocr_id
WHERE ocr_consumer_key = \'%s\'
AND oct_token = \'%s\'
AND oct_usa_id_ref = %d
', $consumer_key, $token, $user_id);
}
}
public function setServerTokenTtl($consumer_key, $token, $token_ttl) {
if ($token_ttl <= 0) {
$this
->deleteServerToken($consumer_key, $token, 0, true);
}
else {
$this
->query('
UPDATE oauth_consumer_token, oauth_consumer_registry
SET ost_token_ttl = DATE_ADD(NOW(), INTERVAL %d SECOND)
WHERE ocr_consumer_key = \'%s\'
AND oct_ocr_id_ref = ocr_id
AND oct_token = \'%s\'
', $token_ttl, $consumer_key, $token);
}
}
public function listServers($q = '', $user_id) {
$q = trim(str_replace('%', '', $q));
$args = array();
if (!empty($q)) {
$where = ' WHERE ( ocr_consumer_key like \'%%%s%%\'
OR ocr_server_uri like \'%%%s%%\'
OR ocr_server_uri_host like \'%%%s%%\'
OR ocr_server_uri_path like \'%%%s%%\')
AND (ocr_usa_id_ref = %d OR ocr_usa_id_ref IS NULL)
';
$args[] = $q;
$args[] = $q;
$args[] = $q;
$args[] = $q;
$args[] = $user_id;
}
else {
$where = ' WHERE ocr_usa_id_ref = %d OR ocr_usa_id_ref IS NULL';
$args[] = $user_id;
}
$servers = $this
->query_all_assoc('
SELECT ocr_id as id,
ocr_usa_id_ref as user_id,
ocr_consumer_key as consumer_key,
ocr_consumer_secret as consumer_secret,
ocr_signature_methods as signature_methods,
ocr_server_uri as server_uri,
ocr_server_uri_host as server_uri_host,
ocr_server_uri_path as server_uri_path,
ocr_request_token_uri as request_token_uri,
ocr_authorize_uri as authorize_uri,
ocr_access_token_uri as access_token_uri
FROM oauth_consumer_registry
' . $where . '
ORDER BY ocr_server_uri_host, ocr_server_uri_path
', $args);
return $servers;
}
public function updateServer($server, $user_id, $user_is_admin = false) {
foreach (array(
'consumer_key',
'server_uri',
) as $f) {
if (empty($server[$f])) {
throw new OAuthException2('The field "' . $f . '" must be set and non empty');
}
}
if (!empty($server['id'])) {
$exists = $this
->query_one('
SELECT ocr_id
FROM oauth_consumer_registry
WHERE ocr_consumer_key = \'%s\'
AND ocr_id <> %d
AND (ocr_usa_id_ref = %d OR ocr_usa_id_ref IS NULL)
', $server['consumer_key'], $server['id'], $user_id);
}
else {
$exists = $this
->query_one('
SELECT ocr_id
FROM oauth_consumer_registry
WHERE ocr_consumer_key = \'%s\'
AND (ocr_usa_id_ref = %d OR ocr_usa_id_ref IS NULL)
', $server['consumer_key'], $user_id);
}
if ($exists) {
throw new OAuthException2('The server with key "' . $server['consumer_key'] . '" has already been registered');
}
$parts = parse_url($server['server_uri']);
$host = isset($parts['host']) ? $parts['host'] : 'localhost';
$path = isset($parts['path']) ? $parts['path'] : '/';
if (isset($server['signature_methods'])) {
if (is_array($server['signature_methods'])) {
$server['signature_methods'] = strtoupper(implode(',', $server['signature_methods']));
}
}
else {
$server['signature_methods'] = '';
}
if ($user_is_admin && array_key_exists('user_id', $server)) {
if (is_null($server['user_id'])) {
$update_user = ', ocr_usa_id_ref = NULL';
}
else {
$update_user = ', ocr_usa_id_ref = ' . intval($server['user_id']);
}
}
else {
$update_user = '';
}
if (!empty($server['id'])) {
if (!$user_is_admin) {
$ocr_usa_id_ref = $this
->query_one('
SELECT ocr_usa_id_ref
FROM oauth_consumer_registry
WHERE ocr_id = %d
', $server['id']);
if ($ocr_usa_id_ref != $user_id) {
throw new OAuthException2('The user "' . $user_id . '" is not allowed to update this server');
}
}
$this
->query('
UPDATE oauth_consumer_registry
SET ocr_consumer_key = \'%s\',
ocr_consumer_secret = \'%s\',
ocr_server_uri = \'%s\',
ocr_server_uri_host = \'%s\',
ocr_server_uri_path = \'%s\',
ocr_timestamp = NOW(),
ocr_request_token_uri = \'%s\',
ocr_authorize_uri = \'%s\',
ocr_access_token_uri = \'%s\',
ocr_signature_methods = \'%s\'
' . $update_user . '
WHERE ocr_id = %d
', $server['consumer_key'], $server['consumer_secret'], $server['server_uri'], strtolower($host), $path, isset($server['request_token_uri']) ? $server['request_token_uri'] : '', isset($server['authorize_uri']) ? $server['authorize_uri'] : '', isset($server['access_token_uri']) ? $server['access_token_uri'] : '', $server['signature_methods'], $server['id']);
}
else {
if (empty($update_user)) {
$update_user = ', ocr_usa_id_ref = ' . intval($user_id);
}
$this
->query('
INSERT INTO oauth_consumer_registry
SET ocr_consumer_key = \'%s\',
ocr_consumer_secret = \'%s\',
ocr_server_uri = \'%s\',
ocr_server_uri_host = \'%s\',
ocr_server_uri_path = \'%s\',
ocr_timestamp = NOW(),
ocr_request_token_uri = \'%s\',
ocr_authorize_uri = \'%s\',
ocr_access_token_uri = \'%s\',
ocr_signature_methods = \'%s\'
' . $update_user, $server['consumer_key'], $server['consumer_secret'], $server['server_uri'], strtolower($host), $path, isset($server['request_token_uri']) ? $server['request_token_uri'] : '', isset($server['authorize_uri']) ? $server['authorize_uri'] : '', isset($server['access_token_uri']) ? $server['access_token_uri'] : '', $server['signature_methods']);
$ocr_id = $this
->query_insert_id();
}
return $server['consumer_key'];
}
public function updateConsumer($consumer, $user_id, $user_is_admin = false) {
if (!$user_is_admin) {
foreach (array(
'requester_name',
'requester_email',
) as $f) {
if (empty($consumer[$f])) {
throw new OAuthException2('The field "' . $f . '" must be set and non empty');
}
}
}
if (!empty($consumer['id'])) {
if (empty($consumer['consumer_key'])) {
throw new OAuthException2('The field "consumer_key" must be set and non empty');
}
if (!$user_is_admin && empty($consumer['consumer_secret'])) {
throw new OAuthException2('The field "consumer_secret" must be set and non empty');
}
if (!$user_is_admin) {
$osr_usa_id_ref = $this
->query_one('
SELECT osr_usa_id_ref
FROM oauth_server_registry
WHERE osr_id = %d
', $consumer['id']);
if ($osr_usa_id_ref != $user_id) {
throw new OAuthException2('The user "' . $user_id . '" is not allowed to update this consumer');
}
}
else {
if (array_key_exists('user_id', $consumer)) {
if (is_null($consumer['user_id'])) {
$this
->query('
UPDATE oauth_server_registry
SET osr_usa_id_ref = NULL
WHERE osr_id = %d
', $consumer['id']);
}
else {
$this
->query('
UPDATE oauth_server_registry
SET osr_usa_id_ref = %d
WHERE osr_id = %d
', $consumer['user_id'], $consumer['id']);
}
}
}
$this
->query('
UPDATE oauth_server_registry
SET osr_requester_name = \'%s\',
osr_requester_email = \'%s\',
osr_callback_uri = \'%s\',
osr_application_uri = \'%s\',
osr_application_title = \'%s\',
osr_application_descr = \'%s\',
osr_application_notes = \'%s\',
osr_application_type = \'%s\',
osr_application_commercial = IF(%d,1,0),
osr_timestamp = NOW()
WHERE osr_id = %d
AND osr_consumer_key = \'%s\'
AND osr_consumer_secret = \'%s\'
', $consumer['requester_name'], $consumer['requester_email'], isset($consumer['callback_uri']) ? $consumer['callback_uri'] : '', isset($consumer['application_uri']) ? $consumer['application_uri'] : '', isset($consumer['application_title']) ? $consumer['application_title'] : '', isset($consumer['application_descr']) ? $consumer['application_descr'] : '', isset($consumer['application_notes']) ? $consumer['application_notes'] : '', isset($consumer['application_type']) ? $consumer['application_type'] : '', isset($consumer['application_commercial']) ? $consumer['application_commercial'] : 0, $consumer['id'], $consumer['consumer_key'], $consumer['consumer_secret']);
$consumer_key = $consumer['consumer_key'];
}
else {
$consumer_key = $this
->generateKey(true);
$consumer_secret = $this
->generateKey();
if ($user_is_admin && array_key_exists('user_id', $consumer)) {
if (is_null($consumer['user_id'])) {
$owner_id = 'NULL';
}
else {
$owner_id = intval($consumer['user_id']);
}
}
else {
$owner_id = intval($user_id);
}
$this
->query('
INSERT INTO oauth_server_registry
SET osr_enabled = 1,
osr_status = \'active\',
osr_usa_id_ref = \'%s\',
osr_consumer_key = \'%s\',
osr_consumer_secret = \'%s\',
osr_requester_name = \'%s\',
osr_requester_email = \'%s\',
osr_callback_uri = \'%s\',
osr_application_uri = \'%s\',
osr_application_title = \'%s\',
osr_application_descr = \'%s\',
osr_application_notes = \'%s\',
osr_application_type = \'%s\',
osr_application_commercial = IF(%d,1,0),
osr_timestamp = NOW(),
osr_issue_date = NOW()
', $owner_id, $consumer_key, $consumer_secret, $consumer['requester_name'], $consumer['requester_email'], isset($consumer['callback_uri']) ? $consumer['callback_uri'] : '', isset($consumer['application_uri']) ? $consumer['application_uri'] : '', isset($consumer['application_title']) ? $consumer['application_title'] : '', isset($consumer['application_descr']) ? $consumer['application_descr'] : '', isset($consumer['application_notes']) ? $consumer['application_notes'] : '', isset($consumer['application_type']) ? $consumer['application_type'] : '', isset($consumer['application_commercial']) ? $consumer['application_commercial'] : 0);
}
return $consumer_key;
}
public function deleteConsumer($consumer_key, $user_id, $user_is_admin = false) {
if ($user_is_admin) {
$this
->query('
DELETE FROM oauth_server_registry
WHERE osr_consumer_key = \'%s\'
AND (osr_usa_id_ref = %d OR osr_usa_id_ref IS NULL)
', $consumer_key, $user_id);
}
else {
$this
->query('
DELETE FROM oauth_server_registry
WHERE osr_consumer_key = \'%s\'
AND osr_usa_id_ref = %d
', $consumer_key, $user_id);
}
}
public function getConsumer($consumer_key, $user_id, $user_is_admin = false) {
$consumer = $this
->query_row_assoc('
SELECT *
FROM oauth_server_registry
WHERE osr_consumer_key = \'%s\'
', $consumer_key);
if (!is_array($consumer)) {
throw new OAuthException2('No consumer with consumer_key "' . $consumer_key . '"');
}
$c = array();
foreach ($consumer as $key => $value) {
$c[substr($key, 4)] = $value;
}
$c['user_id'] = $c['usa_id_ref'];
if (!$user_is_admin && !empty($c['user_id']) && $c['user_id'] != $user_id) {
throw new OAuthException2('No access to the consumer information for consumer_key "' . $consumer_key . '"');
}
return $c;
}
public function getConsumerStatic() {
$consumer = $this
->query_one('
SELECT osr_consumer_key
FROM oauth_server_registry
WHERE osr_consumer_key LIKE \'sc-%%\'
AND osr_usa_id_ref IS NULL
');
if (empty($consumer)) {
$consumer_key = 'sc-' . $this
->generateKey(true);
$this
->query('
INSERT INTO oauth_server_registry
SET osr_enabled = 1,
osr_status = \'active\',
osr_usa_id_ref = NULL,
osr_consumer_key = \'%s\',
osr_consumer_secret = \'\',
osr_requester_name = \'\',
osr_requester_email = \'\',
osr_callback_uri = \'\',
osr_application_uri = \'\',
osr_application_title = \'Static shared consumer key\',
osr_application_descr = \'\',
osr_application_notes = \'Static shared consumer key\',
osr_application_type = \'\',
osr_application_commercial = 0,
osr_timestamp = NOW(),
osr_issue_date = NOW()
', $consumer_key);
$consumer = $this
->getConsumerStatic();
}
return $consumer;
}
public function addConsumerRequestToken($consumer_key, $options = array()) {
$token = $this
->generateKey(true);
$secret = $this
->generateKey();
$osr_id = $this
->query_one('
SELECT osr_id
FROM oauth_server_registry
WHERE osr_consumer_key = \'%s\'
AND osr_enabled = 1
', $consumer_key);
if (!$osr_id) {
throw new OAuthException2('No server with consumer_key "' . $consumer_key . '" or consumer_key is disabled');
}
if (isset($options['token_ttl']) && is_numeric($options['token_ttl'])) {
$ttl = intval($options['token_ttl']);
}
else {
$ttl = $this->max_request_token_ttl;
}
if (!isset($options['oauth_callback'])) {
$options['oauth_callback'] = 'oob';
}
$this
->query('
INSERT INTO oauth_server_token
SET ost_osr_id_ref = %d,
ost_usa_id_ref = 1,
ost_token = \'%s\',
ost_token_secret = \'%s\',
ost_token_type = \'request\',
ost_token_ttl = DATE_ADD(NOW(), INTERVAL %d SECOND),
ost_callback_url = \'%s\'
ON DUPLICATE KEY UPDATE
ost_osr_id_ref = VALUES(ost_osr_id_ref),
ost_usa_id_ref = VALUES(ost_usa_id_ref),
ost_token = VALUES(ost_token),
ost_token_secret = VALUES(ost_token_secret),
ost_token_type = VALUES(ost_token_type),
ost_token_ttl = VALUES(ost_token_ttl),
ost_callback_url = VALUES(ost_callback_url),
ost_timestamp = NOW()
', $osr_id, $token, $secret, $ttl, $options['oauth_callback']);
return array(
'token' => $token,
'token_secret' => $secret,
'token_ttl' => $ttl,
);
}
public function getConsumerRequestToken($token) {
$rs = $this
->query_row_assoc('
SELECT ost_token as token,
ost_token_secret as token_secret,
osr_consumer_key as consumer_key,
osr_consumer_secret as consumer_secret,
ost_token_type as token_type,
ost_callback_url as callback_url,
osr_application_title as application_title,
osr_application_descr as application_descr,
osr_application_uri as application_uri
FROM oauth_server_token
JOIN oauth_server_registry
ON ost_osr_id_ref = osr_id
WHERE ost_token_type = \'request\'
AND ost_token = \'%s\'
AND ost_token_ttl >= NOW()
', $token);
return $rs;
}
public function deleteConsumerRequestToken($token) {
$this
->query('
DELETE FROM oauth_server_token
WHERE ost_token = \'%s\'
AND ost_token_type = \'request\'
', $token);
}
public function authorizeConsumerRequestToken($token, $user_id, $referrer_host = '') {
$verifier = substr(md5(rand()), 0, 10);
$this
->query('
UPDATE oauth_server_token
SET ost_authorized = 1,
ost_usa_id_ref = %d,
ost_timestamp = NOW(),
ost_referrer_host = \'%s\',
ost_verifier = \'%s\'
WHERE ost_token = \'%s\'
AND ost_token_type = \'request\'
', $user_id, $referrer_host, $verifier, $token);
return $verifier;
}
public function countConsumerAccessTokens($consumer_key) {
$count = $this
->query_one('
SELECT COUNT(ost_id)
FROM oauth_server_token
JOIN oauth_server_registry
ON ost_osr_id_ref = osr_id
WHERE ost_token_type = \'access\'
AND osr_consumer_key = \'%s\'
AND ost_token_ttl >= NOW()
', $consumer_key);
return $count;
}
public function exchangeConsumerRequestForAccessToken($token, $options = array()) {
$new_token = $this
->generateKey(true);
$new_secret = $this
->generateKey();
if (isset($options['token_ttl']) && is_numeric($options['token_ttl'])) {
$ttl_sql = 'DATE_ADD(NOW(), INTERVAL ' . intval($options['token_ttl']) . ' SECOND)';
}
else {
$ttl_sql = "'9999-12-31'";
}
if (isset($options['verifier'])) {
$verifier = $options['verifier'];
$this
->query('
UPDATE oauth_server_token
SET ost_token = \'%s\',
ost_token_secret = \'%s\',
ost_token_type = \'access\',
ost_timestamp = NOW(),
ost_token_ttl = ' . $ttl_sql . '
WHERE ost_token = \'%s\'
AND ost_token_type = \'request\'
AND ost_authorized = 1
AND ost_token_ttl >= NOW()
AND ost_verifier = \'%s\'
', $new_token, $new_secret, $token, $verifier);
}
else {
$this
->query('
UPDATE oauth_server_token
SET ost_token = \'%s\',
ost_token_secret = \'%s\',
ost_token_type = \'access\',
ost_timestamp = NOW(),
ost_token_ttl = ' . $ttl_sql . '
WHERE ost_token = \'%s\'
AND ost_token_type = \'request\'
AND ost_authorized = 1
AND ost_token_ttl >= NOW()
', $new_token, $new_secret, $token);
}
if ($this
->query_affected_rows() != 1) {
throw new OAuthException2('Can\'t exchange request token "' . $token . '" for access token. No such token or not authorized');
}
$ret = array(
'token' => $new_token,
'token_secret' => $new_secret,
);
$ttl = $this
->query_one('
SELECT IF(ost_token_ttl >= \'9999-12-31\', NULL, UNIX_TIMESTAMP(ost_token_ttl) - UNIX_TIMESTAMP(NOW())) as token_ttl
FROM oauth_server_token
WHERE ost_token = \'%s\'', $new_token);
if (is_numeric($ttl)) {
$ret['token_ttl'] = intval($ttl);
}
return $ret;
}
public function getConsumerAccessToken($token, $user_id) {
$rs = $this
->query_row_assoc('
SELECT ost_token as token,
ost_token_secret as token_secret,
ost_referrer_host as token_referrer_host,
osr_consumer_key as consumer_key,
osr_consumer_secret as consumer_secret,
osr_application_uri as application_uri,
osr_application_title as application_title,
osr_application_descr as application_descr,
osr_callback_uri as callback_uri
FROM oauth_server_token
JOIN oauth_server_registry
ON ost_osr_id_ref = osr_id
WHERE ost_token_type = \'access\'
AND ost_token = \'%s\'
AND ost_usa_id_ref = %d
AND ost_token_ttl >= NOW()
', $token, $user_id);
if (empty($rs)) {
throw new OAuthException2('No server_token "' . $token . '" for user "' . $user_id . '"');
}
return $rs;
}
public function deleteConsumerAccessToken($token, $user_id, $user_is_admin = false) {
if ($user_is_admin) {
$this
->query('
DELETE FROM oauth_server_token
WHERE ost_token = \'%s\'
AND ost_token_type = \'access\'
', $token);
}
else {
$this
->query('
DELETE FROM oauth_server_token
WHERE ost_token = \'%s\'
AND ost_token_type = \'access\'
AND ost_usa_id_ref = %d
', $token, $user_id);
}
}
public function setConsumerAccessTokenTtl($token, $token_ttl) {
if ($token_ttl <= 0) {
$this
->deleteConsumerAccessToken($token, 0, true);
}
else {
$this
->query('
UPDATE oauth_server_token
SET ost_token_ttl = DATE_ADD(NOW(), INTERVAL %d SECOND)
WHERE ost_token = \'%s\'
AND ost_token_type = \'access\'
', $token_ttl, $token);
}
}
public function listConsumers($user_id) {
$rs = $this
->query_all_assoc('
SELECT osr_id as id,
osr_usa_id_ref as user_id,
osr_consumer_key as consumer_key,
osr_consumer_secret as consumer_secret,
osr_enabled as enabled,
osr_status as status,
osr_issue_date as issue_date,
osr_application_uri as application_uri,
osr_application_title as application_title,
osr_application_descr as application_descr,
osr_requester_name as requester_name,
osr_requester_email as requester_email,
osr_callback_uri as callback_uri
FROM oauth_server_registry
WHERE (osr_usa_id_ref = %d OR osr_usa_id_ref IS NULL)
ORDER BY osr_application_title
', $user_id);
return $rs;
}
public function listConsumerApplications($begin = 0, $total = 25) {
$rs = $this
->query_all_assoc('
SELECT osr_id as id,
osr_enabled as enabled,
osr_status as status,
osr_issue_date as issue_date,
osr_application_uri as application_uri,
osr_application_title as application_title,
osr_application_descr as application_descr
FROM oauth_server_registry
ORDER BY osr_application_title
');
return $rs;
}
public function listConsumerTokens($user_id) {
$rs = $this
->query_all_assoc('
SELECT osr_consumer_key as consumer_key,
osr_consumer_secret as consumer_secret,
osr_enabled as enabled,
osr_status as status,
osr_application_uri as application_uri,
osr_application_title as application_title,
osr_application_descr as application_descr,
ost_timestamp as timestamp,
ost_token as token,
ost_token_secret as token_secret,
ost_referrer_host as token_referrer_host,
osr_callback_uri as callback_uri
FROM oauth_server_registry
JOIN oauth_server_token
ON ost_osr_id_ref = osr_id
WHERE ost_usa_id_ref = %d
AND ost_token_type = \'access\'
AND ost_token_ttl >= NOW()
ORDER BY osr_application_title
', $user_id);
return $rs;
}
public function checkServerNonce($consumer_key, $token, $timestamp, $nonce) {
$r = $this
->query_row('
SELECT MAX(osn_timestamp), MAX(osn_timestamp) > %d + %d
FROM oauth_server_nonce
WHERE osn_consumer_key = \'%s\'
AND osn_token = \'%s\'
', $timestamp, $this->max_timestamp_skew, $consumer_key, $token);
if (!empty($r) && $r[1]) {
throw new OAuthException2('Timestamp is out of sequence. Request rejected. Got ' . $timestamp . ' last max is ' . $r[0] . ' allowed skew is ' . $this->max_timestamp_skew);
}
$this
->query('
INSERT IGNORE INTO oauth_server_nonce
SET osn_consumer_key = \'%s\',
osn_token = \'%s\',
osn_timestamp = %d,
osn_nonce = \'%s\'
', $consumer_key, $token, $timestamp, $nonce);
if ($this
->query_affected_rows() == 0) {
throw new OAuthException2('Duplicate timestamp/nonce combination, possible replay attack. Request rejected.');
}
$this
->query('
DELETE FROM oauth_server_nonce
WHERE osn_consumer_key = \'%s\'
AND osn_token = \'%s\'
AND osn_timestamp < %d - %d
', $consumer_key, $token, $timestamp, $this->max_timestamp_skew);
}
public function addLog($keys, $received, $sent, $base_string, $notes, $user_id = null) {
$args = array();
$ps = array();
foreach ($keys as $key => $value) {
$args[] = $value;
$ps[] = "olg_{$key} = '%s'";
}
if (!empty($_SERVER['REMOTE_ADDR'])) {
$remote_ip = $_SERVER['REMOTE_ADDR'];
}
else {
if (!empty($_SERVER['REMOTE_IP'])) {
$remote_ip = $_SERVER['REMOTE_IP'];
}
else {
$remote_ip = '0.0.0.0';
}
}
$ps[] = "olg_received \t= '%s'";
$args[] = $this
->makeUTF8($received);
$ps[] = "olg_sent \t= '%s'";
$args[] = $this
->makeUTF8($sent);
$ps[] = "olg_base_string= '%s'";
$args[] = $base_string;
$ps[] = "olg_notes \t= '%s'";
$args[] = $this
->makeUTF8($notes);
$ps[] = "olg_usa_id_ref = NULLIF(%d,0)";
$args[] = $user_id;
$ps[] = "olg_remote_ip = IFNULL(INET_ATON('%s'),0)";
$args[] = $remote_ip;
$this
->query('INSERT INTO oauth_log SET ' . implode(',', $ps), $args);
}
public function listLog($options, $user_id) {
$where = array();
$args = array();
if (empty($options)) {
$where[] = 'olg_usa_id_ref = %d';
$args[] = $user_id;
}
else {
foreach ($options as $option => $value) {
if (strlen($value) > 0) {
switch ($option) {
case 'osr_consumer_key':
case 'ocr_consumer_key':
case 'ost_token':
case 'oct_token':
$where[] = 'olg_' . $option . ' = \'%s\'';
$args[] = $value;
break;
}
}
}
$where[] = '(olg_usa_id_ref IS NULL OR olg_usa_id_ref = %d)';
$args[] = $user_id;
}
$rs = $this
->query_all_assoc('
SELECT olg_id,
olg_osr_consumer_key AS osr_consumer_key,
olg_ost_token AS ost_token,
olg_ocr_consumer_key AS ocr_consumer_key,
olg_oct_token AS oct_token,
olg_usa_id_ref AS user_id,
olg_received AS received,
olg_sent AS sent,
olg_base_string AS base_string,
olg_notes AS notes,
olg_timestamp AS timestamp,
INET_NTOA(olg_remote_ip) AS remote_ip
FROM oauth_log
WHERE ' . implode(' AND ', $where) . '
ORDER BY olg_id DESC
LIMIT 0,100', $args);
return $rs;
}
protected abstract function query($sql);
protected abstract function query_all_assoc($sql);
protected abstract function query_row_assoc($sql);
protected abstract function query_row($sql);
protected abstract function query_one($sql);
protected abstract function query_affected_rows();
protected abstract function query_insert_id();
protected abstract function sql_printf($args);
protected abstract function sql_escape_string($s);
protected abstract function sql_errcheck($sql);
}
