View source
<?php
require_once dirname(__FILE__) . '/OAuthStoreAbstract.class.php';
class OAuthStorePostgreSQL extends OAuthStoreAbstract {
protected $max_timestamp_skew = 600;
protected $max_request_token_ttl = 3600;
private $_lastAffectedRows = 0;
public function install() {
throw new OAuthException2('Not yet implemented, see postgresql/pgsql.sql');
}
function __construct($options = array()) {
if (isset($options['conn'])) {
$this->conn = $options['conn'];
}
else {
if (isset($options['server'])) {
$host = $options['server'];
$user = $options['username'];
$dbname = $options['database'];
$connectionString = sprintf('host=%s dbname=%s user=%s', $host, $dbname, $user);
if (isset($options['password'])) {
$connectionString .= ' password=' . $options['password'];
}
$this->conn = pg_connect($connectionString);
}
elseif (isset($options['connectionString'])) {
$this->conn = pg_connect($options['connectionString']);
}
else {
$this->conn = pg_connect();
}
if ($this->conn === false) {
throw new OAuthException2('Could not connect to PostgresSQL database');
}
}
}
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 = SUBSTR(\'%s\', 1, LENGTH(ocr_server_uri_path))
AND (ocr_usa_id_ref = \'%s\' OR ocr_usa_id_ref IS NULL)
AND oct_usa_id_ref = \'%d\'
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 1
', $host, $path, $user_id, $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,
CASE WHEN oct_token_ttl >= \'9999-12-31\' THEN NULL ELSE oct_token_ttl - NOW() END 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 = 'NOW() + INTERVAL \'' . intval($options['token_ttl']) . ' SECOND\'';
}
else {
if ($token_type == 'request') {
$ttl = '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::text = LOWER(\'%s\')::text
AND oct_name = \'%s\'
', $ocr_id, $user_id, $token_type, $name);
$this
->query('
INSERT INTO
oauth_consumer_token(
oct_ocr_id_ref,
oct_usa_id_ref,
oct_name,
oct_token,
oct_token_secret,
oct_token_type,
oct_timestamp,
oct_token_ttl
)
VALUES (%d,%d,\'%s\',\'%s\',\'%s\',\'%s\',NOW(),' . $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 = SUBSTR(\'%s\', 1, LENGTH(ocr_server_uri_path))
AND (ocr_usa_id_ref = \'%s\' OR ocr_usa_id_ref IS NULL)
ORDER BY ocr_usa_id_ref DESC, consumer_secret DESC, LENGTH(ocr_server_uri_path) DESC
LIMIT 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 FROM oauth_consumer_token
USING oauth_consumer_registry
WHERE
oct_ocr_id_ref = ocr_id
AND ocr_consumer_key = \'%s\'
AND oct_token = \'%s\'
', $consumer_key, $token);
}
else {
$this
->query('
DELETE FROM oauth_consumer_token
USING oauth_consumer_registry
WHERE
oct_ocr_id_ref = ocr_id
AND 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
SET ost_token_ttl = (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);
$this
->query('
UPDATE oauth_consumer_registry
SET ost_token_ttl = (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 {
$update_user_field = '';
$update_user_value = '';
if (empty($update_user)) {
$update_user_field = ', ocr_usa_id_ref';
$update_user_value = ', ' . intval($user_id);
}
$this
->query('
INSERT INTO oauth_consumer_registry (
ocr_consumer_key ,
ocr_consumer_secret ,
ocr_server_uri ,
ocr_server_uri_host ,
ocr_server_uri_path ,
ocr_timestamp ,
ocr_request_token_uri,
ocr_authorize_uri ,
ocr_access_token_uri ,
ocr_signature_methods' . $update_user_field . '
)
VALUES (\'%s\', \'%s\', \'%s\', \'%s\', \'%s\', NOW(), \'%s\', \'%s\', \'%s\', \'%s\'' . $update_user_value . ')', $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('oauth_consumer_registry', 'ocr_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 (
osr_enabled,
osr_status,
osr_usa_id_ref,
osr_consumer_key,
osr_consumer_secret,
osr_requester_name,
osr_requester_email,
osr_callback_uri,
osr_application_uri,
osr_application_title,
osr_application_descr,
osr_application_notes,
osr_application_type,
osr_application_commercial,
osr_timestamp,
osr_issue_date
)
VALUES (\'1\', \'active\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%d\', NOW(), 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 (
osr_enabled,
osr_status,
osr_usa_id_ref,
osr_consumer_key,
osr_consumer_secret,
osr_requester_name,
osr_requester_email,
osr_callback_uri,
osr_application_uri,
osr_application_title,
osr_application_descr,
osr_application_notes,
osr_application_type,
osr_application_commercial,
osr_timestamp,
osr_issue_date
)
VALUES (\'1\',\'active\', NULL, \'%s\', \'\', \'\', \'\', \'\', \'\', \'Static shared consumer key\', \'\', \'Static shared consumer key\', \'\', 0, NOW(), 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 (
ost_osr_id_ref,
ost_usa_id_ref,
ost_token,
ost_token_secret,
ost_token_type,
ost_token_ttl,
ost_callback_url
)
VALUES (%d, \'1\', \'%s\', \'%s\', \'request\', NOW() + INTERVAL \'%d SECOND\', \'%s\')', $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 = '(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 (CASE WHEN ost_token_ttl >= \'9999-12-31\' THEN NULL ELSE ost_token_ttl - NOW() END) 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 = (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] === 't') {
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 INTO oauth_server_nonce (
osn_consumer_key,
osn_token,
osn_timestamp,
osn_nonce
)
VALUES (\'%s\', \'%s\', %d, \'%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'] = "'%s'";
$args[] = $this
->makeUTF8($received);
$ps['olg_sent'] = "'%s'";
$args[] = $this
->makeUTF8($sent);
$ps['olg_base_string'] = "'%s'";
$args[] = $base_string;
$ps['olg_notes'] = "'%s'";
$args[] = $this
->makeUTF8($notes);
$ps['olg_usa_id_ref'] = "NULLIF('%d', '0')";
$args[] = $user_id;
$ps['olg_remote_ip'] = "NULLIF('%s','0.0.0.0')";
$args[] = $remote_ip;
$this
->query('
INSERT INTO oauth_log (' . implode(',', array_keys($ps)) . ')
VALUES(' . 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,
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 function query($sql) {
$sql = $this
->sql_printf(func_get_args());
if (!($res = pg_query($this->conn, $sql))) {
$this
->sql_errcheck($sql);
}
$this->_lastAffectedRows = pg_affected_rows($res);
if (is_resource($res)) {
pg_free_result($res);
}
}
protected function query_all_assoc($sql) {
$sql = $this
->sql_printf(func_get_args());
if (!($res = pg_query($this->conn, $sql))) {
$this
->sql_errcheck($sql);
}
$rs = array();
while ($row = pg_fetch_assoc($res)) {
$rs[] = $row;
}
pg_free_result($res);
return $rs;
}
protected function query_row_assoc($sql) {
$sql = $this
->sql_printf(func_get_args());
if (!($res = pg_query($this->conn, $sql))) {
$this
->sql_errcheck($sql);
}
if ($row = pg_fetch_assoc($res)) {
$rs = $row;
}
else {
$rs = false;
}
pg_free_result($res);
return $rs;
}
protected function query_row($sql) {
$sql = $this
->sql_printf(func_get_args());
if (!($res = pg_query($this->conn, $sql))) {
$this
->sql_errcheck($sql);
}
if ($row = pg_fetch_array($res)) {
$rs = $row;
}
else {
$rs = false;
}
pg_free_result($res);
return $rs;
}
protected function query_one($sql) {
$sql = $this
->sql_printf(func_get_args());
if (!($res = pg_query($this->conn, $sql))) {
$this
->sql_errcheck($sql);
}
$val = pg_fetch_row($res);
if ($val && isset($val[0])) {
$val = $val[0];
}
pg_free_result($res);
return $val;
}
protected function query_affected_rows() {
return $this->_lastAffectedRows;
}
protected function query_insert_id($tableName, $primaryKey = null) {
$sequenceName = $tableName;
if ($primaryKey) {
$sequenceName .= "_{$primaryKey}";
}
$sequenceName .= '_seq';
$sql = "\n SELECT\n CURRVAL('%s')\n ";
$args = array(
$sql,
$sequenceName,
);
$sql = $this
->sql_printf($args);
if (!($res = pg_query($this->conn, $sql))) {
return 0;
}
$val = pg_fetch_row($res, 0);
if ($val && isset($val[0])) {
$val = $val[0];
}
pg_free_result($res);
return $val;
}
protected function sql_printf($args) {
$sql = array_shift($args);
if (count($args) == 1 && is_array($args[0])) {
$args = $args[0];
}
$args = array_map(array(
$this,
'sql_escape_string',
), $args);
return vsprintf($sql, $args);
}
protected function sql_escape_string($s) {
if (is_string($s)) {
return pg_escape_string($this->conn, $s);
}
else {
if (is_null($s)) {
return NULL;
}
else {
if (is_bool($s)) {
return intval($s);
}
else {
if (is_int($s) || is_float($s)) {
return $s;
}
else {
return pg_escape_string($this->conn, strval($s));
}
}
}
}
}
protected function sql_errcheck($sql) {
$msg = "SQL Error in OAuthStorePostgreSQL: " . pg_last_error($this->conn) . "\n\n" . $sql;
throw new OAuthException2($msg);
}
}
