Add some very simple DB wrapper so we can use mysqli or PDO instead of mysql_*

[GoogleCodeExporter]

Title says it all.

Nobody uses mysql_ these days. Mysqli_ or PDO should be supported, e.g. via a 
simple wrapper class.

Original issue reported on code.google.com by masterch...@googlemail.com on 12 Nov 2009 at 6:29

[GoogleCodeExporter]

I just submitted a new issue, attaching a store for mysqli.

Original comment by webmaste...@gmail.com on 8 Jan 2010 at 8:12

[GoogleCodeExporter]

Support for mysqli is written. PDO or MDB2 may be implemented if more people 
ask for
it--for now, just pass the mysql/mysqli connection. 

Original comment by brunobg%...@gtempaccount.com on 12 Jan 2010 at 6:46

• Changed state: Started

[GoogleCodeExporter]

Original comment by brunobg%...@gtempaccount.com on 12 Jan 2010 at 6:53

• Added labels: Type-Enhancement
• Removed labels: Type-Defect

[GoogleCodeExporter]

Plz plz plz, PDO Support would be great! :o) All my Database calls using a PDO
Connection. It will be great if i can pass that connection to oauth-php.

André

Original comment by fiedler....@gmail.com on 28 Jan 2010 at 8:43

[GoogleCodeExporter]

@fiedler.andre: I started to work on it. Would you be willing to test the code?

Original comment by brunobg%...@gtempaccount.com on 6 Feb 2010 at 5:03

• Added labels: Priority-High
• Removed labels: Priority-Medium

[GoogleCodeExporter]

@brunobg: Thx, n.p. I´ve tested the code, there are some bugs:

Fatal error: Class 'OAuthStoreSQL' not found in 
\lib\oauth\store\OAuthStoreMySQL.php
 on line 39

Fatal error: Access level to OAuthStorePDO::$conn must be protected (as in class
OAuthStoreMySQL) or weaker in /lib/oauth/store/OAuthStorePDO.php  on line 42

Exception: Undefined index: host in /lib/oauth/OAuthRequest.php on line: 520

Patch:

 if (isset($_SERVER['HTTP_HOST']))
 {
    $ps_pre = parse_url('http://' . $_SERVER['HTTP_HOST']);
    if(isset($ps_pre['host'])) $ps['host'] = $ps_pre['host'];
    if(isset($ps_pre['port'])) $ps['port'] = $ps_pre['port'];
 }
 else
 {
    $ps['host'] = '';
 }

Exception: mysqli_query() expects parameter 1 to be mysqli, object given in
/lib/oauth/store/OAuthStorePDO.php on line: 135

Patch:

remove all "mysqli_*" calls from OAuthStorePDO ;o)

Original comment by fiedler....@gmail.com on 8 Feb 2010 at 6:30

[GoogleCodeExporter]

@fiedler.andre: oooops, I forgot to finish it.

I fixed the bugs you mentioned, and finished the port. I'm not sure if
query_all_assoc is returning the results in the expected format (if not,
query_row_assoc will also not work). Please let me know if any errors remain.

Original comment by brunobg%...@gtempaccount.com on 18 Feb 2010 at 5:11

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Really great work, thx! But there are some more bugs:

Fatal error: main() [function.require]: Failed opening required
'/www/htdocs/w007eff5/ratrr/lib/oauth/store/OAuthStoreSQL.class.php'
(include_path='.:/usr/share/php:..') in
/www/htdocs/w007eff5/ratrr/lib/oauth/store/OAuthStorePDO.php  on line 242

Patch: rename include from "OAuthStoreSQL.class.php" to "OAuthStoreSQL.php"

------------------------------------------------------

Fatal error: Class OAuthStorePDO contains 1 abstract method and must therefore 
be
declared abstract or implement the remaining methods 
(OAuthStoreAbstract::install) in
/www/htdocs/w007eff5/ratrr/lib/oauth/store/OAuthStorePDO.php  on line 37

Patch: implement method "install"

/**
* Initialise the database
*/
public function install ()
{
    require_once dirname(__FILE__) . '/mysql/install.php';
}

------------------------------------------------------

Undefined index: host in '/www/htdocs/w007eff5/ratrr/lib/oauth/OAuthRequest.php'
(line: 521)

Patch: I´ve rewritten first part of "parseUri" method:

protected function parseUri ( $parameters )
{
    $ps = @parse_url($this->uri);

    $proto = (isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] == 'on') ? 'https' : 'http';

    $ps_pre = parse_url(sprintf('%s://%s%s', $proto, $_SERVER['HTTP_HOST'], 
$_SERVER['REQUEST_URI']));

    // Get the current/requested method
    if (empty($ps['scheme']))
    {
        $ps['scheme'] = $ps_pre['scheme'];
    }
    else
    {
        $ps['scheme'] = strtolower($ps['scheme']);
    }

    // Get the current/requested host
    if (empty($ps['host']))
    {
        $ps['host'] = $ps_pre['host'];
    }
    if (empty($ps['port']))
    {
        $ps['port'] = $ps_pre['port'];
    }

    $ps['host'] = mb_strtolower($ps['host']);
    if (!preg_match('/^[a-z0-9\.\-]+$/', $ps['host']))
    {
        throw new OAuthException2('Unsupported characters in host name');
    }

    //...
}

------------------------------------------------------

OAuth Verification Failed: SQL Error in OAuthStoreMySQL: Array
(
    [0] => 42000
    [1] => 1064
    [2] => You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near
'01a1054e2d101c3b0250811f49df96c104b62a89d''
                          AND osr_enabled       = 1' at line 5
)

                        SELECT  osr_id, 
                                osr_consumer_key        as consumer_key,
                                osr_consumer_secret     as consumer_secret
                        FROM oauth_server_registry
                        WHERE osr_consumer_key  = ''01a1054e2d101c3b0250811f49df96c104b62a89d''
                          AND osr_enabled       = 1

Original comment by fiedler....@gmail.com on 19 Feb 2010 at 2:19

Attachments:

• OAuthRequest.php

[GoogleCodeExporter]

SQL Error is because of OAuthStorePDO does add quotes if parameter is a string 
in:

protected function sql_escape_string ( $s )
{
    if (is_string($s))
    {
        return $this->conn->quote($s);
    }
    //...
}

and OAuthStoreSQL does also add quotes when building the sql query. So all 
strings
will get double qouted.

Original comment by fiedler....@gmail.com on 19 Feb 2010 at 2:26

[GoogleCodeExporter]

Wrongt SQL Debug Message, replace "SQL Error in OAuthStoreMySQL: " with "SQL 
Error in
OAuthStorePDO: " in OAuthStorePDO.php

protected function sql_errcheck ( $sql )
{
    $msg =  "SQL Error in OAuthStorePDO: ". print_r($this->conn->errorInfo(), true)
."\n\n" . $sql;
    throw new OAuthException2($msg);
}

Is it possible to get the line number where error is thrown?

Original comment by fiedler....@gmail.com on 19 Feb 2010 at 2:31

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

SQL Error Message: 

You have an error in your SQL syntax; check the manual that corresponds to your 
MySQL
server version for the right syntax to use near 'table' at line 1

in OAuthStorePDO.php in method:

protected function query_row ( $sql )
{
    // TODO: test
    $sql = $this->sql_printf(func_get_args());
    try 
    {
        $row = $this->conn->query("select count(*) from table")->fetch(PDO::FETCH_ASSOC);
    }
    catch (PDOException $e)
    {
        $this->sql_errcheck($sql);
    }
    return $row;
}

Original comment by fiedler....@gmail.com on 19 Feb 2010 at 2:34

[GoogleCodeExporter]

Undefined offset: 1 in OAuthStoreSQL.php on line 1541

var dump of $r is:

array(2) {
  ["MAX(osn_timestamp)"]=>
  NULL
  ["MAX(osn_timestamp) > 1266572663 + 600"]=>
  NULL
}

Patch:

replace

if (!empty($r) && $r[1])

with:

if (isset($r[1]) && $r[1])

Original comment by fiedler....@gmail.com on 19 Feb 2010 at 2:46

[GoogleCodeExporter]

Regarding comment #9: Added all patches, except the parse_uri. I made a 
different
change that I hope will fix the bug.

Regarding comment #10: Fixed.

Regarding comment #11: Yep, I got that info from a debug_backtrace(). It's easy 
to
get more info to debug, if you need it.

Regarding comment #13: Fixed (please test)

And, last but not least, thanks for all this help!

Original comment by brunobg%...@gtempaccount.com on 19 Feb 2010 at 2:56

[GoogleCodeExporter]

Regarding #14: no, your patch won't work. I think the one I just commited 
(version
86) should solve it.

Original comment by brunobg%...@gtempaccount.com on 19 Feb 2010 at 3:01

[GoogleCodeExporter]

k, thx a lot! I will take a look if it works for me.

Original comment by fiedler....@gmail.com on 19 Feb 2010 at 3:06

[GoogleCodeExporter]

http://code.google.com/p/oauth-php/source/diff?spec=svn85&r=85&format=side&path=
/trunk/library/OAuthRequest.php

Does not fix the error. I got "Undefined index: host" because 
$_SERVER['HTTP_HOST']
is set with value "www.xyz.de" and var dump of $ps_pre is

array(1) {
  ["path"]=>
  string(13) "www.xyz.de"
}

Original comment by fiedler....@gmail.com on 19 Feb 2010 at 3:20

[GoogleCodeExporter]

fiedler.andre, I finally reproduced the host bug. The problem is that this->uri 
at
the constructor seems to be wrong; it never has the server in the string! It 
should
be set to the sprintf() of your patch. 

I committed a new tentative patch that passed my first tests.

Original comment by webmaste...@gmail.com on 19 Feb 2010 at 6:21

[GoogleCodeExporter]

k, looks good. One more thing in OAuthStorePDO on line 110:

$result = $stmt->fetch(PDO::FETCH_ASSOC);

should be:

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

http://de3.php.net/manual/de/pdostatement.fetchall.php

Original comment by fiedler....@gmail.com on 19 Feb 2010 at 10:31

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Comment #20: fixed

Original comment by brunobg%...@gtempaccount.com on 22 Feb 2010 at 12:32

[GoogleCodeExporter]

@fiedler.andre: have you had a chance to test the latest version? 

Original comment by brunobg%...@gtempaccount.com on 26 Feb 2010 at 12:27

[GoogleCodeExporter]

hi, no. had no time to test it. Possibly next week or can you test?

Original comment by fiedler....@gmail.com on 26 Feb 2010 at 12:33

[GoogleCodeExporter]

No hurry!

Original comment by brunobg%...@gtempaccount.com on 26 Feb 2010 at 9:02

[GoogleCodeExporter]

Ok, had time to test it. There are no more php erros, but i get the message:

"OAuth Verification Failed: The consumer_key "89c2c379b2f9221fc17a4dd9e672faee" 
token
"" combination does not exist or is not enabled."

The request was:

request url:
http://www.xxxxxx.com/oauth/request_token?oauth_version=1.0&oauth_nonce=dbf68355
11fcfe84868a1dc0c6433fdf&oauth_timestamp=1269102209&oauth_consumer_key=89c2c379b
2f9221fc17a4dd9e672faee&oauth_signature_method=RSA-SHA1&oauth_signature=cIFrY0b4
ySa6501VxmeCyKcrh8%2FYPLL1DP%2BQ28MhWsVTHEqSV1u3Ex3wI6ZVZycfKPA5AIvg6n33OF%2F1mG
cbGTjVOwp44Dm%2FcD1zpy3aR8V5A3CrjUGoqOfq1Kf4XChTa4GVJzqZrm%2F%2FO6Fcz5ycxWEcIO2v
xE1RGeZm%2BQoc8tI%3D

OAuthRequest Object
(
    [parameters:private] => Array
        (
            [oauth_version] => 1.0
            [oauth_nonce] => dbf6835511fcfe84868a1dc0c6433fdf
            [oauth_timestamp] => 1269102209
            [oauth_consumer_key] => 89c2c379b2f9221fc17a4dd9e672faee
            [oauth_signature_method] => RSA-SHA1
            [oauth_signature] =>
cIFrY0b4ySa6501VxmeCyKcrh8/YPLL1DP+Q28MhWsVTHEqSV1u3Ex3wI6ZVZycfKPA5AIvg6n33OF/1
mGcbGTjVOwp44Dm/cD1zpy3aR8V5A3CrjUGoqOfq1Kf4XChTa4GVJzqZrm//O6Fcz5ycxWEcIO2vxE1R
GeZm+Qoc8tI=
        )

    [http_method:private] => GET
    [http_url:private] => http://www.xxxxxx.com/oauth/request_token
    [base_string] =>
GET&http%3A%2F%2Fwww.xxxxxx.com%2Foauth%2Frequest_token&oauth_consumer_key%3D89c
2c379b2f9221fc17a4dd9e672faee%26oauth_nonce%3Ddbf6835511fcfe84868a1dc0c6433fdf%2
6oauth_signature_method%3DRSA-SHA1%26oauth_timestamp%3D1269102209%26oauth_versio
n%3D1.0
)

Original comment by fiedler....@gmail.com on 20 Mar 2010 at 4:27

[GoogleCodeExporter]

I can't find what would have caused this either than a failed insert or failed
select, but I can't find such a bug...

Let's see, I assume you have previously tested this with mysql/mysqli? just to 
be
sure that it's not a general bug, but particular to PDO.

If so, could you check the DB to see if this key was actually inserted?

Original comment by brunobg%...@gtempaccount.com on 22 Mar 2010 at 3:56

[GoogleCodeExporter]

Hi, yes i had tested it with mysql some weeks ago (bevor all those changes to 
PDO).
At that time all worked fine. But haven´t tested it yet. Let´s see if i got 
some time
to change all back to mysql again.

But first i will check the db if there was insert something.

Original comment by fiedler....@gmail.com on 22 Mar 2010 at 5:35

[GoogleCodeExporter]

Ok, i switched the DB Wrapper back to MySQL in the "request token" script. Now 
i get
the response:

oauth_token=1ec959bb72e369131adb7675fa6ac54804ba7c15e&oauth_token_secret=df47fcc
b8e996f37b9ac7b61a1ab735e&xoauth_token_ttl=3600

Works fine. But with PDO Wrapper i get the message mentioned in comment #26.

Original comment by fiedler....@gmail.com on 22 Mar 2010 at 7:16

[GoogleCodeExporter]

Perhaps your problem with the PDO wrapper is due to Issue #33

Original comment by philfreo on 2 Apr 2010 at 5:53

[GoogleCodeExporter]

Thanks! I will test again, when issue #33 has been fixed.

Original comment by fiedler....@gmail.com on 2 Apr 2010 at 6:19

[GoogleCodeExporter]

Just fixed Issue #33.

Original comment by brunobg%...@gtempaccount.com on 5 Apr 2010 at 6:01

[GoogleCodeExporter]

Hi, 

i´ve downloaded r108 to test if the PDO wrapper did work now. But i get these 
error message:

----------------------------------
Exception: OAuthException2

Message

No OAuthSession for Session (file 
/www/htdocs/w007eff5/ratrr/lib/oauth/session/OAuthSessionSession.php)

Stack Trace

lib/oauth/OAuthSession.php (line: 76)
lib/oauth/OAuthServer.php (line: 54)
app/actions/OAuth_RequestToken.php (line: 34)
----------------------------------

How do i fix this? thx Sunny

Original comment by fiedler....@gmail.com on 17 Apr 2010 at 4:54

[GoogleCodeExporter]

Hm, think i got it... the missed file is named "OAuthSessionSESSION.php" ?! 
Because of Linux´s case sensitive file 
system the script cant find this file.

Original comment by fiedler....@gmail.com on 17 Apr 2010 at 5:12

[GoogleCodeExporter]

Now i get this message:

-------------------------
Fatal error: Cannot instantiate abstract class OAuthSessionSESSION in 
lib/oauth/OAuthSession.php on line 67

Original comment by fiedler....@gmail.com on 17 Apr 2010 at 5:14

[GoogleCodeExporter]

I fixed some bugs related to this. It should work now. 

Original comment by brunobg%...@gtempaccount.com on 20 Apr 2010 at 2:25

[GoogleCodeExporter]

Ok, i downloaded r115 and now i get this error:

------------------

OAuth Verification Failed: SQL Error in OAuthStoreMySQL: Array
(
    [0] => 42000
    [1] => 1064
    [2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server 
version for the right syntax to use near 
''01a1054e2d101c3b0250811f49df96c104b62a89d''
                          AND osr_enabled       = 1' at line 5
)

                        SELECT  osr_id, 
                                osr_consumer_key        as consumer_key,
                                osr_consumer_secret     as consumer_secret
                        FROM oauth_server_registry
                        WHERE osr_consumer_key  = '01a1054e2d101c3b0250811f49df96c104b62a89d''
                          AND osr_enabled       = 1

At file /www/.../lib/oauth/store/OAuthStorePDO.php, line 130

Original comment by fiedler....@gmail.com on 22 Apr 2010 at 7:42

[GoogleCodeExporter]

Fixed on r116.

Original comment by brunobg%...@gtempaccount.com on 22 Apr 2010 at 8:08

[GoogleCodeExporter]

Hm, r116 is not working... "osr_consumer_key" seems to be double enquoted, now 
i get:

------------------

... osr_consumer_key    = ''01a1054e2d101c3b0250811f49df96c104b62a89d'' ....

Original comment by fiedler....@gmail.com on 22 Apr 2010 at 8:15

[GoogleCodeExporter]

Damn, I hate when I commit the wrong version. Sorry. See r119.

Original comment by brunobg%...@gtempaccount.com on 22 Apr 2010 at 9:17

[GoogleCodeExporter]

Two more bugs:

Uninitialized string offset: 2

Stack Trace

/www/htdocs/..../lib/oauth/store/OAuthStorePDO.php (line: 223)

BugFix:

            $startcut = 0;
            while (isset($s[$startcut]) && $s[$startcut] == '\'')
                $startcut++;

            $endcut = $len-1;
            while (isset($s[$endcut]) && $s[$endcut] == '\'')
                $endcut--;

------------------------------------

Next Bug:

OAuth Verification Failed: SQL Error in OAuthStoreMySQL: Array ( [0] => 42S22 
[1] => 1054 [2] => Unknown 
column 'ost_callback_url' in 'field list' ) INSERT INTO oauth_server_token SET 
ost_osr_id_ref   = 1, 
ost_usa_id_ref   = 1, ost_token  = 'c2c976bdc6a3a1068bc2b798be51459204be17b15', 
ost_token_secret    
= '52717da609904b3e7e33b3e6fb6f8f2c', ost_token_type     = 'request', 
ost_token_ttl = DATE_ADD(NOW(), 
INTERVAL 3600 SECOND), ost_callback_url = 'oob' 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_timestamp     = NOW() At file 
/www/htdocs/......./lib/oauth/store/OAuthStoreSQL.php, line 1227

To build the database I´ve used the sql file shipped with the r122

Original comment by fiedler....@gmail.com on 5 May 2010 at 2:09

[GoogleCodeExporter]

Fixed the first bug, r123. The second one is odd. The SQL file does have that 
field,
so I can't understand the error. 

Have you dropped the tables before creating them again? Please run a "describe 
table
oauth_server_token" and see if this column 'ost_callback_url' is present.

Original comment by brunobg%...@gtempaccount.com on 6 May 2010 at 2:58

[GoogleCodeExporter]

Ok, seems to work now... but I did have a logical problem. On my OAuth Server 
Authorize Call, i wan´t to 
redirect to the given oauth_callback... but i have to append a "oauth_token". 
Where do get this token?

$server->authorizeFinish(true, 1);

if(isset($_GET['oauth_callback']))
    header('Location: ' . $_GET['oauth_callback'] . '&oauth_token=???????);

The client i´ve put together from this Wiki... it´s the three legged one. And 
now get stuck a Step 4: Exchange 
Request Token For Access Token
Because there´s no "oauth_token" in the callback.

Original comment by fiedler....@gmail.com on 6 May 2010 at 4:39

[GoogleCodeExporter]

Ok, got it... But one more Bug:

Undefined variable: verifier

Stack Trace

/www/htdocs/..../lib/oauth/OAuthServer.php (line: 204)

Original comment by fiedler....@gmail.com on 6 May 2010 at 6:00

[GoogleCodeExporter]

If i fix this bug

[Line 204]   if(isset($verifier) && $verifier) ...

i than get this redirect:

http://oob/?oauth_token=00b557945dcdfb9c6272da23ac75556e04be30445&oauth_verifier
=3f6fcacff3

What means "oob" ? How to fix this?

Original comment by fiedler....@gmail.com on 6 May 2010 at 6:04

[GoogleCodeExporter]

Think this ticket can be closed. Seems to work now.

I´ve a problem with Out-Of-Band. I mentioned this on the wiki page.

thank you very much!

Original comment by fiedler....@gmail.com on 6 May 2010 at 10:22

[GoogleCodeExporter]

One sure way to fix it to set the 'oauth_callback' request query parameter. 

Someone contacted me asking if this is a bug. It may be depending on how you 
setup
the server; if there is a callback set on the database (when you register an
application), it should be used as default. 

Original comment by brunobg%...@gtempaccount.com on 7 May 2010 at 12:20

• Changed state: Fixed