phpBB 3.2.8: twitter oauth issue

Get help with installation and running phpBB 3.2.x here. Please do not post bug reports, feature requests, or extension related questions here.
Post Reply
User avatar
Janvitus
Registered User
Posts: 13
Joined: Mon Sep 30, 2019 12:04 pm

phpBB 3.2.8: twitter oauth issue

Post by Janvitus »

Hello, I've enabled twitter authentication in my forum, users can successfully add the authorization token, but when they try to log-in, the forum returns this error:

Code: Select all

SQL ERROR [ postgres ]

ERROR: operator does not exist: character varying = integer LINE 2: ... WHERE provider = 'twitter' AND oauth_provider_id = 27XXXXXX ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. []
Any idea to resolve?

Thanks.

rxu
Extensions Development Team
Posts: 3050
Joined: Wed Oct 25, 2006 12:46 pm
Location: Siberia, Russian Federation
Name: Ruslan
Contact:

Re: phpBB 3.2.8: twitter oauth issue

Post by rxu »

If you can look in the database directly, in the phpbb_oauth_accounts table what the value of oauth_provider_idis actually looking like for the row where provider is twitter? And what is the type of oauth_provider_id field?

Also in oauth_provider_id = 27XXXXXX the 27XXXXXX should be wrapped by apostrophes so kinda weird that it's not (it's coded here https://github.com/phpbb/phpbb/blob/3.2 ... h.php#L641). Probably you have incorrectly edited the error output.

User avatar
Janvitus
Registered User
Posts: 13
Joined: Mon Sep 30, 2019 12:04 pm

Re: phpBB 3.2.8: twitter oauth issue

Post by Janvitus »

Sorry for late response:

Code: Select all

\dS+ phpbb_oauth_accounts
                                             Table "public.phpbb_oauth_accounts"
      Column       |          Type           |               Modifiers                | Storage  | Stats target | Description 
-------------------+-------------------------+----------------------------------------+----------+--------------+-------------
 user_id           | integer                 | not null default 0                     | plain    |              | 
 provider          | character varying(255)  | not null default ''::character varying | extended |              | 
 oauth_provider_id | character varying(4000) | not null default ''::character varying | extended |              | 
Indexes:
    "phpbb_oauth_accounts_pkey" PRIMARY KEY, btree (user_id, provider)
Check constraints:
    "phpbb_oauth_accounts_user_id_check" CHECK (user_id >= 0)
    "phpbb_oauth_accounts_user_id_check1" CHECK (user_id >= 0)
Has OIDs: no

Code: Select all

 provider |   oauth_provider_id   
----------+-----------------------
 google   | 107004068054728715728
 google   | 111029918042061819905
 twitter  | 1151365051900669952
 facebook | 1444203322508003
 twitter  | 27133158
(5 rows)
Any idea?

Thanks.

User avatar
Brf
Support Team Member
Support Team Member
Posts: 51928
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: phpBB 3.2.8: twitter oauth issue

Post by Brf »

oauth_provider_id is a character string. You need to put its value in apostrophes ' '

User avatar
mrgoldy
Jr. Extension Validator
Posts: 1297
Joined: Tue Oct 06, 2009 7:34 pm
Location: The Netherlands
Name: Gijs
Contact:

Re: phpBB 3.2.8: twitter oauth issue

Post by mrgoldy »

The query that errors is located way higher in the oauth.php file (not the linked to by rxu): https://github.com/phpbb/phpbb/blob/3.2 ... h.php#L226

The problem with this is, is that the WHERE clause is build with $this->db->sql_build_array().
Which in turns validates the values, and only quotes if the value is a string. And seeing that the unique id is an integer, it is just casted with intval(), and not quoted.

So you can try two solutions to fix it:
Either change the data array to enforce data types:

Code: Select all

$data = array(
	'provider'		=> (string) $service_name_original,
	'oauth_provider_id'	=> (string) $unique_id
);
Or change the SQL query to the following:

Code: Select all

$sql = 'SELECT user_id
	FROM ' . $this->auth_provider_oauth_token_account_assoc . "
	WHERE provider = '" . $this->db->sql_escape($service_name_original) . "'
		AND oauth_provider_id = '" . $this->db->sql_escape($unique_id) . "'";

rxu
Extensions Development Team
Posts: 3050
Joined: Wed Oct 25, 2006 12:46 pm
Location: Siberia, Russian Federation
Name: Ruslan
Contact:

Re: phpBB 3.2.8: twitter oauth issue

Post by rxu »

mrgoldy wrote:
Tue Oct 08, 2019 3:15 pm
The query that errors is located way higher in the oauth.php
Ah, indeed. It's a bug to be fixed btw. Ticket? ;)

User avatar
mrgoldy
Jr. Extension Validator
Posts: 1297
Joined: Tue Oct 06, 2009 7:34 pm
Location: The Netherlands
Name: Gijs
Contact:

Re: phpBB 3.2.8: twitter oauth issue

Post by mrgoldy »

Sure, I'll create it. I'll immediately add it to my OAuth clean up PR aswell.
I am not a fan of using the sql_build_array for WHERE clauses, but I guess that's just my preference.
So the most logical way is to just cast the values to (string), that will fix it aswell.

Moreover, I will first await the reponse from Janvitus, to verify the fix.
If you (Janvitus) could first try changing the $data array, with enforcing the data types, that would sublime!
Last edited by mrgoldy on Tue Oct 08, 2019 3:47 pm, edited 1 time in total.

rxu
Extensions Development Team
Posts: 3050
Joined: Wed Oct 25, 2006 12:46 pm
Location: Siberia, Russian Federation
Name: Ruslan
Contact:

Re: phpBB 3.2.8: twitter oauth issue

Post by rxu »

mrgoldy thanks.
To mod: this topic has nothing to do with custom coding, but whatever.

User avatar
Janvitus
Registered User
Posts: 13
Joined: Mon Sep 30, 2019 12:04 pm

Re: phpBB 3.2.8: twitter oauth issue

Post by Janvitus »

mrgoldy wrote:
Tue Oct 08, 2019 3:15 pm
So you can try two solutions to fix it:
Either change the data array to enforce data types:

Code: Select all

$data = array(
	'provider'		=> (string) $service_name_original,
	'oauth_provider_id'	=> (string) $unique_id
);
Ok, was a bug. The fix works, thanks ;)

User avatar
mrgoldy
Jr. Extension Validator
Posts: 1297
Joined: Tue Oct 06, 2009 7:34 pm
Location: The Netherlands
Name: Gijs
Contact:

Re: phpBB 3.2.8: twitter oauth issue

Post by mrgoldy »

Thank you for getting back to us/me, Janvitus.
The tracker ticket and the corresponding pull request have been created.

Post Reply

Return to “[3.2.x] Support Forum”