Solved

PayPal Integration, IPN & PDT, txn_id general query

Posted on 2004-08-06
15
957 Views
Last Modified: 2008-02-01
Hi,

I have set up a Buy now button for payment of goods and general custom invoiced items. It returns the user back to a completed page if the transaction is succesful. There is no processing on the completed page. Paypal also uses IPN (instant payment notification) posting results to my processing page.

The query I have is:
Is the Transaction ID (txn_id) that PayPal creates always unique? I Know the docs state this but it appears there are times it is not while I am testing, has me scratching my head. Maybe this can occur from a PayPal error? I know they have been up and down lately...

The simple code I am using is below. There are no database calls before this on the page yet somehow for the last 24 hrs everytime I test it, it trips that it is a duplicate.... Does anyone know of any gotchas here?



            ' check that TransactionID has not been previously processed

            strSQL = "Select * FROM tb_Payments WHERE TransactionID='" & TransactionID & "'"
            rs.Open strSQL, Conn, adOpenStatic, adLockReadOnly
                  if rs.eof Then
                        validID = True
                  else
                        error_str = error_str & "<br>error:transactionID(TransactionID)=Duplicate"
                        validID = False
                  end if
            rs.Close
            set rs = nothing


Cheers
0
Comment
Question by:Chris_Granger
  • 7
  • 7
15 Comments
 
LVL 11

Expert Comment

by:Slimshaneey
ID: 11734040
You sometimes need to watch out when testing online payments like this that you are re-completeing the entire process and not just one step, and also that all variables are cleared at the beginning , at the end and in the case of an error. A prob I always come up with is, when starting a trans, you get assigned an ID and move to page 1, fill out details, click submit, page does some work and logs the trans, and moves you on to page 2, if at any point you are trying to reload these pages, you may already have these variables set up in the DB, hence the dupes error. This is by no means a solution, just something I have had probs with in the past.
0
 

Author Comment

by:Chris_Granger
ID: 11734110
The "buying" pages are not the pages creating the txn_id - this should be paypal, it shouldn't matter what variable I send to PayPal, if the buy goes through their pages I would have thought the txn_id would always be unique. The page detecting the duplication "ipn.asp " is a server page and is only ever called directly from paypal after the buyer has completed their the transaction within the PayPal buying pages. All pages are under SSL and have the following at the top:

      Response.CacheControl = "no-cache"
      Response.ExpiresAbsolute=Now()-1
      Response.AddHeader "pragma", "no-cache"
      Response.AddHeader "cache-control", "private"
      Response.Expires = -1

I don't think the paypal processing pages or the Msxml2.ServerXMLHTTP method they use to post to mine after completion can ever be using stale data. Is a mystery and making me age before my time...  :-) Along with the PayPal support system of course. In fact maybe my question should be, where is the PayPal support?  :-)  Goes to show the harm a monopoly creates.  I do like the power their system has though, must be the best on the market for its functionality...  Anyway..
0
 
LVL 10

Expert Comment

by:thefritterfatboy
ID: 11734414
txn_id is ALWAYS unique - however when you issue a refund - the IPN will notify your processing page again.

Another problem is if your script does not complete. Your script may connect to your database, write your values and then fall over a bit further on. If Paypal do not get a HTTP 200 status from your processing page they will keep trying. For buy now processing - the first thing you should do is write a text file and append the request.form value in there with a time. this will allow you to open the text file and view EVERYTHING paypal sent to your server.
0
ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

 
LVL 10

Expert Comment

by:thefritterfatboy
ID: 11734419
Another good debug trick is to look at your site's log files. If your site's files log the data that was posted to your site then you can find out what is being sent that way.
0
 

Author Comment

by:Chris_Granger
ID: 11735746
Ok, I've written my own log and been monitoring the server logs:

I can see 2 things happening.

1. there is an error ocurring on my asp page but not sure what the log record means and what line it is actually referring to, could someone explain the results to me?

2. The page is getting called twice..   eeekk..  maybe because of the error i hope...

heres the server log:

2004-08-06 10:10:49 myip - W3SVC942 NT2 my.ip 443 POST /accounts/myipn.asp |243|80040e14|Line_1:_Incorrect_syntax_near_'_'. 500 0 674 742 750 HTTP/1.0 - - -

and then 20 seconds later:

2004-08-06 10:11:08 myip- W3SVC942 NT2 myip 443 POST /accounts/myipn.asp - 200 0 348 742 609 HTTP/1.0 - - -


cheers!
0
 
LVL 10

Expert Comment

by:thefritterfatboy
ID: 11735864
Your error seems to be in the SQL.

Can you show me how you get the "TransactionID"?

As for the 20 seconds gap thing... this is bizarre. Your server throws an error on the first attempt - but accepts the second attempt and returns a HTTP 200 status code.

When posting items from your log - can you also copy and paste the headers at the top of the file. Not all servers put these in the same order or include the same data.
0
 

Author Comment

by:Chris_Granger
ID: 11736226
This is the recieving code:

      ' read post from PayPal system and add 'cmd'
      str = Request.Form & "&cmd=_notify-validate"
      
      ' post back to PayPal system to validate
      set objHttp = Server.CreateObject("Msxml2.ServerXMLHTTP")
      objHttp.open "POST", "https://www.paypal.com/cgi-bin/webscr", false
      objHttp.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
      objHttp.Send str


      TransactionID = URLDecode(Request.Form("txn_id"))

Both times the page loads it gets the same information:
and both get the following for payment status: payment_status=Completed, which means there is nothing pending...
they also both get the correct xml response text:  objHttp.responseText: VERIFIED

Likewise all my request form variables are working also
0
 
LVL 10

Expert Comment

by:thefritterfatboy
ID: 11736306
Any chance of getting some more code?

According to your logs - the error is on line 1. This sounds crazy to me. If you don't want to provide all the code - could you provide line 1 and your urlDecode function.
Can you also tell me what Data type your transactionID is stored as in the database. This error may be due to your SQL looking for a string in a numeric field.
0
 

Author Comment

by:Chris_Granger
ID: 11736521
I finally found the error, was quite hard to debug but pretty obvious error!  All is working now, still baffles me how it was getting called twice by an asp error.

the dumbo error was: overageid_arr = Split(CartItems)  instead of  overageid_arr = Split(CartItems, ",")

So, even in the case of a "Pending" from PayPal, will the transaction sent through to state it is completed after this be unique?
0
 
LVL 10

Expert Comment

by:thefritterfatboy
ID: 11737792
>>So, even in the case of a "Pending" from PayPal, will the transaction sent through to state it is completed after this be unique?

The TXN is transaction unique. If the status is pending then when the complete notification comes through it will post the same TXN.
0
 

Author Comment

by:Chris_Granger
ID: 11738813
So in effect, PayPal's documents, stating to check if the transaction ID is unique as a security measure is not so good because then you would block out a pending transaction...

I guess this would also require a record check to see if this transaction was pending and then let it through...
0
 
LVL 10

Accepted Solution

by:
thefritterfatboy earned 500 total points
ID: 11742120
>> So in effect, PayPal's documents, stating to check if the transaction ID is unique as a security measure is not so good because then you would block out a pending transaction...


No, because you need to also check if the status is "COMPLETED" too. If the txn_id matches another COMPLETED transaction then you know something is wrong. I think PayPal's documentation tells you to check for the "COMPLETED" flag.
0
 

Author Comment

by:Chris_Granger
ID: 11746193
...aha, so it's okay for it to be a duplicate transaction as long as it's not a duplicate completed transaction...

Thanks!
0
 

Author Comment

by:Chris_Granger
ID: 11770156
One last question regarding payments of a non-completed type:  I assume these don't have any transaction number?  I just did a test with the pending and it gave me a 0 for the transaction number - damn!  Be hard to automate that task without first creating a payment record and passing its id through before the item is pruchased... I'm using the transaction number as a reference to db records... Will have to create a temp, table for pending and other non-completed payments and manually mark their status to completed with the transaction number that comes through when it's completed...
0
 
LVL 10

Expert Comment

by:thefritterfatboy
ID: 11770942
The question you have to ask is how important is it to log pending sales? I'm not entirely sure what causes pending sales types but your customers will know when Paypal is pending so just tell them that until you get a "payment complete" notification, your server will not acknowledge payment.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question