Paypal沙盒IPN和mysql

Question

我正在使用 Paypal Sandbox 来测试 IPN，它是成功的，但它没有更新我的 MYSQL 数据库。我如何更改下面的代码，以便当 Paypal 将 IPN 发送到我的网站时它会更新 mysql 数据库？下面的代码是paypalipn.php

 // read the post from PayPal system and add 'cmd'
$req = 'cmd=_notify-validate';
foreach ($_POST as $key => $value) {
$value = urlencode(stripslashes($value));
$req .= "&$key=$value";
}
// post back to PayPal system to validate
$header = "POST /cgi-bin/webscr HTTP/1.0\r\n";
$header .= "Content-Type: application/x-www-form-urlencoded\r\n";
$header .= "Content-Length: " . strlen($req) . "\r\n\r\n";

$fp = fsockopen ('ssl://www.paypal.com', 443, $errno, $errstr, 30);

if (!$fp) {
// HTTP ERROR
} else {
fputs ($fp, $header . $req);
while (!feof($fp)) {
$res = fgets ($fp, 1024);
if (strcmp ($res, "VERIFIED") == 0) {

// PAYMENT VALIDATED & VERIFIED!
$email = $_POST['payer_email'];  
$email = mysql_escape_string($email);
$voted = mysql_query("INSERT INTO user VALUES ('','','','','','','','','','','','','','',''")or die(mysql_error());
mysql_query("UPDATE users SET `suscribed`=1 WHERE `email`='$email'")or die(mysql_error());  

}

else if (strcmp ($res, "INVALID") == 0) {

// PAYMENT INVALID & INVESTIGATE MANUALY!


}
}
fclose ($fp);
}

Answer 1

首先，在开发时始终使用 error_reporting(E_ALL) 启用错误报告，并将 IPN 记录到文本文件（显然在安全的地方）以供参考并查看是否正在接收和接收实际的 IPN。通过你的路由器

等 乍一看，我发现你试图在 user 表中插入一条空白记录，也没有为该语句添加右括号 ) 。

然后您更新另一个表users，可能有一个拼写错误：subscribed，不要使用已弃用的mysql_escape_string函数...mysql_real_escape_string应该使用 code> 来代替，或者最好使用准备好的语句。

编辑：
您可以使用一个简单的示例，其中包括 PDO 和 IPN 日志记录。希望有帮助。

<?php 
/**Simple Paypal validation class**/
class paypal_class {

    var $last_error;
    var $ipn_log;
    var $ipn_log_file;
    var $ipn_response;
    var $ipn_data = array();

    function paypal_class() {
        $this->paypal_url = 'https://www.paypal.com/cgi-bin/webscr';
        $this->last_error = '';
        $this->ipn_response = '';
        $this->ipn_log_file = 'ipn_results.log';
        $this->ipn_log = true;
    }

    function validate_ipn(){
        $url_parsed=parse_url($this->paypal_url);
        $post_string = '';
        foreach($_POST as $field=>$value){
            $this->ipn_data["$field"] = $value;
            $post_string .= $field.'='.urlencode(stripslashes($value)).'&';
        }
        $post_string.="cmd=_notify-validate";

        $fp = fsockopen($url_parsed[host],"80",$err_num,$err_str,30);
        if(!$fp){
            $this->last_error = "fsockopen error no. $errnum: $errstr";
            $this->log_ipn_results(false);
            return false;
        }else{
            // Post the data back to paypal
            fputs($fp, "POST $url_parsed[path] HTTP/1.1\r\n");
            fputs($fp, "Host: $url_parsed[host]\r\n");
            fputs($fp, "Content-type: application/x-www-form-urlencoded\r\n");
            fputs($fp, "Content-length: ".strlen($post_string)."\r\n");
            fputs($fp, "Connection: close\r\n\r\n");
            fputs($fp, $post_string . "\r\n\r\n");

            while(!feof($fp)){
                $this->ipn_response .= fgets($fp, 1024);
            }
            fclose($fp);
        }
        if(eregi("VERIFIED",$this->ipn_response)){
            $this->ipn_log(true);
            return true;
        }else{
            $this->last_error = 'IPN Validation Failed.';
            $this->ipn_log(false);
            return false;
        }
    }

    function ipn_log($success){
        if (!$this->ipn_log) return;
        $text = '['.date('m/d/Y g:i A').'] - ';
        if ($success) $text .= "SUCCESS!\n";
        else $text .= 'FAIL: '.$this->last_error."\n";
        $text .= "IPN POST Vars from Paypal:\n";
        foreach ($this->ipn_data as $key=>$value) {
            $text .= "$key=$value, ";
        }
        $text .= "\nIPN Response from Paypal Server:\n ".$this->ipn_response;
        $fp=fopen($this->ipn_log_file,'a');
        fwrite($fp, $text . "\n\n");
        fclose($fp);
    }
}



class database{
    /**PDO Connect**/
    public function connect($host,$db,$user,$pass){
        $this->dbh = new PDO('mysql:host='.$host.';dbname='.$db, $user, $pass);
    }
    /**Pre Query for prepared statement**/
    public function update_valid($email){
        $this->value = $email;
        $this->prepare();
    }
    /**Delete pending user, when user clicks cancel @ paypal**/
    public function delete_pending($email){
        $this->result = $this->dbh->prepare('DELETE FROM users where email=":value" and subscribed=0');
        $this->result->bindParam(':value', $email);
        $this->execute();
    }

    /**Prepare query for insert**/
    private function prepare(){
        /* Execute a prepared statement by binding PHP variables */
        $this->result = $this->dbh->prepare('UPDATE users SET subscribed=1 WHERE email=":value"');
        $this->result->bindParam(':value', $this->value);
        $this->execute();
    }

    /**Execute prepared statement**/
    private function execute(){
        $this->result->execute();
    }
    /**Close db**/
    public function close(){
        $this->result = null;
    }
}


?>


<?php
//Handle payment (Set You IPN url too http://yoursite.com?payment=ipn & Cancel url to http://yoursite.com?payment=cancel)
if(isset($_GET['payment'])){

    switch ($_GET['payment']) {
        case 'cancel':
            //Order Cancelled
            $db=new database();
            $db->connect('localhost','table','root','password');
            $db->delete_pending($_SESSION['email']); //hold email in session after submitting form
            $db->close();
            header('Location: index.php');
            die();
            break;

        case 'ipn':
            $pp = new paypal_class;

            if ($pp->validate_ipn()){
                //Success
                $db=new database();
                $db->connect('localhost','table','root','password');
                $db->update_valid($ipn['payer_email']);
                $db->close();
            }
            die();
            break;
    }
}
?>