SQL bug (with fix) in PostgreSQL Store checkServerNonce method

What steps will reproduce the problem?
1. Using the PostgreSQL store, replay a request using an already used nonce.

What is the expected output? What do you see instead?
Expected:
AuthException2 [ Error ]: Duplicate timestamp/nonce combination, possible 
replay attack. Request rejected.

Actual:
 ERROR:  duplicate key value violates unique constraint "oauth_server_nonce_osn_consumer_key_osn_token_osn_timestamp_key"

What version of the product are you using? On what operating system?
Version 175, OS: FreeBSD 10.1, PHP 5.4.29, Postgresql 9.3.4

Please provide any additional information below.
Replace lines 1616-1624 of library/store/OAuthStorePostgreSQL.php with the 
following code to fix the issue:

        $this->query('
            INSERT INTO oauth_server_nonce (
                osn_consumer_key,
                osn_token,
                osn_timestamp,
                osn_nonce
            )
            SELECT \'%s\', \'%s\', %d, \'%s\' 
            WHERE NOT EXISTS (
                SELECT 1 FROM oauth_server_nonce 
                WHERE osn_consumer_key = \'%s\'
                    AND osn_token = \'%s\'
                    AND osn_timestamp = %d
                    AND osn_nonce = \'%s\' 
            )',
            $consumer_key, $token, $timestamp, $nonce,
            $consumer_key, $token, $timestamp, $nonce);

Original issue reported on code.google.com by colmw...@gmail.com on 20 Apr 2015 at 4:03

tofaruk / oauth-php

SQL bug (with fix) in PostgreSQL Store checkServerNonce method #148