https://developer.paypal.com/webapps/developer/docs/classic/ipn/integration-guide/IPNIntro/
For this implementation I'm using a MVC Web API Controller (VB.Net) for the listener, along with some helper classes/extensions/methods. I'm also using a SQL Server 2012 table and stored procedure to create a notifications log, which allows you to:
1) Listen
2) Log
3) React
This is important because it allows the listener to do it's job quickly, and lets you focus on the processing later.
Let's start with our SQL Server database Notifications table.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Notifications]( [NotificationID] [INT] IDENTITY(1,1) NOT NULL, [Timestamp] [DATETIME] NOT NULL CONSTRAINT [DF_Notifications_Timestamp] DEFAULT (GETDATE()), [Type] [VARCHAR](50) NOT NULL CONSTRAINT [DF_Notifications_Type] DEFAULT (''), [IPAddress] [VARCHAR](25) NOT NULL CONSTRAINT [DF_Notifications_IPAddress] DEFAULT (''), [UrlRequest] [VARCHAR](MAX) NOT NULL CONSTRAINT [DF_Notifications_UrlRequest] DEFAULT (''), [UserAgent] [VARCHAR](MAX) NOT NULL CONSTRAINT [DF_Notifications_UserAgent] DEFAULT (''), [Data] [VARCHAR](MAX) NOT NULL CONSTRAINT [DF_Notifications_Message] DEFAULT (''), [Status] [VARCHAR](MAX) NOT NULL CONSTRAINT [DF_Notifications_Status] DEFAULT (''), [Method] [VARCHAR](10) NOT NULL CONSTRAINT [DF_Notifications_Method] DEFAULT (''), [Processed] [BIT] NOT NULL CONSTRAINT [DF_Notifications_Processed] DEFAULT ((0)), [ProcessedDate] [DATETIME] NULL, [Notes] [VARCHAR](MAX) NOT NULL DEFAULT (''), [TransactionID] [VARCHAR](50) NOT NULL DEFAULT (''), [TransactionType] [VARCHAR](50) NOT NULL DEFAULT (''), [ItemName] [VARCHAR](128) NOT NULL DEFAULT (''), [ItemNumber] [VARCHAR](128) NOT NULL DEFAULT (''), [Option] [VARCHAR](200) NOT NULL DEFAULT (''), [Email] [VARCHAR](100) NOT NULL DEFAULT (''), [PaymentStatus] [VARCHAR](25) NOT NULL DEFAULT (''), CONSTRAINT [PK_Notifications] PRIMARY KEY CLUSTERED ( [NotificationID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ) GO SET ANSI_PADDING OFF GO
Next is the Notifications_Save stored procedure to save data in the Notifications table.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[Notifications_Save] ( @NotificationID INT, @Type VARCHAR(50), @IPAddress VARCHAR(25), @UrlRequest VARCHAR(MAX), @UserAgent VARCHAR(MAX), @Data VARCHAR(MAX), @Status VARCHAR(MAX), @Method VARCHAR(10), @Processed BIT, @ProcessedDate DATETIME ) AS BEGIN -- check to see if record exists IF EXISTS (SELECT NotificationID FROM dbo.Notifications WHERE NotificationID = @NotificationID) BEGIN -- update UPDATE dbo.Notifications SET Type = @Type, IPAddress = @IPAddress, UrlRequest = @UrlRequest, UserAgent = @UserAgent, Data = @Data, Status = @Status, Method = @Method, Processed = @Processed, ProcessedDate = @ProcessedDate WHERE NotificationID = @NotificationID END ELSE BEGIN -- insert INSERT INTO dbo.Notifications (Type, IPAddress, UrlRequest, UserAgent, Data, Status, Method, Processed, ProcessedDate) VALUES (@Type, @IPAddress, @UrlRequest, @UserAgent, @Data, @Status, @Method, @Processed, @ProcessedDate) -- get identity value SET @NotificationID = SCOPE_IDENTITY() END -- return value RETURN @NotificationID END
Next we'll create the Notification model object in our MVC project.
Public Class Notification Public Property NotificationID As Integer = 0 Public Property Timestamp As DateTime = DateTime.MinValue Public Property Type As String = String.Empty Public Property IPAddress As String = String.Empty Public Property UrlRequest As String = String.Empty Public Property UserAgent As String = String.Empty Public Property Data As String = String.Empty Public Property Status As String = String.Empty Public Property Method As String = String.Empty Public Property Processed As Boolean = False Public Property ProcessedDate As DateTime? End Class
Then we'll create the Notifications data layer class for calling the stored procedure.
Imports Microsoft.Practices.EnterpriseLibrary.Data Imports System.Data.Common Public Class Notifications Public Function Save(ByVal Notification As Model.Notification) As Integer 'variables Dim DB As Database = New DatabaseProviderFactory().CreateDefault() Dim ReturnValue As Integer = -1 'command Using cmd As DbCommand = DB.GetStoredProcCommand("dbo.Notifications_Save") 'parameters With Notification DB.AddInParameter(cmd, "@NotificationID", DbType.Int32, .NotificationID) DB.AddInParameter(cmd, "@Type", DbType.String, .Type) DB.AddInParameter(cmd, "@IPAddress", DbType.String, .IPAddress) DB.AddInParameter(cmd, "@UrlRequest", DbType.String, .UrlRequest) DB.AddInParameter(cmd, "@UserAgent", DbType.String, .UserAgent) DB.AddInParameter(cmd, "@Data", DbType.String, .Data) DB.AddInParameter(cmd, "@Status", DbType.String, .Status) DB.AddInParameter(cmd, "@Method", DbType.String, .Method) DB.AddInParameter(cmd, "@Processed", DbType.Boolean, .Processed) DB.AddInParameter(cmd, "@ProcessedDate", DbType.DateTime, IIf(.ProcessedDate.HasValue AndAlso Not .ProcessedDate.Equals(DateTime.MinValue), .ProcessedDate, DBNull.Value)) End With 'return value parameter DB.AddParameter(cmd, "@ReturnValue", DbType.Int32, 4, ParameterDirection.ReturnValue, False, 0, 0, "@ReturnValue", DataRowVersion.Default, Nothing) 'execute query DB.ExecuteNonQuery(cmd) 'get return value ReturnValue = DB.GetParameterValue(cmd, "@ReturnValue") End Using 'return value Return ReturnValue End Function End Class
Next we have a few helper classes/extensions/methods that we are going to be calling from the listener controller.
1) IsBlank (extension method)
2) ContainsValue (extension method)
3) Result (model object)
4) PostContentToUrl (shared method)
The IsBlank extension method seems trivial, especially for such a small example, but this is just one of the many extension methods that I keep in my utilities library to keep my code, clean, simple, and consistent.
<Extension()> _ Public Function IsBlank(Value As String) As Boolean Dim ReturnValue As Boolean = True If Value IsNot Nothing Then ReturnValue = Value.Trim().Length = 0 End If Return ReturnValue End Function
The ContainsValue extension method is another one I keep in my library to to simplify the code so you don't have to deal with case sensitivity all over the place.
<Extension()> _ Public Function ContainsValue(Value As String, CompareValue As String) As Boolean Dim ReturnValue As Boolean = False If Value IsNot Nothing AndAlso CompareValue IsNot Nothing Then ReturnValue = Value.Trim().IndexOf(CompareValue.Trim(), StringComparison.OrdinalIgnoreCase) >= 0 End If Return ReturnValue End Function
The Result model object is a simple helper class we're going to use when we post our data to the PayPal verification URL, which will allow us to return back a property indicating success or failure, as well as the response body.
Public Class Result Public Property Success As Boolean = False Public Property Message As String = String.Empty End Class
Then we have our PostContentToUrl method, which was written with re-usability in mind. It accepts a URL and a request body. This allows you to use it for POST'ing data generically to any service.
Public Shared Function PostContentToUrl(ByVal Url As String, ByVal Data As String) As Model.Result 'variables Dim Result As New Model.Result Try 'create web request Dim MyRequest As WebRequest = WebRequest.Create(Url) Dim ByteArray As Byte() = Encoding.UTF8.GetBytes(Data) 'properties MyRequest.Method = "POST" MyRequest.ContentType = "application/x-www-form-urlencoded" MyRequest.ContentLength = ByteArray.Length 'get the request stream Using DataStream As Stream = MyRequest.GetRequestStream() 'write the data to the request stream DataStream.Write(ByteArray, 0, ByteArray.Length) 'close the stream object DataStream.Close() End Using 'response Using MyResponse As HttpWebResponse = DirectCast(MyRequest.GetResponse(), HttpWebResponse) Using MyReader As New StreamReader(MyResponse.GetResponseStream()) Result.Message = MyReader.ReadToEnd() Result.Success = True End Using End Using Catch ex As Exception Result.Message = ex.Message End Try 'return Return Result End Function
Next we'll setup a BaseController class to expose several properties for the current HTTP request.
Public Class BaseController Inherits System.Web.Http.ApiController Public ReadOnly Property QueryString() As String Get Try Return DirectCast(Request.Properties("MS_HttpContext"), HttpContextBase).Request.QueryString.ToString Catch ex As Exception Return String.Empty End Try End Get End Property Public ReadOnly Property IPAddress() As String Get Try Return DirectCast(Request.Properties("MS_HttpContext"), HttpContextBase).Request.UserHostAddress Catch ex As Exception Return String.Empty End Try End Get End Property Public ReadOnly Property UrlRequest() As String Get Try Return DirectCast(Request.Properties("MS_HttpContext"), HttpContextBase).Request.Url.AbsoluteUri Catch ex As Exception Return String.Empty End Try End Get End Property Public ReadOnly Property RawUrl() As String Get Try Return DirectCast(Request.Properties("MS_HttpContext"), HttpContextBase).Request.RawUrl Catch ex As Exception Return String.Empty End Try End Get End Property Public ReadOnly Property UrlReferrer() As String Get Try Return DirectCast(Request.Properties("MS_HttpContext"), HttpContextBase).Request.UrlReferrer.AbsoluteUri Catch ex As Exception Return String.Empty End Try End Get End Property Public ReadOnly Property UserAgent() As String Get Try Return DirectCast(Request.Properties("MS_HttpContext"), HttpContextBase).Request.UserAgent Catch ex As Exception Return String.Empty End Try End Get End Property Public ReadOnly Property Crawler() As Boolean Get Try Return DirectCast(Request.Properties("MS_HttpContext"), HttpContextBase).Request.Browser.Crawler Catch ex As Exception Return False End Try End Get End Property Public ReadOnly Property Method() As String Get Try Return DirectCast(Request.Properties("MS_HttpContext"), HttpContextBase).Request.HttpMethod Catch ex As Exception Return False End Try End Get End Property End Class
Now that we've got all the core pieces in place let's take a look at the ListenerController, which is the simplest part of the entire process.
Imports System.Net Imports System.Web.Http Imports System.IO Namespace Controllers <RoutePrefix("listener")> Public Class ListenerController Inherits BaseController <HttpPost> <Route("ipn")> Public Function InstantPaymentNotification() As String 'variables Dim Data As String = String.Empty Dim Notification As New Model.Notification 'get data Try Data = New StreamReader(HttpContext.Current.Request.InputStream).ReadToEnd() Catch ex As Exception Data = String.Empty End Try 'check for valid data If Data.IsBlank Then Return String.Empty End If 'PayPal HTTP POSTs an IPN message to your listener that notifies it of an event. 'Your listener returns an empty HTTP 200 response to PayPal. 'Your listener HTTP POSTs the complete, unaltered message back to PayPal; the message must contain the same fields (in the same order) as the original message and be encoded in the same way as the original message. 'PayPal sends a single word back - either VERIFIED (if the message matches the original) or INVALID (if the message does not match the original). 'Every IPN message you receive from PayPal includes a User-Agent HTTP request header whose value is PayPal IPN ( https://www.paypal.com/ipn ). 'Do not use this header to verify that an IPN really came from PayPal and has not been tampered with. 'Rather, to verify these things, you must use the IPN authentication protocol outlined above. If Not MyBase.UserAgent.ContainsValue("PayPal") Then Return String.Empty End If 'Before you can trust the contents of the message, you must first verify that the message came from PayPal. 'To verify the message, you must send back the contents in the exact order they were received and precede it with the command _notify-validate, as follows: Dim VerifyUrl As String = IIf(Data.ContainsValue("test_ipn=1"), "https://www.sandbox.paypal.com/cgi-bin/webscr", "https://www.paypal.com/cgi-bin/webscr") Dim VerifyData As String = String.Format("cmd=_notify-validate&{0}", Data) Dim Result As Model.Result = Functions.PostContentToUrl(Url:=VerifyUrl, Data:=VerifyData) 'properties Notification.Type = "IPN" Notification.IPAddress = MyBase.IPAddress Notification.UrlRequest = MyBase.UrlRequest Notification.UserAgent = MyBase.UserAgent Notification.Data = Data Notification.Status = Result.Message Notification.Method = MyBase.Method 'save Using DB As New Data.Notifications DB.Save(Notification) End Using 'Important: After you have authenticated an IPN message (received a VERIFIED response from PayPal), you must perform these important checks before you can assume that the IPN is both legitimate and has not already been processed: 'Check that the payment_status is Completed. 'If the payment_status is Completed, check the txn_id against the previous PayPal transaction that you processed to ensure the IPN message is not a duplicate. 'Check that the receiver_email is an email address registered in your PayPal account. 'Check that the price (carried in mc_gross) and the currency (carried in mc_currency) are correct for the item (carried in item_name or item_number). 'Once you have completed these checks, IPN authentication is complete. 'Now, you can update your database with the information provided and initiate any back-end processing that's appropriate. 'return Return String.Empty End Function End Class End Namespace
Because we've used <RoutePrefix("listener")> for the controller and <Route("ipn")> for the InstantPaymentNotification function, our IPN listener service would be exposed as follows:
http://yourdomain.com/listener/ipn
At this point you have an IPN listener that can:
1) Receive IPN notifications from PayPal
2) Respond back instantly to PayPal for the verification handshake
3) Log notification activity to the [Notifications] table in your database
This is the point in the process where you could go several different directions, and ultimately how you validate and process the data is going to be dependent on what types of transaction types (txn_type) you support and what types of services you are selling (e.g. products, subscriptions, etc).
With that said, here is an example of how you might do some data processing in SQL (e.g. a scheduled job that runs every minute).
We'll start with a Split table-valued function to help with splitting the IPN data vertically.
SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[Split] ( @List VARCHAR(MAX), @Delimiter VARCHAR(1) ) RETURNS @Table TABLE ( ID INT IDENTITY(1,1), Value VARCHAR(MAX) ) AS BEGIN -- loop through the list WHILE (CHARINDEX(@Delimiter, @List) > 0) BEGIN -- add the value to the table INSERT INTO @Table (Value) SELECT Value = LTRIM(RTRIM(SUBSTRING(@List, 1, CHARINDEX(@Delimiter, @List)-1))) -- remove the value from the list SET @List = SUBSTRING(@List, CHARINDEX(@Delimiter, @List) + 1, LEN(@List)) END -- insert remaining value from the list INSERT INTO @Table (Value) SELECT Value = LTRIM(RTRIM(@List)) -- return RETURN END GO
Next we'll use a SQL script to process any unprocessed notifications.
/* Important: After you have authenticated an IPN message (received a VERIFIED response from PayPal), you must perform these important checks before you can assume that the IPN is both legitimate and has not already been processed: Check that the payment_status is Completed. If the payment_status is Completed, check the txn_id against the previous PayPal transaction that you processed to ensure the IPN message is not a duplicate. Check that the receiver_email is an email address registered in your PayPal account. Check that the price (carried in mc_gross) and the currency (carried in mc_currency) are correct for the item (carried in item_name or item_number). Once you have completed these checks, IPN authentication is complete. Now, you can update your database with the information provided and initiate any back-end processing that's appropriate. */ -- set nocount on SET NOCOUNT ON; -- if status is not verified mark it as processed and move on UPDATE dbo.Notifications SET Processed = 1, ProcessedDate = GETDATE(), Notes = 'Invalid' WHERE Type = 'IPN' AND Processed = 0 AND Status != 'VERIFIED' -- temp table to get a list of records that are being processed in this batch DECLARE @Batch TABLE ( NotificationID INT NOT NULL DEFAULT 0 ) -- temp table to split out the IPN data vertically DECLARE @Details TABLE ( BatchID VARCHAR(50) NOT NULL DEFAULT '', NotificationID INT NOT NULL DEFAULT 0, Property VARCHAR(50) NOT NULL DEFAULT '', Value VARCHAR(MAX) NOT NULL DEFAULT '' ) -- get unprocessed notifications that are verified and save in temp table INSERT INTO @Batch (NotificationID) SELECT NotificationID FROM dbo.Notifications WHERE Type = 'IPN' AND Processed = 0 AND Status = 'VERIFIED' ORDER BY NotificationID -- declare variables for cursor DECLARE @NotificationID INT = 0 DECLARE @Data VARCHAR(MAX) = '' -- declare cursor DECLARE MyCursor CURSOR LOCAL FAST_FORWARD FOR SELECT n.NotificationID, n.Data FROM dbo.Notifications n JOIN @Batch x ON n.NotificationID = x.NotificationID ORDER BY n.NotificationID -- open cursor OPEN MyCursor -- get the first result FETCH NEXT FROM MyCursor INTO @NotificationID, @Data -- loop through results WHILE @@FETCH_STATUS = 0 BEGIN -- variables DECLARE @BatchID VARCHAR(50) = FORMAT(GETDATE(), 'yyyyMMddHHmmssfffffff') + dbo.GetRandomString(5) -- split IPN data vertically INSERT INTO @Details (BatchID, NotificationID, Property, Value) SELECT BatchID = @BatchID, @NotificationID, Property = (SELECT Value FROM dbo.Split(Value, '=') WHERE ID = 1), Value = (SELECT dbo.DecodeValue(Value) FROM dbo.Split(Value, '=') WHERE ID = 2) FROM dbo.Split(@Data, '&') -- fetch the next record FETCH NEXT FROM MyCursor INTO @NotificationID, @Data END -- close cursor CLOSE MyCursor DEALLOCATE MyCursor -- extract core information that we are interested in for our payment processing -- txn_id, txn_type, item_name, item_number, option_selection1, custom, payment_status UPDATE n SET n.TransactionID = ISNULL(x.txn_id, ''), n.TransactionType = ISNULL(x.txn_type, ''), n.ItemName = ISNULL(x.item_name, ''), n.ItemNumber = ISNULL(x.item_number, ''), n.[Option] = ISNULL(x.option_selection1, ''), n.Email = ISNULL(x.custom, ''), n.PaymentStatus = ISNULL(x.payment_status, '') FROM @Details d PIVOT ( MAX(d.Value) FOR Property IN (txn_id, txn_type, item_name, item_number, option_selection1, custom, payment_status) ) AS x JOIN dbo.Notifications n ON x.NotificationID = n.NotificationID -- process non-payments UPDATE n SET n.Processed = 1, n.ProcessedDate = GETDATE(), n.Notes = 'Non-payment' FROM dbo.Notifications n JOIN @Batch x ON n.NotificationID = x.NotificationID WHERE n.Processed = 0 AND (LEN(n.TransactionID) = 0 OR LEN(n.PaymentStatus) = 0) -- process duplicate transactions UPDATE n SET n.Processed = 1, n.ProcessedDate = GETDATE(), n.Notes = 'Duplicate' FROM dbo.Notifications n JOIN @Batch x ON n.NotificationID = x.NotificationID WHERE n.Processed = 0 AND n.TransactionID IN (SELECT y.TransactionID FROM dbo.Notifications y WHERE y.Processed = 1) -- process non-completed payments UPDATE n SET n.Processed = 1, n.ProcessedDate = GETDATE(), n.Notes = 'Unknown payment status' FROM dbo.Notifications n JOIN @Batch x ON n.NotificationID = x.NotificationID WHERE n.Processed = 0 AND n.PaymentStatus != 'Completed' -- process completed payments UPDATE n SET n.Processed = 1, n.ProcessedDate = GETDATE(), n.Notes = 'Completed' FROM dbo.Notifications n JOIN @Batch x ON n.NotificationID = x.NotificationID WHERE n.Processed = 0 AND n.PaymentStatus = 'Completed'
This is the point where if you have new "completed" payments you could trigger other workflow items in your system, such as email notifications to send a license key, or renewing a subscription, or otherwise giving access to the product/service to the payee.
Remember this is just a starting point and your specific data processing implementation will vary based on your needs.
Matt Pavey is a Microsoft Certified software developer who specializes in ASP.Net, VB.Net, C#, AJAX, LINQ, XML, XSL, Web Services, SQL, jQuery, and more. Follow on Twitter @matthewpavey
0 comments: