duplicate records are getting inserted in the database

[subhomoy]

hello every body...

two

I'm creating an IPN in paypal for my membership site but the problem I'm facing is that on successfull verification of the purchase, four rows are getting inserted in the database...

 

The code is

<?php   
    require '../db.php';
    $paypalmode     =   '.sandbox';        
    $req = 'cmd=' . urlencode('_notify-validate');
    foreach ($_POST as $key => $value) {
        $value = urlencode(stripslashes($value));
        $req .= "&$key=$value";
    }
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, 'https://www'.$paypalmode.'.paypal.com/cgi-bin/webscr');
    curl_setopt($ch, CURLOPT_HEADER, 0);
    curl_setopt($ch, CURLOPT_POST, 1);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER,1);
    curl_setopt($ch, CURLOPT_POSTFIELDS, $req);
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 1);
    curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 2);
    curl_setopt($ch, CURLOPT_HTTPHEADER, array('Host: www'.$paypalmode.'.paypal.com'));
    $res = curl_exec($ch);
    curl_close($ch);

    if (strcmp ($res, "VERIFIED") == 0)
    {
        $transaction_id = $_POST['txn_id'];
        $payerid = $_POST['payer_id'];
        $firstname = $_POST['first_name'];
        $lastname = $_POST['last_name'];
        $payeremail = $_POST['payer_email'];
        $paymentdate = $_POST['payment_date'];
        $paymentstatus = $_POST['payment_status'];
        $mdate= date('Y-m-d h:i:s',strtotime($paymentdate));
        $otherstuff = json_encode($_POST);
        $date = date("y-m-d");
        
            $q = $pdo->connect()->query("INSERT INTO payment (mid,username,amount,paypal_id,txn_id,received_date)   VALUES('{$_SESSION['user_id']}','{$_SESSION['uname']}','{$_POST['mc_gross']}','{$_POST['payer_email']}','{$_POST['txn_id']}','$date')");
            $q->execute();
            $q1 = $pdo->connect()->query("UPDATE members SET amount_loaded = amount_loaded + {$_SESSION['amount']} WHERE mid = '{$_SESSION['user_id']}'");
            $q1->execute();
            //header("Location: funds.php");
            echo "verified";
        
        
}



?>

two for the payment and in the members table, the amount is getting doubled. (i.e if anybody purchases For $2, it shows $4 in the database....)

 

Any help will be really appreciated...

[fastsol]

Looking at it I don't see any reason for the duplication, but you do have issues with the query vars.  The main issue is that you are trying to use session information that would have been specific to the user and their visit of your site.  That will not work in the ipn script cause it runs behind the scenes and those session vars will not exist when paypal sends the info because the actual user is not actually going to that page, only paypal is.

 

The only info you can use in the ipn script is the $_POST that is sent back from paypal.

[mac_gyver]

your page is being requested twice. you would need to do some debugging to find out way. some causes are url rewriting you are doing for your site or even how your web hosting is passing the request to the server where your web site is hosted.

 

however, you cannot prevent duplicate requests in all cases and you need to enforce uniqueness within your application to filter out duplicates. your payment table should be setup with a unique composite key that prevents duplicate transition id/user id combinations.

 

next, when dealing with real money or any sort of data that you need to have the ability to audit the data, you should not simply add the amount to a sum in a column. you should treat this as a deposit/debit account, where you use a query to sum up the transactions to determine the current amount in the account each time you need the current total.

[fastsol]

Ah yes I didn't think about it maybe being called twice.  You for sure need a check in there to see if the txn_id has already been inserted into the database, that way it can't be inserted again for the same transaction.  They even talk about this somewhere in the paypal example codes, that there are certain basic checks you should do before inserting the info in the db.

 

I have 4 quick checks in my ipn script.  If any return true then I skip to the end and just log the $what for investigation manually.

if($payment_status != 'Completed'){ $bad = TRUE; $what='status';}
if(checkPurchaseTxn($txn_id) === TRUE) { $bad = TRUE; $what='txn';}
if($receiver_email != $settings['paypal_email']) { $bad = TRUE; $what='email';}
if($payment_currency != 'USD') { $bad = TRUE; $what='currency';}

[Jacques1]

No offense, but are you sure you're ready to take actual money from actual people?

 

Appearently you haven't even bothered to read the manual and get a basic understanding of how IPN actually works. Receiving an IPN message does not mean you should randomly add the amount to some account. PayPal will send you all kinds of status updates, and, yes, you may receive duplicate messages.

 

Besides that, the code you've posted is wide open to SQL injections. You don't even seem to be aware that your application must be protected. So that's where you will manage the accounts of your users? And anybody with basic SQL knowledge is free to change any account to any balance?

 

I strongly recommend that you forget about this project for now. People are willing to accept mistakes as long as it's all just for fun. But when their money is involved, that's usually when the fun ends.

[subhomoy]

then what should I do... Plz help.. I've already started the project...

@ mac_gyver u said that I should use unique composite key..

Lets try it out...

 

BTW thanks for da reply....

[Jacques1]

No, you should not have a unique key over the user ID and the transaction ID. RTFM, for heaven's sake. That's where the info is. PayPal will send you various status updates with the same transaction ID (and obviously the same user ID). This is how the system works. If your database rejects the “completed” message because it already has a “pending” message for the same transaction ID, you have a serious problem.

 

I wish you lots of luck. You'll need it.

Edited August 15, 2014 by Jacques1

[mac_gyver]

@subhomoy, the following paypal link describes the process you will need to use for the IPN information - https://www.paypal.com/in/cgi-bin/webscr?cmd=p/acc/ipn-info-outside

 

as the last step, to finally record the payment as being successful/complete for a user, for a specific order he has made, you need to check the payment_status value, which is something that fastsol included in his post. in fact, since you are not using or storing that value, it's possible that your duplicate data in the payment table has two different statuses.

 

you may want to log each set of data your ipn script receives so that you can detect nefarious activity.

 

next, just because the data your ipn script receives is coming from paypal, doesn't mean it is safe. since you appear to be using the PDO database class, you should be using prepared queries.

 

lastly, your code appears to be making a database connection for each query you are running - $pdo->connect()->query(...). if that is the case, you should only make ONE database connection and use that connection throughout your script.

[subhomoy]

Thanks guyss for all the help.....

 

I've done upto this... Can any1 tell me whether i'm going in a correct way or not...

<?php    
    require '../db.php';
    $req = 'cmd=' . urlencode('_notify-validate');
    foreach ($_POST as $key => $value) {
        $value = urlencode(stripslashes($value));
        $req .= "&$key=$value";
    }
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, 'https://www.sandbox.paypal.com/cgi-bin/webscr');
    curl_setopt($ch, CURLOPT_HEADER, 0);
    curl_setopt($ch, CURLOPT_POST, 1);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER,1);
    curl_setopt($ch, CURLOPT_POSTFIELDS, $req);
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 1);
    curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 2);
    curl_setopt($ch, CURLOPT_HTTPHEADER, array('Host: www.sandbox.paypal.com'));
    $res = curl_exec($ch);
    curl_close($ch);

    if (strcmp ($res, "VERIFIED") == 0)
    {
        $transaction_id = $_POST['txn_id'];
        $payerid = $_POST['payer_id'];
        $firstname = $_POST['first_name'];
        $lastname = $_POST['last_name'];
        $payeremail = $_POST['payer_email'];
        $paymentdate = $_POST['payment_date'];
        $paymentstatus = $_POST['payment_status'];
        $mdate= date('Y-m-d h:i:s',strtotime($paymentdate));
        $otherstuff = json_encode($_POST);
        
        if($paymentstatus === "Completed")
        {
            if(check_txn_id($transaction_id))
             {
                 $to = "superioradsmedia@gmail.com";
                 $subject = "Verified";
                 $message = "$otherstuff";
                 $from = "From: Superior Ads Media";
                 mail($to,$subject,$message,$from);
                 //  database insertion
             }
             else
             {
                 $to = "superioradsmedia@gmail.com";
                 $subject = "Duplicate";
                 $message = "$otherstuff";
                 $from = "From: Superior Ads Media";
                 mail($to,$subject,$message,$from);
             }
        }
}
else
{
    "<h1 style='font-family:verdana,san-seriff,seriff;color:red'>You are not allowed to view this page.</h1>";
}

function check_txn_id($txn_id)
{
    $q = $this->connect()->prepare("SELECT count(*) FROM payment WHERE txn_id = ?");
    $q->bindParam(1, $transaction_id, PDO::PARAM_STR);
     $q->execute();
     if($q->fetchColumn()>0)
     {
         return FALSE;
     }
     else
     {
         return TRUE;
     }
}

?>

Then i've used the return url while creating the buynow button... Where the user willl be redirected. There i'll check any new entry is done from the user id.. If yes then I will upgrade...

[fastsol]

Yes that is much better, but still has some issues with the check_txn_id().  You're trying to bindParam on $transaction_id but you defined $txn_id.  Also you're trying to call $this which doesn't exist.  You need to pass the db object to the function for it to work.  Lastly, your check_txn_id function itself should be towards the top of the page, php reads top-to-bottom, so you'll try to call on a function that hasn't been defined until later, so that won't work.

[subhomoy]

Thanks for the info...

 

BTW the bindParam one I know.. It was done by mistake...

 

Actually i found dis on net..

 

WHen ever i'm testing using IPN SIMULATOR, it always gives me invalid... ANY IDEAS???

<?php
 
// STEP 1: read POST data
 
// Reading POSTed data directly from $_POST causes serialization issues with array data in the POST.
// Instead, read raw POST data from the input stream.
$raw_post_data = file_get_contents('php://input');
$raw_post_array = explode('&', $raw_post_data);
$myPost = array();
foreach ($raw_post_array as $keyval) {
$keyval = explode ('=', $keyval);
if (count($keyval) == 2)
$myPost[$keyval[0]] = urldecode($keyval[1]);
}
// read the IPN message sent from PayPal and prepend 'cmd=_notify-validate'
$req = 'cmd=_notify-validate';
if(function_exists('get_magic_quotes_gpc')) {
$get_magic_quotes_exists = true;
}
foreach ($myPost as $key => $value) {
if($get_magic_quotes_exists == true && get_magic_quotes_gpc() == 1) {
$value = urlencode(stripslashes($value));
} else {
$value = urlencode($value);
}
$req .= "&$key=$value";
}
 
// STEP 2: POST IPN data back to PayPal to validate
  $msg = json_encode($_POST);
$ch = curl_init('https://www.sandbox.paypal.com/cgi-bin/webscr');
curl_setopt($ch, CURLOPT_HTTP_VERSION, CURL_HTTP_VERSION_1_1);
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_RETURNTRANSFER,1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $req);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 1);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 2);
curl_setopt($ch, CURLOPT_FORBID_REUSE, 1);
curl_setopt($ch, CURLOPT_HTTPHEADER, array('Connection: Close'));
 
// In wamp-like environments that do not come bundled with root authority certificates,
// please download 'cacert.pem' from "http://curl.haxx.se/docs/caextract.html" and set
// the directory path of the certificate as shown below:
// curl_setopt($ch, CURLOPT_CAINFO, dirname(__FILE__) . '/cacert.pem');
if( !($res = curl_exec($ch)) ) {
// error_log("Got " . curl_error($ch) . " when processing IPN data");
curl_close($ch);
exit;
}
curl_close($ch);
 
// STEP 3: Inspect IPN validation result and act accordingly

if (strcmp ($res, "VERIFIED") == 0) {
// The IPN is verified, process it:
// check whether 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 the notification
 
// 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'];
$to = "superioradsmedia@gmail.com";
        $subject = "Verified";
        $message = "$otherstuff";
        $from = "From: superior";
        mail($to,$subject,$message,$from);
 
// IPN message values depend upon the type of notification sent.
// To loop through the &_POST array and print the NV pairs to the screen:
foreach($_POST as $key => $value) {
echo $key." = ". $value."<br>";
}
} else if (strcmp ($res, "INVALID") == 0) {
// IPN invalid, log for manual investigation
echo "The response from IPN was: <b>" .$res ."</b>";
   $to = "superioradsmedia@gmail.com";
        $subject = "INVALID";
        $message = "$msg";
        $from = "From: superior";
        mail($to,$subject,$message,$from);
}
?>

Edited August 18, 2014 by subhomoy

[fastsol]

You're saying the latest script you posted gives you INVALID or the script you've been working on gives you INVALID?  I don't really see any issues with the recent script, but I know that there are a couple versions of the IPN scripts floating around the net that are old and won't work, so maybe this is one of them.  I only use the examples that PayPal provides since those will be the most up to date versions of what they require or reccommend.

[subhomoy]

No i'm using the script from paypal only....

 

Always it showing INVALID... ANY HELP....

[fastsol]

Are you sure you're setting the IPN output to verified in the test console?  Or are you running an actual test transaction like a customer would if it was live?

[subhomoy]

I'm runniig it from the IPN simulator using a sandbox account....