Capturing PayPal subscription cancellation

Question

I am having a lot of problems trying to capture the subscription cancellation. This is the IPN Im using which I got from Paypal's code sample page. I added the mySQL script to subtract 4000 credits when the user ends subscription. When I test the code with another paypal account, the 4000 credits are not subtracted once i cancel the subscription.
Is the paypal IPN wrong or is the button wrong or WHAT!?

<?php
  session_start();
  // 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);

// assign posted variables to local variables
$item_name = $_POST['item_name'];
$item_number = $_POST['item_number'];
$payment_status = $_POST['payment_status'];
$payment_amount = $_POST['mc_gross'];
$payment_currency = $_POST['mc_currency'];
$txn_id = $_POST['txn_id'];
$receiver_email = $_POST['receiver_email'];
$payer_email = $_POST['payer_email'];
//////////////////////////////////////////////I added this here also. Check if its canceled
  if("subscr_cancel" = $_POST['txn_type']){

if (!$fp) {
// HTTP ERROR
} else {
fputs ($fp, $header . $req);
while (!feof($fp)) {
$res = fgets ($fp, 1024);
if (strcmp ($res, "VERIFIED") == 0) {
// check the payment_status is Completed
// check that txn_id has not been previously processed
// check that receiver_email is your Primary PayPal email
// check that payment_amount/payment_currency are correct
// process payment
  /////I added this mysql
       $password = secret  
    $username = secret
    $websites= "websites";
    $database = "k29803_1";
   mysql_connect("localhost", $username, $password) or die(mysql_error());
   mysql_select_db($database);

  $query = "SELECT * FROM users WHERE username= '$_SESSION[username]' AND password = '$_SESSION[password]'";
  $results = mysql_query($query)or die(mysql_error());
    if(mysql_num_rows($results)==1){
        $add_credits = "UPDATE users SET credits = credits +1000
        WHERE username= '$_SESSION[username]' AND password = '$_SESSION[password]'";
        mysql_query($add_credits) or die(mysql_error());


      }

}
else if (strcmp ($res, "INVALID") == 0) {
// log for manual investigation
}
}
fclose ($fp);
}
  }
  ?>

The button looks like this

 <form action="https://www.paypal.com/cgi-bin/webscr" method="post">
<input type="hidden" name="cmd" value="_s-xclick">
<input type="hidden" name="hosted_button_id" value="NQMGM2LCZUQRE">

  <input type="hidden" name="item_name" value="1" />
    <input type="hidden" name="item_number" value="01" />
    <input type="hidden" name="amount" value="1.99" />
    <input type="hidden" name="currency_code" value="USD" />
<input type="image" src="https://www.paypalobjects.com/en_US/i/btn/btn_subscribeCC_LG.gif" border="0" name="submit" alt="PayPal - The safer, easier way to pay online!">
<img alt="" border="0" src="https://www.paypalobjects.com/en_US/i/scr/pixel.gif" width="1" height="1">
</form>

I get redirected to this page, but I just dont get the MYSQL values subtracted

Chris Thompson Chris Thompson · Accepted Answer · 2011-10-25T04:32:03

The main problem I see is that you are using session variables. Please note that the request that hits your PHP code will be coming directly from the PayPal server and has nothing to do with the currently logged in user.

You need to pass the user information to PayPal so that PayPal can pass it back to your server as part of the IPN request. The other thing you can do is check some of the values coming in the IPN, for example, the payer's email address (payer_email) or the parent_txn_id, or payer_id.

I'm not sure about subscriptions, but refund and cancellation IPNs will include a parent_txn_id that provides the txn_id of the original purchase, so you can use that to locate the user's account.

What you need to do is make sure that when a purchase is made that you're saving all the information from the IPN and tying that by the txn_id or similar to the user's account. When the cancelation comes in, use the parent_txn_id or whatever value you use to locate the account in the database and then apply the change to the user's account.

I see a number of other issues with the code above.

$query = "SELECT * FROM users WHERE username= '$_SESSION[username]' AND password = '$_SESSION[password]'";

You need to use mysql_real_escape_string on all variables that you add into a query. You should do this:

$query = "SELECT * FROM users WHERE username='".mysql_real_escape_string($username)."' AND password = '.mysql_real_escape_string($password).'";

Second, array references should use quotes around the key name:

$_SESSION[password] => $_SESSION['password']

Third, you should probably be locating the user in the database by their row ID in the users table (you do have a primary key of "id" set as auto-increment, right?) instead of their username and password. After you authenticate a user, you really shouldn't be keeping their password in a session variable.

Capturing PayPal subscription cancellation

2 Answers