I installed the Oauth and Twitter modules on my Drupal 7.22 site on an Ubuntu 12.04 server, w/ Postgres 9.1/PHP5.3. I added an application in twitter, and added the keys in the module settings tab. When I added the account/authorize app, in the tweets tab, and checked tweets on the checkbox, I see a view link appear after I save the configuration. When I click on that link it takes me to this page:

http://nimhq.net/?q=tweets/nimhq

I get this error: 

PDOException: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type numeric: "nimhq" LINE 3: WHERE twitter_uid = 'nimhq' ^: SELECT * FROM {twitter_account} WHERE twitter_uid = :id OR screen_name = :id; Array ( [:id] => nimhq ) in twitter_account_load() (line 82 of /home/john/public/sites/all/modules/twitter/twitter.inc).

I've found this report here which seems similar:

http://drupal.org/node/1889336

And these reports in Drupal Core:

http://drupal.org/node/1932612
http://drupal.org/node/1003788

Not really sure if something patched there will fix this, or what really is the problem. I did try reinstalling the modules a few times, but consistently get the same error above.

CommentFileSizeAuthor
#17 twitter-n1985708-17.patch1.71 KBdamienmckenna
#11 twitter-n1985708-11.patch2.7 KBdamienmckenna
#3 twitter-postgres-1985708-3.patch1.26 KBerik.erskine

Comments

christoph’s picture

christoph commented

The issue arises from Postgres not automatically casting between data types (Mysql does - I think not per standards, but not sure). The fix is relatively easy - cast the value in PHP before passing it into the query. So line 78 of twitter.inc could read:-

  if ($values = db_query('SELECT *
                          FROM {twitter_account}
                          WHERE twitter_uid = :tid
                            OR screen_name  = :id',
                          array(':tid' => intval($id), ':id' => $id))->fetchAssoc()) {

Note that the value :tid is cast to integer by intaval($id). This fixed the issue for me.

floydwilde’s picture

floydwilde commented

Thanks for that christoph. There was a code change in 5.8 that effected that same block of code: 

--- twitter-5.7/twitter.inc	2013-04-19 06:42:08.000000000 +0000
+++ twitter-5.8/twitter.inc	2013-06-27 23:10:00.000000000 +0000
@@ -75,11 +75,13 @@
  *   TwitterUser object or NULL.
  */
 function twitter_account_load($id) {
-  if ($values = db_query('SELECT *
-                          FROM {twitter_account}
-                          WHERE twitter_uid = :id
-                            OR screen_name  = :id',
-                          array(':id' => $id))->fetchAssoc()) {
+  $values = db_query('SELECT *
+                      FROM {twitter_account}
+                      WHERE twitter_uid = :id_1
+                      OR screen_name  = :id_2',
+                      array(':id_1' => $id, ':id_2' => $id))
+              ->fetchAssoc();
+  if (!empty($values)) {
     $values['id'] = $values['twitter_uid'];
     $account = new TwitterUser($values);
     $account->set_auth($values);

I would still get the same error w/ that code, and I wasn't sure how to apply christoph's patch to that, so I reverted my "twitter.inc" in the 5.8 module to the one from 5.7, and applied this: 

--- twitter-5.7/twitter.inc	2013-04-19 06:42:08.000000000 +0000
+++ twitter/twitter.inc	2013-06-27 23:30:31.000000000 +0000
@@ -77,9 +77,9 @@
 function twitter_account_load($id) {
   if ($values = db_query('SELECT *
                           FROM {twitter_account}
-                          WHERE twitter_uid = :id
+                          WHERE twitter_uid = :tid
                             OR screen_name  = :id',
-                          array(':id' => $id))->fetchAssoc()) {
+                          array(':tid' => intval($id), ':id' => $id))->fetchAssoc()) {
     $values['id'] = $values['twitter_uid'];
     $account = new TwitterUser($values);
     $account->set_auth($values);

And now things are working as expected for me on Postgres.

FYI: the patch in 5.8 was related to this bug on SQL Server: https://drupal.org/node/1905324

erik.erskine’s picture

erik.erskine commented
Status: Active » Needs review
StatusFileSize
new twitter-postgres-1985708-3.patch1.26 KB

As @christoph mentions in #1, PostgreSQL (and possibly other databases) don't like comparing the numeric twitter_uid column to a string.

Given that $id parameter in twitter_account_load can be either a twitter uid or a screen name, we probably need to do one of two queries depending on it's type.

If $id is numeric, it could be either a twitter uid or a screen name:

    $query = db_query('SELECT *
                       FROM {twitter_account}
                       WHERE twitter_uid = :twitter_uid
                       OR screen_name  = :screen_name',
                       array(':twitter_uid' => $id, ':screen_name' => $id));

But if $id is a string, it is only ever a screen name:

    $query = db_query('SELECT *
                       FROM {twitter_account}
                       WHERE screen_name  = :screen_name',
                       array(':screen_name' => $id));

Patch is attached (works on both postgresql and mysql)

thatoneguy’s picture

thatoneguy commented

The patch in #3 would be the way to go, and it works. intval(string) evaluates to 0, which is probably not what was intended.

thatoneguy’s picture

thatoneguy commented
Issue summary: View changes

Is there a case where a Twitter username could conflict with another user's uid?

erik.erskine’s picture

erik.erskine commented

Theoretically yes, but I think that is outside the scope of this issue.

If you had one account with a screen name of 5 and another with a uid of <5> then the query concerned would match both. That's true regardless of this change though. All this patch does is assume that if id is non-numeric, it cannot be a uid, thereby avoiding the error that occurs when trying to cast it to a number.

damienmckenna’s picture

damienmckenna
Location TN, USA
commented
Status: Needs review » Needs work

Triggering testbot.

damienmckenna’s picture

damienmckenna
Location TN, USA
commented
Version: 7.x-5.7 » 7.x-5.x-dev
Status: Needs work » Needs review

Triggering testbot.

Status: Needs review » Needs work

The last submitted patch, 3: twitter-postgres-1985708-3.patch, failed testing.

damienmckenna’s picture

damienmckenna
Location TN, USA
commented
Parent issue: » #2402307: Plan for Twitter v7.x-5.9 release
damienmckenna’s picture

damienmckenna
Location TN, USA
commented
Status: Needs work » Needs review
StatusFileSize
new twitter-n1985708-11.patch2.7 KB

Given that we control the internal API, I think a much better approach would be to specifically tell twitter_account_load() whether the $id is a uid or screen name.

Could someone please give this a quick test?

damienmckenna’s picture

damienmckenna
Location TN, USA
commented

Of course the 7.x-6.x branch solved this by switching to using the screen name for everything. Doh.

damienmckenna’s picture

damienmckenna
Location TN, USA
commented

OTOH the 6.x-5.x branch uses the uid for everything.

erik.erskine’s picture

erik.erskine commented

#11 works for me. Using postgres 9.3 and 7.x-5.x branch.

  • DamienMcKenna committed 8230e63 on 7.x-5.x 
    Issue #1985708 by DamienMcKenna, ingaro: Internal API incorrectly...
damienmckenna’s picture

damienmckenna
Location TN, USA
commented
Status: Needs review » Fixed

@ingaro: Thanks for the review. This has been committed to the 7.x-5.x branch.

damienmckenna’s picture

damienmckenna
Location TN, USA
commented
Version: 7.x-5.x-dev » 6.x-5.x-dev
Status: Fixed » Needs review
StatusFileSize
new twitter-n1985708-17.patch1.71 KB

This is a backport of the changes to the 6.x-5.x branch.

damienmckenna’s picture

damienmckenna
Location TN, USA
commented
Status: Needs review » Fixed

I've committed the changes to the 6.x-5.x branch too.

  • DamienMcKenna committed a894f1a on 6.x-5.x 
    Issue #1985708 by DamienMcKenna, ingaro: Internal API incorrectly...

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.