abstract class OAuthStoreSQL in Lingotek Translation 7.2

Hierarchy

• class \OAuthStoreAbstract
  • class \OAuthStoreSQL

File

lib/oauth-php/library/store/OAuthStoreSQL.php, line 39

abstract class OAuthStoreSQL extends OAuthStoreAbstract {

  /**
   * Maximum delta a timestamp may be off from a previous timestamp.
   * Allows multiple consumers with some clock skew to work with the same token.
   * Unit is seconds, default max skew is 10 minutes.
   */
  protected $max_timestamp_skew = 600;

  /**
   * Default ttl for request tokens
   */
  protected $max_request_token_ttl = 3600;

  /**
   * Construct the OAuthStoreMySQL.
   * In the options you have to supply either:
   * - server, username, password and database (for a mysql_connect)
   * - conn (for the connection to be used)
   *
   * @param array options
   */
  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 {

        // Try the default mysql connect
        $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');
    }
  }

  /**
   * Find stored credentials for the consumer key and token. Used by an OAuth server
   * when verifying an OAuth request.
   *
   * @param string consumer_key
   * @param string token
   * @param string token_type		false, 'request' or 'access'
   * @exception OAuthException2 when no secrets where found
   * @return array	assoc (consumer_secret, token_secret, osr_id, ost_id, user_id)
   */
  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;
  }

  /**
   * Find the server details for signing a request, always looks for an access token.
   * The returned credentials depend on which local user is making the request.
   *
   * The consumer_key must belong to the user or be public (user id is null)
   *
   * For signing we need all of the following:
   *
   * consumer_key			consumer key associated with the server
   * consumer_secret		consumer secret associated with this server
   * token				access token associated with this server
   * token_secret			secret for the access token
   * signature_methods	signing methods supported by the server (array)
   *
   * @todo filter on token type (we should know how and with what to sign this request, and there might be old access tokens)
   * @param string uri	uri of the server
   * @param int user_id	id of the logged on user
   * @param string name	(optional) name of the token (case sensitive)
   * @exception OAuthException2 when no credentials found
   * @return array
   */
  public function getSecretsForSignature($uri, $user_id, $name = '') {

    // Find a consumer key and token for the given uri
    $ps = parse_url($uri);
    $host = isset($ps['host']) ? $ps['host'] : 'localhost';
    $path = isset($ps['path']) ? $ps['path'] : '';
    if (empty($path) || substr($path, -1) != '/') {
      $path .= '/';
    }

    // The owner of the consumer_key is either the user or nobody (public consumer key)
    $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;
  }

  /**
   * Get the token and token secret we obtained from a server.
   *
   * @param string	consumer_key
   * @param string 	token
   * @param string	token_type
   * @param int		user_id			the user owning the token
   * @param string	name			optional name for a named token
   * @exception OAuthException2 when no credentials found
   * @return array
   */
  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"');
    }

    // Take the most recent token of the given type
    $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;
  }

  /**
   * Add a request token we obtained from a server.
   *
   * @todo remove old tokens for this user and this ocr_id
   * @param string consumer_key	key of the server in the consumer registry
   * @param string token_type		one of 'request' or 'access'
   * @param string token
   * @param string token_secret
   * @param int 	 user_id			the user owning the token
   * @param array  options			extra options, name and token_ttl
   * @exception OAuthException2 when server is not known
   * @exception OAuthException2 when we received a duplicate token
   */
  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"');
    }

    // Maximum time to live for this token
    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 . '"');
    }

    // Named tokens, unique per user/consumer key
    if (isset($options['name']) && $options['name'] != '') {
      $name = $options['name'];
    }
    else {
      $name = '';
    }

    // Delete any old tokens with the same type and name for this user/server combination
    $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);

    // Insert the new token
    $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 . '"');
    }
  }

  /**
   * Delete a server key.  This removes access to that site.
   *
   * @param string consumer_key
   * @param int user_id	user registering this server
   * @param boolean user_is_admin
   */
  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);
    }
  }

  /**
   * Get a server from the consumer registry using the consumer key
   *
   * @param string consumer_key
   * @param int user_id
   * @param boolean user_is_admin (optional)
   * @exception OAuthException2 when server is not found
   * @return array
   */
  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;
  }

  /**
   * Find the server details that might be used for a request
   *
   * The consumer_key must belong to the user or be public (user id is null)
   *
   * @param string uri	uri of the server
   * @param int user_id	id of the logged on user
   * @exception OAuthException2 when no credentials found
   * @return array
   */
  public function getServerForUri($uri, $user_id) {

    // Find a consumer key and token for the given uri
    $ps = parse_url($uri);
    $host = isset($ps['host']) ? $ps['host'] : 'localhost';
    $path = isset($ps['path']) ? $ps['path'] : '';
    if (empty($path) || substr($path, -1) != '/') {
      $path .= '/';
    }

    // The owner of the consumer_key is either the user or nobody (public consumer key)
    $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;
  }

  /**
   * Get a list of all server token this user has access to.
   *
   * @param int usr_id
   * @return array
   */
  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;
  }

  /**
   * Count how many tokens we have for the given server
   *
   * @param string consumer_key
   * @return int
   */
  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;
  }

  /**
   * Get a specific server token for the given user
   *
   * @param string consumer_key
   * @param string token
   * @param int user_id
   * @exception OAuthException2 when no such token found
   * @return array
   */
  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;
  }

  /**
   * Delete a token we obtained from a server.
   *
   * @param string consumer_key
   * @param string token
   * @param int user_id
   * @param boolean user_is_admin
   */
  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);
    }
  }

  /**
   * Set the ttl of a server access token.  This is done when the
   * server receives a valid request with a xoauth_token_ttl parameter in it.
   *
   * @param string consumer_key
   * @param string token
   * @param int token_ttl
   */
  public function setServerTokenTtl($consumer_key, $token, $token_ttl) {
    if ($token_ttl <= 0) {

      // Immediate delete when the token is past its ttl
      $this
        ->deleteServerToken($consumer_key, $token, 0, true);
    }
    else {

      // Set maximum time to live for this token
      $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);
    }
  }

  /**
   * Get a list of all consumers from the consumer registry.
   * The consumer keys belong to the user or are public (user id is null)
   *
   * @param string q	query term
   * @param int user_id
   * @return array
   */
  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;
  }

  /**
   * Register or update a server for our site (we will be the consumer)
   *
   * (This is the registry at the consumers, registering servers ;-) )
   *
   * @param array server
   * @param int user_id	user registering this server
   * @param boolean user_is_admin
   * @exception OAuthException2 when fields are missing or on duplicate consumer_key
   * @return consumer_key
   */
  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'] = '';
    }

    // When the user is an admin, then the user can update the user_id of this record
    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'])) {

      // Check if the current user can update this server definition
      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');
        }
      }

      // Update the consumer registration
      $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)) {

        // Per default the user owning the key is the user registering the key
        $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'];
  }

  /**
   * Insert/update a new consumer with this server (we will be the server)
   * When this is a new consumer, then also generate the consumer key and secret.
   * Never updates the consumer key and secret.
   * When the id is set, then the key and secret must correspond to the entry
   * being updated.
   *
   * (This is the registry at the server, registering consumers ;-) )
   *
   * @param array consumer
   * @param int user_id	user registering this consumer
   * @param boolean user_is_admin
   * @return string 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');
      }

      // Check if the current user can update this server definition
      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 {

        // User is an admin, allow a key owner to be changed or key to be shared
        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();

      // When the user is an admin, then the user can be forced to something else that the user
      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 {

        // No admin, take the user id as the owner id.
        $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;
  }

  /**
   * Delete a consumer key.  This removes access to our site for all applications using this key.
   *
   * @param string consumer_key
   * @param int user_id	user registering this server
   * @param boolean user_is_admin
   */
  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);
    }
  }

  /**
   * Fetch a consumer of this server, by consumer_key.
   *
   * @param string consumer_key
   * @param int user_id
   * @param boolean user_is_admin (optional)
   * @exception OAuthException2 when consumer not found
   * @return array
   */
  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;
  }

  /**
   * Fetch the static consumer key for this provider.  The user for the static consumer
   * key is NULL (no user, shared key).  If the key did not exist then the key is created.
   *
   * @return string
   */
  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);

      // Just make sure that if the consumer key is truncated that we get the truncated string
      $consumer = $this
        ->getConsumerStatic();
    }
    return $consumer;
  }

  /**
   * Add an unautorized request token to our server.
   *
   * @param string consumer_key
   * @param array options		(eg. token_ttl)
   * @return array (token, token_secret)
   */
  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'])) {

      // 1.0a Compatibility : store callback url associated with request token
      $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,
    );
  }

  /**
   * Fetch the consumer request token, by request token.
   *
   * @param string token
   * @return array  token and consumer details
   */
  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;
  }

  /**
   * Delete a consumer token.  The token must be a request or authorized token.
   *
   * @param string token
   */
  public function deleteConsumerRequestToken($token) {
    $this
      ->query('
					DELETE FROM oauth_server_token
					WHERE ost_token 	 = \'%s\'
					  AND ost_token_type = \'request\'
					', $token);
  }

  /**
   * Upgrade a request token to be an authorized request token.
   *
   * @param string token
   * @param int	 user_id  user authorizing the token
   * @param string referrer_host used to set the referrer host for this token, for user feedback
   */
  public function authorizeConsumerRequestToken($token, $user_id, $referrer_host = '') {

    // 1.0a Compatibility : create a token verifier
    $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;
  }

  /**
   * Count the consumer access tokens for the given consumer.
   *
   * @param string consumer_key
   * @return int
   */
  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;
  }

  /**
   * Exchange an authorized request token for new access token.
   *
   * @param string token
   * @param array options		options for the token, token_ttl
   * @exception OAuthException2 when token could not be exchanged
   * @return array (token, token_secret)
   */
  public function exchangeConsumerRequestForAccessToken($token, $options = array()) {
    $new_token = $this
      ->generateKey(true);
    $new_secret = $this
      ->generateKey();

    // Maximum time to live for this token
    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'];

      // 1.0a Compatibility : check token against oauth_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 {

      // 1.0
      $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;
  }

  /**
   * Fetch the consumer access token, by access token.
   *
   * @param string token
   * @param int user_id
   * @exception OAuthException2 when token is not found
   * @return array  token and consumer details
   */
  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;
  }

  /**
   * Delete a consumer access token.
   *
   * @param string token
   * @param int user_id
   * @param boolean user_is_admin
   */
  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);
    }
  }

  /**
   * Set the ttl of a consumer access token.  This is done when the
   * server receives a valid request with a xoauth_token_ttl parameter in it.
   *
   * @param string token
   * @param int ttl
   */
  public function setConsumerAccessTokenTtl($token, $token_ttl) {
    if ($token_ttl <= 0) {

      // Immediate delete when the token is past its ttl
      $this
        ->deleteConsumerAccessToken($token, 0, true);
    }
    else {

      // Set maximum time to live for this token
      $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);
    }
  }

  /**
   * Fetch a list of all consumer keys, secrets etc.
   * Returns the public (user_id is null) and the keys owned by the user
   *
   * @param int user_id
   * @return array
   */
  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;
  }

  /**
   * List of all registered applications. Data returned has not sensitive
   * information and therefore is suitable for public displaying.
   *
   * @param int $begin
   * @param int $total
   * @return array
   */
  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
				');

    // TODO: pagination
    return $rs;
  }

  /**
   * Fetch a list of all consumer tokens accessing the account of the given user.
   *
   * @param int user_id
   * @return array
   */
  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;
  }

  /**
   * Check an nonce/timestamp combination.  Clears any nonce combinations
   * that are older than the one received.
   *
   * @param string	consumer_key
   * @param string 	token
   * @param int		timestamp
   * @param string 	nonce
   * @exception OAuthException2	thrown when the timestamp is not in sequence or nonce is not unique
   */
  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);
    }

    // Insert the new combination
    $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.');
    }

    // Clean up all timestamps older than the one we just received
    $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);
  }

  /**
   * Add an entry to the log table
   *
   * @param array keys (osr_consumer_key, ost_token, ocr_consumer_key, oct_token)
   * @param string received
   * @param string sent
   * @param string base_string
   * @param string notes
   * @param int (optional) user_id
   */
  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';
      }
    }

    // Build the SQL
    $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);
  }

  /**
   * Get a page of entries from the log.  Returns the last 100 records
   * matching the options given.
   *
   * @param array options
   * @param int user_id	current user
   * @return array log records
   */
  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;
  }

  /* ** Some simple helper functions for querying the mysql db ** */

  /**
   * Perform a query, ignore the results
   *
   * @param string sql
   * @param vararg arguments (for sprintf)
   */
  protected abstract function query($sql);

  /**
   * Perform a query, ignore the results
   *
   * @param string sql
   * @param vararg arguments (for sprintf)
   * @return array
   */
  protected abstract function query_all_assoc($sql);

  /**
   * Perform a query, return the first row
   *
   * @param string sql
   * @param vararg arguments (for sprintf)
   * @return array
   */
  protected abstract function query_row_assoc($sql);

  /**
   * Perform a query, return the first row
   *
   * @param string sql
   * @param vararg arguments (for sprintf)
   * @return array
   */
  protected abstract function query_row($sql);

  /**
   * Perform a query, return the first column of the first row
   *
   * @param string sql
   * @param vararg arguments (for sprintf)
   * @return mixed
   */
  protected abstract function query_one($sql);

  /**
   * Return the number of rows affected in the last query
   */
  protected abstract function query_affected_rows();

  /**
   * Return the id of the last inserted row
   *
   * @return int
   */
  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);

}

Members

Name	Modifiers	Type	Description	Overrides
OAuthStoreAbstract::generateKey	public	function	* Generate a unique key * *
OAuthStoreAbstract::install	abstract public	function		6
OAuthStoreAbstract::isUTF8	protected	function	* Check to see if a string is valid utf8 * *
OAuthStoreAbstract::makeUTF8	protected	function	* Make a string utf8, replacing all non-utf8 chars with a '.' * *
OAuthStoreSQL::$max_request_token_ttl	protected	property	* Default ttl for request tokens
OAuthStoreSQL::$max_timestamp_skew	protected	property	* Maximum delta a timestamp may be off from a previous timestamp. * Allows multiple consumers with some clock skew to work with the same token. * Unit is seconds, default max skew is 10 minutes.
OAuthStoreSQL::addConsumerRequestToken	public	function	* Add an unautorized request token to our server. * * Overrides OAuthStoreAbstract::addConsumerRequestToken
OAuthStoreSQL::addLog	public	function	* Add an entry to the log table * * Overrides OAuthStoreAbstract::addLog	1
OAuthStoreSQL::addServerToken	public	function	* Add a request token we obtained from a server. * * @todo remove old tokens for this user and this ocr_id * Overrides OAuthStoreAbstract::addServerToken
OAuthStoreSQL::authorizeConsumerRequestToken	public	function	* Upgrade a request token to be an authorized request token. * * Overrides OAuthStoreAbstract::authorizeConsumerRequestToken
OAuthStoreSQL::checkServerNonce	public	function	* Check an nonce/timestamp combination. Clears any nonce combinations * that are older than the one received. * * Overrides OAuthStoreAbstract::checkServerNonce
OAuthStoreSQL::countConsumerAccessTokens	public	function	* Count the consumer access tokens for the given consumer. * * Overrides OAuthStoreAbstract::countConsumerAccessTokens
OAuthStoreSQL::countServerTokens	public	function	* Count how many tokens we have for the given server * * Overrides OAuthStoreAbstract::countServerTokens
OAuthStoreSQL::deleteConsumer	public	function	* Delete a consumer key. This removes access to our site for all applications using this key. * * Overrides OAuthStoreAbstract::deleteConsumer
OAuthStoreSQL::deleteConsumerAccessToken	public	function	* Delete a consumer access token. * * Overrides OAuthStoreAbstract::deleteConsumerAccessToken
OAuthStoreSQL::deleteConsumerRequestToken	public	function	* Delete a consumer token. The token must be a request or authorized token. * * Overrides OAuthStoreAbstract::deleteConsumerRequestToken
OAuthStoreSQL::deleteServer	public	function	* Delete a server key. This removes access to that site. * * Overrides OAuthStoreAbstract::deleteServer
OAuthStoreSQL::deleteServerToken	public	function	* Delete a token we obtained from a server. * * Overrides OAuthStoreAbstract::deleteServerToken
OAuthStoreSQL::exchangeConsumerRequestForAccessToken	public	function	* Exchange an authorized request token for new access token. * * Overrides OAuthStoreAbstract::exchangeConsumerRequestForAccessToken
OAuthStoreSQL::getConsumer	public	function	* Fetch a consumer of this server, by consumer_key. * * Overrides OAuthStoreAbstract::getConsumer
OAuthStoreSQL::getConsumerAccessToken	public	function	* Fetch the consumer access token, by access token. * * Overrides OAuthStoreAbstract::getConsumerAccessToken
OAuthStoreSQL::getConsumerRequestToken	public	function	* Fetch the consumer request token, by request token. * * Overrides OAuthStoreAbstract::getConsumerRequestToken
OAuthStoreSQL::getConsumerStatic	public	function	* Fetch the static consumer key for this provider. The user for the static consumer * key is NULL (no user, shared key). If the key did not exist then the key is created. * * Overrides OAuthStoreAbstract::getConsumerStatic
OAuthStoreSQL::getSecretsForSignature	public	function	* Find the server details for signing a request, always looks for an access token. * The returned credentials depend on which local user is making the request. * * The consumer_key must belong to the user or be public (user id is null) * *… Overrides OAuthStoreAbstract::getSecretsForSignature
OAuthStoreSQL::getSecretsForVerify	public	function	* Find stored credentials for the consumer key and token. Used by an OAuth server * when verifying an OAuth request. * * Overrides OAuthStoreAbstract::getSecretsForVerify
OAuthStoreSQL::getServer	public	function	* Get a server from the consumer registry using the consumer key * * Overrides OAuthStoreAbstract::getServer
OAuthStoreSQL::getServerForUri	public	function	* Find the server details that might be used for a request * * The consumer_key must belong to the user or be public (user id is null) * * Overrides OAuthStoreAbstract::getServerForUri
OAuthStoreSQL::getServerToken	public	function	* Get a specific server token for the given user * * Overrides OAuthStoreAbstract::getServerToken
OAuthStoreSQL::getServerTokenSecrets	public	function	* Get the token and token secret we obtained from a server. * * Overrides OAuthStoreAbstract::getServerTokenSecrets
OAuthStoreSQL::listConsumerApplications	public	function	* List of all registered applications. Data returned has not sensitive * information and therefore is suitable for public displaying. * * Overrides OAuthStoreAbstract::listConsumerApplications
OAuthStoreSQL::listConsumers	public	function	* Fetch a list of all consumer keys, secrets etc. * Returns the public (user_id is null) and the keys owned by the user * * Overrides OAuthStoreAbstract::listConsumers
OAuthStoreSQL::listConsumerTokens	public	function	* Fetch a list of all consumer tokens accessing the account of the given user. * * Overrides OAuthStoreAbstract::listConsumerTokens
OAuthStoreSQL::listLog	public	function	* Get a page of entries from the log. Returns the last 100 records * matching the options given. * * Overrides OAuthStoreAbstract::listLog	1
OAuthStoreSQL::listServers	public	function	* Get a list of all consumers from the consumer registry. * The consumer keys belong to the user or are public (user id is null) * * Overrides OAuthStoreAbstract::listServers
OAuthStoreSQL::listServerTokens	public	function	* Get a list of all server token this user has access to. * * Overrides OAuthStoreAbstract::listServerTokens
OAuthStoreSQL::query	abstract protected	function	* Perform a query, ignore the results * *	2
OAuthStoreSQL::query_affected_rows	abstract protected	function	* Return the number of rows affected in the last query	2
OAuthStoreSQL::query_all_assoc	abstract protected	function	* Perform a query, ignore the results * *	2
OAuthStoreSQL::query_insert_id	abstract protected	function	* Return the id of the last inserted row * *	2
OAuthStoreSQL::query_one	abstract protected	function	* Perform a query, return the first column of the first row * *	2
OAuthStoreSQL::query_row	abstract protected	function	* Perform a query, return the first row * *	2
OAuthStoreSQL::query_row_assoc	abstract protected	function	* Perform a query, return the first row * *	2
OAuthStoreSQL::setConsumerAccessTokenTtl	public	function	* Set the ttl of a consumer access token. This is done when the * server receives a valid request with a xoauth_token_ttl parameter in it. * * Overrides OAuthStoreAbstract::setConsumerAccessTokenTtl
OAuthStoreSQL::setServerTokenTtl	public	function	* Set the ttl of a server access token. This is done when the * server receives a valid request with a xoauth_token_ttl parameter in it. * *
OAuthStoreSQL::sql_errcheck	abstract protected	function		2
OAuthStoreSQL::sql_escape_string	abstract protected	function		2
OAuthStoreSQL::sql_printf	abstract protected	function		2
OAuthStoreSQL::updateConsumer	public	function	* Insert/update a new consumer with this server (we will be the server) * When this is a new consumer, then also generate the consumer key and secret. * Never updates the consumer key and secret. * When the id is set, then the key and secret… Overrides OAuthStoreAbstract::updateConsumer
OAuthStoreSQL::updateServer	public	function	* Register or update a server for our site (we will be the consumer) * * (This is the registry at the consumers, registering servers ;-) ) * * Overrides OAuthStoreAbstract::updateServer
OAuthStoreSQL::__construct		function	* Construct the OAuthStoreMySQL. * In the options you have to supply either: * - server, username, password and database (for a mysql_connect) * - conn (for the connection to be used) * *	3