Solved

PayPal Integration, IPN & PDT, txn_id general query

Posted on 2004-08-06
15
954 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 10

Expert Comment

by:thefritterfatboy
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 10

Expert Comment

by:thefritterfatboy
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
>> 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
Comment Utility
...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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now